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 fulltext indexes.
If you are using InnoDB
tables, you should
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/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 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's status. See Section 16.1.1.4, “Obtaining the Master Replication Information”.
In a separate session, shut down the MySQL server:
shell> mysqladmin shutdown
Make a copy of the MySQL data files. Examples are shown below for common ways to do this - you need to choose only one of them:
shell>tar cf
shell>/tmp/db.tar
./data
zip -r
shell>/tmp/db.zip
./data
rsync --recursive
./data
/tmp/dbdata
Start up the MySQL instance on the master.
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:
Acquire a read lock and get the master's status. See Section 16.1.1.4, “Obtaining the Master Replication Information”.
Make 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
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.
User Comments
Add your own comment.