Home

MySQL Backup Best Practices

Table Of Contents Make 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: Document and script the restore procedures. Do you know the steps required to restore a full backup – or a single table? Keep copies of the backups off-site. Do

Read More »

Easy Execution of Dynamic Queries with the sys Schema

Table Of Contents Make 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 as views to make the database administrator's life easier. One of these is the execute_prepared_stmt() procedure which can be used to execute a query given as a string. An

Read More »

Testing MySQL NDB Cluster with dbdeployer

Table Of Contents Make 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 Data Charmer, @datacharmer on Twitter) has for many years maintained sandbox tools for MySQL, first with MySQL Sandbox and now with dbdeployer. One of the most recent features of

Read More »

Using SQLAlchemy with MySQL 8

Table Of Contents Make 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 MySQL 8. In order for you to be able to use MySQL 8 with SQLAlchemy, you need three pieces of software: MySQL Server, MySQL Connector/Python, and SQLAlchemy. I will

Read More »

The Format for Timestamps in MySQL Logs

Table Of Contents Make 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 new format, and how to change the time zone that is used. Latest in a comment to my blog about log_slow_extra in 8.0.14, where the question was what T

Read More »

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

Table Of Contents Make 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 CRUD style API is more intuitive than SQL statements for some programmers. Either way, the X DevAPI allows you to mix JSON documents, SQL tables, CRUD methods, and SQL

Read More »

Reduce MySQL Core Dump Size by Excluding the InnoDB Buffer Pool

Table Of Contents Make 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 crashes, one of the most powerful things to investigate the cause of the crash is a core dump. As the amount of memory allocated to processes such as MySQL

Read More »

MySQL Connector/Python 8.0.15: allow_local_infile Disabled by Default

Table Of Contents Make 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 insert data into a table from a file with the data delimited by commas, tabs, or another delimiter. This is particularly useful when you need to bulk import data,

Read More »

More Statistics for Slow Queries: log_slow_extra

Table Of Contents Make 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 method of recording slow query, so the database administrator can determine which queries are in the most need for optimization. Since MySQL 5.6, it has to some extend been

Read More »

Automatic Decryption of MySQL Binary Logs Using Python

Table Of Contents Make 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 DumpsExampleIntroduction and BackgroundIntroducing binlog_decrypt.pyInstalling PrerequisitesUsing binlog_decrypt.pyThe Full Source CodeOne of the new features in MySQL 8.0.14 is support for encrypting the binary logs. While encryption makes the data more secure (provided the key is secret of course),

Read More »