开发者社区> miles_wong> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

mysqlbinlog

简介: 软件版本 mysql> select version(); +------------+ | version() | +------------+ | 5.6.27-log | +------------+ 1 row in set (0.00 sec) 官方文档地址 ROW模式 优点: row level模式下,bin-log中可以不记录执行的sql语句的上下文相关的信息,仅仅只需要记录那一条记录被修改了,修改成什么样了。
+关注继续查看

软件版本

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.27-log |
+------------+
1 row in set (0.00 sec)

官方文档地址

ROW模式

优点:
row level模式下,bin-log中可以不记录执行的sql语句的上下文相关的信息,仅仅只需要记录那一条记录被修改了,修改成什么样了。所以row level的日志内容会非常清楚的记录下每一行数据修改的细节。且不会出现某些特定情况下的存储过程,或function,以及 trigger的调用和触发无法被正确复制的问题。
缺点:
row level模式下,所有的执行的语句当记录到日志中的时候,都将以每行记录的修改来记录,这样可能会产生大量的日志内容,比如有这样一条update语句:update product set owner_member_id = ‘b’ where owner_member_id = ‘a’,执行之后,日志中记录的不是这条update语句所对应额事件(MySQL以事件的形式来记录bin-log日志),而是这条语句所更新的每一条记录的变化情况,这样就记录成很多条记录被更新的很多个事件。自然,bin-log日志的量就会很大。尤其是当执行alter table之类的语句的时候,产生的日志量是惊人的。因为MySQL对于alter table之类的表结构变更语句的处理方式是整个表的每一条记录都需要变动,实际上就是重建了整个表。那么该表的每一条记录都会被记录到日志中。

binlog状态

mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-bin.000028
         Position: 1082
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

测试数据

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql>  SET SESSION binlog_format = 'ROW';
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from t1 where  id >5;
Query OK, 2 rows affected (0.01 sec)

mysql>  insert into t1 values (6,'name6',6),(7,'name7',7);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> update t1 set name='nameX' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

查看binlog

# mysqlbinlog -v  --base64-output=decode-rows -v --start-position=1082 --database test  mysql-bin.000028
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 1082
#160729 21:01:49 server id 168030  end_log_pos 1154 CRC32 0x8731195e    Query   thread_id=7     exec_time=1     error_code=0
SET TIMESTAMP=1469797309/*!*/;
SET @@session.pseudo_thread_id=7/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
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=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 1154
#160729 21:01:49 server id 168030  end_log_pos 1203 CRC32 0x295e098d    Table_map: `test`.`t1` mapped to number 72
# at 1203
#160729 21:01:49 server id 168030  end_log_pos 1268 CRC32 0xcb0bab01    Delete_rows: table id 72 flags: STMT_END_F
### DELETE FROM `test`.`t1`
### WHERE
###   @1=6 /* INT meta=0 nullable=0 is_null=0 */
###   @2='name6' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
###   @3=6 /* INT meta=0 nullable=0 is_null=0 */
### DELETE FROM `test`.`t1`
### WHERE
###   @1=7 /* INT meta=0 nullable=0 is_null=0 */
###   @2='name7' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
###   @3=7 /* INT meta=0 nullable=0 is_null=0 */
# at 1268
#160729 21:01:56 server id 168030  end_log_pos 1317 CRC32 0x8b73fec3    Table_map: `test`.`t1` mapped to number 72
# at 1317
#160729 21:01:56 server id 168030  end_log_pos 1382 CRC32 0x8df0f07c    Write_rows: table id 72 flags: STMT_END_F
### INSERT INTO `test`.`t1`
### SET
###   @1=6 /* INT meta=0 nullable=0 is_null=0 */
###   @2='name6' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
###   @3=6 /* INT meta=0 nullable=0 is_null=0 */
### INSERT INTO `test`.`t1`
### SET
###   @1=7 /* INT meta=0 nullable=0 is_null=0 */
###   @2='name7' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
###   @3=7 /* INT meta=0 nullable=0 is_null=0 */
# at 1382
#160729 21:02:31 server id 168030  end_log_pos 1431 CRC32 0x32c40fac    Table_map: `test`.`t1` mapped to number 72
# at 1431
#160729 21:02:31 server id 168030  end_log_pos 1497 CRC32 0x2ec069d8    Update_rows: table id 72 flags: STMT_END_F
### UPDATE `test`.`t1`
### WHERE
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='name2' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
###   @3=10 /* INT meta=0 nullable=0 is_null=0 */
### SET
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='nameX' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
###   @3=10 /* INT meta=0 nullable=0 is_null=0 */
# at 1497
#160729 21:04:27 server id 168030  end_log_pos 1528 CRC32 0xe9081ac6    Xid = 170
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

STATEMENT模式

优点:
statement level下的优点首先就是解决了row level下的缺点,不需要记录每一行数据的变化,减少bin-log日志量,节约IO,提高性能。因为他只需要记录在Master上所执行的语句的细节,以及执行语句时候的上下文的信息。
缺点:
由于他是记录的执行语句,所以,为了让这些语句在slave端也能正确执行,那么他还必须记录每条语句在执行的时候的一些相关信息,也就是上下文信息,以保证所有语句在slave端杯执行的时候能够得到和在master端执行时候相同的结果。另外就是,由于MySQL现在发展比较快,很多的新功能不断的加入,使MySQL得复制遇到了不小的挑战,自然复制的时候涉及到越复杂的内容,bug也就越容易出现。在statement level下,目前已经发现的就有不少情况会造成MySQL的复制出现问题,主要是修改数据的时候使用了某些特定的函数或者功能的时候会出现,比如:sleep()函数在有些版本中就不能真确复制,在存储过程中使用了last_insert_id()函数,可能会使slave和master上得到不一致的id等等。由于row level是基于每一行来记录的变化,所以不会出现类似的问题。

binlog状态

mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-bin.000028
         Position: 1528
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

测试数据

mysql> SET SESSION binlog_format = 'STATEMENT';
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql>  delete from t1 where  id >5;
Query OK, 2 rows affected (0.01 sec)

mysql> insert into t1 values (6,'name6',6),(7,'name7',7);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> update t1 set name='nameX' where id=2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

查看binlog

# mysqlbinlog  -v --start-position=1528 --database test  mysql-bin.000028
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#700101  8:00:00 server id 168030  end_log_pos 120 CRC32 0x3562200b     Start: binlog v 4, server v 5.6.27-log created 700101  8:00:00
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
AAAAAA9ekAIAdAAAAHgAAAABAAQANS42LjI3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAQsg
YjU=
'/*!*/;
# at 1528
#160729 21:17:24 server id 168030  end_log_pos 1607 CRC32 0xf0b1a6ec    Query   thread_id=7     exec_time=0     error_code=0
SET TIMESTAMP=1469798244/*!*/;
SET @@session.pseudo_thread_id=7/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
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=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 1607
#160729 21:17:24 server id 168030  end_log_pos 1708 CRC32 0x91b80f3a    Query   thread_id=7     exec_time=0     error_code=0
use `test`/*!*/;
SET TIMESTAMP=1469798244/*!*/;
delete from t1 where  id >5
/*!*/;
# at 1708
#160729 21:17:34 server id 168030  end_log_pos 1831 CRC32 0x281b83d4    Query   thread_id=7     exec_time=0     error_code=0
SET TIMESTAMP=1469798254/*!*/;
insert into t1 values (6,'name6',6),(7,'name7',7)
/*!*/;
# at 1831
#160729 21:17:40 server id 168030  end_log_pos 1942 CRC32 0xdc3b8c4e    Query   thread_id=7     exec_time=0     error_code=0
SET TIMESTAMP=1469798260/*!*/;
update t1 set name='nameX' where id=2
/*!*/;
# at 1942
#160729 21:17:45 server id 168030  end_log_pos 1973 CRC32 0x43ef8f69    Xid = 178
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

MIXED模式

Mixed模式,可以理解为是前两种模式的结合。
Mixed模式下,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式,也就是在Statement和Row之间选择一种。
新版本中的Statment level还是和以前一样,仅仅记录执行的语句。而新版本的MySQL中队row level模式也被做了优化,并不是所有的修改都会以row level来记录,像遇到表结构变更的时候就会以statement模式来记录,如果sql语句确实就是update或者delete等修改数据的语句,那么还是会记录所有行的变更。

binlog状态

mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-bin.000028
         Position: 1973
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

测试数据

mysql> SET SESSION binlog_format = 'MIXED';
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from t1 where  id >5;
Query OK, 2 rows affected (0.01 sec)

mysql> insert into t1 values (6,'name6',6),(7,'name7',7);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> update t1 set name='nameX' where id=2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

查看binlog

# mysqlbinlog  -v --start-position=1973 --database test  mysql-bin.000028
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#700101  8:00:00 server id 168030  end_log_pos 120 CRC32 0x3562200b     Start: binlog v 4, server v 5.6.27-log created 700101  8:00:00
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
AAAAAA9ekAIAdAAAAHgAAAABAAQANS42LjI3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAQsg
YjU=
'/*!*/;
# at 1973
#160729 21:21:20 server id 168030  end_log_pos 2052 CRC32 0xa7bda1f9    Query   thread_id=7     exec_time=0     error_code=0
SET TIMESTAMP=1469798480/*!*/;
SET @@session.pseudo_thread_id=7/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
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=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 2052
#160729 21:21:20 server id 168030  end_log_pos 2153 CRC32 0xd9247bfd    Query   thread_id=7     exec_time=0     error_code=0
use `test`/*!*/;
SET TIMESTAMP=1469798480/*!*/;
delete from t1 where  id >5
/*!*/;
# at 2153
#160729 21:21:24 server id 168030  end_log_pos 2276 CRC32 0x8e0e641c    Query   thread_id=7     exec_time=0     error_code=0
SET TIMESTAMP=1469798484/*!*/;
insert into t1 values (6,'name6',6),(7,'name7',7)
/*!*/;
# at 2276
#160729 21:21:28 server id 168030  end_log_pos 2387 CRC32 0x03317cda    Query   thread_id=7     exec_time=0     error_code=0
SET TIMESTAMP=1469798488/*!*/;
update t1 set name='nameX' where id=2
/*!*/;
# at 2387
#160729 21:21:31 server id 168030  end_log_pos 2418 CRC32 0x21f01c0d    Xid = 186
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
MySQL binlog
binlog是Mysql sever层维护的一种二进制日志,与innodb引擎中的redo/undo log是完全不同的日志;其主要是用来记录对mysql数据更新或潜在发生更新的SQL语句,并以"事务"的形式保存在磁盘中。
763 0
使用mysqlbinlog报错
不知道怎么了,今天使用mysqlbinlog这个命令查看数据库的日志文件,出现了如下报错: [root@Darren1 logs]# mysqlbinlog -vv --base64-output=decode-rows mysql-bin.
1111 0
+关注
miles_wong
Java核心技术 数据存储与数据库 分布式系统与计算 系统研发与运维 编程语言
87
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载