本文深入探讨MySQL Binlog的两种模式：statement与row。通过实际案例对比两种模式下Binlog的内容差异，并演示如何恢复数据，揭示不同模式的特点及其对数据一致性的影响。
--在statement模式下
mysql@ogg1:/home/mysql/mysql/data||-->>mysql -uroot -p test
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.23-log Source distribution
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select * from t5;
Empty set (0.00 sec)
mysql> desc t5;
+-------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id | varchar(36) | NO | | NULL | |
| name | varchar(30) | YES | | NULL | |
| birth | datetime | YES | | NULL | |
| score | decimal(18,3) | YES | | NULL | |
+-------+---------------+------+-----+---------+-------+
4 rows in set (0.07 sec)
mysql> show variables like 'binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | STATEMENT |
+---------------+-------+
1 row in set (0.00 sec)
mysql> insert into t5 values(uuid(),'zjadolf',now(),23.456);
Query OK, 1 row affected, 1 warning (0.03 sec)
mysql> insert into t5 values(uuid(),'chenlin',now(),25.456);
Query OK, 1 row affected, 1 warning (0.04 sec)
mysql> select * from t5;
+--------------------------------------+---------+---------------------+--------+
| id | name | birth | score |
+--------------------------------------+---------+---------------------+--------+
| 3429fe4d-7f50-11e4-8196-080027695f02 | zjadolf | 2014-12-09 11:05:17 | 23.456 |
| 3ad0720b-7f50-11e4-8196-080027695f02 | chenlin | 2014-12-09 11:05:28 | 25.456 |
+--------------------------------------+---------+---------------------+--------+
2 rows in set (0.00 sec)
mysql> exit
Bye
mysql@ogg1:/home/mysql/mysql/data||-->>ls
ibdata1 mysql-bin.000001 mysql-bin.000006 mysql-bin.000011 mysql-bin.000016 mysql-bin.000021 ogg1.pid ogg1-relay-bin.000005 ogg1-relay-bin.000010 relay-log.info
ib_logfile0 mysql-bin.000002 mysql-bin.000007 mysql-bin.000012 mysql-bin.000017 mysql-bin.000022 ogg1-relay-bin.000001 ogg1-relay-bin.000006 ogg1-relay-bin.000011 test
ib_logfile1 mysql-bin.000003 mysql-bin.000008 mysql-bin.000013 mysql-bin.000018 mysql-bin.000023 ogg1-relay-bin.000002 ogg1-relay-bin.000007 ogg1-relay-bin.000012
master.info mysql-bin.000004 mysql-bin.000009 mysql-bin.000014 mysql-bin.000019 mysql-bin.index ogg1-relay-bin.000003 ogg1-relay-bin.000008 ogg1-relay-bin.index
mysql mysql-bin.000005 mysql-bin.000010 mysql-bin.000015 mysql-bin.000020 ogg1.err ogg1-relay-bin.000004 ogg1-relay-bin.000009 performance_schema
mysql@ogg1:/home/mysql/mysql/data||-->>mysqlbinlog mysql-bin.000023 |more
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#141209 10:42:00 server id 1 end_log_pos 107 Start: binlog v 4, server v 5.5.23-log created 141209 10:42:00 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
eGGGVA8BAAAAZwAAAGsAAAABAAQANS41LjIzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAB4YYZUEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107
#141209 10:44:16 server id 1 end_log_pos 262 Query thread_id=2 exec_time=1 error_code=0
use test/*!*/;
SET TIMESTAMP=1418093056/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create table t5(id varchar(36) not null,name varchar(30),birth datetime,score decimal(18,3))
/*!*/;
# at 262
#141209 11:05:17 server id 1 end_log_pos 338 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1418094317/*!*/;
SET @@session.time_zone='SYSTEM'/*!*/;
BEGIN
/*!*/;
# at 338
#141209 11:05:17 server id 1 end_log_pos 461 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1418094317/*!*/;
insert into t5 values(uuid(),'zjadolf',now(),23.456) --在statement模式下binlog日志记录的是sql语句
/*!*/;
# at 461
#141209 11:05:17 server id 1 end_log_pos 488 Xid = 17
COMMIT/*!*/;
# at 488
#141209 11:05:28 server id 1 end_log_pos 564 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1418094328/*!*/;
BEGIN
/*!*/;
# at 564
#141209 11:05:28 server id 1 end_log_pos 687 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1418094328/*!*/;
insert into t5 values(uuid(),'chenlin',now(),25.456) --在statement模式下binlog日志记录的是sql语句
/*!*/;
# at 687
#141209 11:05:28 server id 1 end_log_pos 714 Xid = 18
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
--切换到row模式下
mysql@ogg1:/home/mysql/mysql/data||-->>mysql -uroot -p test
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.23-log Source distribution
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show variables like 'binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.00 sec)
mysql> insert into t5 values(uuid(),'sunjian',now(),123.456); --插入2条记录
Query OK, 1 row affected (0.06 sec)
mysql> select * from t5;
+--------------------------------------+----------+---------------------+---------+
| id | name | birth | score |
+--------------------------------------+----------+---------------------+---------+
| 3429fe4d-7f50-11e4-8196-080027695f02 | zjadolf | 2014-12-09 11:05:17 | 23.456 |
| 3ad0720b-7f50-11e4-8196-080027695f02 | chenlin | 2014-12-09 11:05:28 | 25.456 |
| da4b0651-7f50-11e4-8b39-080027695f02 | zhangkai | 2014-12-09 11:09:56 | 45.678 |
| 171278cb-7f51-11e4-8b39-080027695f02 | sunjian | 2014-12-09 11:11:38 | 123.456 |
+--------------------------------------+----------+---------------------+---------+
4 rows in set (0.00 sec)
mysql> exit
Bye
mysql@ogg1:/home/mysql/mysql/data||-->>mysqlbinlog mysql-bin.000024 --因为修改了binlog_format参数重启了mysql服务此处日志序号变为了24
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#141209 11:09:30 server id 1 end_log_pos 107 Start: binlog v 4, server v 5.5.23-log created 141209 11:09:30 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
6meGVA8BAAAAZwAAAGsAAAABAAQANS41LjIzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADqZ4ZUEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107
#141209 11:09:56 server id 1 end_log_pos 183 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1418094596/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.time_zone='SYSTEM'/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 183
# at 233
#141209 11:09:56 server id 1 end_log_pos 233 Table_map: `test`.`t5` mapped to number 33
#141209 11:09:56 server id 1 end_log_pos 326 Write_rows: table id 33 flags: STMT_END_F
BINLOG '
BGiGVBMBAAAAMgAAAOkAAAAAACEAAAAAAAEABHRlc3QAAnQ1AAQPDwz2BiQAHgASAw4=
BGiGVBcBAAAAXQAAAEYBAAAAACEAAAAAAAEABP/wJGRhNGIwNjUxLTdmNTAtMTFlNC04YjM5LTA4
MDAyNzY5NWYwMgh6aGFuZ2thaaxJnX1REgAAgAAAAAAALQKm --可以看到加密的日志记录
'/*!*/;
# at 326
#141209 11:09:56 server id 1 end_log_pos 353 Xid = 3
COMMIT/*!*/;
# at 353
#141209 11:11:38 server id 1 end_log_pos 429 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1418094698/*!*/;
BEGIN
/*!*/;
# at 429
# at 479
#141209 11:11:38 server id 1 end_log_pos 479 Table_map: `test`.`t5` mapped to number 33
#141209 11:11:38 server id 1 end_log_pos 571 Write_rows: table id 33 flags: STMT_END_F
BINLOG '
amiGVBMBAAAAMgAAAN8BAAAAACEAAAAAAAEABHRlc3QAAnQ1AAQPDwz2BiQAHgASAw4=
amiGVBcBAAAAXAAAADsCAAAAACEAAAAAAAEABP/wJDE3MTI3OGNiLTdmNTEtMTFlNC04YjM5LTA4
MDAyNzY5NWYwMgdzdW5qaWFuYkqdfVESAACAAAAAAAB7Acg= --可以看到加密的日志记录
'/*!*/;
# at 571
#141209 11:11:38 server id 1 end_log_pos 598 Xid = 8
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
这是加密的格式，可以使用如下方式查看sql
mysql@ogg1:/home/mysql/mysql/data||-->>mysqlbinlog --base64-output=decode-rows -v mysql-bin.000024
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#141209 11:09:30 server id 1 end_log_pos 107 Start: binlog v 4, server v 5.5.23-log created 141209 11:09:30 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
# at 107
#141209 11:09:56 server id 1 end_log_pos 183 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1418094596/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.time_zone='SYSTEM'/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 183
# at 233
#141209 11:09:56 server id 1 end_log_pos 233 Table_map: `test`.`t5` mapped to number 33
#141209 11:09:56 server id 1 end_log_pos 326 Write_rows: table id 33 flags: STMT_END_F
### INSERT INTO test.t5
### SET
### @1='da4b0651-7f50-11e4-8b39-080027695f02' --可以看到插入的数据尤其是id列 uuid存入的是值，而不是statement状态下的uuid()
### @2='zhangkai'
### @3=2014-12-09 11:09:56
### @4=000000045.678000000
# at 326
#141209 11:09:56 server id 1 end_log_pos 353 Xid = 3
COMMIT/*!*/;
# at 353
#141209 11:11:38 server id 1 end_log_pos 429 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1418094698/*!*/;
BEGIN
/*!*/;
# at 429
# at 479
#141209 11:11:38 server id 1 end_log_pos 479 Table_map: `test`.`t5` mapped to number 33
#141209 11:11:38 server id 1 end_log_pos 571 Write_rows: table id 33 flags: STMT_END_F
### INSERT INTO test.t5
### SET
### @1='171278cb-7f51-11e4-8b39-080027695f02' --可以看到插入的数据尤其是id列 uuid存入的是值，而不是statement状态下的uuid()
### @2='sunjian'
### @3=2014-12-09 11:11:38
### @4=000000123.456000000
# at 571
#141209 11:11:38 server id 1 end_log_pos 598 Xid = 8
COMMIT/*!*/;
# at 598
#141209 11:16:23 server id 1 end_log_pos 666 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1418094983/*!*/;
BEGIN
/*!*/;
# at 666
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
看到了日志的差异后，我们通过恢复数据测试下statement状态下和row状态的差异
##首先是statement生成的日志mysql-bin.000023
mysql@ogg1:/home/mysql/mysql/data||-->>mysqlbinlog --start-date='2014-12-09 11:05:01' mysql-bin.000023 |mysql -uroot -p123456 test
mysql@ogg1:/home/mysql/mysql/data||-->>mysql -uroot -p test
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.5.23-log Source distribution
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select * from t5;
+--------------------------------------+---------+---------------------+--------+
| id | name | birth | score |
+--------------------------------------+---------+---------------------+--------+
| 2a580932-7f52-11e4-8b39-080027695f02 | zjadolf | 2014-12-09 11:05:17 | 23.456 |
| 2a5fb626-7f52-11e4-8b39-080027695f02 | chenlin | 2014-12-09 11:05:28 | 25.456 | --恢复后的数据
+--------------------------------------+---------+---------------------+--------+
2 rows in set (0.00 sec)
发现插入了两条数据，和我们刚才执行的操作一样，但是有个问题啊，和刚才插入的数据一对比问题来了，id值变动了:
| 3429fe4d-7f50-11e4-8196-080027695f02 | zjadolf | 2014-12-09 11:05:17 | 23.456 |
| 3ad0720b-7f50-11e4-8196-080027695f02 | chenlin | 2014-12-09 11:05:28 | 25.456 | --之前插入的数据
和binlog日志记录的一样基于statement日志记录的只是执行sql语句，不是具体的插入数值，如果要记录数值的话应该设置成row格式;
insert into t5 values(uuid(),'zjadolf',now(),23.456) --恢复的时候uuid()重新生成了
##下面试下row格式下的恢复日志文件应该是mysql-binlog.000024
mysql@ogg1:/home/mysql/mysql/data||-->>mysqlbinlog --base64-output=decode-rows -v mysql-bin.000024 |mysql -uroot -p test
Enter password:
恢复后查看数据,再对比之前插入的记录
--恢复后的记录
| da4b0651-7f50-11e4-8b39-080027695f02 | zhangkai | 2014-12-09 11:09:56 | 45.678 |
| 171278cb-7f51-11e4-8b39-080027695f02 | sunjian | 2014-12-09 11:11:38 | 123.456 |
--之前插入记录
| da4b0651-7f50-11e4-8b39-080027695f02 | zhangkai | 2014-12-09 11:09:56 | 45.678 |
| 171278cb-7f51-11e4-8b39-080027695f02 | sunjian | 2014-12-09 11:11:38 | 123.456 |
发现一模一样,这样就印证了采用row模式的binlog记录模式，记录下来的是数据库中存的什么值就是什么值！
通过这个例子就能明白statement row模式的区别了!
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
