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 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.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.