浅析MySQL二上制日志

查MySQL二进制文件中之始末来半点种方法

  1.  mysqlbinlog

  2.  SHOW BINLOG EVENTS [IN ‘log_name’] [FROM pos] [LIMIT
    [offset,] row_count]

 

脚来测试一下,在mysql中实行如下操作

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

mysql> insert into test.t1 values(1,'a');
Query OK, 1 row affected (0.00 sec)

mysql> use test
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> insert into test.t1 values(2,'b');
Query OK, 1 row affected (0.00 sec)

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

 

针对诺二进制日志中之内容如下

通过mysqlbinlog查看

# mysqlbinlog mysql-bin.000021

/*!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
#160817  4:53:02 server id 1  end_log_pos 120 CRC32 0xf9bbe803     Start: binlog v 4, server v 5.6.31-log created 160817  4:53:02
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
Ln2zVw8BAAAAdAAAAHgAAAABAAQANS42LjMxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAQPo
u/k=
'/*!*/;

# at 120
#160817  4:53:06 server id 1  end_log_pos 195 CRC32 0x0182ee55     Query    thread_id=3    exec_time=0    error_code=0
SET TIMESTAMP=1471380786/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
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 195
#160817  4:53:06 server id 1  end_log_pos 298 CRC32 0xf9049380     Query    thread_id=3    exec_time=0    error_code=0
SET TIMESTAMP=1471380786/*!*/;
insert into test.t1 values(1,'a')
/*!*/;

# at 298
#160817  4:53:06 server id 1  end_log_pos 329 CRC32 0xdb58b5b4     Xid = 25
COMMIT/*!*/;

# at 329
#160817  4:53:15 server id 1  end_log_pos 408 CRC32 0xcc370a55     Query    thread_id=3    exec_time=0    error_code=0
SET TIMESTAMP=1471380795/*!*/;
BEGIN
/*!*/;

# at 408
#160817  4:53:15 server id 1  end_log_pos 515 CRC32 0x4fa06a6e     Query    thread_id=3    exec_time=0    error_code=0
use `test`/*!*/;
SET TIMESTAMP=1471380795/*!*/;
insert into test.t1 values(2,'b')
/*!*/;

# at 515
#160817  4:53:15 server id 1  end_log_pos 546 CRC32 0x5f51e8bd     Xid = 33
COMMIT/*!*/;

DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

 

分析如下:

  1. at xxx,不仅仅是事件初步的岗位,同样是二进制日志的大体大小

    譬如上述日志中,结束位置是end_log_pos
546,则二上制日志的大大小小也是546.

# ll mysql-bin.000021
-rw-rw---- 1 mysql mysql 546 Aug 17 04:53 mysql-bin.000021

 

  1. at
    4,对应的风波类是FORMAT_DESCRIPTION_EVENT,是所有binlog文件中之率先个事件,在一个binlog中只出现相同坏,MySQL会冲FORMAT_DESCRIPTION_EVENT事件之概念来解析binlog中之其它事件。该事件类型定义了binlog版本,MySQL
    Server的本子,binlog的创建时间等于。

 

  1. at
    120,是首先个工作开始之偏移量,对应的事件类是QUERY_EVENT,实际上为只有实行了一个BEGIN操作。

 

  1. 下面来截取一个事件来探视

    # at 195
    #160817 4:53:06 server id 1 end_log_pos 298 CRC32 0xf9049380 Query thread_id=3 exec_time=0 error_code=0
    SET TIMESTAMP=1471380786/!/;
    insert into test.t1 values(1,’a’)
    /!/;

   该事件对应之风波类是QUERY_EVENT

   QUERY_EVENT类型的轩然大波屡见不鲜以偏下几栽状态下行使。

   1> 事务开始时之BEGIN操作

   2> 对于STATEMENT格式的DML操作

   3> 对于ROW格式的DDL操作。

   该事件会指明server_id,slave_proxy_id(会话的线程id),execution
time(查询从开头履行及记录到binlog所消费的时日,单位吗秒),error-code(错误码),status-vars(status-vars是盖键值对的款型保存起来的如出一辙多重由SET命令设置的上下文信息,譬如当前的时光戳),schema(当前摘的数据库),query(原生的DML语句,譬如insert
into test.t1 values(1,’a’))

 

  1. 同是insert操作,一个没有切换schema,直接执行insert into test.t1
    values(1,’a’),一个凡先use
    test,再实行insert操作,反映在binlog中之情节吗非雷同,实际上,这会影响根据库的片段复制的论断逻辑。

 

6.
在实践基于binlog的一对恢复时,截止之光阴点应该是commit操作的end_log_pos,而无是commit操作前的之at
xxx。

    譬如,针对地方的commit操作

# at 515
#160817  4:53:15 server id 1  end_log_pos 546 CRC32 0x5f51e8bd     Xid = 33
COMMIT/*!*/;

    如果要是执行第二单insert语句,则–stop-position=546,而非是515。

 

7. 于为此mysqlbinlog查看binlog后还见面带来及ROLLBACK操作,这个以实行基于binlog的一对恢复时,会发因此处。

    # mysqlbinlog –stop-position=515 mysql-bin.000021

....
# at 408
#160817  4:53:15 server id 1  end_log_pos 515 CRC32 0x4fa06a6e     Query    thread_id=3    exec_time=0    error_code=0
use `test`/*!*/;
SET TIMESTAMP=1471380795/*!*/;
insert into test.t1 values(2,'b')
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

 
 我独自以到binlog偏移量为515之职务,这个上偏偏发insert操作,而没有针对该操作的commit,所以mysqlbinlog会显式增加一个rollback操作,直接回滚事务。

 

通过SHOW
BINLOG EVENTS查看

透过这种艺术查看或蛮直观的

mysql> show binlog events in 'mysql-bin.000021';
+------------------+-----+-------------+-----------+-------------+-----------------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                          |
+------------------+-----+-------------+-----------+-------------+-----------------------------------------------+
| mysql-bin.000021 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.31-log, Binlog ver: 4         |
| mysql-bin.000021 | 120 | Query       |         1 |         195 | BEGIN                                         |
| mysql-bin.000021 | 195 | Query       |         1 |         298 | insert into test.t1 values(1,'a')             |
| mysql-bin.000021 | 298 | Xid         |         1 |         329 | COMMIT /* xid=25 */                           |
| mysql-bin.000021 | 329 | Query       |         1 |         408 | BEGIN                                         |
| mysql-bin.000021 | 408 | Query       |         1 |         515 | use `test`; insert into test.t1 values(2,'b') |
| mysql-bin.000021 | 515 | Xid         |         1 |         546 | COMMIT /* xid=33 */                           |
+------------------+-----+-------------+-----------+-------------+-----------------------------------------------+
7 rows in set (0.00 sec)

 

事务之操作是何时写副到binlog中之?

MySQL使用binlog_cache_mngr结构来缓存一个工作的持有操作,如果用户执行commit操作,则拿binlog_cache_mngr中之始末写副到binlog中;如果用户执行rollback操作,则直丢弃binlog_cache_mngr中的始末。否则的话,如果事情中的操作就写副到binlog中,那么在回滚时就相当麻烦。

这生某些用注意的凡,对于非事务的贮存引擎,所有的改动会及时写副到binlog中。

诸如下面的测试着,t_myisam是myisam表,t1是innodb表,在片布置表中分头插入一长长的记下,再实践回滚。

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

mysql> insert into t_myisam values(1,'a');
Query OK, 1 row affected (0.07 sec)

mysql> insert into t1 values(4,'d');
Query OK, 1 row affected (0.06 sec)

mysql> rollback;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+---------------------------------------------------------------+
| Level   | Code | Message                                                       |
+---------+------+---------------------------------------------------------------+
| Warning | 1196 | Some non-transactional changed tables couldn't be rolled back |
+---------+------+---------------------------------------------------------------+

而经查看binlog日志的情节,即便该业务回滚了,针对t_myisam表的操作还是写入到binlog中了

mysql> show binlog events in 'mysql-bin.000017';
+------------------+-----+-------------+-----------+-------------+------------------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                           |
+------------------+-----+-------------+-----------+-------------+------------------------------------------------+
| mysql-bin.000017 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.31-log, Binlog ver: 4          |
| mysql-bin.000017 | 120 | Query       |         1 |         199 | BEGIN                                          |
| mysql-bin.000017 | 199 | Query       |         1 |         307 | use `test`; insert into t_myisam values(1,'a') |
| mysql-bin.000017 | 307 | Query       |         1 |         387 | COMMIT                                         |
+------------------+-----+-------------+-----------+-------------+------------------------------------------------+
4 rows in set (0.00 sec)

 

binlog的相关参数

max_binlog_size

指定binlog文件的轻重缓急,如果手上binlog文件的大大小小及了参数指定的阀值,则会创造一个新的binlog文件。

只顾:binlog文件的深浅或者会见超越max_binlog_size的价值,因为一个事务所发出的有事件都不能不使记录在跟一个binlog文件被,所以就算binlog文件之尺寸超过max_binlog_size的价值,也会等于交即事情的富有操作全部勾副到binlog文件中才能够切换。

 

sql_log_bin

对话变量,设置sql_log_bin=0表示禁用当前对话的binlog功能。

 

sync_binlog

MySQL
5.7.7前,默认为0,即binlog文件于历次写副内容后连无会见即刻持久化到磁盘中,具体的持久化操作交给操作系统去处理。如果操作系统崩溃,可能造成对binlog的改动丢失。

以避免这种情形,可拿sync_binlog设置为1,这样在历次事务提交时,该事务的操作写副到binlog后,都见面调用fsync操作将binlog的修改并到磁盘中。但这么会减低MySQL的特性,所以只是将sync_binlog设置为N,代表N个事务后才行同一涂鸦fsync操作。

事实上,在引入binlog group
commit后,上述持久化的单位并无是工作了,而是同组工作。

官档解释如下:

Controls the number of binary log commit groups to collect before synchronizing the binary log to disk. When sync_binlog=0, the binary log is never synchronized to disk, and when sync_binlog is set to a value greater than 0 this number of binary log commit groups is periodically synchronized to disk. When sync_binlog=1, all transactions are synchronized to the binary log before they are committed.

 

参考

  1. MariaDB原理和实现

2. MySQL 5.7 Reference Manual

 

网站地图xml地图