Home

Install MySQL 8 on Linux with lower_case_table_names = 1

Table Of Contents MySQL 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 the correct depends on your personal preference

Read More »

MySQL Backup Best Practices

Table Of Contents MySQL 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: Make sure you can restore your backups:

Read More »

Easy Execution of Dynamic Queries with the sys Schema

Table Of Contents MySQL 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 several stored procedures and functions as well

Read More »

Testing MySQL NDB Cluster with dbdeployer

Table Of Contents MySQL 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 test. Giuseppe Maxia (also known as the

Read More »

Using SQLAlchemy with MySQL 8

Table Of Contents MySQL 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, I will look at using SQLAlchemy with

Read More »

The Format for Timestamps in MySQL Logs

Table Of Contents MySQL 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 a few times seen questions about the

Read More »

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

Table Of Contents MySQL 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 tables at the same time. Furthermore, the

Read More »

Reduce MySQL Core Dump Size by Excluding the InnoDB Buffer Pool

Table Of Contents MySQL 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 things go horrible wrong and a process

Read More »

MySQL Connector/Python 8.0.15: allow_local_infile Disabled by Default

Table Of Contents MySQL 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 Server has a feature where you can

Read More »

More Statistics for Slow Queries: log_slow_extra

Table Of Contents MySQL 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 DumpsExampleThe slow query log is the trusted old

Read More »