Skip navigation links
16.2.1.2 Backing Up Raw Data from a Slave »
Section Navigation      [Toggle]

16.2.1.1. Backing Up a Slave Using mysqldump

Using mysqldump to create a copy of the database enables you to capture all of the data in the database in a format that allows the information to be imported into another instance of MySQL. Because the format of the information is SQL statements the file can easily be distributed and applied to running servers in the event that you need access to the data in an emergency. However, if the size of your data set is very large then mysqldump may be impractical.

When using mysqldump you should stop the slave before starting the dump process to ensure that the dump contains a consistent set of data:

  1. Stop the slave from processing requests. You can either stop the slave completely using mysqladmin: 

    shell> mysqladmin stop-slave

    Alternatively, you can stop processing the relay log files by stopping the replication SQL thread. Using this method will allow the binary log data to be transferred. Within busy replication environments this may speed up the catch-up process when you start the slave processing again: 

    shell> mysql -e 'STOP SLAVE SQL_THREAD;'

  2. Run mysqldump to dump your databases. You may either select databases to be dumped, or dump all databases. For more information, see Section 4.5.4, “mysqldump — A Database Backup Program”. For example, to dump all databases: 

    shell> mysqldump --all-databases >fulldb.dump

  3. Once the dump has completed, start slave operations again: 

    shell> mysqladmin start-slave

In the preceding example you may want to add login credentials (user name, password) to the commands, and bundle the process up into a script that you can run automatically each day.

If you use this approach, make sure you monitor the slave replication process to ensure that the time taken to run the backup in this way is not affecting the slave's ability to keep up with events from the master. See Section 16.1.4.1, “Checking Replication Status”. If the slave is unable to keep up you may want to add another server and distribute the backup process. For an example of how to configure this scenario, see Section 16.2.4, “Replicating Different Databases to Different Slaves”.


User Comments

Posted by Michael Maguire on September 20 2008 3:33pm[Delete] [Edit]

This is a half-question/half-tip (I welcome being corrected on this).

Wouldn't adding the --lock-all-tables command obviate the need for stop slave/start slave?

Add your own comment.