To guarantee the integrity of the files that are copied, backing
up the raw data files on your MySQL replica should take place
while your replica 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 replica server can
be shut down during the backup process without affecting the
execution of the source it makes sense to take advantage of this
capability.
To shut down the server and back up the files:
Shut down the replica MySQL server:
$> 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:
$> tar cf /tmp/dbbackup.tar ./data
Start the MySQL server again. Under Unix:
$> mysqld_safe &
Under Windows:
C:\> "C:\Program Files\MySQL\MySQL Server 8.4\bin\mysqld"
Normally you should back up the entire data directory for the replica MySQL server. If you want to be able to restore the data and operate as a replica (for example, in the event of failure of the replica), in addition to the data, you need to have the replica's connection metadata repository and applier metadata repository, and the relay log files. These items are needed to resume replication after you restore the replica's data. Assuming tables have been used for the replica's connection metadata repository and applier metadata repository (see Section 19.2.4, “Relay Log and Replication Metadata Repositories”), which is the default in MySQL 8.4, these tables are backed up along with the data directory. If files have been used for the repositories, which is deprecated, you must back these up separately. The relay log files must be backed up separately if they have been placed in a different location to the data directory.
If you lose the relay logs but still have the
relay-log.info
file, you can check it to
determine how far the replication SQL thread has executed in the
source's binary logs. Then you can use
CHANGE REPLICATION SOURCE TO
with
the SOURCE_LOG_FILE
and
SOURCE_LOG_POS
options to tell the replica to
re-read the binary logs from that point. This requires that the
binary logs still exist on the source server.
If your replica is replicating LOAD
DATA
statements, you should also back up any
SQL_LOAD-*
files that exist in the
directory that the replica uses for this purpose. The replica
needs these files to resume replication of any interrupted
LOAD DATA
operations. The
location of this directory is the value of the system variable
replica_load_tmpdir
. If the
server was not started with that variable set, the directory
location is the value of the
tmpdir
system variable.