load data语句如何保证主备复制数据一致性(一)

最新推荐文章于 2021-02-01 17:18:08 发布
最新推荐文章于 2021-02-01 17:18:08 发布
阅读量139 收藏
点赞数
文章标签： 数据库

  • 背景

    • 机器配置
      * 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、制造测试数据

  • 建库 
 
 
  1.  
   
     
    
     
     
    1.  admin@localhost : (none) 07:34:39> create database if not exists xiaoboluo;  
    2.  Query OK, 1 row affected (0.00 sec) 
     
    
     
   
     
 
 
  •  建表 
 
 
 
 
  1.  
   
     
    
     
     
    1.  admin@localhost : (none) 10:04:52> use xiaoboluo  
    2.  Database changed 
    3.  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)); 
    4.  Query OK, 0 rows affected (0.01 sec) 
     
    
     
   
     
 
 
  •  插入测试数据 
 
 
 
 
  1.  
   
     
    
     
     
    1.  admin@localhost : xiaoboluo 10:05:32> insert into test_load(test) values('1'),('2'),('null'),('4');  
    2.  Query OK, 4 rows affected (0.01 sec) 
    3.  Records: 4 Duplicates: 0 Warnings: 0 
    4.    
    5.    
    6.  admin@localhost : xiaoboluo 10:06:01> select * from test_load; 
    7.  +----+------+ 
    8.  | id | test | 
    9.  +----+------+ 
    10.  | 2 | 1 | 
    11.  | 4 | 2 | 
    12.  | 6 | null | 
    13.  | 8 | 4 | 
    14.  +----+------+ 
    15.  4 rows in set (0.00 sec) 
     
    
     
   
     
 
 
  •  执行select …into outifile语句生成load data需要的数据文本文件 
 
 
 
 
  1.  
   
     
    
     
     
    1.  admin@localhost : xiaoboluo 10:12:41> select * from test_load into outfile "/tmp/test_load.txt";  
    2.  Query OK, 4 rows affected (0.01 sec) 
    3.    
    4.    
    5.  admin@localhost : xiaoboluo 10:12:42> system cat /tmp/test_load.txt; 
    6.  2 1 
    7.  4 2 
    8.  6 null 
    9.  8 4 
     
    
     
   
     
 
 
 2、read-uncommitted隔离级别 
 
 
 2.1. binlog_format=statement 
 
 
  •  先在主库中把binlog_format修改为statement，隔离级别修改为read-uncommitted，并刷新主从库binlog 
 
 
 
 
  1.  
   
     
    
     
     
    1.  # 主库  
    2.  admin@localhost : xiaoboluo 09:37:09> set binlog_format=statement; 
    3.  Query OK, 0 rows affected (0.00 sec) 
    4.    
    5.    
    6.  admin@localhost : xiaoboluo 09:39:24> set tx_isolation='read-uncommitted'; 
    7.  Query OK, 0 rows affected (0.00 sec) 
    8.    
    9.    
    10.  admin@localhost : xiaoboluo 09:39:44> flush binary logs; 
    11.  Query OK, 0 rows affected (0.01 sec) 
    12.    
    13.    
    14.  # 从库 
    15.  mysql> flush binary logs; 
    16.  Query OK, 0 rows affected (0.01 sec) 
    17.    
    18.    
    19.  mysql> 
     
    
     
   
     
 
 
  •  主库清空表test_load并执行load data语句 
 
 
 
 
  1.  
   
     
    
     
     
    1.  admin@localhost : xiaoboluo 09:39:54> truncate test_load;  
    2.  Query OK, 0 rows affected (0.00 sec) 
    3.  admin@localhost : xiaoboluo 09:41:13> load data infile '/tmp/test_load.txt' into table test_load; 
    4.  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. 
    5.  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. 
    6.  Error (Code 1015): Can 
     
    
     
   
     
 
 
  •  从以上结果中可以看到，在RU隔离级别下，binlog_format设置为statement不允许load data语句执行,从报错信息上可以看到，在RC和RU隔离级别下，binlog只能以row格式记录，所以statement无法记录binlog 
  •  查看主库表test_load中的数据 
 
 
 
 
  1.  
   
     
    
     
     
    1.  admin@localhost : xiaoboluo 09:41:52> select * from test_load;  
    2.  Empty set (0.00 sec) 
     
    
     
   
     
 
 
  •  从以上结果中可以看到，表数据并没有导入成功，现在解析binlog查看一下 
 
 
 
 
  1.  
   
     
    
     
     
    1.  [root@5f1772e3-0c7a-4537-97f9-9b57cf6a04c2 binlog]# ll  
    2.  total 196 
    3.  -rw-r----- 1 mysql mysql 4916 May 4 00:11 mysql-bin.000014 
    4.    
    5.  -rw-r----- 1 mysql mysql 4557 May 4 00:12 mysql-bin.000015 
    6.  -rw-r----- 1 mysql mysql 5595 May 4 09:39 mysql-bin.000016 
    7.  -rw-r----- 1 mysql mysql 366 May 4 09:41 mysql-bin.000017 
    8.  -rw-r----- 1 mysql mysql 884 May 4 09:39 mysql-bin.index 
    9.  [root@5f1772e3-0c7a-4537-97f9-9b57cf6a04c2 binlog]# mysqlbinlog -vvv --base64-output=decode-rows mysql-bin.000017 
    10.  ...... 
    11.  use `xiaoboluo`/*!*/; 
    12.  ...... 
    13.  truncate test_load 
    14.  /*!*/; 
    15.  SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; 
    16.  DELIMITER ; 
    17.  # End of log file 
    18.  ...... 
     
    
     
   
     
 
 
  •  从上面的结果中可以看到，解析binlog的结果中除了use db和truncate table语句之外，没有其他的操作了，说明并没有数据写入，load data语句被mysql server直接拒绝执行了 
 
 
 2.2. binlog_format=mixed 
 
 
  •  先在主库中把binlog_format修改为mixed，隔离级别不要动，并刷新主从库binlog 
 
 
 
 
  1.  
   
     
    
     
     
    1.  # 主库  
    2.  admin@localhost : xiaoboluo 09:44:05> set binlog_format=mixed; 
    3.  Query OK, 0 rows affected (0.00 sec) 
    4.    
    5.    
    6.  admin@localhost : xiaoboluo 09:49:49> flush binary logs; 
    7.  Query OK, 0 rows affected (0.01 sec) 
    8.    
    9.    
    10.  # 从库 
    11.  mysql> flush binary logs; 
    12.  Query OK, 0 rows affected (0.01 sec) 
    13.    
    14.    
    15.  mysql> 
     
    
     
   
     
 
 
  •  主库清空表test_load并执行load data语句 
 
 
 
 
  1.  
   
     
    
     
     
    1.  admin@localhost : xiaoboluo 09:49:57> truncate test_load;  
    2.  Query OK, 0 rows affected (0.01 sec) 
    3.    
    4.    
    5.  admin@localhost : xiaoboluo 09:51:12> load data infile '/tmp/test_load.txt' into table test_load; 
    6.  Query OK, 4 rows affected (0.00 sec) 
    7.  Records: 4 Deleted: 0 Skipped: 0 Warnings: 0 
     
    
     
   
     
 
 
  •  从以上结果中可以看到，在RU隔离级别下，binlog_format设置为mixed允许load data语句执行 
  •  查看主库表test_load中的数据 
 
 
 
 
  1.  
   
     
    
     
     
    1.  admin@localhost : xiaoboluo 09:51:24> select * from test_load;  
    2.  +----+------+ 
    3.  | id | test | 
    4.  +----+------+ 
    5.  | 2 | 1 | 
    6.  | 4 | 2 | 
    7.  | 6 | null | 
    8.  | 8 | 4 | 
    9.  +----+------+ 
    10.  4 rows in set (0.00 sec) 
     
    
     
   
     
 
 
  •  从以上结果中可以看到，表数据导入成功，现在解析binlog查看一下 
 
 
 
 
  1.  
   
     
    
    1.  [root@5f1772e3-0c7a-4537-97f9-9b57cf6a04c2 binlog]# ll  
    2.  total 196 
    3.  -rw-r----- 1 mysql mysql 4916 May 4 00:11 mysql-bin.000014 
    4.  -rw-r----- 1 mysql mysql 4557 May 4 00:12 mysql-bin.000015 
    5.  -rw-r----- 1 mysql mysql 5595 May 4 09:39 mysql-bin.000016 
    6.  -rw-r----- 1 mysql mysql 366 May 4 09:41 mysql-bin.000017 
    7.  -rw-r----- 1 mysql mysql 744 May 4 09:51 mysql-bin.000018 
    8.  -rw-r----- 1 mysql mysql 936 May 4 09:49 mysql-bin.index 
    9.  [root@5f1772e3-0c7a-4537-97f9-9b57cf6a04c2 binlog]# mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000018 
    10.  # at 259 
    11.  #170504 9:51:12 server id 3306241 end_log_pos 354 CRC32 0xff83f0ce Query thread_id=155 exec_time=0 error_code=0 
    12.  use `xiaoboluo`/*!*/; 
    13.  ...... 
    14.  truncate test_load 
    15.  ...... 
    16.  BEGIN 
    17.  /*!*/; 
    18.  # at 501 
    19.  #170504 9:51:24 server id 3306241 end_log_pos 583 CRC32 0xfef1767a Rows_query 
    20.  # load data infile '/tmp/test_load.txt' into table test_load # 这里可以看到load data语句在这里，这里是开启了参数binlog_rows_query_log_events=ON之后记录的原生sql，默认被加了注释，主备复制不会执行 
    21.  # at 583 
    22.  #170504 9:51:24 server id 3306241 end_log_pos 643 CRC32 0x349b62a4 Table_map: `xiaoboluo`.`test_load` mapped to number 340 
    23.  # at 643 
    24.  #170504 9:51:24 server id 3306241 end_log_pos 713 CRC32 0x4ceacc10 Write_rows: table id 340 flags: STMT_END_F 
    25.  ### INSERT INTO `xiaoboluo`.`test_load` #这里是用于主备复制的数据变更日志，可以看到在mixed格式下被转换为了row格式 
    26.  ### SET 
    27.  ### @1=2 /* INT meta=0 nullable=0 is_null=0 */ 
    28.  ### @2='1' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */ 
    29.  ### INSERT INTO `xiaoboluo`.`test_load` 
    30.  ### SET 
    31.  ### @1=4 /* INT meta=0 nullable=0 is_null=0 */ 
    32.  ### @2='2' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */ 
    33.  ### INSERT INTO `xiaoboluo`.`test_load` 
    34.  ### SET 
    35.  ### @1=6 /* INT meta=0 nullable=0 is_null=0 */ 
    36.  ### @2='null' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */ 
    37.  ### INSERT INTO `xiaoboluo`.`test_load` 
    38.  ### SET 
    39.  ### @1=8 /* INT meta=0 nullable=0 is_null=0 */ 
    40.  ### @2='4' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */ 
    41.  # at 713 
    42.  #170504 9:51:24 server id 3306241 end_log_pos 744 CRC32 0xfa76965b Xid = 1237 
    43.  COMMIT/*!*/; 
    44.  SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; 
    45.  DELIMITER ; 
    46.  # End of log file 
    47.  /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; 
    48.  /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/ 
     
   
     
 
 
  •  
     从上面解析binlog的结果中可以看到，数据写入成功，load data语句被转换为row格式记录在binlog中 
     
  •  
     查看备库中数据是否正确复制 
     
 
 
 
 
  1.  
   
     
    
     
     
    1.  mysql> use xiaoboluo  
    2.  Reading table information for completion of table and column names 
    3.  You can turn off this feature to get a quicker startup with -A 
    4.    
    5.    
    6.  Database changed 
    7.  mysql> select * from test_load; 
    8.  +----+------+ 
    9.  | id | test | 
    10.  +----+------+ 
    11.  | 2 | 1 | 
    12.  | 4 | 2 | 
    13.  | 6 | null | 
    14.  | 8 | 4 | 
    15.  +----+------+ 
    16.  4 rows in set (0.00 sec) 
     
    
     
   
     
 
 
  •  解析备库binlog中是如何记录的load data语句 
 
 
 
 
  1.  
   
     
    
    1.  [root@e710d318-d5b4-4bc7-a606-d09f06ff5f5d binlog]# ll  
    2.  total 100 
    3.  -rw-r----- 1 mysql mysql 990 May 1 01:36 mysql-bin.000001 
    4.  -rw-r----- 1 mysql mysql 54766 May 4 00:02 mysql-bin.000002 
    5.  -rw-r----- 1 mysql mysql 21376 May 4 09:40 mysql-bin.000003 
    6.  -rw-r----- 1 mysql mysql 401 May 4 09:50 mysql-bin.000004 
    7.  -rw-r----- 1 mysql mysql 730 May 4 09:51 mysql-bin.000005 
    8.  -rw-r----- 1 mysql mysql 260 May 4 09:50 mysql-bin.index 
    9.  [root@e710d318-d5b4-4bc7-a606-d09f06ff5f5d binlog]# mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000005 
    10.  ... 
    11.  #170504 9:51:12 server id 3306241 end_log_pos 354 CRC32 0xff83f0ce Query thread_id=155 exec_time=0 error_code=0 
    12.  use `xiaoboluo`/*!*/; 
    13.  ... 
    14.  truncate test_load 
    15.  ... 
    16.  BEGIN 
    17.  /*!*/; 
    18.  # at 487 
    19.  #170504 9:51:24 server id 3306241 end_log_pos 569 CRC32 0x67a31998 Rows_query 
    20.  # load data infile '/tmp/test_load.txt' into table test_load # 这里可以看到load data语句在这里，这里是开启了参数binlog_rows_query_log_events=ON之后记录的原生sql，默认被加了注释，主备复制不会执行 
    21.  # at 569 
    22.  #170504 9:51:24 server id 3306241 end_log_pos 629 CRC32 0x6420d19b Table_map: `xiaoboluo`.`test_load` mapped to number 301 
    23.  # at 629 
    24.  #170504 9:51:24 server id 3306241 end_log_pos 699 CRC32 0xf5fff0d8 Write_rows: table id 301 flags: STMT_END_F 
    25.  ### INSERT INTO `xiaoboluo`.`test_load` #这里是备库记录的主库binlog日志，可以看到在mixed格式下被转换为了row格式 
    26.  ### SET 
    27.  ### @1=2 /* INT meta=0 nullable=0 is_null=0 */ 
    28.  ### @2='1' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */ 
    29.  ### INSERT INTO `xiaoboluo`.`test_load` 
    30.  ### SET 
    31.  ### @1=4 /* INT meta=0 nullable=0 is_null=0 */ 
    32.  ### @2='2' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */ 
    33.  ### INSERT INTO `xiaoboluo`.`test_load` 
    34.  ### SET 
    35.  ### @1=6 /* INT meta=0 nullable=0 is_null=0 */ 
    36.  ### @2='null' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */ 
    37.  ### INSERT INTO `xiaoboluo`.`test_load` 
    38.  ### SET 
    39.  ### @1=8 /* INT meta=0 nullable=0 is_null=0 */ 
    40.  ### @2='4' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */ 
    41.  # at 699 
    42.  #170504 9:51:24 server id 3306241 end_log_pos 730 CRC32 0xd5a3bda4 Xid = 424 
    43.  COMMIT/*!*/; 
    44.  ... 
     
   
     
 
 
 2.3. binlog_format=row 
 
 
  •  先在主库中把binlog_format修改为row，隔离级别不要动，并刷新主从库binlog 
 
 
 
 
  1.  
   
     
    
    1.  # 主库  
    2.  admin@localhost : xiaoboluo 11:05:20> set binlog_format=row; 
    3.  Query OK, 0 rows affected (0.00 sec) 
    4.  admin@localhost : xiaoboluo 11:18:23> flush binary logs; 
    5.  Query OK, 0 rows affected (0.01 sec) 
    6.  # 从库 
    7.  mysql> flush binary logs; 
    8.  Query OK, 0 rows affected (0.01 sec) 
    9.  mysql> 
     
   
     
 
 
  •  主库清空表test_load并执行load data语句 
 
 
 
 
  1.  
   
     
    
     
     
    1.  admin@localhost : xiaoboluo 11:18:26> truncate test_load;  
    2.  Query OK, 0 rows affected (0.01 sec) 
    3.  admin@localhost : xiaoboluo 11:18:51> load data infile '/tmp/test_load.txt' into table test_load; 
    4.  Query OK, 4 rows affected (0.00 sec) 
    5.  Records: 4 Deleted: 0 Skipped: 0 Warnings: 0 
     
    
     
   
     
 
 
  •  从以上结果中可以看到，在RU隔离级别下，binlog_format设置为row允许load data语句执行 
  •  查看主库表test_load中的数据 
 
 
 
 
  1.  
   
     
    
     
     
    1.  admin@localhost : xiaoboluo 11:18:57> select * from test_load;  
    2.  +----+------+ 
    3.  | id | test | 
    4.  +----+------+ 
    5.  | 2 | 1 | 
    6.  | 4 | 2 | 
    7.  | 6 | null | 
    8.  | 8 | 4 | 
    9.  +----+------+ 
    10.  4 rows in set (0.00 sec) 
     
    
     
   
     
 
 
  •  从以上结果中可以看到，表数据导入成功，现在解析binlog查看一下 
 
 
 
 
  1.  
   
     
    
    1.  [root@5f1772e3-0c7a-4537-97f9-9b57cf6a04c2 binlog]# ll  
    2.  total 196 
    3.  -rw-r----- 1 mysql mysql 4916 May 4 00:11 mysql-bin.000014 
    4.  -rw-r----- 1 mysql mysql 4557 May 4 00:12 mysql-bin.000015 
    5.  -rw-r----- 1 mysql mysql 5595 May 4 09:39 mysql-bin.000016 
    6.  -rw-r----- 1 mysql mysql 366 May 4 09:41 mysql-bin.000017 
    7.  -rw-r----- 1 mysql mysql 744 May 4 09:51 mysql-bin.000018 
    8.  -rw-r----- 1 mysql mysql 744 May 4 11:18 mysql-bin.000019 
    9.  -rw-r----- 1 mysql mysql 988 May 4 11:18 mysql-bin.index 
    10.  [root@5f1772e3-0c7a-4537-97f9-9b57cf6a04c2 binlog]# mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000018 
    11.  # at 259 
    12.  #170504 11:18:51 server id 3306241 end_log_pos 354 CRC32 0x9d5985ad Query thread_id=155 exec_time=0 error_code=0 
    13.  use `xiaoboluo`/*!*/; 
    14.  ...... 
    15.  truncate test_load 
    16.  ...... 
    17.  BEGIN 
    18.  /*!*/; 
    19.  # at 501 
    20.  #170504 11:18:57 server id 3306241 end_log_pos 583 CRC32 0x0e8f7603 Rows_query 
    21.  # load data infile '/tmp/test_load.txt' into table test_load # 这里可以看到load data语句在这里，这里是开启了参数binlog_rows_query_log_events=ON之后记录的原生sql，默认被加了注释，主备复制不会执行 
    22.  # at 583 
    23.  #170504 11:18:57 server id 3306241 end_log_pos 643 CRC32 0xe1c5d4d0 Table_map: `xiaoboluo`.`test_load` mapped to number 341 
    24.  # at 643 
    25.  #170504 11:18:57 server id 3306241 end_log_pos 713 CRC32 0x31619df3 Write_rows: table id 341 flags: STMT_END_F 
    26.  ### INSERT INTO `xiaoboluo`.`test_load` #这里是用于主备复制的数据变更日志，可以看到在row格式下被转换为了row格式 
    27.  ### SET 
    28.  ### @1=2 /* INT meta=0 nullable=0 is_null=0 */ 
    29.  ### @2='1' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */ 
    30.  ### INSERT INTO `xiaoboluo`.`test_load` 
    31.  ### SET 
    32.  ### @1=4 /* INT meta=0 nullable=0 is_null=0 */ 
    33.  ### @2='2' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */ 
    34.  ### INSERT INTO `xiaoboluo`.`test_load` 
    35.  ### SET 
    36.  ### @1=6 /* INT meta=0 nullable=0 is_null=0 */ 
    37.  ### @2='null' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */ 
    38.  ### INSERT INTO `xiaoboluo`.`test_load` 
    39.  ### SET 
    40.  ### @1=8 /* INT meta=0 nullable=0 is_null=0 */ 
    41.  ### @2='4' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */ 
    42.  # at 713 
    43.  #170504 11:18:57 server id 3306241 end_log_pos 744 CRC32 0x18b14b52 Xid = 1245 
    44.  COMMIT/*!*/; 
    45.  ...... 
     
   
     
 
 
  •  
     从上面解析binlog的结果中可以看到，数据写入成功，load data语句被转换为row格式记录在binlog中 
     
  •  
     查看备库中数据是否正确复制 
     
 
 
 
 
  1.  
   
     
    
     
     
    1.  mysql> use xiaoboluo  
    2.  Database changed 
    3.  mysql> select * from test_load; 
    4.  +----+------+ 
    5.  | id | test | 
    6.  +----+------+ 
    7.  | 2 | 1 | 
    8.  | 4 | 2 | 
    9.  | 6 | null | 
    10.  | 8 | 4 | 
    11.  +----+------+ 
    12.  4 rows in set (0.00 sec) 
     
    
     
   
     
   
     
    
     
     
    1.  [root@5f1772e3-0c7a-4537-97f9-9b57cf6a04c2 binlog]# ll  
    2.  total 196 
    3.  -rw-r----- 1 mysql mysql 4916 May 4 00:11 mysql-bin.000014 
    4.    
    5.  -rw-r----- 1 mysql mysql 4557 May 4 00:12 mysql-bin.000015 
    6.  -rw-r----- 1 mysql mysql 5595 May 4 09:39 mysql-bin.000016 
    7.  -rw-r----- 1 mysql mysql 366 May 4 09:41 mysql-bin.000017 
    8.  -rw-r----- 1 mysql mysql 744 May 4 09:51 mysql-bin.000018 
    9.    
    10.  -rw-r----- 1 mysql mysql 744 May 4 11:18 mysql-bin.000019 
    11.  -rw-r----- 1 mysql mysql 988 May 4 11:18 mysql-bin.index 
    12.    
    13.    
    14.  [root@5f1772e3-0c7a-4537-97f9-9b57cf6a04c2 binlog]# mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000018 
    15.  # at 259 
    16.  #170504 11:18:51 server id 3306241 end_log_pos 354 CRC32 0x9d5985ad Query thread_id=155 exec_time=0 error_code=0 
    17.  use `xiaoboluo`/*!*/; 
    18.  ...... 
    19.  truncate test_load 
    20.  ...... 
    21.  BEGIN 
    22.  /*!*/; 
    23.  # at 501 
    24.  #170504 11:18:57 server id 3306241 end_log_pos 583 CRC32 0x0e8f7603 Rows_query 
    25.  # load data infile '/tmp/test_load.txt' into table test_load # 这里可以看到load data语句在这里，这里是开启了参数binlog_rows_query_log_events=ON之后记录的原生sql，默认被加了注释，主备复制不会执行 
    26.  # at 583 
    27.  #170504 11:18:57 server id 3306241 end_log_pos 643 CRC32 0xe1c5d4d0 Table_map: `xiaoboluo`.`test_load` mapped to number 341 
    28.  # at 643 
    29.  #170504 11:18:57 server id 3306241 end_log_pos 713 CRC32 0x31619df3 Write_rows: table id 341 flags: STMT_END_F 
    30.  ### INSERT INTO `xiaoboluo`.`test_load` #这里是用于主备复制的数据变更日志，可以看到在row格式下被转换为了row格式 
    31.  ### SET 
    32.  ### @1=2 /* INT meta=0 nullable=0 is_null=0 */ 
    33.  ### @2='1' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */ 
    34.  ### INSERT INTO `xiaoboluo`.`test_load` 
    35.  ### SET 
    36.  ### @1=4 /* INT meta=0 nullable=0 is_null=0 */ 
    37.  ### @2='2' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */ 
    38.  ### INSERT INTO `xiaoboluo`.`test_load` 
    39.  ### SET 
    40.  ### @1=6 /* INT meta=0 nullable=0 is_null=0 */ 
    41.  ### @2='null' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */ 
    42.  ### INSERT INTO `xiaoboluo`.`test_load` 
    43.  ### SET 
    44.  ### @1=8 /* INT meta=0 nullable=0 is_null=0 */ 
    45.  ### @2='4' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */ 
    46.  # at 713 
    47.  #170504 11:18:57 server id 3306241 end_log_pos 744 CRC32 0x18b14b52 Xid = 1245 
    48.  COMMIT/*!*/; 
    49.  ...... 
     
    
     
   
     
 
 
  •  解析备库binlog中是如何记录的load data语句 
 
 
 
 
  1.  
   
     
    
     
     
    1.  [root@e710d318-d5b4-4bc7-a606-d09f06ff5f5d binlog]# ll  
    2.  total 100 
    3.  -rw-r----- 1 mysql mysql 990 May 1 01:36 mysql-bin.000001 
    4.  -rw-r----- 1 mysql mysql 54766 May 4 00:02 mysql-bin.000002 
    5.  -rw-r----- 1 mysql mysql 21376 May 4 09:40 mysql-bin.000003 
    6.  -rw-r----- 1 mysql mysql 401 May 4 09:50 mysql-bin.000004 
    7.  -rw-r----- 1 mysql mysql 730 May 4 09:51 mysql-bin.000005 
    8.  -rw-r----- 1 mysql mysql 730 May 4 11:18 mysql-bin.000006 
    9.  -rw-r----- 1 mysql mysql 312 May 4 11:18 mysql-bin.index 
    10.  [root@e710d318-d5b4-4bc7-a606-d09f06ff5f5d binlog]# mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000009 
    11.  ...... 
    12.  # at 259 
    13.  #170504 11:18:51 server id 3306241 end_log_pos 354 CRC32 0x9d5985ad Query thread_id=155 exec_time=0 error_code=0 
    14.  use `xiaoboluo`/*!*/; 
    15.  ...... 
    16.  truncate test_load 
    17.  ...... 
    18.  # at 487 
    19.  #170504 11:18:57 server id 3306241 end_log_pos 569 CRC32 0x97dd19e1 Rows_query 
    20.  # load data infile '/tmp/test_load.txt' into table test_load # 这里可以看到load data语句在这里，这里是开启了参数binlog_rows_query_log_events=ON之后记录的原生sql，默认被加了注释，主备复制不会执行 
    21.  # at 569 
    22.  #170504 11:18:57 server id 3306241 end_log_pos 629 CRC32 0x6bef4f90 Table_map: `xiaoboluo`.`test_load` mapped to number 302 
    23.  # at 629 
    24.  #170504 11:18:57 server id 3306241 end_log_pos 699 CRC32 0x5a86b7f1 Write_rows: table id 302 flags: STMT_END_F 
    25.  ### INSERT INTO `xiaoboluo`.`test_load` #这里是备库记录的主库binlog日志，可以看到在row格式下被转换为了row格式，与mixed格式下记录的内容相同 
    26.  ### SET 
    27.  ### @1=2 /* INT meta=0 nullable=0 is_null=0 */ 
    28.  ### @2='1' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */ 
    29.  ### INSERT INTO `xiaoboluo`.`test_load` 
    30.  ### SET 
    31.  ### @1=4 /* INT meta=0 nullable=0 is_null=0 */ 
    32.  ### @2='2' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */ 
    33.  ### INSERT INTO `xiaoboluo`.`test_load` 
    34.  ### SET 
    35.  ### @1=6 /* INT meta=0 nullable=0 is_null=0 */ 
    36.  ### @2='null' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */ 
    37.  ### INSERT INTO `xiaoboluo`.`test_load` 
    38.  ### SET 
    39.  ### @1=8 /* INT meta=0 nullable=0 is_null=0 */ 
    40.  ### @2='4' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */ 
    41.  # at 699 
    42.  #170504 11:18:57 server id 3306241 end_log_pos 730 CRC32 0x983a4a33 Xid = 440 
    43.  COMMIT/*!*/; 
    44.  ...... 
     
    
     
   
     
 
 
 
 
 来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28218939/viewspace-2139837/，如需转载，请注明出处，否则将追究法律责任。 
 

 

转载于:http://blog.itpub.net/28218939/viewspace-2139837/

                

        

        




    

  


    

      

        

            

              
                
                  congcheng1062
                
              
            

            

                关注
              
            

        

        

          
      

      









                



	

		

			

				
					
MySQL中由load data语句引起死锁的解决案例

				
			

			

				

					09-10
				

			

		

		

			
				
在MySQL中，`LOAD DATA INFILE`语句常用于快速导入大量数据到InnoDB表中。然而，由于InnoDB引擎的锁机制，这种操作有时可能会引发死锁。死锁是指两个或多个事务在执行过程中，因争夺资源而造成的一种相互等待的现象...

			
		

	



                

              
                



	

		

			

				
					
mysql load data 原理_MySQL 执行load data infile时同步原理及注意事项(额外一点补充)...

				
			

			

				

					weixin_29798625的博客
				

				

					01-19
					
					841
					
				

			

		

		

			
				
1)在客户端也可以向远程MySQL服务器执行 ‘load data‘ 命令，比如，客户端IP： 192.168.204.132服务器IP： 192.168.204.131可以在192.168.204.132上，执行命令：-h192.168.204.131 -utest -ptest test -e'loadlocal data infile"/opt/xxxxx.txt"into table lo...

			
		

	



                


  
              

                


	

		

			

				
					
load data 方式导入的数据不可以用binlog日志进行恢复

				
			

			

				

					
				

				

					12-06
					
					198
					
				

			

		

		

			
				
QQ群里面有人问起这个问题: 用loaddata导入数据的时候，在binlog文件中记录的不是insert语句,这样的话，如果用loaddata导入数据，当需要恢复数据库的时候binlog恢复就不行了
...

			
		

	





	

		

			

				
					
mysql load data 原理_MySQL 执行load data infile时同步原理及注意事项(额外一点补充)...

				
			

			

				

					weixin_39883440的博客
				

				

					01-27
					
					411
					
				

			

		

		

			
				
MySQL 执行load data infile时同步原理及注意事项(额外一点补充)1)在客户端也可以向远程MySQL服务器执行 ‘load data‘ 命令，比如，客户端IP： 192.168.204.132服务器IP： 192.168.204.131可以在192.168.204.132上，执行命令： mysql -h 192.168.204.131 -utest -ptest test -e'...

			
		

	



		

			
		



	

		

			

				
					
深入浅出MySQL(十一)关于mysql里面的bin-log日志

				
			

			

				

					Danny_idea的博客
				

				

					01-22
					
					184
					
				

			

		

		

			
				
mysql里面的bin-log日志，相信大家都有过相应的了解，但是对于bin-log日志而言，具体的内容是做什么的，今天小编特意抽空学习了一下，下边是我的笔记心得。
主从同步的一个基本原理
MySQL的二进制日志可以说是MySQL最重要的日志了，它记录了所有的DDL和DML(除了数据查询语句)语句，以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。
由于在主从架构...

			
		

	





	

		

			

				
					
mysql load 记录日志_load data方式导入的数据不可以用binlog日志进行恢复，因为binlo_MySQL...

				
			

			

				

					weixin_39684454的博客
				

				

					01-27
					
					1005
					
				

			

		

		

			
				
bitsCN.comload data方式导入的数据不可以用binlog日志进行恢复，因为binlog里面不产生insert sql语句QQ群里面有人问起这个问题:  用load data 导入数据的时候，在binlog文件中记录的不是insert 语句,这样的话，如果用load data 导入数据，当需要恢复数据库的时候  binlog恢复就不行了load data local infile '...

			
		

	





	

		

			

				
					
mysql中的Load data的使用方法

				
			

			

				

					09-10
				

			

		

		

			
				
其中，`LOAD DATA`语句被广泛应用于将文本文件中的数据加载到数据库表中，尤其适用于大规模数据集的导入场景。本文将详细介绍`LOAD DATA`的使用方法，包括其基本语法、参数选项以及常见应用场景等。  #### 一、基本...

			
		

	





	

		

			

				
					
mysql Load Data InFile 的用法

				
			

			

				

					12-15
				

			

		

		

			
				
MySQL的`LOAD DATA INFILE`语句是一种高效的数据导入工具，用于将文本文件中的数据批量导入到数据库表中。这个功能对于处理大量数据时非常有用，因为它比使用`INSERT INTO`语句快得多。以下是对`LOAD DATA INFILE`...

			
		

	





	

		

			

				
					
DataLoad v4.2gziolHH.

				
			

			

				

					12-26
				

			

		

		

			
				
DataLoad是一款广泛应用于数据加载和数据迁移的工具，主要用于数据库管理和大数据处理。在这个场景中，我们讨论的是DataLoad的v4.2版本，文件名标识为"DataLoad v4.2gziolHH"，并且提供了对应的zip压缩包，表明这是...

			
		

	





	

		

			

				
					
mysql binlog 数据介绍

				
			

			

				

					lyj1101066558的博客
				

				

					06-21
					
					1009
					
				

			

		

		

			
				
1.binlog 基础介绍
binlog 文件包含控制事件 ：Format_description（文件头） 事件跟 Rotate 事件（文件尾）以及一些事件组，slave 在复制事件的时候，只会完整的复制一个事件
每个 binlog 事件由 4 部分组成：
通用头：包含事件的基本信息，其中最重要的字段的是事件类型和事件的大小
提交头：提交头与特定的事件类型有关，对于不同的事件类型，该字段

			
		

	





	

		

			

				
					
load data 方式导入的数据不可以用binlog日志进行恢复，因为binlog里面不产生insert sql语句。...

				
			

			

				

					系统 运维
				

				

					07-18
					
					190
					
				

			

		

		

			
				
里面有人问起这个问题: 用loaddata导入数据的时候，在binlog文件中记录的不是insert语句,这样的话，如果用loaddata导入数据，当需要恢复数据库的时候binlog恢复就不行了

load data local infile '/root/table.txt' into table test.table ;
生成binlog 日志
然后用mysqlbinlog解析binl...

			
		

	





	

		

			

				
					
Mysql中load data infile主从复制注意点

				
			

			

				

					sawen21的专栏
				

				

					04-22
					
					1636
					
				

			

		

		

			
				
本文转自我的原创blog:http://www.javali.org/document/tips-on-replication-in-load-data-infile-in-mysql.html




在mysql官方文档的描述里，load data infile加载数据要比普通的insert快20倍，可以说是插入数据最快的方式了，所以在很多场合都会用它来提升入库速度。

那它在主

			
		

	





	

		

			

				
					
MySQL- 'load data infile'语句

				
			

			

				

					weixin_33975951的博客
				

				

					03-03
					
					1268
					
				

			

		

		

			
				
基本语法：
load data [low_priority] [local] infile 'file_name txt' [replace | ignore]
into table tbl_name
[fields
[terminated by't']
[OPTIONALLY] enclosed by '']
[escaped by'\' ]]
[lines...

			
		

	





	

		

			

				
					
mysql load data 原理_一篇比较详细的MySQL的复制原理及配置（转）

				
			

			

				

					weixin_35415045的博客
				

				

					02-01
					
					841
					
				

			

		

		

			
				
mysql的数据库的高可用性的架构大概有以下几种：集群，读写分离，主备。而后面两种都是通过复制来实现的。下面将简单介绍复制的原理及配置，以及一些常见的问题。一。复制的原理MySQL复制基于主服务器在二进制日志中跟踪所有对数据库的更改(更新、删除等等)。每个从服务器从主服务器接收主服务器已经记录到其二进制日志的保存的更新，以便从服务器可以对其数据拷贝执行相同的更新。将主服务器的数据拷贝到从服务器的一...

			
		

	





	

		

			

				
					
mysql主从 load data_合理使用mysql中的load data infile导入数据

				
			

			

				

					weixin_39633134的博客
				

				

					01-19
					
					161
					
				

			

		

		

			
				
基本语法：load data [low_priority] [local] infile 'file_name txt' [replace | ignore]into table tbl_name[fields[terminated by't'][OPTIONALLY] enclosed by ''][escaped by'' ]][lines terminated by'n'][ignore ...

			
		

	





	

		

			

				
					
MySQL LOAD DATA 语句的一些主从一致性测试

				
			

			

				

					老叶茶馆
				

				

					10-29
					
					620
					
				

			

		

		

			
				
导读作者：沃趣-罗小波沃趣科技高级数据库技术专家，主要负责MySQL RDS产品的原型与架构设计。熟悉MySQL体系结构，Innodb存储引擎，喜好专研开源技术，追求完美...

			
		

	





	

		

			

				
					
分布式Redis主备复制

				
			

			

				

					weixin_34376562的博客
				

				

					01-07
					
					77
					
				

			

		

		

			
				
　　当数据落在不同节点上时，如何保证数据节点之间的一致性是非常关键的　　Redis采用主备复制的方式保证一致性，所有节点中，只有一个节点为主节点(master)，它对外提供写服务，然后异步的将数据复制到其他节点上　　主备复制流程　　Redis包含master 和slave 2种节点：　　master 对外提供写服务　　slave 节点作为master的数据备份，不可以提供写服务　　主备复制由mas...

			
		

	





	

		

			

				
					
【Postgresql源码分析之一】主备同步复制介绍

				
			

			

				

					
				

				

					07-24
					
					1172
					
				

			

		

		

			
				
postgresql主备热备
postgresql支持主机和备机以及级联之间的热备，即通过先进行一次基础备份，让其拥有相同的基础数据，然后通过传输XLOG日志的方式，在备机或者级联备机上REDO模拟主机上的操作，保证他们之间数据的一致性。目前热备传输方式有下面两种;
异步复制（async）：主机上产生xlog日志，只需要将该xlog日志发送给备机就返回给客户端结果，不关心备机是否接收到该日志

			
		

	





	

		

			

				
					
简述通过loaddata语句向指定数据表加载数据文件的注意事项

					
最新发布

				
			

			

				

					06-08
				

			

		

		

			
				
通过loaddata语句向指定数据表加载数据文件时，需要注意以下事项：  1. 数据文件必须符合指定格式，通常为CSV、JSON等格式。数据文件的列数和数据类型必须与目标表的结构一致。  2. 数据文件中的每一行数据必须以...

			
		

	



              




          




        



    





    



      

      

        
      

      





	

		评论	
	

	
	




  

    

      添加红包
      
    

    

      

        
        

          
          
        

        
请填写红包祝福语或标题

      

      

        
        

          
          个
        

        
红包个数最小为10个

      

      

        
        

          
          元
        

        
红包金额最低5元

      

      

        
        

          当前余额3.43元
          前往充值 >
        

      

      

        

          需支付：10.00元
        

        
        
      

    

  





  

    
    
  

  

    
    
  








  

    

      

        

          

            
            
成就一亿技术人!

          

          

        

        

          

          

            领取后你会自动成为博主和红包主的粉丝
            规则
          

        

      

      

        

          

            
              
            
            

              
hope_wisdom
 发出的红包
            

          

        

        

          

          

          

        

      

    

    

  



      
      

      
实付元

      
使用余额支付

      

        

          

            
            点击重新获取
          

        

        
扫码支付

      

      

        
          
            
          
          
        
      

      

        
        钱包余额
          0
          

            
            

              

                
抵扣说明：

                
 1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
 2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

              

            

          

      

      余额充值