小编给大家分享一下数据库中load data语句怎么保证主备复制数据一致性，希望大家阅读完这篇文章之后都有所收获，下面让我们一起去探讨吧！

背景

机器配置
* CPU：16 vcpus
* 磁盘：100G flash卡 data&binlog混用
* 内存：64G

数据库版本：MySQL 5.7.18

数据库IP信息：主库IP 10.10.30.241，从库IP 10.10.30.250

数据库参数配置：
* 主库：双一，log_slave_updates，log-bin，secure_file_priv=''，server-id=3306241,binlog_rows_query_log_events=ON,sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
* 从库：双一，log_slave_updates，log-bin，binlog_rows_query_log_events=ON,server-id=3306250,sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

测试目的：通过示例演示并查看过程中binlog中如何记录load data语句等方式来验证 load data语句如何保证主备复制数据一致性
* 将分别在四种隔离级别下（会话级别修改tx_isolation）针对binlog_format（会话级别修改binlog_format）的三种格式分别进行演示执行load data语句，观察MySQL server层如何处理，主库binlog中如何记录load data语句，备库的binlog中如何如何记录load data语句
* 本文假定你已经搭建好了主备复制环境，如果未搭建请先自行搭建主备复制环境

PS：本文仅针对测试目的做验证演示，关于binlog_format三种格式以及事务的四种隔离级别详细说明请查阅相关资料，本文不做解读
* binlog_format参考资料：https://dev.mysql.com/doc/refman/5.7/en/replication-formats.html
* 事务隔离级别参考资料：https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html

建库

admin@localhost:(none)07:34:39>createdatabaseifnotexistsxiaoboluo;QueryOK,1rowaffected(0.00sec)

建表

admin@localhost:(none)10:04:52>usexiaoboluoDatabasechangedadmin@localhost:xiaoboluo10:04:57>createtableifnotexiststest_load(idintunsignednotnullprimarykeyauto_increment,testvarchar(100));QueryOK,0rowsaffected(0.01sec)

插入测试数据

admin@localhost:xiaoboluo10:05:32>insertintotest_load(test)values('1'),('2'),('null'),('4');QueryOK,4rowsaffected(0.01sec)Records:4Duplicates:0Warnings:0admin@localhost:xiaoboluo10:06:01>select*fromtest_load;+----+------+|id|test|+----+------+|2|1||4|2||6|null||8|4|+----+------+4rowsinset(0.00sec)

执行select …into outifile语句生成load data需要的数据文本文件

admin@localhost:xiaoboluo10:12:41>select*fromtest_loadintooutfile"/tmp/test_load.txt";QueryOK,4rowsaffected(0.01sec)admin@localhost:xiaoboluo10:12:42>systemcat/tmp/test_load.txt;21426null842、read-uncommitted隔离级别2.1. binlog_format=statement

先在主库中把binlog_format修改为statement，隔离级别修改为read-uncommitted，并刷新主从库binlog

#主库admin@localhost:xiaoboluo09:37:09>setbinlog_format=statement;QueryOK,0rowsaffected(0.00sec)admin@localhost:xiaoboluo09:39:24>settx_isolation='read-uncommitted';QueryOK,0rowsaffected(0.00sec)admin@localhost:xiaoboluo09:39:44>flushbinarylogs;QueryOK,0rowsaffected(0.01sec)#从库mysql>flushbinarylogs;QueryOK,0rowsaffected(0.01sec)mysql>

主库清空表test_load并执行load data语句

admin@localhost:xiaoboluo09:39:54>truncatetest_load;QueryOK,0rowsaffected(0.00sec)admin@localhost:xiaoboluo09:41:13>loaddatainfile'/tmp/test_load.txt'intotabletest_load;ERROR1665(HY000):Cannotexecutestatement:impossibletowritetobinarylogsinceBINLOG_FORMAT=STATEMENTandatleastonetableusesastorageenginelimitedtorow-basedlogging.InnoDBislimitedtorow-loggingwhentransactionisolationlevelisREADCOMMITTEDorREADUNCOMMITTED.Error(Code1665):Cannotexecutestatement:impossibletowritetobinarylogsinceBINLOG_FORMAT=STATEMENTandatleastonetableusesastorageenginelimitedtorow-basedlogging.InnoDBislimitedtorow-loggingwhentransactionisolationlevelisREADCOMMITTEDorREADUNCOMMITTED.Error(Code1015):Can

从以上结果中可以看到，在RU隔离级别下，binlog_format设置为statement不允许load data语句执行,从报错信息上可以看到，在RC和RU隔离级别下，binlog只能以row格式记录，所以statement无法记录binlog

查看主库表test_load中的数据

admin@localhost:xiaoboluo09:41:52>select*fromtest_load;Emptyset(0.00sec)

从以上结果中可以看到，表数据并没有导入成功，现在解析binlog查看一下

[root@5f1772e3-0c7a-4537-97f9-9b57cf6a04c2binlog]#lltotal196-rw-r-----1mysqlmysql4916May400:11mysql-bin.000014-rw-r-----1mysqlmysql4557May400:12mysql-bin.000015-rw-r-----1mysqlmysql5595May409:39mysql-bin.000016-rw-r-----1mysqlmysql366May409:41mysql-bin.000017-rw-r-----1mysqlmysql884May409:39mysql-bin.index[root@5f1772e3-0c7a-4537-97f9-9b57cf6a04c2binlog]#mysqlbinlog-vvv--base64-output=decode-rowsmysql-bin.000017......use`xiaoboluo`/*!*/;......truncatetest_load/*!*/;SET@@SESSION.GTID_NEXT='AUTOMATIC'/*addedbymysqlbinlog*//*!*/;DELIMITER;#Endoflogfile......

从上面的结果中可以看到，解析binlog的结果中除了use db和truncate table语句之外，没有其他的操作了，说明并没有数据写入，load data语句被mysql server直接拒绝执行了

先在主库中把binlog_format修改为mixed，隔离级别不要动，并刷新主从库binlog

#主库admin@localhost:xiaoboluo09:44:05>setbinlog_format=mixed;QueryOK,0rowsaffected(0.00sec)admin@localhost:xiaoboluo09:49:49>flushbinarylogs;QueryOK,0rowsaffected(0.01sec)#从库mysql>flushbinarylogs;QueryOK,0rowsaffected(0.01sec)mysql>

主库清空表test_load并执行load data语句

admin@localhost:xiaoboluo09:49:57>truncatetest_load;QueryOK,0rowsaffected(0.01sec)admin@localhost:xiaoboluo09:51:12>loaddatainfile'/tmp/test_load.txt'intotabletest_load;QueryOK,4rowsaffected(0.00sec)Records:4Deleted:0Skipped:0Warnings:0

从以上结果中可以看到，在RU隔离级别下，binlog_format设置为mixed允许load data语句执行

查看主库表test_load中的数据

admin@localhost:xiaoboluo09:51:24>select*fromtest_load;+----+------+|id|test|+----+------+|2|1||4|2||6|null||8|4|+----+------+4rowsinset(0.00sec)

从以上结果中可以看到，表数据导入成功，现在解析binlog查看一下

[root@5f1772e3-0c7a-4537-97f9-9b57cf6a04c2binlog]#lltotal196-rw-r-----1mysqlmysql4916May400:11mysql-bin.000014-rw-r-----1mysqlmysql4557May400:12mysql-bin.000015-rw-r-----1mysqlmysql5595May409:39mysql-bin.000016-rw-r-----1mysqlmysql366May409:41mysql-bin.000017-rw-r-----1mysqlmysql744May409:51mysql-bin.000018-rw-r-----1mysqlmysql936May409:49mysql-bin.index[root@5f1772e3-0c7a-4537-97f9-9b57cf6a04c2binlog]#mysqlbinlog-vv--base64-output=decode-rowsmysql-bin.000018#at259#1705049:51:12serverid3306241end_log_pos354CRC320xff83f0ceQuerythread_id=155exec_time=0error_code=0use`xiaoboluo`/*!*/;......truncatetest_load......BEGIN/*!*/;#at501#1705049:51:24serverid3306241end_log_pos583CRC320xfef1767aRows_query#loaddatainfile'/tmp/test_load.txt'intotabletest_load#这里可以看到loaddata语句在这里，这里是开启了参数binlog_rows_query_log_events=ON之后记录的原生sql，默认被加了注释，主备复制不会执行#at583#1705049:51:24serverid3306241end_log_pos643CRC320x349b62a4Table_map:`xiaoboluo`.`test_load`mappedtonumber340#at643#1705049:51:24serverid3306241end_log_pos713CRC320x4ceacc10Write_rows:tableid340flags:STMT_END_F###INSERTINTO`xiaoboluo`.`test_load`#这里是用于主备复制的数据变更日志，可以看到在mixed格式下被转换为了row格式###SET###@1=2/*INTmeta=0nullable=0is_null=0*/###@2='1'/*VARSTRING(300)meta=300nullable=1is_null=0*/###INSERTINTO`xiaoboluo`.`test_load`###SET###@1=4/*INTmeta=0nullable=0is_null=0*/###@2='2'/*VARSTRING(300)meta=300nullable=1is_null=0*/###INSERTINTO`xiaoboluo`.`test_load`###SET###@1=6/*INTmeta=0nullable=0is_null=0*/###@2='null'/*VARSTRING(300)meta=300nullable=1is_null=0*/###INSERTINTO`xiaoboluo`.`test_load`###SET###@1=8/*INTmeta=0nullable=0is_null=0*/###@2='4'/*VARSTRING(300)meta=300nullable=1is_null=0*/#at713#1705049:51:24serverid3306241end_log_pos744CRC320xfa76965bXid=1237COMMIT/*!*/;SET@@SESSION.GTID_NEXT='AUTOMATIC'/*addedbymysqlbinlog*//*!*/;DELIMITER;#Endoflogfile/*!50003SETCOMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530SET@@SESSION.PSEUDO_SLAVE_MODE=0*/

从上面解析binlog的结果中可以看到，数据写入成功，load data语句被转换为row格式记录在binlog中

查看备库中数据是否正确复制

mysql>usexiaoboluoReadingtableinformationforcompletionoftableandcolumnnamesYoucanturnoffthisfeaturetogetaquickerstartupwith-ADatabasechangedmysql>select*fromtest_load;+----+------+|id|test|+----+------+|2|1||4|2||6|null||8|4|+----+------+4rowsinset(0.00sec)

解析备库binlog中是如何记录的load data语句

[root@e710d318-d5b4-4bc7-a606-d09f06ff5f5dbinlog]#lltotal100-rw-r-----1mysqlmysql990May101:36mysql-bin.000001-rw-r-----1mysqlmysql54766May400:02mysql-bin.000002-rw-r-----1mysqlmysql21376May409:40mysql-bin.000003-rw-r-----1mysqlmysql401May409:50mysql-bin.000004-rw-r-----1mysqlmysql730May409:51mysql-bin.000005-rw-r-----1mysqlmysql260May409:50mysql-bin.index[root@e710d318-d5b4-4bc7-a606-d09f06ff5f5dbinlog]#mysqlbinlog-vv--base64-output=decode-rowsmysql-bin.000005...#1705049:51:12serverid3306241end_log_pos354CRC320xff83f0ceQuerythread_id=155exec_time=0error_code=0use`xiaoboluo`/*!*/;...truncatetest_load...BEGIN/*!*/;#at487#1705049:51:24serverid3306241end_log_pos569CRC320x67a31998Rows_query#loaddatainfile'/tmp/test_load.txt'intotabletest_load#这里可以看到loaddata语句在这里，这里是开启了参数binlog_rows_query_log_events=ON之后记录的原生sql，默认被加了注释，主备复制不会执行#at569#1705049:51:24serverid3306241end_log_pos629CRC320x6420d19bTable_map:`xiaoboluo`.`test_load`mappedtonumber301#at629#1705049:51:24serverid3306241end_log_pos699CRC320xf5fff0d8Write_rows:tableid301flags:STMT_END_F###INSERTINTO`xiaoboluo`.`test_load`#这里是备库记录的主库binlog日志，可以看到在mixed格式下被转换为了row格式###SET###@1=2/*INTmeta=0nullable=0is_null=0*/###@2='1'/*VARSTRING(300)meta=300nullable=1is_null=0*/###INSERTINTO`xiaoboluo`.`test_load`###SET###@1=4/*INTmeta=0nullable=0is_null=0*/###@2='2'/*VARSTRING(300)meta=300nullable=1is_null=0*/###INSERTINTO`xiaoboluo`.`test_load`###SET###@1=6/*INTmeta=0nullable=0is_null=0*/###@2='null'/*VARSTRING(300)meta=300nullable=1is_null=0*/###INSERTINTO`xiaoboluo`.`test_load`###SET###@1=8/*INTmeta=0nullable=0is_null=0*/###@2='4'/*VARSTRING(300)meta=300nullable=1is_null=0*/#at699#1705049:51:24serverid3306241end_log_pos730CRC320xd5a3bda4Xid=424COMMIT/*!*/;...2.3. binlog_format=row

先在主库中把binlog_format修改为row，隔离级别不要动，并刷新主从库binlog

#主库admin@localhost:xiaoboluo11:05:20>setbinlog_format=row;QueryOK,0rowsaffected(0.00sec)admin@localhost:xiaoboluo11:18:23>flushbinarylogs;QueryOK,0rowsaffected(0.01sec)#从库mysql>flushbinarylogs;QueryOK,0rowsaffected(0.01sec)mysql>

主库清空表test_load并执行load data语句

admin@localhost:xiaoboluo11:18:26>truncatetest_load;QueryOK,0rowsaffected(0.01sec)admin@localhost:xiaoboluo11:18:51>loaddatainfile'/tmp/test_load.txt'intotabletest_load;QueryOK,4rowsaffected(0.00sec)Records:4Deleted:0Skipped:0Warnings:0

从以上结果中可以看到，在RU隔离级别下，binlog_format设置为row允许load data语句执行

查看主库表test_load中的数据

admin@localhost:xiaoboluo11:18:57>select*fromtest_load;+----+------+|id|test|+----+------+|2|1||4|2||6|null||8|4|+----+------+4rowsinset(0.00sec)

从以上结果中可以看到，表数据导入成功，现在解析binlog查看一下

[root@5f1772e3-0c7a-4537-97f9-9b57cf6a04c2binlog]#lltotal196-rw-r-----1mysqlmysql4916May400:11mysql-bin.000014-rw-r-----1mysqlmysql4557May400:12mysql-bin.000015-rw-r-----1mysqlmysql5595May409:39mysql-bin.000016-rw-r-----1mysqlmysql366May409:41mysql-bin.000017-rw-r-----1mysqlmysql744May409:51mysql-bin.000018-rw-r-----1mysqlmysql744May411:18mysql-bin.000019-rw-r-----1mysqlmysql988May411:18mysql-bin.index[root@5f1772e3-0c7a-4537-97f9-9b57cf6a04c2binlog]#mysqlbinlog-vv--base64-output=decode-rowsmysql-bin.000018#at259#17050411:18:51serverid3306241end_log_pos354CRC320x9d5985adQuerythread_id=155exec_time=0error_code=0use`xiaoboluo`/*!*/;......truncatetest_load......BEGIN/*!*/;#at501#17050411:18:57serverid3306241end_log_pos583CRC320x0e8f7603Rows_query#loaddatainfile'/tmp/test_load.txt'intotabletest_load#这里可以看到loaddata语句在这里，这里是开启了参数binlog_rows_query_log_events=ON之后记录的原生sql，默认被加了注释，主备复制不会执行#at583#17050411:18:57serverid3306241end_log_pos643CRC320xe1c5d4d0Table_map:`xiaoboluo`.`test_load`mappedtonumber341#at643#17050411:18:57serverid3306241end_log_pos713CRC320x31619df3Write_rows:tableid341flags:STMT_END_F###INSERTINTO`xiaoboluo`.`test_load`#这里是用于主备复制的数据变更日志，可以看到在row格式下被转换为了row格式###SET###@1=2/*INTmeta=0nullable=0is_null=0*/###@2='1'/*VARSTRING(300)meta=300nullable=1is_null=0*/###INSERTINTO`xiaoboluo`.`test_load`###SET###@1=4/*INTmeta=0nullable=0is_null=0*/###@2='2'/*VARSTRING(300)meta=300nullable=1is_null=0*/###INSERTINTO`xiaoboluo`.`test_load`###SET###@1=6/*INTmeta=0nullable=0is_null=0*/###@2='null'/*VARSTRING(300)meta=300nullable=1is_null=0*/###INSERTINTO`xiaoboluo`.`test_load`###SET###@1=8/*INTmeta=0nullable=0is_null=0*/###@2='4'/*VARSTRING(300)meta=300nullable=1is_null=0*/#at713#17050411:18:57serverid3306241end_log_pos744CRC320x18b14b52Xid=1245COMMIT/*!*/;......

从上面解析binlog的结果中可以看到，数据写入成功，load data语句被转换为row格式记录在binlog中

查看备库中数据是否正确复制

[root@5f1772e3-0c7a-4537-97f9-9b57cf6a04c2binlog]#lltotal196-rw-r-----1mysqlmysql4916May400:11mysql-bin.000014-rw-r-----1mysqlmysql4557May400:12mysql-bin.000015-rw-r-----1mysqlmysql5595May409:39mysql-bin.000016-rw-r-----1mysqlmysql366May409:41mysql-bin.000017-rw-r-----1mysqlmysql744May409:51mysql-bin.000018-rw-r-----1mysqlmysql744May411:18mysql-bin.000019-rw-r-----1mysqlmysql988May411:18mysql-bin.index[root@5f1772e3-0c7a-4537-97f9-9b57cf6a04c2binlog]#mysqlbinlog-vv--base64-output=decode-rowsmysql-bin.000018#at259#17050411:18:51serverid3306241end_log_pos354CRC320x9d5985adQuerythread_id=155exec_time=0error_code=0use`xiaoboluo`/*!*/;......truncatetest_load......BEGIN/*!*/;#at501#17050411:18:57serverid3306241end_log_pos583CRC320x0e8f7603Rows_query#loaddatainfile'/tmp/test_load.txt'intotabletest_load#这里可以看到loaddata语句在这里，这里是开启了参数binlog_rows_query_log_events=ON之后记录的原生sql，默认被加了注释，主备复制不会执行#at583#17050411:18:57serverid3306241end_log_pos643CRC320xe1c5d4d0Table_map:`xiaoboluo`.`test_load`mappedtonumber341#at643#17050411:18:57serverid3306241end_log_pos713CRC320x31619df3Write_rows:tableid341flags:STMT_END_F###INSERTINTO`xiaoboluo`.`test_load`#这里是用于主备复制的数据变更日志，可以看到在row格式下被转换为了row格式###SET###@1=2/*INTmeta=0nullable=0is_null=0*/###@2='1'/*VARSTRING(300)meta=300nullable=1is_null=0*/###INSERTINTO`xiaoboluo`.`test_load`###SET###@1=4/*INTmeta=0nullable=0is_null=0*/###@2='2'/*VARSTRING(300)meta=300nullable=1is_null=0*/###INSERTINTO`xiaoboluo`.`test_load`###SET###@1=6/*INTmeta=0nullable=0is_null=0*/###@2='null'/*VARSTRING(300)meta=300nullable=1is_null=0*/###INSERTINTO`xiaoboluo`.`test_load`###SET###@1=8/*INTmeta=0nullable=0is_null=0*/###@2='4'/*VARSTRING(300)meta=300nullable=1is_null=0*/#at713#17050411:18:57serverid3306241end_log_pos744CRC320x18b14b52Xid=1245COMMIT/*!*/;......mysql>usexiaoboluoDatabasechangedmysql>select*fromtest_load;+----+------+|id|test|+----+------+|2|1||4|2||6|null||8|4|+----+------+4rowsinset(0.00sec)

解析备库binlog中是如何记录的load data语句

[root@e710d318-d5b4-4bc7-a606-d09f06ff5f5dbinlog]#lltotal100-rw-r-----1mysqlmysql990May101:36mysql-bin.000001-rw-r-----1mysqlmysql54766May400:02mysql-bin.000002-rw-r-----1mysqlmysql21376May409:40mysql-bin.000003-rw-r-----1mysqlmysql401May409:50mysql-bin.000004-rw-r-----1mysqlmysql730May409:51mysql-bin.000005-rw-r-----1mysqlmysql730May411:18mysql-bin.000006-rw-r-----1mysqlmysql312May411:18mysql-bin.index[root@e710d318-d5b4-4bc7-a606-d09f06ff5f5dbinlog]#mysqlbinlog-vv--base64-output=decode-rowsmysql-bin.000009......#at259#17050411:18:51serverid3306241end_log_pos354CRC320x9d5985adQuerythread_id=155exec_time=0error_code=0use`xiaoboluo`/*!*/;......truncatetest_load......#at487#17050411:18:57serverid3306241end_log_pos569CRC320x97dd19e1Rows_query#loaddatainfile'/tmp/test_load.txt'intotabletest_load#这里可以看到loaddata语句在这里，这里是开启了参数binlog_rows_query_log_events=ON之后记录的原生sql，默认被加了注释，主备复制不会执行#at569#17050411:18:57serverid3306241end_log_pos629CRC320x6bef4f90Table_map:`xiaoboluo`.`test_load`mappedtonumber302#at629#17050411:18:57serverid3306241end_log_pos699CRC320x5a86b7f1Write_rows:tableid302flags:STMT_END_F###INSERTINTO`xiaoboluo`.`test_load`#这里是备库记录的主库binlog日志，可以看到在row格式下被转换为了row格式，与mixed格式下记录的内容相同###SET###@1=2/*INTmeta=0nullable=0is_null=0*/###@2='1'/*VARSTRING(300)meta=300nullable=1is_null=0*/###INSERTINTO`xiaoboluo`.`test_load`###SET###@1=4/*INTmeta=0nullable=0is_null=0*/###@2='2'/*VARSTRING(300)meta=300nullable=1is_null=0*/###INSERTINTO`xiaoboluo`.`test_load`###SET###@1=6/*INTmeta=0nullable=0is_null=0*/###@2='null'/*VARSTRING(300)meta=300nullable=1is_null=0*/###INSERTINTO`xiaoboluo`.`test_load`###SET###@1=8/*INTmeta=0nullable=0is_null=0*/###@2='4'/*VARSTRING(300)meta=300nullable=1is_null=0*/#at699#17050411:18:57serverid3306241end_log_pos730CRC320x983a4a33Xid=440COMMIT/*!*/;......

看完了这篇文章，相信你对“数据库中load data语句怎么保证主备复制数据一致性”有了一定的了解，如果想了解更多相关知识，欢迎关注亿速云行业资讯频道，感谢各位的阅读！