- 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 as of MySQL 4.1
and removed in MySQL 5.5.
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.3, “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.11, “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.