mysql二进制日志格式介绍

Binary  Logging  Formats
Binary log有三种format：
--binlog-format=STATEMENT：基于statement，顾名思义，不管对数据库有没有影响数据的sql statement都会存储在binary log里面
--binlog-format=ROW：基于row，是对表的行数据有影响的sql statement 存储在binary log里面
--binlog-format=MIXED：基于mixed，默认情况下使用statement-based logging，但是某些场合下logging mode会自动转化为 row -based

1. 基于statement的binlog

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

mysql> show variables like '%binlog%';
+-----------------------------------------+----------------------+
| Variable_name                           | Value                |
+-----------------------------------------+----------------------+
| binlog_cache_size                       | 32768                |
| binlog_checksum                         | CRC32                |
| binlog_direct_non_transactional_updates | OFF                  |
| binlog_error_action                     | IGNORE_ERROR         |
| binlog_format                           | STATEMENT            |
| binlog_gtid_simple_recovery             | OFF                  |
| binlog_max_flush_queue_time             | 0                    |
| binlog_order_commits                    | ON                   |
| binlog_row_image                        | FULL                 |
| binlog_rows_query_log_events            | OFF                  |
| binlog_stmt_cache_size                  | 32768                |
| binlogging_impossible_mode              | IGNORE_ERROR         |
| innodb_api_enable_binlog                | OFF                  |
| innodb_locks_unsafe_for_binlog          | OFF                  |
| max_binlog_cache_size                   | 18446744073709547520 |
| max_binlog_size                         | 1073741824           |
| max_binlog_stmt_cache_size              | 18446744073709547520 |
| simplified_binlog_gtid_recovery         | OFF                  |
| sync_binlog                             | 0                    |
+-----------------------------------------+----------------------+
19 rows in set (0.00 sec)



mysql> insert into emp select * from emp;
Query OK, 1048576 rows affected (35.34 sec)
Records: 1048576  Duplicates: 0  Warnings: 0

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000003 |       167 |
| mysql-bin.000004 |       167 |
| mysql-bin.000005 |       337 |
+------------------+-----------+
3 rows in set (0.00 sec)

[root@mysqldb mysql]# mysqlbinlog mysql-bin.000005>mysql-bin.000005.txt

/*!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
#150817  5:46:35 server id 1  end_log_pos 120 CRC32 0x7dbdc343  Start: binlog v 4, server v 5.6.23-enterprise-commercial-advanced-log created 150817  5:46:35
BINLOG '
e63RVQ8BAAAAdAAAAHgAAAAAAAQANS42LjIzLWVudGVycHJpc2UtY29tbWVyY2lhbC1hZHZhbmNl
ZC1sb2cAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAUPD
vX0=
'/*!*/;
# at 120
#150817  5:46:52 server id 1  end_log_pos 199 CRC32 0x264835a1  Query   thread_id=5     exec_time=35    error_code=0
SET TIMESTAMP=1439804812/*!*/;
SET @@session.pseudo_thread_id=5/*!*/;
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=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 199
#150817  5:46:52 server id 1  end_log_pos 306 CRC32 0xd1e24c8f  Query   thread_id=5     exec_time=35    error_code=0
use `test`/*!*/;
SET TIMESTAMP=1439804812/*!*/;
insert into emp select * from emp
/*!*/;
# at 306
#150817  5:46:52 server id 1  end_log_pos 337 CRC32 0xb17220e6  Xid = 47
COMMIT/*!*/;
# at 337
#150817  5:47:55 server id 1  end_log_pos 384 CRC32 0x8ad6fdc8  Rotate to mysql-bin.000006  pos: 4
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;


2. 基于row的binlog
日志中会记录成每一行数据被修改的形式，然后在 slave 端再对相同的数据进行修改。

优点：在 row 模式下，bin-log 中可以不记录执行的 SQL 语句的上下文相关的信息，仅仅只需要记录那一条记录被修改了，修改成什么样了。所以 row 的日志内容会非常清楚的记录下每一行数据修改的细节，非常容易理解。而且不会出现某些特定情况下的存储过程或 function ，以及 trigger 的调用和触发无法被正确复制的问题。

缺点：在 row 模式下，所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容

mysql> show variables like '%binlog%';
+-----------------------------------------+----------------------+
| Variable_name                           | Value                |
+-----------------------------------------+----------------------+
| binlog_cache_size                       | 32768                |
| binlog_checksum                         | CRC32                |
| binlog_direct_non_transactional_updates | OFF                  |
| binlog_error_action                     | IGNORE_ERROR         |
| binlog_format                           | ROW                  |
| binlog_gtid_simple_recovery             | OFF                  |
| binlog_max_flush_queue_time             | 0                    |
| binlog_order_commits                    | ON                   |
| binlog_row_image                        | FULL                 |
| binlog_rows_query_log_events            | OFF                  |
| binlog_stmt_cache_size                  | 32768                |
| binlogging_impossible_mode              | IGNORE_ERROR         |
| innodb_api_enable_binlog                | OFF                  |
| innodb_locks_unsafe_for_binlog          | OFF                  |
| max_binlog_cache_size                   | 18446744073709547520 |
| max_binlog_size                         | 1073741824           |
| max_binlog_stmt_cache_size              | 18446744073709547520 |
| simplified_binlog_gtid_recovery         | OFF                  |
| sync_binlog                             | 0                    |
+-----------------------------------------+----------------------+
19 rows in set (0.01 sec)


mysql> flush logs;
Query OK, 0 rows affected (0.08 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000008 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> insert into emp values(100,'aaa');
Query OK, 1 row affected (0.06 sec)

[root@mysqldb mysql]# mysqlbinlog mysql-bin.000008
/*!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
#150817  6:16:37 server id 1  end_log_pos 120 CRC32 0x0b48cd47  Start: binlog v 4, server v 5.6.23-enterprise-commercial-advanced-log created 150817  6:16:37
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
hbTRVQ8BAAAAdAAAAHgAAAABAAQANS42LjIzLWVudGVycHJpc2UtY29tbWVyY2lhbC1hZHZhbmNl
ZC1sb2cAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAUfN
SAs=
'/*!*/;
# at 120
#150817  6:16:58 server id 1  end_log_pos 192 CRC32 0x905d8c11  Query   thread_id=7     exec_time=0     error_code=0
SET TIMESTAMP=1439806618/*!*/;
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=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 192
#150817  6:16:58 server id 1  end_log_pos 241 CRC32 0xc70e6a66  Table_map: `test`.`emp` mapped to number 70
# at 241
#150817  6:16:58 server id 1  end_log_pos 285 CRC32 0xd9fea6a2  Write_rows: table id 70 flags: STMT_END_F


BINLOG '
mrTRVRMBAAAAMQAAAPEAAAAAAEYAAAAAAAEABHRlc3QAA2VtcAACAw8CFAADZmoOxw==
mrTRVR4BAAAALAAAAB0BAAAAAEYAAAAAAAEAAgAC//xkAAAAA2FhYaKm/tk=
'/*!*/;
# at 285
#150817  6:16:58 server id 1  end_log_pos 316 CRC32 0x1a216142  Xid = 89
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10972173/viewspace-1774808/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10972173/viewspace-1774808/