The Geek Diary

How to take Logical Backups on a MySQL Replication Slave using shell script

by

The sample script in this post uses the bash shell. It is also assumed that MySQL has been installed and the slave has been initialized. For MySQL 5.7 and later, the script only works for single-source replication. The server to be backed up should be configured as a slave.

If you have one or more slave servers set up to replicate the databases on a master server, you can periodically stop replication on one of the slaves to make a clean, logical backup of the database. Such a script could look something like the listed sample script below.

This is a simplified script and is meant only as an example. You should write something more elaborate that at least adds the following:

  • Error checking and handling
  • mysqladmin, mysql, and mysqldump all need to authenticate against the database. See the respective pages in the MySQL Reference Manual for more information:
    • mysqladmin
    • mysql command line client
    • mysqldump
    • End-user guidelines for password security
Note: It is also strongly recommended not to include the user name and password directly as command line options as that will allow other users on the system to see the user name and password in plain text.

The mysqladmin utility is used to stop and start replication on the slave server. On the first line, the script is capturing the date using the system function date and putting it into a good format (e.g., 20110926-141500). This variable is used with mysqldump in the script for altering the name of the dump file each day. You would set the file path and the name of the dump file to your preferences.

The SHOW SLAVE STATUS will pickup the name of the binlog file and the position on the master.

For using mysqldump with InnoDB exclusively, the –single-transaction option can be used to prevent locking the tables for the whole duration of the dumping process. It is still however necessary to ensure that the MyISAM tables in the mysql system database are not updated while the dump is made. All of this is the same as is the case when performing the dump directly on the master. If the –single-transaction option is used, it is possible to start the slave threads again as soon as mysqldump has started dumping the first table provided that no updates are made to any of the MyISAM (or other non-transactional tables) including updating user information with the GRANT command.

Note: When backup includes the mysql database, it is recommended to –flush-privileges option to mysqldump to ensure all privileges have been loaded from the grant tables before the stored programs and views are restored from the dump.

The Script

Here is the small script:

#!/bin/bash

date=$(date +%Y%m%d-%H%M%S)
mysqladmin stop-slave

mysql --vertical -e "SHOW SLAVE STATUS" | egrep 'Relay_Master_Log_File|Exec_Master_Log_Pos' > /backups/mysql/backup-${date}.txt
mysqldump --opt --flush-privileges --all-databases > /backups/mysql/backup-${date}.sql

mysqladmin start-slave

You May Also Like