mysql小白系列_04 binlog(未完)

mysql打开、查看、清理binlog

1.开启日志

log_bin=/var/lib/mysql/mysql-bin
mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             |
| log_bin_basename                | /var/lib/mysql/ON              |
| log_bin_index                   | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                            |
| log_bin_use_v1_row_events       | OFF                            |
| sql_log_bin                     | ON                             |
+---------------------------------+--------------------------------+
 rows in set (0.00 sec)

log_bin=ON 打开binlog日志
log_bin_basename=/var/lib/mysql/mysql-bin binlog日志的基本文件名，后面会追加标识来表示每一个文件
log_bin_index=/var/lib/mysql/mysql-bin.index binlog文件的索引文件，这个文件管理了所有的binlog文件的目录

2.binlog日志模式

默认如下：

mysql> show variables like '%binlog_format%';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
 row in set (0.00 sec)

• 复制模式一：基于SQL语句的复制（statement-based replication,SBR）
• 复制模式二：基于行的复制（row-based replication,RBR）
• 复制模式三：以上两种模式的混合模式（mixed-based replication,MBR）

分别对应binlog三种模式：

• statement（SBR) 记录每一条修改数据的sql语句到binlog中
  优点： 不需要记录每一条sql语句和每一行的数据变化，减少binlog日志量，节省IO，提高性能
  缺点：需要记录上下文信息；某些存储过程、函数无法正确复制

• ROW（RBR)
  优点：不记录上下文信息，仅记录哪条数据被修改成什么样子，可以正确复制存储过程、函数、触发器
  缺点：每一条数据记录都记录，占用空间、消耗IO性能

• MIXED（MBR）
  以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。

3.保留时间，默认永远不删除

mysql> show variables like '%expire_logs%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days |      |
+------------------+-------+
 row in set (0.00 sec)

4.查看当前使用的binlog和position

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

6.查看binlog的内容

• mysql命令直接查看，不指定就是第一个binlog日志文件

mysql> show binlog events;
+------------------+-----+-------------+-----------+-------------+-----------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                    |
+------------------+-----+-------------+-----------+-------------+-----------------------------------------+
| mysql-bin. |    | Format_desc |          |          | Server ver: 5.6.-log, Binlog ver:    |
| mysql-bin. |  | Query       |          |          | BEGIN                                   |
| mysql-bin. |  | Query       |          |          | use `db1`; insert into t1(id) values() |
| mysql-bin. |  | Xid         |          |          | COMMIT /* xid=35 */                     |
| mysql-bin. |  | Rotate      |          |          | mysql-bin.;pos=                  |
+------------------+-----+-------------+-----------+-------------+-----------------------------------------+
 rows in set (0.00 sec)

• mysql命令查看指定binlog文件

mysql>  show binlog events in 'mysql-bin.000007';
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                  |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysql-bin. |    | Format_desc |          |          | Server ver: 5.6.-log, Binlog ver:  |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
 row in set (0.00 sec)  

• 或者shell环境指定binlog文件

[root@docker01 mysql]# mysqlbinlog mysql-bin.
/*!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
# :: server id   end_log_pos  CRC32 0x77f71e56  Start: binlog v , server v 5.6.-log created  ::
BINLOG '
orxuWg8BAAAAdAAAAHgAAAAAAAQANS42LjM5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAVYe
93c=
'/*!*/;
# at
# :: server id   end_log_pos  CRC32 0x8fbc381e  Query   thread_id=     exec_time=     error_code=
use `db2`/*!*/;
SET TIMESTAMP=/*!*/;
SET @@session.pseudo_thread_id=/*!*/;
SET @@session.foreign_key_checks=, @@session.sql_auto_is_null=, @@session.unique_checks=, @@session.autocommit=/*!*/;
SET @@session.sql_mode=/*!*/;
SET @@session.auto_increment_increment=, @@session.auto_increment_offset=/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=,@@session.collation_connection=,@@session.collation_server=/*!*/;
SET @@session.lc_time_names=/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create table t1 (id int, name varchar())
/*!*/;
# at
# :: server id   end_log_pos  CRC32 0x6e5df96f  Query   thread_id=     exec_time=     error_code=
SET TIMESTAMP=/*!*/;
BEGIN
/*!*/;
# at
# :: server id   end_log_pos  CRC32 0x6ed4b068  Query   thread_id=     exec_time=     error_code=
SET TIMESTAMP=/*!*/;
insert into t1 values(,'yzw')
/*!*/;
# at
# :: server id   end_log_pos  CRC32 0xeeb12b2a  Xid =
COMMIT/*!*/;
# at
# :: server id   end_log_pos  CRC32 0x6ca7654c  Rotate to mysql-bin.  pos:
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

其他查询方式：

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin. |        |
| mysql-bin. |        |
| mysql-bin. |        |
| mysql-bin. |        |
+------------------+-----------+
 rows in set (0.00 sec)

a）查询第一个(最早)的binlog日志：
mysql> show binlog events\G;

b）指定查询 mysql-bin.000002这个文件：
mysql> show binlog events in 'mysql-bin.000002'\G;

c）指定查询 mysql-bin.000002这个文件，从pos点:624开始查起：
mysql> show binlog events in 'mysql-bin.000002' from 624\G;

d）指定查询 mysql-bin.000002这个文件，从pos点:624开始查起，查询10条（即10条语句）
mysql> show binlog events in 'mysql-bin.000002' from 624 limit 10\G;

e）指定查询 mysql-bin.000002这个文件，从pos点:624开始查起，偏移2行（即中间跳过2个），查询10条
mysql> show binlog events in 'mysql-bin.000002' from 624 limit 2,10\G;

7.删除binlog

• reset master; 删除master的binlog
• reset slave; 删除slave的中继日志
• reset slave all; ??
• purge master logs before 'YYYY-mm-DD HH:MM:SS'; 基于时间点的日志文件删除
• purge master logs to 'mysql-bin.000002'; 删除到指定binlog文件前的所有日志文件，不包括指定的binlog文件

8.mysqlbinlog help

Mysqlbinlog解析工具

Mysqlbinlog功能是将Mysql的binlog日志转换成Mysql语句，默认情况下binlog日志是二进制文件，无法直接查看。

参数	描述
-d	指定库的binlog
-r	相当于重定向到指定文件
--start-position--stop-position	按照指定位置精确解析binlog日志（精确），如不接--stop-positiion则一直到binlog日志结尾
--start-datetime--stop-datetime	按照指定时间解析binlog日志（模糊，不准确），如不接--stop-datetime则一直到binlog日志结尾

备注：myslqlbinlog分库导出binlog，如使用-d参数，更新数据时必须使用use database。 例：解析ceshi数据库的binlog日志并写入my.sql文件

#mysqlbinlog -d ceshi mysql-bin.000003 -r my.sql

使用位置精确解析binlog日志

mysqlbinlog mysql-bin.000003 --start-position=100  --stop-position=200 -r my.sql

9.使用binlog恢复数据

mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码 数据库名

基于时间或者scn的恢复
mysqlbinlog mysql-bin.000009 --start-position 154 --stop-position 755 | mysql -uroot -p mytest

--start-datetime：从二进制日志中读取指定等于时间戳或者晚于本地计算机的时间
--stop-datetime：从二进制日志中读取指定小于时间戳或者等于本地计算机的时间 取值和上述一样
--start-position：从二进制日志中读取指定position 事件位置作为开始。
--stop-position：从二进制日志中读取指定position 事件位置作为事件截至

---

binlog格式

https://dev.mysql.com/doc/internals/en/binary-log-versions.html
5.0以后，用的是第四版本的binlog格式

binlog events https://dev.mysql.com/doc/internals/en/binlog-event.html

+=====================================+
| event  | timestamp         0 : 4    |
| header +----------------------------+
|        | type_code         4 : 1    | = FORMAT_DESCRIPTION_EVENT = 15
|        +----------------------------+
|        | server_id         5 : 4    |
|        +----------------------------+
|        | event_length      9 : 4    | >= 91
|        +----------------------------+
|        | next_position    13 : 4    |
|        +----------------------------+
|        | flags            17 : 2    |
+=====================================+
| event  | binlog_version   19 : 2    | = 4
| data   +----------------------------+
|        | server_version   21 : 50   |
|        +----------------------------+
|        | create_timestamp 71 : 4    |
|        +----------------------------+
|        | header_length    75 : 1    |
|        +----------------------------+
|        | post-header      76 : n    | = array of n bytes, one byte per event
|        | lengths for all            |   type that the server knows about
|        | event types                |
+=====================================+

v4 event sturcture

+=====================================+
| event  | timestamp         0 : 4    |
| header +----------------------------+
|        | type_code         4 : 1    |
|        +----------------------------+
|        | server_id         5 : 4    |
|        +----------------------------+
|        | event_length      9 : 4    |
|        +----------------------------+
|        | next_position    13 : 4    |
|        +----------------------------+
|        | flags            17 : 2    |
|        +----------------------------+
|        | extra_headers    19 : x-19 |
+=====================================+
| event  | fixed part        x : y    |
| data   +----------------------------+
|        | variable part              |
+=====================================+

单独在一个binlog插入一条记录，查看binlong的内容 insert into t1 values (2);

00000000  fe 62 69 6e fa 40 7d 5a  0f 69 08 00 00 74 00 00  |.bin.@}Z.i...t..|
00000010  00 78 00 00 00 00 00 04  00 35 2e 36 2e 33 39 2d  |.x.......5.6.39-|
00000020  6c 6f 67 00 00 00 00 00  00 00 00 00 00 00 00 00  |log.............|
00000030  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
00000040  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 13  |................|
00000050  38 0d 00 08 00 12 00 04  04 04 04 12 00 00 5c 00  |8.............\.|
00000060  04 1a 08 00 00 00 08 08  08 02 00 00 00 0a 0a 0a  |................|
00000070  19 19 00 01 36 37 2b d4  32 41 7d 5a 04 69 08 00  |....67+.2A}Z.i..|
00000080  00 2f 00 00 00 a7 00 00  00 00 00 04 00 00 00 00  |./..............|
00000090  00 00 00 6d 79 73 71 6c  2d 62 69 6e 2e 30 30 30  |...mysql-bin.000|
000000a0  30 30 38 0d 40 ad 3f                              |008.@.?|
000000a7

第一部分4个字节

• 最开始的4个字节magic number：fe62696e 前4个字节是固定的magic number

通过十六进制转ASCII如下（http://www.ab126.com/goju/1711.htm/）

A binary log file begins with a 4-byte magic number followed by an initial descriptor event that identifies the format of the file. In v4, it is called a "format description event."

第二部分19个字节的事件头部(event header)

https://dev.mysql.com/doc/internals/en/format-description-event.html

• 0-4字节时间戳(timestamp 0 : 4)：fa 40 7d 5a ，小字节序存储应该是：5a7d40fa

通过十六进制转十进制获取时间戳是：1518158074，北京时间：2018/2/9 下午2:34:34
十六进制转十进制工具：https://tool.lu/hexconvert/
 时间戳转北京时间工具：http://tools.sharejs.com/unixtime.html

• 1字节类型码(type_code 4 : 1)：0f ，表示FORMAT_DESCRIPTION_EVENT

其他字节码含义参考：https://dev.mysql.com/doc/internals/en/binlog-event-type.html

• 4字节server_id(server_id 5 : 4)：69 08 00 00，小字节序存储：00000869,转10进制是：2153

mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     |   |
+---------------+-------+
 row in set (0.00 sec)

• 4字节事件长度(event_length 9 : 4)：74 00 00 00，小字节序存储：00000074,转10进制是：116
• 4字节下一个事件起始位置(next_position 13 : 4)：78 00 00 00，小字节序存储：00000078,转10进制是：120
• 2个字节的flags(flags 17 : 2):00 00 ,binlog已关闭

其他flag含义参考：https://dev.mysql.com/doc/internals/en/binlog-event-flag.html
00 01表示binlog还在使用

• extra_headers为空

第三部分事件数据(event data) 事件数据有固定数据fixed data和可变数据variable data组成

https://dev.mysql.com/doc/internals/en/event-data-for-specific-event-types.html
84个字节长度的Fixed data

• Variable data

http://www.cnblogs.com/ryan0003/articles/8404065.html event header http://www.ab126.com/goju/1711.html https://tool.lu/hexconvert/https://www.jianshu.com/p/c16686b35807 https://dev.mysql.com/doc/internals/en/binary-log-versions.htmlhttps://dev.mysql.com/doc/internals/en/binlog-event-header.html https://dev.mysql.com/doc/internals/en/binlog-event.htmlhttp://tools.sharejs.com/unixtime.html