mysqlbinlog

软件版本

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*/;
时间： 2024-10-01 16:04:14

mysqlbinlog的相关文章

利用Mysqlbinlog工具恢复MySQL数据库的例子

MYSQL启用日志 [root@jianshe99]# whereis my.ini [root@jianshe99]# vi /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Default to using old password format for compatibility with mysql 3.x # clients (those using the

MYSQL启用日志，查看日志，利用Mysqlbinlog工具恢复MySQL数据库

MYSQL启用日志 [root@jianshe99]# whereis my.ini [root@jianshe99]# vi /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Default to using old password format for compatibility with mysql 3.x # clients (those using the

Mysql通过mysqlbinlog工具恢复误删除数据

一.误删除的数据恢复 1.配置/etc/my.cnf,添加如下参数打开binlog server_id=181 log_bin=binlog binlog_format=ROW 2.创建表及测试数据 mysql> create table test(id int(4),name varchar(22)); Query OK, 0 rows affected (0.07 sec) insert into test values(1,'a'); insert into test values(2,'

使用mysqlbinlog提取二进制日志

    MySQL binlog日志记录了MySQL数据库从启用日志以来所有对当前数据库的变更.binlog日志属于二进制文件,我们可以从binlog提取出来生成可阅读的SQL语句来重建当前数据库以及根据需要实现时点恢复或不完全恢复.本文主要描述了如果提取binlog日志,并给出相关示例.     有关binlog的介绍与描述请参考:MySQL 二进制日志(Binary Log)   1.提取mysqlbinlog的几种方式a.使用show binlog events方式可以获取当前以及指定bi

mysqlbinlog can not parse row based events

    最近的MHA测试过程中,碰到了mysqlbinlog客户端的版本低于服务端版本的问题.即这个错误提示:mysqlbinlog is 3.2 (included in MySQL Client 5.0 or lower), but MySQL server version is 5.6.22-log. mysqlbinlog can not parse row based events.这个应该是个比较常见的错误.主要是由于在安装Linux期间通常在自带安装mysql相关rpm包,后来又安

MySQL数据库恢复(使用mysqlbinlog命令)_Mysql

1:开启binlog日志记录 修改mysql配置文件mysql.ini,在[mysqld]节点下添加 复制代码 代码如下: # log-bin log-bin = E:/log/logbin.log 路径中不要包含中文和空格.重启mysql服务.通过命令行停止和启动mysql服务 复制代码 代码如下: c:\>net stop mysql; c:\>net start mysql; 进入命令行进入mysql并查看二进制日志是否已经启动 Sql代码 复制代码 代码如下: mysql>sho

当mysqlbinlog版本与mysql不一致时可能导致出哪些问题_Mysql

首先要确定当前版本是不是mysqlbinlog版本,当不是mysqlbinlog版本时可能会导致出哪些问题,下面通过模拟场景的方法给大家做介绍,希望对大家有所帮助. 看当前mysqlbinlog版本的方法: mysqlbinlog --version mysqlbinlog Ver 3.3 for Linux at x86_64 场景1:mysql服务器为mysql 5.6,要求mysqlbinlog版本为3.4及以上,否则mysqlbinlog解析时会直接报错,之前已经碰到过很多次,但是没有记

mysql慢查询记录和mysqlbinlog配置教程

mysql可以记录所有查询很慢的记录并把sql语句记录在指定的文件中,也可以通过mysqlbinlog记录添加更新 等记录,也可以通过mysqlbinlog进行数据库还原. mysql慢查询记录 编辑my.cnf文件 linux通常在/etc/my.cnf 或者/usr/local/mysql/etc/my.cnf windows 自己找 在[mysqld]标签在下面加上:  代码如下 复制代码 log-slow-queries=/var/lib/mysql/slow.log #慢日志保存路径,

mysql mysqlbinlog-关于mysqlbinlog的提示错误

问题描述 关于mysqlbinlog的提示错误 在使用mysqlbinlog查看mysql二进制文件时一直提示file ' ' not found , 哪位碰到过类似的问题,给指点一下怎么解决.谢谢 解决方案 问题已经解决. 去掉语句后面的":",就执行成功了.

mysql xtrabackup mysqlbinlog Point-In-Time recovery

恢复到特定的时间点,可以使用innobackupex 和数据库binlog日志 首先,我们需要一个数据库快照,使用innobackupex 生成一个全库备份 # innobackupex --defaults-file=/etc/my.cnf --user root --password XXXX --no-timestamp /home/ssd/ali_backup/full_xtra_3306_20160825/ 模拟测试数据 mysql> create database miles; Qu