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 shut down. If the MySQL
server is still running, background tasks may still be updating
the database files, particularly those involving storage engines
with background processes such as InnoDB
.
With InnoDB
, these problems should be
resolved during crash recovery, but since the slave server can
be shut down during the backup process without affecting the
execution of the master it makes sense to take advantage of this
capability.
To shut down the server and back up the files:
Shut down the slave MySQL server:
shell> mysqladmin shutdown
Copy the data files. You can use any suitable copying or archive utility, including cp, tar or WinZip. For example, assuming that the data directory is located under the current directory, you can archive the entire directory as follows:
shell> tar cf /tmp/dbbackup.tar ./data
Start the MySQL server again. Under Unix:
shell> mysqld_safe &
Under Windows:
C:\> "C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld"
Normally you should back up the entire data directory 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 in addition to the slave's data, you should
also back up the slave status files,
master.info
and
relay-log.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. This requires that the
binary logs still exist on the master server.
If your slave is 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 location of this directory is
the value of the
--slave-load-tmpdir
option. If
the server was not started with that option, the directory
location is the value of the
tmpdir
system variable.
User Comments
Add your own comment.