This section describes how to create a data snapshot using the raw files which make up the database. Employing this method with a table using a storage engine that has complex caching or logging algorithms requires extra steps to produce a perfect“point in time” snapshot: the initial copy command could 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.
If you useInnoDB tables, you can use themysqlbackup 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 used on the replica. MySQL Enterprise Backup is a commercial product that is included as part of a MySQL Enterprise subscription. SeeMySQL Enterprise Backup Overview for detailed information.
This method also does not work reliably if the source and replica have different values forft_stopword_file,ft_min_word_len, orft_max_word_len and you are copying tables having full-text indexes.
Assuming the above exceptions do not apply to your database, use thecold backup technique to obtain a reliable binary snapshot ofInnoDB tables: do aslow shutdown of the MySQL Server, then copy the data files manually.
To create a raw data snapshot ofMyISAM tables when your MySQL data files exist on a single file system, you can use standard file copy tools such ascp orcopy, a remote copy tool such asscp orrsync, an archiving tool such aszip ortar, or a file system snapshot tool such asdump. If you are replicating only certain databases, copy only those files that relate to those tables. ForInnoDB, all tables in all databases are stored in thesystem tablespace files, unless you have theinnodb_file_per_table option enabled.
The following files are not required for replication:
Files relating to the
mysqldatabase.The replica's connection metadata repository file, if used (seeSection 5.4, “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.
Depending on whether you are usingInnoDB tables or not, choose one of the following:
If you are usingInnoDB tables, and also 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. SeeSection 2.2.3, “Obtaining the Replication Source's Binary Log Coordinates”.
In a separate session, shut down the source server:
$> mysqladmin shutdownMake 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/dbdataRestart the source server.
If you are not usingInnoDB 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. SeeSection 2.2.3, “Obtaining the Replication Source's 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/dbdataIn 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.