- 12.5.2 SQL Statements for Controlling Slave Servers
- 12.5.2.1 CHANGE MASTER TO Syntax
- 12.5.2.2 LOAD DATA FROM MASTER Syntax
- 12.5.2.3 LOAD TABLE tbl_name FROM MASTER Syntax
- 12.5.2.4 MASTER_POS_WAIT() Syntax
- 12.5.2.5 RESET SLAVE Syntax
- 12.5.2.6 SET GLOBAL sql_slave_skip_counter Syntax
- 12.5.2.7 START SLAVE Syntax
- 12.5.2.8 STOP SLAVE Syntax
LOAD DATA FROM MASTER
This feature is deprecated and should be avoided. It is subject to removal in a future version of MySQL.
Since the current implementation of LOAD DATA FROM
MASTER and LOAD TABLE FROM MASTER
is very limited, these statements are deprecated in versions 4.1
of MySQL and above. We will introduce a more advanced technique
(called “online backup”) in a future version. That
technique will have the additional advantage of working with
more storage engines.
For MySQL 5.4 and earlier, the recommended alternative solution
to using LOAD DATA FROM MASTER or
LOAD TABLE FROM MASTER is using
mysqldump or mysqlhotcopy.
The latter requires Perl and two Perl modules
(DBI and DBD:mysql) and
works for MyISAM and
ARCHIVE tables only. With
mysqldump, you can create SQL dumps on the
master and pipe (or copy) these to a mysql
client on the slave. This has the advantage of working for all
storage engines, but can be quite slow, since it works using
SELECT.
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 nonunique 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
MyISAMtables. Attempting to load a non-MyISAMtable 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.1.4, “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 12.4.5.15, “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.
User Comments
A behavior that me off the first time I set up replication using LOAD DATA FROM MASTER is that LOAD DATA FROM MASTER always says "0 rows affected" even if the database(s) have been successfully retrieved/updated.mm
LOAD DATA FROM MASTER, used as indicated above, does not work on 3.23.x. There is no Super_priv. FYI.
Add your own comment.