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 | +---------------------------------+--------------------------------+ 6 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 | +---------------+-----------+ 1 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 | 0 | +------------------+-------+ 1 row in set (0.00 sec)
4.查看当前使用的binlog和position
mysql> show master status\G *************************** 1. row *************************** File: mysql-bin.000001 Position: 120 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 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.000004 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.39-log, Binlog ver: 4 | | mysql-bin.000004 | 120 | Query | 1 | 197 | BEGIN | | mysql-bin.000004 | 197 | Query | 1 | 297 | use `db1`; insert into t1(id) values(4) | | mysql-bin.000004 | 297 | Xid | 1 | 328 | COMMIT /* xid=35 */ | | mysql-bin.000004 | 328 | Rotate | 1 | 375 | mysql-bin.000005;pos=4 | +------------------+-----+-------------+-----------+-------------+-----------------------------------------+ 5 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.000007 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.39-log, Binlog ver: 4 | +------------------+-----+-------------+-----------+-------------+---------------------------------------+ 1 row in set (0.00 sec)
- 或者shell环境指定binlog文件
[root@docker01 mysql]# mysqlbinlog mysql-bin.000006 /*!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 #180129 14:18:10 server id 1 end_log_pos 120 CRC32 0x77f71e56 Start: binlog v 4, server v 5.6.39-log created 180129 14:18:10 BINLOG ' orxuWg8BAAAAdAAAAHgAAAAAAAQANS42LjM5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAVYe 93c= '/*!*/; # at 120 #180129 14:18:39 server id 1 end_log_pos 234 CRC32 0x8fbc381e Query thread_id=3 exec_time=0 error_code=0 use `db2`/*!*/; SET TIMESTAMP=1517206719/*!*/; 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=8/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; create table t1 (id int, name varchar(20)) /*!*/; # at 234 #180129 14:19:29 server id 1 end_log_pos 311 CRC32 0x6e5df96f Query thread_id=3 exec_time=0 error_code=0 SET TIMESTAMP=1517206769/*!*/; BEGIN /*!*/; # at 311 #180129 14:19:29 server id 1 end_log_pos 413 CRC32 0x6ed4b068 Query thread_id=3 exec_time=0 error_code=0 SET TIMESTAMP=1517206769/*!*/; insert into t1 values(1,'yzw') /*!*/; # at 413 #180129 14:19:29 server id 1 end_log_pos 444 CRC32 0xeeb12b2a Xid = 54 COMMIT/*!*/; # at 444 #180129 14:19:37 server id 1 end_log_pos 491 CRC32 0x6ca7654c Rotate to mysql-bin.000007 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*/;
其他查询方式：
mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000004 | 375 | | mysql-bin.000005 | 167 | | mysql-bin.000006 | 491 | | mysql-bin.000007 | 120 | +------------------+-----------+ 4 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 | 2153 | +---------------+-------+ 1 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
