溫馨提示×
您好，登錄后才能下訂單哦！
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
小編給大家分享一下數(shù)據(jù)庫(kù)中l(wèi)oad data語(yǔ)句怎么保證主備復(fù)制數(shù)據(jù)一致性，希望大家閱讀完這篇文章之后都有所收獲，下面讓我們一起去探討吧！
背景
機(jī)器配置
* CPU：16 vcpus
* 磁盤(pán)：100G flash卡 data&binlog混用
* 內(nèi)存：64G
數(shù)據(jù)庫(kù)版本：MySQL 5.7.18
數(shù)據(jù)庫(kù)IP信息：主庫(kù)IP 10.10.30.241，從庫(kù)IP 10.10.30.250
數(shù)據(jù)庫(kù)參數(shù)配置：
*
主庫(kù)：雙一，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'
*
從庫(kù)：雙一，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'
測(cè)試目的：通過(guò)示例演示并查看過(guò)程中binlog中如何記錄load data語(yǔ)句等方式來(lái)驗(yàn)證 load data語(yǔ)句如何保證主備復(fù)制數(shù)據(jù)一致性
*
將分別在四種隔離級(jí)別下（會(huì)話級(jí)別修改tx_isolation）針對(duì)binlog_format（會(huì)話級(jí)別修改binlog_format）的三種格式分別進(jìn)行演示執(zhí)行l(wèi)oad
data語(yǔ)句，觀察MySQL server層如何處理，主庫(kù)binlog中如何記錄load
data語(yǔ)句，備庫(kù)的binlog中如何如何記錄load data語(yǔ)句
* 本文假定你已經(jīng)搭建好了主備復(fù)制環(huán)境，如果未搭建請(qǐng)先自行搭建主備復(fù)制環(huán)境
PS：本文僅針對(duì)測(cè)試目的做驗(yàn)證演示，關(guān)于binlog_format三種格式以及事務(wù)的四種隔離級(jí)別詳細(xì)說(shuō)明請(qǐng)查閱相關(guān)資料，本文不做解讀
* binlog_format參考資料：https://dev.mysql.com/doc/refman/5.7/en/replication-formats.html
* 事務(wù)隔離級(jí)別參考資料：https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html
建庫(kù)
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)
插入測(cè)試數(shù)據(jù)
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) 執(zhí)行select …into outifile語(yǔ)句生成load data需要的數(shù)據(jù)文本文件
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
先在主庫(kù)中把binlog_format修改為statement，隔離級(jí)別修改為read-uncommitted，并刷新主從庫(kù)binlog
# 主庫(kù) 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) # 從庫(kù) mysql> flush binary logs; Query OK, 0 rows affected (0.01 sec) mysql>
主庫(kù)清空表test_load并執(zhí)行l(wèi)oad data語(yǔ)句
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
從以上結(jié)果中可以看到，在RU隔離級(jí)別下，binlog_format設(shè)置為statement不允許load data語(yǔ)句執(zhí)行,從報(bào)錯(cuò)信息上可以看到，在RC和RU隔離級(jí)別下，binlog只能以row格式記錄，所以statement無(wú)法記錄binlog
查看主庫(kù)表test_load中的數(shù)據(jù)
admin@localhost : xiaoboluo 09:41:52> select * from test_load; Empty set (0.00 sec)
從以上結(jié)果中可以看到，表數(shù)據(jù)并沒(méi)有導(dǎo)入成功，現(xiàn)在解析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 ......
從上面的結(jié)果中可以看到，解析binlog的結(jié)果中除了use db和truncate table語(yǔ)句之外，沒(méi)有其他的操作了，說(shuō)明并沒(méi)有數(shù)據(jù)寫(xiě)入，load data語(yǔ)句被mysql server直接拒絕執(zhí)行了
先在主庫(kù)中把binlog_format修改為mixed，隔離級(jí)別不要?jiǎng)樱⑺⑿轮鲝膸?kù)binlog
# 主庫(kù) 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) # 從庫(kù) mysql> flush binary logs; Query OK, 0 rows affected (0.01 sec) mysql>
主庫(kù)清空表test_load并執(zhí)行l(wèi)oad data語(yǔ)句
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
從以上結(jié)果中可以看到，在RU隔離級(jí)別下，binlog_format設(shè)置為mixed允許load data語(yǔ)句執(zhí)行
查看主庫(kù)表test_load中的數(shù)據(jù)
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)
從以上結(jié)果中可以看到，表數(shù)據(jù)導(dǎo)入成功，現(xiàn)在解析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語(yǔ)句在這里，這里是開(kāi)啟了參數(shù)binlog_rows_query_log_events=ON之后記錄的原生sql，默認(rèn)被加了注釋，主備復(fù)制不會(huì)執(zhí)行 # 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` #這里是用于主備復(fù)制的數(shù)據(jù)變更日志，可以看到在mixed格式下被轉(zhuǎn)換為了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的結(jié)果中可以看到，數(shù)據(jù)寫(xiě)入成功，load data語(yǔ)句被轉(zhuǎn)換為row格式記錄在binlog中
查看備庫(kù)中數(shù)據(jù)是否正確復(fù)制
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)
解析備庫(kù)binlog中是如何記錄的load data語(yǔ)句
[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語(yǔ)句在這里，這里是開(kāi)啟了參數(shù)binlog_rows_query_log_events=ON之后記錄的原生sql，默認(rèn)被加了注釋，主備復(fù)制不會(huì)執(zhí)行 # 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` #這里是備庫(kù)記錄的主庫(kù)binlog日志，可以看到在mixed格式下被轉(zhuǎn)換為了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/*!*/; ...
先在主庫(kù)中把binlog_format修改為row，隔離級(jí)別不要?jiǎng)樱⑺⑿轮鲝膸?kù)binlog
# 主庫(kù) 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) # 從庫(kù) mysql> flush binary logs; Query OK, 0 rows affected (0.01 sec) mysql>
主庫(kù)清空表test_load并執(zhí)行l(wèi)oad data語(yǔ)句
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
從以上結(jié)果中可以看到，在RU隔離級(jí)別下，binlog_format設(shè)置為row允許load data語(yǔ)句執(zhí)行
查看主庫(kù)表test_load中的數(shù)據(jù)
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)
從以上結(jié)果中可以看到，表數(shù)據(jù)導(dǎo)入成功，現(xiàn)在解析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語(yǔ)句在這里，這里是開(kāi)啟了參數(shù)binlog_rows_query_log_events=ON之后記錄的原生sql，默認(rèn)被加了注釋，主備復(fù)制不會(huì)執(zhí)行 # 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` #這里是用于主備復(fù)制的數(shù)據(jù)變更日志，可以看到在row格式下被轉(zhuǎn)換為了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的結(jié)果中可以看到，數(shù)據(jù)寫(xiě)入成功，load data語(yǔ)句被轉(zhuǎn)換為row格式記錄在binlog中
查看備庫(kù)中數(shù)據(jù)是否正確復(fù)制
[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語(yǔ)句在這里，這里是開(kāi)啟了參數(shù)binlog_rows_query_log_events=ON之后記錄的原生sql，默認(rèn)被加了注釋，主備復(fù)制不會(huì)執(zhí)行 # 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` #這里是用于主備復(fù)制的數(shù)據(jù)變更日志，可以看到在row格式下被轉(zhuǎn)換為了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)
解析備庫(kù)binlog中是如何記錄的load data語(yǔ)句
[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語(yǔ)句在這里，這里是開(kāi)啟了參數(shù)binlog_rows_query_log_events=ON之后記錄的原生sql，默認(rèn)被加了注釋，主備復(fù)制不會(huì)執(zhí)行 # 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` #這里是備庫(kù)記錄的主庫(kù)binlog日志，可以看到在row格式下被轉(zhuǎn)換為了row格式，與mixed格式下記錄的內(nèi)容相同 ### 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/*!*/; ......
看完了這篇文章，相信你對(duì)“數(shù)據(jù)庫(kù)中l(wèi)oad data語(yǔ)句怎么保證主備復(fù)制數(shù)據(jù)一致性”有了一定的了解，如果想了解更多相關(guān)知識(shí)，歡迎關(guān)注億速云行業(yè)資訊頻道，感謝各位的閱讀！
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號(hào)
手機(jī)網(wǎng)站二維碼