Additional languages
« 6.2.1.1 Backing up using mysqldump
6.2.2 Using Replication with different Master and Slave Storage Engines »
Section Navigation      [Toggle]

6.2.1.2. Backing up raw data

To guarantee the integrity of the files that are copied, backing up the raw data files on your MySQL replication slave should take place while your slave server is shutdown. If the MySQL server is still running then background tasks, particularly with storage engines with background processes such as InnoDB, may still be updating the database files. With InnoDB, these problems should be resolved during crash recovery, but since the slave server can be shutdown during the backup process without affecting the execution of the master it makes sense to take advantage of this facility.

To shutdown the server and backup the files:

  1. Shutdown the slave MySQL server: 

    shell> mysqladmin shutdown

  2. Copy the data files. You can use any suitable copying or archive utility, including cp, tar or WinZip: 

    tar cf /tmp/dbbackup.tar ./data

  3. Start up the mysqld process again: 

    shell> mysqld_safe &

    Under Windows: 

    C:\> "C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld"

Normally you should back up the entire data folder for the slave MySQL server. If you want to be able to restore the data and operate as a slave (for example, in the event of failure of the slave), then when you back up the slave's data, you should back up the slave status files, master.info and relay.info, along with the relay log files. These files are needed to resume replication after you restore the slave's data.

If you lose the relay logs but still have the relay-log.info file, you can check it to determine how far the SQL thread has executed in the master binary logs. Then you can use CHANGE MASTER TO with the MASTER_LOG_FILE and MASTER_LOG_POS options to tell the slave to re-read the binary logs from that point. Of course, this requires that the binary logs still exist on the master server.

If your slave is subject to replicating LOAD DATA INFILE statements, you should also back up any SQL_LOAD-* files that exist in the directory that the slave uses for this purpose. The slave needs these files to resume replication of any interrupted LOAD DATA INFILE operations. The directory location is specified using the --slave-load-tmpdir option. If this option is not specified, the directory location is the value of the tmpdir system variable.