Creating and restoring database backups with mysqldump and MySQL Enterprise Backup – Part 2 of 2
July 25, 2013 3 Comments
In part one of this post, I gave you a couple examples of how to backup your MySQL databases using mysqldump. In part two, I will show you how to use the MySQL Enterprise Backup (MEB) to create a full and partial backup.
MySQL Enterprise Backup provides enterprise-grade backup and recovery for MySQL. It delivers hot, online, non-blocking backups on multiple platforms including Linux, Windows, Mac & Solaris. To learn more, you may download a whitepaper on MEB.
MySQL Enterprise Backup delivers:
- NEW! Continuous monitoring – Monitor the progress and disk space usage
- “Hot” Online Backups – Backups take place entirely online, without interrupting MySQL transactions
- High Performance – Save time with faster backup and recovery
- Incremental Backup – Backup only data that has changed since the last backup
- Partial Backup – Target particular tables or tablespaces
- Compression – Cut costs by reducing storage requirements up to 90%
- Backup to Tape – Stream backup to tape or other media management solutions
- Fast Recovery – Get servers back online and create replicated servers
- Point-in-Time Recovery (PITR) – Recover to a specific transaction
- Partial restore – Recover targeted tables or tablespaces
- Restore to a separate location – Rapidly create clones for fast replication setup
- Reduce Failures – Use a proven high quality solution from the developers of MySQL
- Multi-platform – Backup and Restore on Linux, Windows, Mac & Solaris(from http://www.mysql.com/products/enterprise/backup.html
While mysqldump is free to use, MEB is part of MySQL’s Enterprise Edition (EE) – so you need a license to use it. But if you are using MySQL in a production environment, you might want to look at EE, as:
MySQL Enterprise Edition includes the most comprehensive set of advanced features, management tools and technical support to achieve the highest levels of MySQL scalability, security, reliability, and uptime. It reduces the risk, cost, and complexity in developing, deploying, and managing business-critical MySQL applications.
(from: http://www.mysql.com/products/enterprise/)Before using MEB and backing up your database for the first time, you will need some information:
Information to gather – Where to Find It – How It Is Used
- Path to MySQL configuration file – Default system locations, hardcoded application default locations, or from –defaults-file option in mysqld startup script. – This is the preferred way to convey database configuration information to the mysqlbackup command, using the –defaults-file option. When connection and data layout information is available from the configuration file, you can skip most of the other choices listed below.
- MySQL port – MySQL configuration file or mysqld startup script. Used to connect to the database instance during backup operations. Specified via the –port option of mysqlbackup. –port is not needed if available from MySQL configuration file. Not needed when doing an offline (cold) backup, which works directly on the files using OS-level file permissions.
- Path to MySQL data directory – MySQL configuration file or mysqld startup script. – Used to retrieve files from the database instance during backup operations, and to copy files back to the database instance during restore operations. Automatically retrieved from database connection for hot and warm backups. Taken from MySQL configuration file for cold backups.
- ID and password of privileged MySQL user – You record this during installation of your own databases, or get it from the DBA when backing up databases you do not own. Not needed when doing an offline (cold) backup, which works directly on the files using OS-level file permissions. For cold backups, you log in as an administrative user. – Specified via the –password option of the mysqlbackup. Prompted from the terminal if the –password option is present without the password argument.
- Path under which to store backup data – You choose this. See Section 3.1.3, of the MySQL online manual – “Designate a Location for Backup Data” for details. – By default, this directory must be empty for mysqlbackup to write data into it, to avoid overwriting old backups or mixing up data from different backups. Use the –with-timestamp option to automatically create a subdirectory with a unique name, when storing multiple sets of backup data under the same main directory.
- Owner and permission information for backed-up files (for Linux, Unix, and OS X systems) – In the MySQL data directory. – If you do the backup using a different OS user ID or a different umask setting than applies to the original files, you might need to run commands such as chown and chmod on the backup data. See Section A.1, of the MySQL online manual – “Limitations of mysqlbackup Command” for details.
- Size of InnoDB redo log files – Calculated from the values of the innodb_log_file_size and innodb_log_files_in_group configuration variables. Use the technique explained for the –incremental-with-redo-log-only option. – Only needed if you perform incremental backups using the –incremental-with-redo-log-only option rather than the –incremental option. The size of the InnoDB redo log and the rate of generation for redo data dictate how often you must perform incremental backups.
- Rate at which redo data is generated – Calculated from the values of the InnoDB logical sequence number at different points in time. Use the technique explained for the –incremental-with-redo-log-only option. – Only needed if you perform incremental backups using the –incremental-with-redo-log-only option rather than the –incremental option. The size of the InnoDB redo log and the rate of generation for redo data dictate how often you must perform incremental backups.For most backup operations, the mysqlbackup command connects to the MySQL server through –user and –password options. If you aren’t going to use the root user, then you will need to create a separate user. Follow these instructions for setting the proper permissions.
All backup-related operations either create new files or reference existing files underneath a specified directory that holds backup data. Choose this directory in advance, on a file system with sufficient storage. (It could even be remotely mounted from a different server.) You specify the path to this directory with the –backup-dir option for many invocations of the mysqlbackup command.
Once you establish a regular backup schedule with automated jobs, it is preferable to keep each backup within a timestamped subdirectory underneath the main backup directory. To make the mysqlbackup command create these subdirectories automatically, specify the –with-timestamp option each time you run mysqlbackup.
For one-time backup operations, for example when cloning a database to set up a replication slave, you might specify a new directory each time, or specify the –force option of mysqlbackup to overwrite older backup files.
If you haven’t downloaded and installed mysqlbackup, you may download it from edelivery.oracle.com (registration is required). Install the MySQL Enterprise Backup product on each database server whose contents you intend to back up. You perform all backup and restore operations locally, by running the mysqlbackup command on the same server as the MySQL instance.
Now that we have gathered all of the required information and installed mysqlbackup, let’s run a simple and easy backup of the entire database. I installed MEB in my /usr/local directory, so I am including the full path of mysqlbackup. I am using the backup-and-apply-log option, which combines the –backup and the –apply-log options into one. The –backup option performs the initial phase of a backup. The second phase is performed later by running mysqlbackup again with the –apply-log option, which brings the InnoDB tables in the backup up-to-date, including any changes made to the data while the backup was running.
$ /usr/local/meb/bin/mysqlbackup --user=root --password --backup-dir=/Users/tonydarnell/hotbackups backup-and-apply-log MySQL Enterprise Backup version 3.12.0 [2014/11/12] Copyright (c) 2003, 2014, Oracle and/or its affiliates. All Rights Reserved. mysqlbackup: INFO: Starting with following command line ... /usr/local/meb/bin/mysqlbackup --user=root --password --backup-dir=/Users/tonydarnell/hotbackups backup-and-apply-log Enter password: mysqlbackup: INFO: MySQL server version is '5.6.9-rc-log'. mysqlbackup: INFO: Got some server configuration information from running server. IMPORTANT: Please check that mysqlbackup run completes successfully. At the end of a successful 'backup-and-apply-log' run mysqlbackup prints "mysqlbackup completed OK!". -------------------------------------------------------------------- Server Repository Options: -------------------------------------------------------------------- datadir = /usr/local/mysql/data/ innodb_data_home_dir = /usr/local/mysql/data innodb_data_file_path = ibdata1:40M:autoextend innodb_log_group_home_dir = /usr/local/mysql/data innodb_log_files_in_group = 2 innodb_log_file_size = 5242880 innodb_page_size = 16384 innodb_checksum_algorithm = innodb innodb_undo_directory = /usr/local/mysql/data/ innodb_undo_tablespaces = 0 innodb_undo_logs = 128 -------------------------------------------------------------------- Backup Config Options: -------------------------------------------------------------------- datadir = /Users/tonydarnell/hotbackups/datadir innodb_data_home_dir = /Users/tonydarnell/hotbackups/datadir innodb_data_file_path = ibdata1:40M:autoextend innodb_log_group_home_dir = /Users/tonydarnell/hotbackups/datadir innodb_log_files_in_group = 2 innodb_log_file_size = 5242880 innodb_page_size = 16384 innodb_checksum_algorithm = innodb innodb_undo_directory = /Users/tonydarnell/hotbackups/datadir innodb_undo_tablespaces = 0 innodb_undo_logs = 128 mysqlbackup: INFO: Unique generated backup id for this is 13742482113579320 mysqlbackup: INFO: Creating 14 buffers each of size 16777216. 130719 11:36:53 mysqlbackup: INFO: Full Backup operation starts with following threads 1 read-threads 6 process-threads 1 write-threads 130719 11:36:53 mysqlbackup: INFO: System tablespace file format is Antelope. 130719 11:36:53 mysqlbackup: INFO: Starting to copy all innodb files... 130719 11:36:53 mysqlbackup: INFO: Copying /usr/local/mysql/data/ibdata1 (Antelope file format). 130719 11:36:53 mysqlbackup: INFO: Found checkpoint at lsn 135380756. 130719 11:36:53 mysqlbackup: INFO: Starting log scan from lsn 135380480. 130719 11:36:53 mysqlbackup: INFO: Copying log... 130719 11:36:54 mysqlbackup: INFO: Log copied, lsn 135380756. (I have truncated some of the database and table output to save space) ..... 130719 11:36:56 mysqlbackup: INFO: Copying /usr/local/mysql/data/mysql/innodb_index_stats.ibd (Antelope file format). 130719 11:36:56 mysqlbackup: INFO: Copying /usr/local/mysql/data/mysql/innodb_table_stats.ibd (Antelope file format). 130719 11:36:56 mysqlbackup: INFO: Copying /usr/local/mysql/data/mysql/slave_master_info.ibd (Antelope file format). 130719 11:36:56 mysqlbackup: INFO: Copying /usr/local/mysql/data/mysql/slave_relay_log_info.ibd (Antelope file format). 130719 11:36:56 mysqlbackup: INFO: Copying /usr/local/mysql/data/mysql/slave_worker_info.ibd (Antelope file format). ..... 130719 11:36:56 mysqlbackup: INFO: Copying /usr/local/mysql/data/testcert/t1.ibd (Antelope file format). 130719 11:36:56 mysqlbackup: INFO: Copying /usr/local/mysql/data/testcert/t3.ibd (Antelope file format). ..... 130719 11:36:57 mysqlbackup: INFO: Copying /usr/local/mysql/data/watchdb/watches.ibd (Antelope file format). ..... 130719 11:36:57 mysqlbackup: INFO: Completing the copy of innodb files. 130719 11:36:58 mysqlbackup: INFO: Preparing to lock tables: Connected to mysqld server. 130719 11:36:58 mysqlbackup: INFO: Starting to lock all the tables... 130719 11:36:58 mysqlbackup: INFO: All tables are locked and flushed to disk 130719 11:36:58 mysqlbackup: INFO: Opening backup source directory '/usr/local/mysql/data/' 130719 11:36:58 mysqlbackup: INFO: Starting to backup all non-innodb files in subdirectories of '/usr/local/mysql/data/' ..... 130719 11:36:58 mysqlbackup: INFO: Copying the database directory 'comicbookdb' ..... 130719 11:36:59 mysqlbackup: INFO: Copying the database directory 'mysql' 130719 11:36:59 mysqlbackup: INFO: Copying the database directory 'performance_schema' ..... 130719 11:36:59 mysqlbackup: INFO: Copying the database directory 'test' ..... 130719 11:36:59 mysqlbackup: INFO: Copying the database directory 'watchdb' 130719 11:36:59 mysqlbackup: INFO: Completing the copy of all non-innodb files. 130719 11:37:00 mysqlbackup: INFO: A copied database page was modified at 135380756. (This is the highest lsn found on page) Scanned log up to lsn 135384397. Was able to parse the log up to lsn 135384397. Maximum page number for a log record 375 130719 11:37:00 mysqlbackup: INFO: All tables unlocked 130719 11:37:00 mysqlbackup: INFO: All MySQL tables were locked for 1.589 seconds. 130719 11:37:00 mysqlbackup: INFO: Full Backup operation completed successfully. 130719 11:37:00 mysqlbackup: INFO: Backup created in directory '/Users/tonydarnell/hotbackups' 130719 11:37:00 mysqlbackup: INFO: MySQL binlog position: filename mysql-bin.000013, position 85573 ------------------------------------------------------------- Parameters Summary ------------------------------------------------------------- Start LSN : 135380480 End LSN : 135384397 ------------------------------------------------------------- mysqlbackup: INFO: Creating 14 buffers each of size 65536. 130719 11:37:00 mysqlbackup: INFO: Apply-log operation starts with following threads 1 read-threads 1 process-threads 130719 11:37:00 mysqlbackup: INFO: ibbackup_logfile's creation parameters: start lsn 135380480, end lsn 135384397, start checkpoint 135380756. mysqlbackup: INFO: InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percent: 0 1 .... 99 Setting log file size to 5242880 Setting log file size to 5242880 130719 11:37:00 mysqlbackup: INFO: We were able to parse ibbackup_logfile up to lsn 135384397. mysqlbackup: INFO: Last MySQL binlog file position 0 85573, file name mysql-bin.000013 130719 11:37:00 mysqlbackup: INFO: The first data file is '/Users/tonydarnell/hotbackups/datadir/ibdata1' and the new created log files are at '/Users/tonydarnell/hotbackups/datadir' 130719 11:37:01 mysqlbackup: INFO: Apply-log operation completed successfully. 130719 11:37:01 mysqlbackup: INFO: Full backup prepared for recovery successfully. mysqlbackup completed OK!Now, I can take a look at the backup file that was created:
root@macserver01: $ pwd /Users/tonydarnell/hotbackups root@macserver01: $ ls -l total 8 -rw-r--r-- 1 root staff 351 Jul 19 11:36 backup-my.cnf drwx------ 21 root staff 714 Jul 19 11:37 datadir drwx------ 6 root staff 204 Jul 19 11:37 meta $ ls -l datadir total 102416 drwx------ 5 root staff 170 Jul 19 11:36 comicbookdb -rw-r----- 1 root staff 5242880 Jul 19 11:37 ib_logfile0 -rw-r----- 1 root staff 5242880 Jul 19 11:37 ib_logfile1 -rw-r--r-- 1 root staff 4608 Jul 19 11:37 ibbackup_logfile -rw-r--r-- 1 root staff 41943040 Jul 19 11:37 ibdata1 drwx------ 88 root staff 2992 Jul 19 11:36 mysql drwx------ 55 root staff 1870 Jul 19 11:36 performance_schema drwx------ 3 root staff 102 Jul 19 11:36 test drwx------ 30 root staff 1020 Jul 19 11:36 testcert drwx------ 19 root staff 646 Jul 19 11:36 watchdb root@macserver01: $ ls -l meta total 216 -rw-r--r-- 1 root staff 90786 Jul 19 11:37 backup_content.xml -rw-r--r-- 1 root staff 5746 Jul 19 11:36 backup_create.xml -rw-r--r-- 1 root staff 265 Jul 19 11:37 backup_gtid_executed.sql -rw-r--r-- 1 root staff 321 Jul 19 11:37 backup_variables.txt
As you can see, the backup was created in /Users/tonydarnell/hotbackups. If I wanted to have a unique folder for this backup, I can use the –with-timestamp.
The –with-timestamp option places the backup in a subdirectory created under the directory you specified above. The name of the backup subdirectory is formed from the date and the clock time of the backup run.
I will run the same backup command again, but with the –with-timestamp option:
(I am not going to duplicate the entire output – but I will only show you the output where it creates the sub-directory under /Users/tonydarnell/hotbackups)
$ /usr/local/meb/bin/mysqlbackup --user=root --password --backup-dir=/Users/tonydarnell/hotbackups backup-and-apply-log --with-timestamp ...... 130719 11:49:54 mysqlbackup: INFO: The first data file is '/Users/tonydarnell/hotbackups/2015-05-19_11-49-48/datadir/ibdata1' and the new created log files are at '/Users/tonydarnell/hotbackups/2015-05-19_11-49-48/datadir' 130719 11:49:54 mysqlbackup: INFO: Apply-log operation completed successfully. 130719 11:49:54 mysqlbackup: INFO: Full backup prepared for recovery successfully. mysqlbackup completed OK!So, I ran the backup again to get a unique directory. Instead of the backup files/directories being placed in /Users/tonydarnell/hotbackups, it created a sub-directory with a timestamp for the directory name:
$ pwd /Users/tonydarnell/hotbackups root@macserver01: $ ls -l total 0 drwx------ 5 root staff 170 Jul 19 11:49 2015-05-19_11-49-48 $ ls -l 2015-05-19_11-49-48 total 8 -rw-r--r-- 1 root staff 371 Jul 19 11:49 backup-my.cnf drwx------ 21 root staff 714 Jul 19 11:49 datadir drwx------ 6 root staff 204 Jul 19 11:49 meta
Note: If you don’t use the backup-and-apply-log option you will need to read this: Immediately after the backup job completes, the backup files might not be in a consistent state, because data could be inserted, updated, or deleted while the backup is running. These initial backup files are known as the raw backup.
You must update the backup files so that they reflect the state of the database corresponding to a specific InnoDB log sequence number. (The same kind of operation as crash recovery.) When this step is complete, these final files are known as the prepared backup.
During the backup, mysqlbackup copies the accumulated InnoDB log to a file called ibbackup_logfile. This log file is used to “roll forward” the backed-up data files, so that every page in the data files corresponds to the same log sequence number of the InnoDB log. This phase also creates new ib_logfiles that correspond to the data files.
The mysqlbackup option for turning a raw backup into a prepared backup is apply-log. You can run this step on the same database server where you did the backup, or transfer the raw backup files to a different system first, to limit the CPU and storage overhead on the database server.
Note: Since the apply-log operation does not modify any of the original files in the backup, nothing is lost if the operation fails for some reason (for example, insufficient disk space). After fixing the problem, you can safely retry apply-log and by specifying the –force option, which allows the data and log files created by the failed apply-log operation to be overwritten.
For simple backups (without compression or incremental backup), you can combine the initial backup and the apply-log step using the option backup-and-apply-log.
MEB 3.9 and later creates two .cnf files based on the output of SHOW GLOBAL VARIABLES: server-my.cnf (non-default values) and server-all.cnf (all values).
Now that we have a completed backup, we are going to copy the backup files and the my.cnf file over to a different server to restore the databases. We will be using a server that was setup as a slave server to the server where the backup occurred. If you need to restore the backup to the same server, you will need to refer to the mysqlbackup manual. I copied the backup files as well as the my.cnf file to the new server:
# pwd /Users/tonydarnell/hotbackups # ls -l total 16 drwxrwxrwx 5 tonydarnell staff 170 Jul 19 15:38 2015-05-19_11-49-48
On the new server (where I will restore the data), I shutdown the mysqld process (mysqladmin -uroot -p shutdown), copied the my.cnf file to the proper directory, and now I can restore the database to the new server, using the copy-back option. The copy-back option requires the database server to be already shut down, then copies the data files, logs, and other backed-up files from the backup directory back to their original locations, and performs any required postprocessing on them.
# /usr/local/meb/bin/mysqlbackup --defaults-file=/etc/my.cnf --backup-dir=/Users/tonydarnell/hotbackups/2015-05-19_11-49-48 copy-back MySQL Enterprise Backup version 3.12.0 [2014/11/12] Copyright (c) 2003, 2014, Oracle and/or its affiliates. All Rights Reserved. mysqlbackup: INFO: Starting with following command line ... /usr/local/meb/bin/mysqlbackup --defaults-file=/etc/my.cnf --backup-dir=/Users/tonydarnell/hotbackups/2015-05-19_11-49-48 copy-back IMPORTANT: Please check that mysqlbackup run completes successfully. At the end of a successful 'copy-back' run mysqlbackup prints "mysqlbackup completed OK!". -------------------------------------------------------------------- Server Repository Options: -------------------------------------------------------------------- datadir = /usr/local/mysql/data innodb_data_home_dir = /usr/local/mysql/data innodb_data_file_path = ibdata1:40M:autoextend innodb_log_group_home_dir = /usr/local/mysql/data innodb_log_files_in_group = 2 innodb_log_file_size = 5M innodb_page_size = Null innodb_checksum_algorithm = innodb -------------------------------------------------------------------- Backup Config Options: -------------------------------------------------------------------- datadir = /Users/tonydarnell/hotbackups/2015-05-19_11-49-48/datadir innodb_data_home_dir = /Users/tonydarnell/hotbackups/2015-05-19_11-49-48/datadir innodb_data_file_path = ibdata1:40M:autoextend innodb_log_group_home_dir = /Users/tonydarnell/hotbackups/2015-05-19_11-49-48/datadir innodb_log_files_in_group = 2 innodb_log_file_size = 5242880 innodb_page_size = 16384 innodb_checksum_algorithm = innodb innodb_undo_directory = /Users/tonydarnell/hotbackups/2015-05-19_11-49-48/datadir innodb_undo_tablespaces = 0 innodb_undo_logs = 128 mysqlbackup: INFO: Creating 14 buffers each of size 16777216. 130719 15:54:41 mysqlbackup: INFO: Copy-back operation starts with following threads 1 read-threads 1 write-threads 130719 15:54:41 mysqlbackup: INFO: Copying /Users/tonydarnell/hotbackups/2015-05-19_11-49-48/datadir/ibdata1. ..... 130719 15:54:42 mysqlbackup: INFO: Copying /Users/tonydarnell/hotbackups/2015-05-19_11-49-48/datadir/comicbookdb/comics.ibd. ..... 130719 15:54:42 mysqlbackup: INFO: Copying /Users/tonydarnell/hotbackups/2015-05-19_11-49-48/datadir/mysql/innodb_index_stats.ibd. 130719 15:54:42 mysqlbackup: INFO: Copying /Users/tonydarnell/hotbackups/2015-05-19_11-49-48/datadir/mysql/innodb_table_stats.ibd. 130719 15:54:42 mysqlbackup: INFO: Copying /Users/tonydarnell/hotbackups/2015-05-19_11-49-48/datadir/mysql/slave_master_info.ibd. 130719 15:54:42 mysqlbackup: INFO: Copying /Users/tonydarnell/hotbackups/2015-05-19_11-49-48/datadir/mysql/slave_relay_log_info.ibd. 130719 15:54:42 mysqlbackup: INFO: Copying /Users/tonydarnell/hotbackups/2015-05-19_11-49-48/datadir/mysql/slave_worker_info.ibd. ..... 130719 15:54:43 mysqlbackup: INFO: Copying /Users/tonydarnell/hotbackups/2015-05-19_11-49-48/datadir/watchdb/watches.ibd. ..... 130719 15:54:43 mysqlbackup: INFO: Copying the database directory 'comicbookdb' ..... 130719 15:54:43 mysqlbackup: INFO: Copying the database directory 'mysql' 130719 15:54:43 mysqlbackup: INFO: Copying the database directory 'performance_schema' ..... 130719 15:54:43 mysqlbackup: INFO: Copying the database directory 'test' ..... 130719 15:54:43 mysqlbackup: INFO: Copying the database directory 'watchdb' 130719 15:54:43 mysqlbackup: INFO: Completing the copy of all non-innodb files. 130719 15:54:43 mysqlbackup: INFO: Copying the log file 'ib_logfile0' 130719 15:54:43 mysqlbackup: INFO: Copying the log file 'ib_logfile1' 130719 15:54:44 mysqlbackup: INFO: Copy-back operation completed successfully. 130719 15:54:44 mysqlbackup: INFO: Finished copying backup files to '/usr/local/mysql/data' mysqlbackup completed OK!I can now restart MySQL. I have a very small database (less than 50 megabytes). But it took less than a minute to restore the database. If I had to rebuild my database using mysqldump, it would take a lot longer. If you have a very large database, the different in using mysqlbackup and mysqldump could be in hours. For example, a 32-gig database with 33 tables takes about eight minutes to restore with mysqlbackup. Restoring the same database with a mysqldump file takes over two hours.
An easy way to check to see if the databases match (assuming that I haven’t added any new records in any of the original databases – which I haven’t), I can use one of the MySQL Utilities – mysqldbcompare. I wrote about how to do this in an earlier blog about using it to test two replicated databases, but it will work here as well – see Using MySQL Utilities Workbench Script mysqldbcompare To Compare Two Databases In Replication.
The mysqldbcompare utility “compares the objects and data from two databases to find differences. It identifies objects having different definitions in the two databases and presents them in a diff-style format of choice. Differences in the data are shown using a similar diff-style format. Changed or missing rows are shown in a standard format of GRID, CSV, TAB, or VERTICAL.” (from: mysqldbcompare — Compare Two Databases and Identify Differences)
Some of the syntax may have changed for mysqldbcompare since I wrote that blog, so you will need to reference the help notes for mysqldbcompare. You would need to run this for each of your databases.
$ mysqldbcompare --server1=scripts:scripts999@192.168.1.2 --server2=scripts:scripts999@192.168.1.123 --run-all-tests --difftype=context comicbookdb:comicbookdb # server1 on 192.168.1.2: ... connected. # server2 on 192.168.1.123: ... connected. # Checking databases comicbookdb on server1 and comicbookdb on server2 Defn Row Data Type Object Name Diff Count Check --------------------------------------------------------------------------- TABLE comics pass pass pass Databases are consistent. # ...doneYou can try and run this for the mysql database, but you may get a few errors regarding the mysql.backup_history and mysql.backup_progress tables:
$ mysqldbcompare --server1=scripts:scripts999@192.168.1.2 --server2=scripts:scripts999@192.168.1.123 --run-all-tests --difftype=context mysql:mysql # server1 on 192.168.1.2: ... connected. # server2 on 192.168.1.123: ... connected. # Checking databases mysql on server1 and mysql on server2 Defn Row Data Type Object Name Diff Count Check --------------------------------------------------------------------------- TABLE backup_history pass FAIL SKIP Row counts are not the same among mysql.backup_history and mysql.backup_history. No primary key found. TABLE backup_progress pass FAIL SKIP Row counts are not the same among mysql.backup_progress and mysql.backup_progress. No primary key found. TABLE columns_priv pass pass pass TABLE db pass pass pass TABLE event pass pass pass TABLE func pass pass pass TABLE general_log pass pass SKIP No primary key found. TABLE help_category pass pass pass TABLE help_keyword pass pass pass TABLE help_relation pass pass pass TABLE help_topic pass pass pass TABLE innodb_index_stats pass pass pass TABLE innodb_table_stats pass pass pass TABLE inventory pass pass pass TABLE ndb_binlog_index pass pass pass TABLE plugin pass pass pass TABLE proc pass pass pass TABLE procs_priv pass pass pass TABLE proxies_priv pass pass pass TABLE servers pass pass pass TABLE slave_master_info pass pass pass TABLE slave_relay_log_info pass pass pass TABLE slave_worker_info pass pass pass TABLE slow_log pass pass SKIP No primary key found. TABLE tables_priv pass pass pass TABLE time_zone pass pass pass TABLE time_zone_leap_second pass pass pass TABLE time_zone_name pass pass pass TABLE time_zone_transition pass pass pass TABLE time_zone_transition_type pass pass pass TABLE user pass pass pass Database consistency check failed. # ...doneFor example, when you compare the mysql.backup_history tables, the original database will have two entries – as I ran mysqlbackup twice. But the second backup entry doesn’t get entered until after the backup has occurred, and it isn’t reflected in the backup files.
Original Server
mysql> select count(*) from mysql.backup_history; +----------+ | count(*) | +----------+ | 2 | +----------+ 1 row in set (0.00 sec)
Restored Server
mysql> select count(*) from mysql.backup_history; +----------+ | count(*) | +----------+ | 1 | +----------+ 1 row in set (0.00 sec)
For the mysql.backup_progress tables, the original database has ten rows, while the restored database has seven.
There are many options for using mysqlbackup, including (but not limited to) incremental backup, partial backup , compression, backup to tape, point-in-time recovery (PITR), partial restore, etc. If you are running MySQL in a production environment, then you should look at MySQL Enterprise Edition, which includes MySQL Enterprise Backup. Of course, you should always have a backup and recovery plan in place. Finally, if and when possible, practice restoring your backup on a regular basis, to make sure that if your server crashes, you can restore your database quickly.
Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn. 
Tony is the author of Twenty Forty-Four: The League of Patriots Visit http://2044thebook.com for more information.
info [at] Scripting
Scripting MySQL 