Home

MySQL Shell 8.0.16: Built-in Reports

Table Of Contents Built-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 thing, and the curses library is both powerful and easy to use, it is not well supported on Microsoft

Read More »

Install MySQL 8 on Linux with lower_case_table_names = 1

Table Of Contents Built-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 preserved but the operating system by default makes it look like it is case insensitive. Which convention that is

Read More »

MySQL Backup Best Practices

Table Of Contents Built-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. Before heading into the gory details, let’s first take a look at the best practices at a high level:

Read More »

Easy Execution of Dynamic Queries with the sys Schema

Table Of Contents Built-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 you can take advantage of the sys schema to simplify the use of dynamic queries. The sys schema includes

Read More »

Testing MySQL NDB Cluster with dbdeployer

Table Of Contents Built-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 installation steps with a single command making the whole process very simple, and it allows for automation of the

Read More »

Using SQLAlchemy with MySQL 8

Table Of Contents Built-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 the Django framework with MySQL 8. There are also other Python frameworks that are worth considering. In this blog,

Read More »

The Format for Timestamps in MySQL Logs

Table Of Contents Built-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 CodeThe MySQL 5.7 ChangeChanging the Time ZoneThat Do T and Z Mean?MySQL changed the timestamp format in the log files in MySQL 5.7. Since then, I have

Read More »

MySQL Connector/Python 8.0.14+: Changed Expression Handling in mysqlx

Table Of Contents Built-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 CodeThe MySQL 5.7 ChangeChanging the Time ZoneThat Do T and Z Mean?Expression HandlingExampleInline ExpressionExplicit ExpressionsFurther ReadingThe X DevAPI allows you to work with JSON documents and SQL

Read More »

Reduce MySQL Core Dump Size by Excluding the InnoDB Buffer Pool

Table Of Contents Built-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 CodeThe MySQL 5.7 ChangeChanging the Time ZoneThat Do T and Z Mean?Expression HandlingExampleInline ExpressionExplicit ExpressionsFurther ReadingEnabling Core DumpsEnabling Core Dumps in MySQLCore Size LimitProblems with Core DumpsExampleWhen

Read More »

MySQL Connector/Python 8.0.15: allow_local_infile Disabled by Default

Table Of Contents Built-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 CodeThe MySQL 5.7 ChangeChanging the Time ZoneThat Do T and Z Mean?Expression HandlingExampleInline ExpressionExplicit ExpressionsFurther ReadingEnabling Core DumpsEnabling Core Dumps in MySQLCore Size LimitProblems with Core DumpsExampleMySQL

Read More »