MySQL Backup Strategies – What you should know before considering MySQL DR solutions ? 

MySQL powers all the major internet properties, Which include Google, Facebook, Twitter, LinkedIn, Uber etc. So how do we plan for MySQL disaster recovery and what are the most common MySQL DR tools used today for building highly reliable database infrastructure operations ? There can be several reasons for a MySQL database outage: hardware failure, power outage, human error, natural disaster etc. We may not be able prevent all the disaster from happening but investing on a robust disaster recovery plan is very important for building fault-tolerant database infrastructure operations on MySQL.  Every MySQL DBA is accountable for developing a disaster recovery plan addressing data sensitivity, data loss tolerance and data security. Functionally you have several database backup strategies available with MySQL:

• Full backup – Full backup backs up the whole database, This also include transaction log so that the full database can be recovered after a full database backup is restored. Full database backups represent the database at the time the backup finished. Full backups are storage resource intensive and takes more time to finish. If you have for a large database, we strongly recommend to supplement a full database backup with a series of differential database backups.
• Differential backup – A differential backup is based on the most recent, previous full data backup. A differential backup captures only the data that has changed since that full backup. The full backup upon which a differential backup is based is known as the base of the differential. Full backups, except for copy-only backups, can serve as the base for a series of differential backups, including database backups, partial backups, and file backups. The base backup for a file differential backup can be contained within a full backup, a file backup, or a partial backup. The differential backups are most recommended when the subset of a database is modified more frequently than the rest of the database.
• Incremental backup – A incremental backup contains all changes to the data since the last backup. Both differential and incremental backup does only backing up changed files. But they differ significantly in how they do it, and how useful the result is.while an incremental backup only includes the data that has changed since the previous backup, a differential backup contains all of the data that has changed since the last full backup. The advantage that differential backup offers over incremental backups is a shorter restore time. Because, the backup has to be reconstituted from the last full backup and all the incremental backups since.

MySQL Backup tools

The following are the list of MySQL backup tools (logical and physical) discussed in this blog:

mysqldump

mysqldump is a MySQL client utility which can be used to perform logical backups  , The mysqldump generate output in SQL ( default and most commonly used to reproduce MySQL schema objects and data), CSV, other delimited text or XML format. We have copied below the restrictions of mysqldump:

• mysqldump does not dump performance_schema or sys schema be default. To enforce dumping or logical backup of any of these schema objects, You have to explicitly mention them –databases option or if you want to just dump performance_schema use –skip-lock-tables option.
• mysqldump does not dump the INFORMATION_SCHEMA schema.
• mysqldump does not dump the InnoDB CREATE TABLESPACE statements.
• mysqldump does not dump the NDB Cluster ndbinfo information database.
• mysqldump includes statements required to recreate the general_log and slow_query_log tables for dumps of the mysql database. But, Log table contents are not dumped

Script to dump all the databases: 

shell> mysqldump --all-databases > all_databases.sql

Script to dump the entire database:

shell> mysqldump db_name > db_name_dump.sql

Script to dump several databases with one command:

shell> mysqldump --databases db_name1 [db_name2 ...] > databases_dump.sql

mysqlpump 

The mysqlpump is another client utility for logical backup of MySQL database like mysqldump which is capable of parallel processing of databases and other schema objects with databases to perform high performance dumping process, We listed below mysqlpump most compelling features:

• MySQL dump with parallel processing capabilities for databases and other objects within databases.
• MySQL user accounts will be dumped as account-management statements (CREATE USER, GRANT) rather than as inserts into the mysql system database.
• By using mysqlpump you can create a compressed output.
• Much faster compared to mysqldump. Because, The InnoDB secondary indexes are created after rows are inserted to the table.

P.S. – We have blogged about “How to use mysqlpump for faster MySQL logical backup ? ” here

MySQL Enterprise Backup 

• Transparent page compression for InnoDB.
• Backup history available for all members of Group Replication by making sure backup_history table is updated on primary node after each mysqlbackup operation.
• Storage engine of the mysql.backup_history table on a backed-up server has switched from CSV to InnoDB.
• mysqlbackup now supports encrypted InnoDB undo logs .
• mysqlbackup now supports high performance incremental backup by setting page tracking functionality on MySQL (set –incremental=page-track).
• Much better MySQL Enterprise Backup 8.0 troubleshooting with now mysqlbackup prints a stack trace after being terminated by a signal.
• Selective restores of tables or schema from full backup for Table-Level Recovery (TLR)