MySQL 执行load data infile时同步原理及注意事项

Master上执行

1
mysql> load data INFILE '/tmp/del_proid.txt' INTO table del_proid;

在Binlog中记录的SQL是这样的

1
load data LOCAL INFILE '/tmp/SQL_LOAD_MB-1-0' INTO table del_proid

并且在/tmp目录中也确实出现了这个文件,内容和del_proid.txt一模一样。

同步时,Master的Binlog Dump进程会读取SQL_LOAD_MB-1-0文件的数据传,送给Slave的IO进程写入Relaylog,当Slave同步到该语句时,SQL进 程从Relaylog中解出数据,仍以文件名SQL_LOAD_MB-1-0写入slave-load-tmpdir参数打定的目录下,然后执行 load data LOCAL INFILE '/tmp/SQL_LOAD_MB-1-0' INTO table del_proid , 完成后,删除该文件。

slave-load-tmpdir参数,在默认或未设置的情况下,使用tmpdir参数值文档:

It appears that the file you load with LOAD DATA INFILE on the master are automatically transferred via the replication log from the master to the slave. The slave loads these files when it gets to the LOAD DATA INFILE in the statement-based replication queue.

I’m inferring this from a couple of statements in the docs:

16.1: Backing Up Replication Slaves

If your MySQL server is a slave replication server, then regardless of the backup method you choose, you should also back up the master.info and relay-log.info files when you back up your slave’s data. These files are always needed to resume replication after you restore the slave’s data._ If your slave is subject to replicating LOAD DATA INFILE commands, you should also back up any SQL_LOAD-* files that may exist in the directory specified by the_ –slave-load-tmpdir option.

16.1.2.3: Replication Slave Options and Variables

When the slave SQL thread replicates a LOAD DATA INFILE statement, it extracts the file to be loaded from the relay log into temporary files, and then loads these into the table. If the file loaded on the master is huge, the temporary files on the slave are huge, too. Therefore, it might be advisable to use this option to tell the slave to put temporary files in a directory located in some filesystem that has a lot of available space. In that case, the relay logs are huge as well, so you might also want to use the –relay-log option to place the relay logs in that filesystem.