Home

Performance Schema Functions

The sys schema was created to make it easier to use the Performance Schema. This included several functions, for example to convert the picoseconds used by the Performance Schema into human readable strings. In MySQL 8.0.16, three of these functions have been implemented as native functions in MySQL Server. Why do away with the sys schema functions? There are two reasons: performance and ease of use. The native functions are written in C++ like the rest of the server whereas the sys schema functions were written as stored functions. Function written in C++ are inherently faster than stored functions. Additionally, that the functions are native means you no longer need to prefix them with sys. to tell MySQL where to

Read More »

Connector/Python Connection Attributes

Table Of Contents Connection Attributes in MySQL ServerConnector/Python AttributesEnabled with Default AttributesCustom AttributesMySQL Server has since version 5.6 supported connection attributes for the clients. This has allowed a client to provide information such as which program or connector the client is, the client version, the license, etc. The database administrator can use this information for example to verify whether all clients have been upgraded, which client is executing a particular query, and so forth. In MySQL 8.0.16 this feature has been included for the X DevAPI in the MySQL connectors as well, including MySQL Connector/Python which I will cover in this blog. First though, let's take a look at how the attributes are exposed in MySQL Server. Connection Attributes in

Read More »

MySQL Server 8.0.16: Thanks for the Contributions

Table Of Contents Connection Attributes in MySQL ServerConnector/Python AttributesEnabled with Default AttributesCustom AttributesMySQL 8.0.16 was released last week and includes many great features including support for CHECK constraints and upgrades without the need of mysql_upgrade. As usual there are also several contributions from the community. These are the ones, I would like to highlight in this blog to say “thank you for the contributions”. Facebook has contributed with another two patches and so has Daniel Black. Additionally, there are patches from Yuhui Wang, Wei Zhao, Yan Huang, and Dirkjan Bussink. The contributions are: mysql-test-run.pl now supports the MTR_UNIQUE_IDS_DIR environment variable, which may be set to specify a unique-IDs directory to be used as the common location for all chroot environments

Read More »

MySQL Shell 8.0.16: User Defined Reports

Table Of Contents Connection Attributes in MySQL ServerConnector/Python AttributesEnabled with Default AttributesCustom AttributesBackgroundExample Custom ReportThe Report FunctionRegistering the ReportTesting the ReportThe shell.reports ObjectIn my blog yesterday, I wrote about the new reporting framework in MySQL Shell. It is part of the 8.0.16 release. I also noted that it includes the possibility to create your own custom reports and use those with the \show and \watch commands. This blog will explore how you can create a report and register it, so it automatically is available when you start MySQL Shell. Background You can write the code that generates the report in either JavaScript or Python. The reports can be used from either language mode – even SQL – irrespective of which

Read More »

MySQL Shell 8.0.16: Built-in Reports

Table Of Contents Connection Attributes in MySQL ServerConnector/Python AttributesEnabled with Default AttributesCustom AttributesBackgroundExample Custom ReportThe Report FunctionRegistering the ReportTesting the ReportThe shell.reports ObjectBuilt-In FeaturesThe \show CommandThe Query ReportThe \watch CommandConclusionReaders of my blog know that I like how MySQL Shell allows you to customize it and use it's Python and JavaScript support to create custom libraries with tools that help with your daily tasks and even creating auto-refreshing reports. Lefred has even taken this a step further and started to port Innotop to MySQL Shell. One disadvantage of my example of auto-refreshing reports and the Innotop port is they both rely on the curses Python module to refresh the screen. While avoiding to reinvent the wheel is usually a good

Read More »

Install MySQL 8 on Linux with lower_case_table_names = 1

Table Of Contents Connection Attributes in MySQL ServerConnector/Python AttributesEnabled with Default AttributesCustom AttributesBackgroundExample Custom ReportThe Report FunctionRegistering the ReportTesting the ReportThe shell.reports ObjectBuilt-In FeaturesThe \show CommandThe Query ReportThe \watch CommandConclusionMySQL 8 and lower_case_table_namesInstalling MySQL 8 with Case Insensitive Identifier Names1. Install the MySQL Repository2. Remove Previous Installations3. Clean the Data Directory4. Install MySQL 85. Initialize with lower_case_table_names = 1MySQL stores several files on disk. Even in MySQL 8 where the data dictionary is stored in InnoDB tables, there are still all the tablespace files. Different file system behave differently, and one particular challenge is case sensitivity. On Microsoft Windows, the case does not matter, on Linux the case is important, and on macOS the case of the file names is

Read More »

MySQL Backup Best Practices

Table Of Contents Connection Attributes in MySQL ServerConnector/Python AttributesEnabled with Default AttributesCustom AttributesBackgroundExample Custom ReportThe Report FunctionRegistering the ReportTesting the ReportThe shell.reports ObjectBuilt-In FeaturesThe \show CommandThe Query ReportThe \watch CommandConclusionMySQL 8 and lower_case_table_namesInstalling MySQL 8 with Case Insensitive Identifier Names1. Install the MySQL Repository2. Remove Previous Installations3. Clean the Data Directory4. Install MySQL 85. Initialize with lower_case_table_names = 1Make Sure You Can Restore Your BackupsThe Restore ProcedureCopy the Backups Off SiteVerify Your BackupsMonitor the BackupsCreating BackupsBackups Are a Never Ending ProcessToday is World Backup Day, so I thought I would use the opportunity to discuss some best practices and general considerations regarding backing up MySQL instances. While I focus on MySQL, several of these tips apply to backups in general.

Read More »

Easy Execution of Dynamic Queries with the sys Schema

Table Of Contents Connection Attributes in MySQL ServerConnector/Python AttributesEnabled with Default AttributesCustom AttributesBackgroundExample Custom ReportThe Report FunctionRegistering the ReportTesting the ReportThe shell.reports ObjectBuilt-In FeaturesThe \show CommandThe Query ReportThe \watch CommandConclusionMySQL 8 and lower_case_table_namesInstalling MySQL 8 with Case Insensitive Identifier Names1. Install the MySQL Repository2. Remove Previous Installations3. Clean the Data Directory4. Install MySQL 85. Initialize with lower_case_table_names = 1Make Sure You Can Restore Your BackupsThe Restore ProcedureCopy the Backups Off SiteVerify Your BackupsMonitor the BackupsCreating BackupsBackups Are a Never Ending ProcessDynamic Queries and Prepared Statementssys.execute_prepared_stmt()When you write stored procedures in MySQL, you sometimes need to generate queries on the fly, for example as you process the result of another query. This is supported using prepared statements. This blog explores how

Read More »

Testing MySQL NDB Cluster with dbdeployer

Table Of Contents Connection Attributes in MySQL ServerConnector/Python AttributesEnabled with Default AttributesCustom AttributesBackgroundExample Custom ReportThe Report FunctionRegistering the ReportTesting the ReportThe shell.reports ObjectBuilt-In FeaturesThe \show CommandThe Query ReportThe \watch CommandConclusionMySQL 8 and lower_case_table_namesInstalling MySQL 8 with Case Insensitive Identifier Names1. Install the MySQL Repository2. Remove Previous Installations3. Clean the Data Directory4. Install MySQL 85. Initialize with lower_case_table_names = 1Make Sure You Can Restore Your BackupsThe Restore ProcedureCopy the Backups Off SiteVerify Your BackupsMonitor the BackupsCreating BackupsBackups Are a Never Ending ProcessDynamic Queries and Prepared Statementssys.execute_prepared_stmt()What is MySQL NDB Cluster?Installing dbdeployerInstalling a Test ClusterTesting the ClusterVerdictA great way to install MySQL when you need to do quick tests is to use a sandbox tool. This allows you to perform all the

Read More »

Using SQLAlchemy with MySQL 8

Table Of Contents Connection Attributes in MySQL ServerConnector/Python AttributesEnabled with Default AttributesCustom AttributesBackgroundExample Custom ReportThe Report FunctionRegistering the ReportTesting the ReportThe shell.reports ObjectBuilt-In FeaturesThe \show CommandThe Query ReportThe \watch CommandConclusionMySQL 8 and lower_case_table_namesInstalling MySQL 8 with Case Insensitive Identifier Names1. Install the MySQL Repository2. Remove Previous Installations3. Clean the Data Directory4. Install MySQL 85. Initialize with lower_case_table_names = 1Make Sure You Can Restore Your BackupsThe Restore ProcedureCopy the Backups Off SiteVerify Your BackupsMonitor the BackupsCreating BackupsBackups Are a Never Ending ProcessDynamic Queries and Prepared Statementssys.execute_prepared_stmt()What is MySQL NDB Cluster?Installing dbdeployerInstalling a Test ClusterTesting the ClusterVerdictInstalling MySQL Server RPM Install on Enterprise Linux Microsoft WindowsPreparing MySQL ServerInstalling MySQL Connector/Python and SQLAlchemySQLAlchemy ExampleComplete Example CodeI few months ago, I wrote about using

Read More »