MySQL AB :: MySQL 5.1 Reference Manual :: 6.1.1.6 Creating a Data Snapshot Using Raw Data Files

6.1.1.6. Creating a Data Snapshot Using Raw Data Files

If your database is particularly large then 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 cache and/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 will depend on the crash recovery abilities.

For example, when you have acquired a global read lock, you can start a filesystem snapshot of your InnoDB tables. Internally (inside the InnoDB storage engine) the snapshot won't be consistent (because the InnoDB caches are not flushed), but this is not a cause for concern, because InnoDB resolves this at startup and delivers a consistent result. This means that InnoDB can perform crash recovery when started on this snapshot, without corruption. However, there is no way to stop the MySQL server while insuring a consistent snapshot of your InnoDB tables.

To create your raw data snapshot 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 filesystem. If you are only replicating certain databases then make sure you only copy those files that related to those tables. For InnoDB, all tables in all databases are stored in a single file 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 server during the process, as below:

Acquire a read lock and get the master status. See Section 6.1.1.4, “Obtaining the Master Replication Information”.
In a separate session, shutdown the MySQL server:
```
shell> mysqladmin shutdown
```
Take a copy of the MySQL data files. Examples are shown below for common solutions - you need to choose only one of these solutions:
```
shell> tar cf /tmp/db.tar ./data
shell> zip -r /tmp/db.zip ./data
shell> rsync --recursive ./data /tmp/dbdata
```
Startup the MySQL instance on the master.

To get a snapshot of the system from a master without shutting down the database:

Acquire a read lock and get the master status. See Section 6.1.1.4, “Obtaining the Master Replication Information”.
Take a copy of the MySQL data files. Examples are shown below for common solutions - you need to choose only one of these solutions:
```
shell> tar cf /tmp/db.tar ./data
shell> zip -r /tmp/db.zip ./data
shell> rsync --recursive ./data /tmp/dbdata
```
If you are using InnoDB tables, ideally you should use the InnoDB Hot Backup tool, which takes a consistent snapshot without acquiring any locks on the master server, and records the log name and offset corresponding to the snapshot to be later used on the slave. Hot Backup is an additional non-free (commercial) tool that is not included in the standard MySQL distribution. See the InnoDB Hot Backup home page at http://www.innodb.com/manual.php for detailed information.
In the client where you acquired read lock, free 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.

6.1.1.6. Creating a Data Snapshot Using Raw Data Files

User Comments