13.6.2.2. LOAD DATA FROM MASTER Syntax
LOAD DATA FROM MASTER
This statement takes a snapshot of the master and copies it to
the slave. It updates the values of
MASTER_LOG_FILE and
MASTER_LOG_POS so that the slave starts
replicating from the correct position. Any table and database
exclusion rules specified with the
--replicate-*-do-* and
--replicate-*-ignore-* options are honored.
--replicate-rewrite-db is
not taken into account because a user
could use this option to set up a non-unique mapping such as
--replicate-rewrite-db="db1->db3" and
--replicate-rewrite-db="db2->db3", which
would confuse the slave when loading tables from the master.
Use of this statement is subject to the following conditions:
-
It works only for MyISAM tables.
Attempting to load a non-MyISAM table
results in the following error:
ERROR 1189 (08S01): Net error reading from master
It acquires a global read lock on the master while taking
the snapshot, which prevents updates on the master during
the load operation.
If you are loading large tables, you might have to increase
the values of net_read_timeout and
net_write_timeout on both the master and
slave servers. See Section 5.2.2, “Server System Variables”.
Note that LOAD DATA FROM MASTER does
not copy any tables from the
mysql database. This makes it easy to have
different users and privileges on the master and the slave.
To use LOAD DATA FROM MASTER, the
replication account that is used to connect to the master must
have the RELOAD and
SUPER privileges on the master and the
SELECT privilege for all master tables you
want to load. All master tables for which the user does not
have the SELECT privilege are ignored by
LOAD DATA FROM MASTER. This is because the
master hides them from the user: LOAD DATA FROM
MASTER calls SHOW DATABASES to
know the master databases to load, but SHOW
DATABASES returns only databases for which the user
has some privilege. See Section 13.5.4.9, “SHOW DATABASES Syntax”. On
the slave side, the user that issues LOAD DATA FROM
MASTER must have privileges for dropping and
creating the databases and tables that are copied.
