Description of LOAD DATA from master

Illustration 1:

The LOAD data from Master is a command initiated from the server after the master-slave server is set up to replicate data from the primary server to the MyISAM table only, which requires the remote user to have reload and super privileges on master. The experiment found that the command changed only Master.info and Mysql-relay.info, changed to master's newest state (that is, Binlog_file and Binlog_pos changed to the latest), and other database files and tables did not change. (Reason: The SELECT permission is not granted for the loaded table, and the table and database can be copied only after the SELECT permission is granted).

Generally do not use this command, while synchronizing data directly copies the data files in Var, you must stop the update of the database (Read_Only 1) before copying, and for the MyISAM table, you should use the Flush Tables command to close all open tables (tables being read or written) before copying them. , otherwise the MyISAM table will have an error that needs to be checked and repaired with MYISAMCHK (this step can be replaced with select COUNT (*) from table because the number of rows in the MyISAM table is stored in a fixed area. If the table does not make a mistake, this command returns a value quickly, which verifies that the MyISAM table is corrupted.

Illustration 2:
The LOAD DATA from master is used to snapshot the primary server and copy it to the secondary server. It can update the values of Master_log_file and master_log_pos so that the secondary server can replicate from the correct location. Table and database exclusion rules specified using the--replicate-*-do-* and--replicate-*-ignore-* options are honored. --replicate-rewrite-db is not considered. This is because this option allows users to set up a--replicate-rewrite-db=db1->db3 mapping, such as the one for the--REPLICATE-REWRITE-DB=DB2->DB3 and the other. This mapping causes the secondary server to be confused when loading a table from the primary server.
The use of this statement is governed by the following conditions:

Only works on the MyISAM table. If you attempt to load a non-MyISAM table, the following error is caused:

Error 1189 (08S01): Net Error reading from master

When a snapshot is taken, a global read lock is obtained for the primary server. During a load operation, this lock blocks updates to the primary server.


If you are loading a large table, you may have to add net_read_timeout and net_write_timeout values to both the primary server and the secondary server. See section 5.3.3, "Server System variables."

Note that the LOAD DATA from master does not copy any tables from the MySQL database. This makes it easier to have different users and permissions for the primary server and the subordinate server.

The LOAD DATA from master statement requires the replication account that is used to connect to the primary server so that the primary server has reload and Super permissions, and has SELECT permissions for all the master server tables that you want to load. All users that do not have a SELECT permission for the primary server table are ignored by load DATA from master. This is because the primary server hides them from the user: Load DATA from master invokes show databases to understand the primary server database to be loaded, but show databases only returns a database where the user has partial permissions. See section 13.5.4.6, "Show databases Syntax". On the secondary server side, users who publish the load DATA from master should have authorization to cancel or create replicated databases and tables.