Skip navigation links
MySQL 5.5 Reference Manual :: 16 Replication :: 16.1 Replication Configuration :: 16.1.1 How to Set Up Replication :: 16.1.1.6 Creating a Data Snapshot Using Raw Data Files
16.1.1.7 Setting Up Replication with New Master and Slaves »
Section Navigation      [Toggle]

16.1.1.6. Creating a Data Snapshot Using Raw Data Files

If your database is particularly large, copying the raw data files may be more efficient than using mysqldump and importing the file on each slave.

However, using this method with tables in storage engines with complex caching or logging algorithms may not give you a perfect “in time” snapshot as cache information and logging updates may not have been applied, even if you have acquired a global read lock. How the storage engine responds to this depends on its crash recovery abilities.

In addition, this method does not work reliably if the master and slave 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 are using InnoDB tables, you can use the InnoDB Hot Backup tool to obtain a consistent snapshot. This tool records the log name and offset corresponding to the snapshot to be later used on the slave. Hot Backup is a nonfree (commercial) tool that is not included in the standard MySQL distribution. See the InnoDB Hot Backup home page at http://www.innodb.com/wp/products/hot-backup/ for detailed information.

Otherwise, you can obtain a reliable binary snapshot of InnoDB tables only after shutting down 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 then make sure you copy only those files that related to those tables. (For InnoDB, all tables in all databases are stored in the shared tablespace files, unless you have the innodb_file_per_table option enabled.)

You may want to specifically exclude the following files from your archive:

  • Files relating to the mysql database.

  • The master.info file.

  • The master's binary log files.

  • Any relay log files.

To get the most consistent results with a raw data snapshot you should shut down the master server during the process, as follows:

  1. Acquire a read lock and get the master's status. See Section 16.1.1.4, “Obtaining the Replication Master Binary Log Coordinates”.

  2. In a separate session, shut down the master server: 

    shell> mysqladmin shutdown

  3. 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: 

    shell> tar cf /tmp/db.tar ./data
shell> zip -r /tmp/db.zip ./data
shell> rsync --recursive ./data /tmp/dbdata

  4. Restart the master server.

If you are not using InnoDB tables, you can get a snapshot of the system from a master without shutting down the server as described in the following steps:

  1. Acquire a read lock and get the master's status. See Section 16.1.1.4, “Obtaining the Replication Master Binary Log Coordinates”.

  2. 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: 

    shell> tar cf /tmp/db.tar ./data
shell> zip -r /tmp/db.zip ./data
shell> rsync --recursive ./data /tmp/dbdata

  3. 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, you will need to copy the files to each slave before starting the slave replication process.