89276

Backing Up a Slave Using mysąidump

Using mysąidump to create a copy of a database enables you to capture all of the data in the

database in a format that enables the information to be imported into another instance of MySQL

Server (see Section 4.5.4, “mysąidump — A Database Backup Program”). 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, mysąidump may be impractical.

When using mysąidump, you should stop replication on the slave before starting the dump process to

ensure that the dump contains a consistent set of data:

1.    Stop the slave from processing reąuests. You can stop replication completely on the slave using

mysąladmin:

Shell>nysqladmin stop-slave

Alternatively, you can stop only the slave SQL thread to pause event execution:

shell>nysql -e 'STOP SLAVE SQL_THREAD;'

This enables the slave to continue to receive data change events from the master's binary log

and storę them in the relay logs using the l/O thread, but prevents the slave from executing these

events and changing its data. Within busy replication environments, permitting the l/O thread to run

during backup may speed upthe catch-up process when you restart the slave SQL thread.

2.    Run mysąidump to dump your databases. You may either dump all databases or select databases

to be dumped. For example, to dump all databases:

3hell>mysqlduinp —all-databases > fulldb.dump

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

shell>irysqladnvin start-slave