If your database is large, copying the raw data files can be
more efficient than using mysqldump and
importing the file on each replica. This technique skips the
overhead of updating indexes as the INSERT
statements are replayed.
Using this method with tables in storage engines with complex caching or logging algorithms requires extra steps to produce a perfect “point in time” snapshot: the initial copy command might leave out cache information and logging updates, even if you have acquired a global read lock. How the storage engine responds to this depends on its crash recovery abilities.
This method also does not work reliably if the source and
replica have different values for
ft_stopword_file
,
ft_min_word_len
, or
ft_max_word_len
and you are
copying tables having full-text indexes.
If you use InnoDB
tables, you can
use the mysqlbackup command from the MySQL
Enterprise Backup component to produce a consistent snapshot.
This command records the log name and offset corresponding to
the snapshot to be later used on the replica. MySQL Enterprise
Backup is a commercial product that is included as part of a
MySQL Enterprise subscription. See
MySQL Enterprise Backup Overview for detailed
information.
Otherwise, use the cold
backup technique to obtain a reliable binary snapshot of
InnoDB
tables: copy all data files after
doing a slow shutdown
of the MySQL Server.
To create a raw data snapshot of MyISAM
tables, you can use standard copy tools such as
cp or copy, a remote copy
tool such as scp or rsync,
an archiving tool such as zip or
tar, or a file system snapshot tool such as
dump, providing that your MySQL data files
exist on a single file system. If you are replicating only
certain databases, copy only those files that relate to those
tables. (For InnoDB
, all tables in all
databases are stored in the
system tablespace
files, unless you have the
innodb_file_per_table
option
enabled.)
You might want to specifically exclude the following files from your archive:
Files relating to the
mysql
database.The replica's connection metadata repository file, if used (see Section 5.2, “Relay Log and Replication Metadata Repositories”).
The source's binary log files, with the exception of the binary log index file if you are going to use this to locate the source's binary log coordinates for the replica.
Any relay log files.
To get the most consistent results with a raw data snapshot, shut down the source server during the process, as follows:
Acquire a read lock and get the source's status. See Section 2.1.4, “Obtaining the Replication Source Binary Log Coordinates”.
In a separate session, shut down the source server:
$> mysqladmin shutdown
Make a copy of the MySQL data files. The following examples show common ways to do this. You need to choose only one of them:
$> tar cf /tmp/db.tar ./data $> zip -r /tmp/db.zip ./data $> rsync --recursive ./data /tmp/dbdata
Restart the source server.
If you are not using InnoDB
tables, you can
get a snapshot of the system from a source without shutting down
the server as described in the following steps:
Acquire a read lock and get the source's status. See Section 2.1.4, “Obtaining the Replication Source Binary Log Coordinates”.
Make a copy of the MySQL data files. The following examples show common ways to do this. You need to choose only one of them:
$> tar cf /tmp/db.tar ./data $> zip -r /tmp/db.zip ./data $> rsync --recursive ./data /tmp/dbdata
In the client where you acquired the read lock, release the lock:
mysql> UNLOCK TABLES;
Once you have created the archive or copy of the database, copy the files to each replica before starting the replication process.