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
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
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
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
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
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
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
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,
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
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),