数据库中load data语句怎么保证主备复制数据一致性

小编给大家分享一下数据库中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

1、制造测试数据

  • 建库

admin@localhost : (none) 07:34:39> create database if not exists xiaoboluo;  
					
						Query OK, 1 row affected (0.00 sec) 					
  • 建表

admin@localhost : (none) 10:04:52> use xiaoboluo  
					
						Database changed					
						admin@localhost : xiaoboluo 10:04:57> create table if not exists test_load(id int unsigned not null primary key auto_increment,test varchar(100)); 
					
						Query OK, 0 rows affected (0.01 sec) 					
  • 插入测试数据

admin@localhost : xiaoboluo 10:05:32> insert into test_load(test) values('1'),('2'),('null'),('4');  
					
						Query OK, 4 rows affected (0.01 sec) 
					
						Records: 4 Duplicates: 0 Warnings: 0					
						 					
						 					
						admin@localhost : xiaoboluo 10:06:01> select * from test_load; 
					+----+------+ 
					| id | test | 
					+----+------+ 
					| 2 | 1 | 
					| 4 | 2 | 
					| 6 | null | 
					| 8 | 4 | 
					+----+------+ 
					
						4 rows in set (0.00 sec) 					
  • 执行select …into outifile语句生成load data需要的数据文本文件

admin@localhost : xiaoboluo 10:12:41> select * from test_load into outfile "/tmp/test_load.txt";  
					
						Query OK, 4 rows affected (0.01 sec) 
					
						 					
						 					
						admin@localhost : xiaoboluo 10:12:42> system cat /tmp/test_load.txt; 
					
						2 1					
						4 2					
						6 null					
						8 4					

2、read-uncommitted隔离级别

2.1. binlog_format=statement
  • 先在主库中把binlog_format修改为statement,隔离级别修改为read-uncommitted,并刷新主从库binlog

# 主库  
					
						admin@localhost : xiaoboluo 09:37:09> set binlog_format=statement; 
					
						Query OK, 0 rows affected (0.00 sec) 
					
						 					
						 					
						admin@localhost : xiaoboluo 09:39:24> set tx_isolation='read-uncommitted'; 
					
						Query OK, 0 rows affected (0.00 sec) 
					
						 					
						 					
						admin@localhost : xiaoboluo 09:39:44> flush binary logs; 
					
						Query OK, 0 rows affected (0.01 sec) 
					
						 					
						 					
						# 从库					
						mysql> flush binary logs; 
					
						Query OK, 0 rows affected (0.01 sec) 
					
						 					
						 					
						mysql> 					
  • 主库清空表test_load并执行load data语句

admin@localhost : xiaoboluo 09:39:54> truncate test_load;  
					
						Query OK, 0 rows affected (0.00 sec) 
					
						admin@localhost : xiaoboluo 09:41:13> load data infile '/tmp/test_load.txt' into table test_load; 
					
						ERROR 1665 (HY000): Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED. 
					
						Error (Code 1665): Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED. 
					
						Error (Code 1015): Can					
  • 从以上结果中可以看到,在RU隔离级别下,binlog_format设置为statement不允许load data语句执行,从报错信息上可以看到,在RC和RU隔离级别下,binlog只能以row格式记录,所以statement无法记录binlog

  • 查看主库表test_load中的数据

admin@localhost : xiaoboluo 09:41:52> select * from test_load;  
					
						Empty set (0.00 sec) 					
  • 从以上结果中可以看到,表数据并没有导入成功,现在解析binlog查看一下

[root@5f1772e3-0c7a-4537-97f9-9b57cf6a04c2 binlog]# ll  
					
						total 196					-rw-r----- 1 mysql mysql 4916 May 4 00:11 mysql-bin.000014					
						 					-rw-r----- 1 mysql mysql 4557 May 4 00:12 mysql-bin.000015					-rw-r----- 1 mysql mysql 5595 May 4 09:39 mysql-bin.000016					-rw-r----- 1 mysql mysql 366 May 4 09:41 mysql-bin.000017					-rw-r----- 1 mysql mysql 884 May 4 09:39 mysql-bin.index					[root@5f1772e3-0c7a-4537-97f9-9b57cf6a04c2 binlog]# mysqlbinlog -vvv --base64-output=decode-rows mysql-bin.000017					...... 
					
						use `xiaoboluo`/*!*/; 
					...... 
					
						truncate test_load					
						/*!*/; 
					
						SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; 
					
						DELIMITER ; 
					
						# End of log file					...... 					
  • 从上面的结果中可以看到,解析binlog的结果中除了use db和truncate table语句之外,没有其他的操作了,说明并没有数据写入,load data语句被mysql server直接拒绝执行了

2.2. binlog_format=mixed
  • 先在主库中把binlog_format修改为mixed,隔离级别不要动,并刷新主从库binlog

# 主库  
					
						admin@localhost : xiaoboluo 09:44:05> set binlog_format=mixed; 
					
						Query OK, 0 rows affected (0.00 sec) 
					
						 					
						 					
						admin@localhost : xiaoboluo 09:49:49> flush binary logs; 
					
						Query OK, 0 rows affected (0.01 sec) 
					
						 					
						 					
						# 从库					
						mysql> flush binary logs; 
					
						Query OK, 0 rows affected (0.01 sec) 
					
						 					
						 					
						mysql> 					
  • 主库清空表test_load并执行load data语句

admin@localhost : xiaoboluo 09:49:57> truncate test_load;  
					
						Query OK, 0 rows affected (0.01 sec) 
					
						 					
						 					
						admin@localhost : xiaoboluo 09:51:12> load data infile '/tmp/test_load.txt' into table test_load; 
					
						Query OK, 4 rows affected (0.00 sec) 
					
						Records: 4 Deleted: 0 Skipped: 0 Warnings: 0					
  • 从以上结果中可以看到,在RU隔离级别下,binlog_format设置为mixed允许load data语句执行

  • 查看主库表test_load中的数据

admin@localhost : xiaoboluo 09:51:24> select * from test_load;  
					+----+------+ 
					| id | test | 
					+----+------+ 
					| 2 | 1 | 
					| 4 | 2 | 
					| 6 | null | 
					| 8 | 4 | 
					+----+------+ 
					
						4 rows in set (0.00 sec) 					
  • 从以上结果中可以看到,表数据导入成功,现在解析binlog查看一下

[root@5f1772e3-0c7a-4537-97f9-9b57cf6a04c2 binlog]# ll  
				
					total 196				-rw-r----- 1 mysql mysql 4916 May 4 00:11 mysql-bin.000014				-rw-r----- 1 mysql mysql 4557 May 4 00:12 mysql-bin.000015				-rw-r----- 1 mysql mysql 5595 May 4 09:39 mysql-bin.000016				-rw-r----- 1 mysql mysql 366 May 4 09:41 mysql-bin.000017				-rw-r----- 1 mysql mysql 744 May 4 09:51 mysql-bin.000018				-rw-r----- 1 mysql mysql 936 May 4 09:49 mysql-bin.index				[root@5f1772e3-0c7a-4537-97f9-9b57cf6a04c2 binlog]# mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000018				
					# at 259				
					#170504  9:51:12 server id 3306241  end_log_pos 354 CRC32 0xff83f0ce Query thread_id=155 exec_time=0 error_code=0				
					use `xiaoboluo`/*!*/; 
				...... 
				
					truncate test_load				...... 
				
					BEGIN				
					/*!*/; 
				
					# at 501				
					#170504  9:51:24 server id 3306241  end_log_pos 583 CRC32 0xfef1767a Rows_query				
					# load data infile '/tmp/test_load.txt' into table test_load  # 这里可以看到load data语句在这里,这里是开启了参数binlog_rows_query_log_events=ON之后记录的原生sql,默认被加了注释,主备复制不会执行				
					# at 583				
					#170504  9:51:24 server id 3306241  end_log_pos 643 CRC32 0x349b62a4 Table_map: `xiaoboluo`.`test_load` mapped to number 340				
					# at 643				
					#170504  9:51:24 server id 3306241  end_log_pos 713 CRC32 0x4ceacc10 Write_rows: table id 340 flags: STMT_END_F				
					### INSERT INTO `xiaoboluo`.`test_load`  #这里是用于主备复制的数据变更日志,可以看到在mixed格式下被转换为了row格式				
					### SET				
					###   @1=2 /* INT meta=0 nullable=0 is_null=0 */ 
				
					###   @2='1' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */ 
				
					### INSERT INTO `xiaoboluo`.`test_load`				
					### SET				
					###   @1=4 /* INT meta=0 nullable=0 is_null=0 */ 
				
					###   @2='2' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */ 
				
					### INSERT INTO `xiaoboluo`.`test_load`				
					### SET				
					###   @1=6 /* INT meta=0 nullable=0 is_null=0 */ 
				
					###   @2='null' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */ 
				
					### INSERT INTO `xiaoboluo`.`test_load`				
					### SET				
					###   @1=8 /* INT meta=0 nullable=0 is_null=0 */ 
				
					###   @2='4' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */ 
				
					# at 713				
					#170504  9:51:24 server id 3306241  end_log_pos 744 CRC32 0xfa76965b Xid = 1237				
					COMMIT/*!*/; 
				
					SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; 
				
					DELIMITER ; 
				
					# End of log file				
					/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; 
				
					/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/ 				
  • 从上面解析binlog的结果中可以看到,数据写入成功,load data语句被转换为row格式记录在binlog中

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

mysql> use xiaoboluo  
					
						Reading table information for completion of table and column names					
						You can turn off this feature to get a quicker startup with -A					
						 					
						 					
						Database changed					
						mysql> select * from test_load; 
					+----+------+ 
					| id | test | 
					+----+------+ 
					| 2 | 1 | 
					| 4 | 2 | 
					| 6 | null | 
					| 8 | 4 | 
					+----+------+ 
					
						4 rows in set (0.00 sec) 					
  • 解析备库binlog中是如何记录的load data语句

[root@e710d318-d5b4-4bc7-a606-d09f06ff5f5d binlog]# ll  
				
					total 100				-rw-r----- 1 mysql mysql 990 May 1 01:36 mysql-bin.000001				-rw-r----- 1 mysql mysql 54766 May 4 00:02 mysql-bin.000002				-rw-r----- 1 mysql mysql 21376 May 4 09:40 mysql-bin.000003				-rw-r----- 1 mysql mysql 401 May 4 09:50 mysql-bin.000004				-rw-r----- 1 mysql mysql 730 May 4 09:51 mysql-bin.000005				-rw-r----- 1 mysql mysql 260 May 4 09:50 mysql-bin.index				[root@e710d318-d5b4-4bc7-a606-d09f06ff5f5d binlog]# mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000005				... 
				
					#170504  9:51:12 server id 3306241  end_log_pos 354 CRC32 0xff83f0ce Query thread_id=155 exec_time=0 error_code=0				
					use `xiaoboluo`/*!*/; 
				... 
				
					truncate test_load				... 
				
					BEGIN				
					/*!*/; 
				
					# at 487				
					#170504  9:51:24 server id 3306241  end_log_pos 569 CRC32 0x67a31998 Rows_query				
					# load data infile '/tmp/test_load.txt' into table test_load  # 这里可以看到load data语句在这里,这里是开启了参数binlog_rows_query_log_events=ON之后记录的原生sql,默认被加了注释,主备复制不会执行				
					# at 569				
					#170504  9:51:24 server id 3306241  end_log_pos 629 CRC32 0x6420d19b Table_map: `xiaoboluo`.`test_load` mapped to number 301				
					# at 629				
					#170504  9:51:24 server id 3306241  end_log_pos 699 CRC32 0xf5fff0d8 Write_rows: table id 301 flags: STMT_END_F				
					### INSERT INTO `xiaoboluo`.`test_load`  #这里是备库记录的主库binlog日志,可以看到在mixed格式下被转换为了row格式				
					### SET				
					###   @1=2 /* INT meta=0 nullable=0 is_null=0 */ 
				
					###   @2='1' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */ 
				
					### INSERT INTO `xiaoboluo`.`test_load`				
					### SET				
					###   @1=4 /* INT meta=0 nullable=0 is_null=0 */ 
				
					###   @2='2' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */ 
				
					### INSERT INTO `xiaoboluo`.`test_load`				
					### SET				
					###   @1=6 /* INT meta=0 nullable=0 is_null=0 */ 
				
					###   @2='null' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */ 
				
					### INSERT INTO `xiaoboluo`.`test_load`				
					### SET				
					###   @1=8 /* INT meta=0 nullable=0 is_null=0 */ 
				
					###   @2='4' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */ 
				
					# at 699				
					#170504  9:51:24 server id 3306241  end_log_pos 730 CRC32 0xd5a3bda4 Xid = 424				
					COMMIT/*!*/; 
				... 				
2.3. binlog_format=row
  • 先在主库中把binlog_format修改为row,隔离级别不要动,并刷新主从库binlog

# 主库  
				
					admin@localhost : xiaoboluo 11:05:20> set binlog_format=row; 
				
					Query OK, 0 rows affected (0.00 sec) 
				
					admin@localhost : xiaoboluo 11:18:23> flush binary logs; 
				
					Query OK, 0 rows affected (0.01 sec) 
				
					# 从库				
					mysql> flush binary logs; 
				
					Query OK, 0 rows affected (0.01 sec) 
				
					mysql> 				
  • 主库清空表test_load并执行load data语句

admin@localhost : xiaoboluo 11:18:26> truncate test_load;  
					
						Query OK, 0 rows affected (0.01 sec) 
					
						admin@localhost : xiaoboluo 11:18:51> load data infile '/tmp/test_load.txt' into table test_load; 
					
						Query OK, 4 rows affected (0.00 sec) 
					
						Records: 4 Deleted: 0 Skipped: 0 Warnings: 0					
  • 从以上结果中可以看到,在RU隔离级别下,binlog_format设置为row允许load data语句执行

  • 查看主库表test_load中的数据

admin@localhost : xiaoboluo 11:18:57> select * from test_load;  
					+----+------+ 
					| id | test | 
					+----+------+ 
					| 2 | 1 | 
					| 4 | 2 | 
					| 6 | null | 
					| 8 | 4 | 
					+----+------+ 
					
						4 rows in set (0.00 sec) 					
  • 从以上结果中可以看到,表数据导入成功,现在解析binlog查看一下

[root@5f1772e3-0c7a-4537-97f9-9b57cf6a04c2 binlog]# ll  
				
					total 196				-rw-r----- 1 mysql mysql 4916 May 4 00:11 mysql-bin.000014				-rw-r----- 1 mysql mysql 4557 May 4 00:12 mysql-bin.000015				-rw-r----- 1 mysql mysql 5595 May 4 09:39 mysql-bin.000016				-rw-r----- 1 mysql mysql 366 May 4 09:41 mysql-bin.000017				-rw-r----- 1 mysql mysql 744 May 4 09:51 mysql-bin.000018				-rw-r----- 1 mysql mysql 744 May 4 11:18 mysql-bin.000019				-rw-r----- 1 mysql mysql 988 May 4 11:18 mysql-bin.index				[root@5f1772e3-0c7a-4537-97f9-9b57cf6a04c2 binlog]# mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000018				
					# at 259				
					#170504 11:18:51 server id 3306241  end_log_pos 354 CRC32 0x9d5985ad Query thread_id=155 exec_time=0 error_code=0				
					use `xiaoboluo`/*!*/; 
				...... 
				
					truncate test_load				...... 
				
					BEGIN				
					/*!*/; 
				
					# at 501				
					#170504 11:18:57 server id 3306241  end_log_pos 583 CRC32 0x0e8f7603 Rows_query				
					# load data infile '/tmp/test_load.txt' into table test_load # 这里可以看到load data语句在这里,这里是开启了参数binlog_rows_query_log_events=ON之后记录的原生sql,默认被加了注释,主备复制不会执行				
					# at 583				
					#170504 11:18:57 server id 3306241  end_log_pos 643 CRC32 0xe1c5d4d0 Table_map: `xiaoboluo`.`test_load` mapped to number 341				
					# at 643				
					#170504 11:18:57 server id 3306241  end_log_pos 713 CRC32 0x31619df3 Write_rows: table id 341 flags: STMT_END_F				
					### INSERT INTO `xiaoboluo`.`test_load`   #这里是用于主备复制的数据变更日志,可以看到在row格式下被转换为了row格式				
					### SET				
					###   @1=2 /* INT meta=0 nullable=0 is_null=0 */ 
				
					###   @2='1' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */ 
				
					### INSERT INTO `xiaoboluo`.`test_load`				
					### SET				
					###   @1=4 /* INT meta=0 nullable=0 is_null=0 */ 
				
					###   @2='2' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */ 
				
					### INSERT INTO `xiaoboluo`.`test_load`				
					### SET				
					###   @1=6 /* INT meta=0 nullable=0 is_null=0 */ 
				
					###   @2='null' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */ 
				
					### INSERT INTO `xiaoboluo`.`test_load`				
					### SET				
					###   @1=8 /* INT meta=0 nullable=0 is_null=0 */ 
				
					###   @2='4' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */ 
				
					# at 713				
					#170504 11:18:57 server id 3306241  end_log_pos 744 CRC32 0x18b14b52 Xid = 1245				
					COMMIT/*!*/; 
				...... 				
  • 从上面解析binlog的结果中可以看到,数据写入成功,load data语句被转换为row格式记录在binlog中

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

[root@5f1772e3-0c7a-4537-97f9-9b57cf6a04c2 binlog]# ll  
					
						total 196					-rw-r----- 1 mysql mysql 4916 May 4 00:11 mysql-bin.000014					
						 					-rw-r----- 1 mysql mysql 4557 May 4 00:12 mysql-bin.000015					-rw-r----- 1 mysql mysql 5595 May 4 09:39 mysql-bin.000016					-rw-r----- 1 mysql mysql 366 May 4 09:41 mysql-bin.000017					-rw-r----- 1 mysql mysql 744 May 4 09:51 mysql-bin.000018					
						 					-rw-r----- 1 mysql mysql 744 May 4 11:18 mysql-bin.000019					-rw-r----- 1 mysql mysql 988 May 4 11:18 mysql-bin.index					
						 					
						 					[root@5f1772e3-0c7a-4537-97f9-9b57cf6a04c2 binlog]# mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000018					
						# at 259					
						#170504 11:18:51 server id 3306241  end_log_pos 354 CRC32 0x9d5985ad Query thread_id=155 exec_time=0 error_code=0					
						use `xiaoboluo`/*!*/; 
					...... 
					
						truncate test_load					...... 
					
						BEGIN					
						/*!*/; 
					
						# at 501					
						#170504 11:18:57 server id 3306241  end_log_pos 583 CRC32 0x0e8f7603 Rows_query					
						# load data infile '/tmp/test_load.txt' into table test_load # 这里可以看到load data语句在这里,这里是开启了参数binlog_rows_query_log_events=ON之后记录的原生sql,默认被加了注释,主备复制不会执行					
						# at 583					
						#170504 11:18:57 server id 3306241  end_log_pos 643 CRC32 0xe1c5d4d0 Table_map: `xiaoboluo`.`test_load` mapped to number 341					
						# at 643					
						#170504 11:18:57 server id 3306241  end_log_pos 713 CRC32 0x31619df3 Write_rows: table id 341 flags: STMT_END_F					
						### INSERT INTO `xiaoboluo`.`test_load`   #这里是用于主备复制的数据变更日志,可以看到在row格式下被转换为了row格式					
						### SET					
						###   @1=2 /* INT meta=0 nullable=0 is_null=0 */ 
					
						###   @2='1' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */ 
					
						### INSERT INTO `xiaoboluo`.`test_load`					
						### SET					
						###   @1=4 /* INT meta=0 nullable=0 is_null=0 */ 
					
						###   @2='2' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */ 
					
						### INSERT INTO `xiaoboluo`.`test_load`					
						### SET					
						###   @1=6 /* INT meta=0 nullable=0 is_null=0 */ 
					
						###   @2='null' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */ 
					
						### INSERT INTO `xiaoboluo`.`test_load`					
						### SET					
						###   @1=8 /* INT meta=0 nullable=0 is_null=0 */ 
					
						###   @2='4' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */ 
					
						# at 713					
						#170504 11:18:57 server id 3306241  end_log_pos 744 CRC32 0x18b14b52 Xid = 1245					
						COMMIT/*!*/; 
					...... 
					mysql> use xiaoboluo  
					
						Database changed					
						mysql> select * from test_load; 
					+----+------+ 
					| id | test | 
					+----+------+ 
					| 2 | 1 | 
					| 4 | 2 | 
					| 6 | null | 
					| 8 | 4 | 
					+----+------+ 
					
						4 rows in set (0.00 sec) 					
  • 解析备库binlog中是如何记录的load data语句

[root@e710d318-d5b4-4bc7-a606-d09f06ff5f5d binlog]# ll  
					
						total 100					-rw-r----- 1 mysql mysql 990 May 1 01:36 mysql-bin.000001					-rw-r----- 1 mysql mysql 54766 May 4 00:02 mysql-bin.000002					-rw-r----- 1 mysql mysql 21376 May 4 09:40 mysql-bin.000003					-rw-r----- 1 mysql mysql 401 May 4 09:50 mysql-bin.000004					-rw-r----- 1 mysql mysql 730 May 4 09:51 mysql-bin.000005					-rw-r----- 1 mysql mysql 730 May 4 11:18 mysql-bin.000006					-rw-r----- 1 mysql mysql 312 May 4 11:18 mysql-bin.index					[root@e710d318-d5b4-4bc7-a606-d09f06ff5f5d binlog]# mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000009					...... 
					
						# at 259					
						#170504 11:18:51 server id 3306241  end_log_pos 354 CRC32 0x9d5985ad Query thread_id=155 exec_time=0 error_code=0					
						use `xiaoboluo`/*!*/; 
					...... 
					
						truncate test_load					...... 
					
						# at 487					
						#170504 11:18:57 server id 3306241  end_log_pos 569 CRC32 0x97dd19e1 Rows_query					
						# load data infile '/tmp/test_load.txt' into table test_load  # 这里可以看到load data语句在这里,这里是开启了参数binlog_rows_query_log_events=ON之后记录的原生sql,默认被加了注释,主备复制不会执行					
						# at 569					
						#170504 11:18:57 server id 3306241  end_log_pos 629 CRC32 0x6bef4f90 Table_map: `xiaoboluo`.`test_load` mapped to number 302					
						# at 629					
						#170504 11:18:57 server id 3306241  end_log_pos 699 CRC32 0x5a86b7f1 Write_rows: table id 302 flags: STMT_END_F					
						### INSERT INTO `xiaoboluo`.`test_load`  #这里是备库记录的主库binlog日志,可以看到在row格式下被转换为了row格式,与mixed格式下记录的内容相同					
						### SET					
						###   @1=2 /* INT meta=0 nullable=0 is_null=0 */ 
					
						###   @2='1' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */ 
					
						### INSERT INTO `xiaoboluo`.`test_load`					
						### SET					
						###   @1=4 /* INT meta=0 nullable=0 is_null=0 */ 
					
						###   @2='2' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */ 
					
						### INSERT INTO `xiaoboluo`.`test_load`					
						### SET					
						###   @1=6 /* INT meta=0 nullable=0 is_null=0 */ 
					
						###   @2='null' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */ 
					
						### INSERT INTO `xiaoboluo`.`test_load`					
						### SET					
						###   @1=8 /* INT meta=0 nullable=0 is_null=0 */ 
					
						###   @2='4' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */ 
					
						# at 699					
						#170504 11:18:57 server id 3306241  end_log_pos 730 CRC32 0x983a4a33 Xid = 440					
						COMMIT/*!*/; 
					...... 					

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


【AD】美国洛杉矶/香港/日本VPS推荐,回程电信CN2 GIA线路,延迟低、稳定性高、免费备份_搬瓦工