mysql binlog 基础 & 实战
摘抄自： https://juejin.cn/post/6892397821152985102?utm_source=gold_browser_extension#heading-8
1. binlog简介
binlog在mysql 3.23.14被首次引入，是 mysql server 实例产生的二进制日志，记录的是mysql所有更新数据及可能更新数据(例如一个未命中任何行的delete语句)的语句，binlog与mysql使用的存储引擎无关，无论使用innodb还是myisam，都会产生相同的binlog。
binlog还包括其他的一些元数据，例如：
- 重新生成语句的mysql server 的状态信息
- Error code
- 维护bin log所需要的元数据，例如 rotate events
binlog是mysql在一系列操作中维护的mysql server的全局状态的变更链路。更通俗一点的说，bin log描述了用于重现mysql server上曾经发生过的所有全局状态变更事件。
类型
有三种binlog类型，分别是基于sql语句的日志（statement base）、基于行的日志（row base）、混合日志（mixed base）
- statement base
日志中包含了引起数据变更的sql，例如insert、update、delete等。
缺点：只保留了sql变更，强依赖各个sql的上下文关系，强依赖主从间的完全一致，例如主从复制时可能会有函数表现不一致（例如sleep，current_timestamp）。
- row base（since mysql 5.1）
日志中包含了有效的行变更
缺点：保留了数据变更，会产生大量的数据文件，例如一条update语句产生的binlog可能都会是巨量的。
- mixed base
默认会使用statement日志，在必要的情况下会切换到row日志。
用途
主从同步
mysql支持高并发的关键，主从同步一般也都会和读写分离进行绑定，主库用于写入，从库们进行读取，这样可以用从库分担大量的读请求，提升mysql整体的性能。binlog记录了mysql master节点的数据变更。slave节点通过订阅的方式，定期同步master的bin log记录，slave节点将未执行的binlog记录到relay log 中，relay log和bin log的格式相同。slave节点通过对relay log进行解析、执行，来重现在mysql master节点上发生的变更。
Mysql 主从同步流程
开启log-bin选项
要保证开启log-bin选项，mysql才会写入binlog，如下
mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/mysql-bin |
| 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为OFF状态，在/etc/mysql/mysql.conf.d/mysqld.cnf 中 写入
复制代码log-bin=on
数据备份/恢复
复制代码部分的数据恢复。在mysql用备份文件进行还原后，将会重新执行在备份文件后记录的bin log，这些bin log 保证了mysql 数据不会丢，数据备份。
2. 实战主从同步 & 数据恢复
以下示例使用 docker for mac，后续可以出一期docker的基本使用😁
使用的mysql版本：mysql 5.6.49-log
拓扑图
采用一主二从的架构，当然原理是相同的，同样可以配置二主二从，多主多从等等。
由于docker for mac的限制，无法直接使用容器的ip+port与mac进行通信，所以采用将mysql容器的3306端口映射到mac上进行通信，而docker内部依然使用容器的ip进行通信。
主从同步
- 启动三台实例，进行相应的端口映射
docker run --name mysql_m -e MYSQL_ROOT_PASSWORD=1234 -d -P -p 32000:3306 mysql:5.6
docker run --name mysql_s1 -e MYSQL_ROOT_PASSWORD=1234 -d -P -p 32001:3306 mysql:5.6
docker run --name mysql_s2 -e MYSQL_ROOT_PASSWORD=1234 -d -P -p 32002:3306 mysql:5.6
查看是否开启bin log,log_bin 若为OFF状态则未开启bin_log
复制代码mysql -h0.0.0.0 -P32000 -uroot -p1234 -se "show variables like '%log_bin%'"
Variable_name Value
log_bin OFF
log_bin_basename
log_bin_index
log_bin_trust_function_creators OFF
log_bin_use_v1_row_events OFF
sql_log_bin ON
m开启bin_log，并配置server_id
复制代码docker exec -it mysql_m bash //进入容器
apt-get update && apt-get install vim //安装vim
vim /etc/mysql/mysql.conf.d/mysqld.cnf //进入配置文件
配置文件路径：/etc/mysql/mysql.conf.d/mysqld.cnf
复制代码在[mysqld]下追加两行
log-bin = binlog #开启bin log,并且设置bin log文件名
server-id = 1 #主从同步机器的唯一标识
s1、s2配置server-id
复制代码配置文件路径：/etc/mysql/mysql.conf.d/mysqld.cnf
在[mysqld]下追加
server-id = 2 #s1 配置
复制代码server-id = 3 #s2 配置
重启各个实例
复制代码docker restart mysql_m
docker restart mysql_s1
docker restart mysql_s2
m创建账号并赋予replacation和client权限用于主从同步
复制代码create user 'slave'@'%' identified by 'slave';
grant replication slave, replication client on *.* to 'slave'@'%';
s1、s2 master指向 m,并开启同步
复制代码change master to master_host='172.17.0.2', master_user='slave', master_password='slave', master_port=3306;
start slave;
在m创建test库，创建user表,并插入一条数据，不出意外的话，在从库也会自动复现上述数据
复制代码create database test;
create table user(
id int,
name varchar(100)
);
insert into user values(1,"ab");
上述mysql_m, mysql_s1已经push到了docker hub，感兴趣的可以down一下: )
复制代码docker push ifndef666/mysql_m:v1
docker push ifndef666/mysql_s1:v1
复制代码数据恢复
- 当前test 库内有两条数据
mysql> select * from user;
+------+------+
| id | name |
+------+------+
| 1 | ab |
| 2 | abc |
+------+------+
2 rows in set (0.00 sec)
记录当前binlog位置，使用show master status
复制代码mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000005 | 336 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
插入一个id为3的数据
复制代码mysql> insert into user values(3,'abcd')
-> ;
Query OK, 1 row affected (0.02 sec)
mysql> select * from user;
+------+------+
| id | name |
+------+------+
| 1 | ab |
| 2 | abc |
| 3 | abcd |
+------+------+
3 rows in set (0.00 sec)
再次记录当前binlog位置
复制代码mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000005 | 554 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
删除id为3的数据
复制代码delete from user where id = 3;
mysql> delete from user where id = 3;
Query OK, 1 row affected (0.01 sec)
mysql> select * from user;
+------+------+
| id | name |
+------+------+
| 1 | ab |
| 2 | abc |
+------+------+
2 rows in set (0.00 sec)
登录到实例，导出指定位置binlog数据
复制代码root@55f5cfce56c8:/var/lib/mysql# mysqlbinlog --start-position=336 --stop-position=554 binlog.000005 > backup.sql
恢复数据
复制代码source /var/lib/mysql/backup.sql
...
mysql> select * from user;
+------+------+
| id | name |
+------+------+
| 1 | ab |
| 2 | abc |
| 3 | abcd |
+------+------+
3 rows in set (0.00 sec)
总结
复制代码恢复数据其实就是将mysql binlog指定区间内的sql再次执行一遍。当然，恢复过程也会产生binlog，也可以再恢复你的恢复，emmmm，禁止套娃😸。
3. binlog格式解析
bin log组成
-
包含一系列的binlog日志文件和一个索引文件
-
每个日志文件包含一个4个的"magic number"，其后紧跟着一系列的数据变更描述事件
-
这4个"magic number" 分别是
0xfe 0x62 0x69 0x6e -
每一个事件包含了事件header和header后的data
- header描述了事件的类型、产生时间、由哪个server产生等等
- data则是指定事件的具体描述，例如部分的数据变更
-
第一个事件指定了当前binlog文件使用的格式化版本
-
其余的事件则记录了数据变更
-
最后的事件指向下一个日志文件，维护binlog间的关联关系
-
-
索引文件则是binlog文件的一个list
实战
下面可以到我们配置的mysql_m实例下去查看一下binlog文件的具体格式
ps:为了方便阅读，以下隐掉了一些无用的信息 我们配置的binlog文件目录路径在这里：/var/lib/mysql 进入指定目录，可以查看一下目录结构
ls -l
-rw-rw---- 1 mysql mysql 143 Nov 2 05:57 binlog.000001
-rw-rw---- 1 mysql mysql 143 Nov 2 05:58 binlog.000002
-rw-rw---- 1 mysql mysql 1540 Nov 3 12:38 binlog.000003
-rw-rw---- 1 mysql mysql 80 Nov 6 05:51 binlog.index
复制代码可以看到有1个索引文件binlog.index和3个binlog日志文件
root@55f5cfce56c8:/var/lib/mysql# cat binlog.index ./binlog.000001 ./binlog.000002 ./binlog.000003 binlog索引文件的组成还是非常简单的，维护了binlog的日志链
下面我们查看我们现在正在使用的binlog日志文件 使用show master status 查看当前在使用的文件，可以看在目前在用的文件时binlog.00000
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000003 | 1540 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
复制代码查看一下binlog文件，发现文件内都是一些 “乱码” cat binlog.000003
\bin��_tx5.6.49-log��_8
���_�"@std!!
mysqlCREATE USER 'slave'@'%' IDENTIFIED BY PASSWORD '*51125B3597BEE0FC43E0BCBFEE002EF8641B44CF'�A�_��*@std!!
root%
mysqlGRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%'lj�u�_�9*@std!!
root%
mysqlgrant replication slave, replication client on *.* to 'slave'@'%'V��)��_^!@std!!
testtestcreate database testZ}��_x !@std!!
testtestcreate table user(
id int,
name varchar(100)
)��_���_O^ !@std!!
testtestBEGIN�[2���_n� !@std!!
testtestinsert into user values(1,"小明")o*��_�K3ї��_O: !@std
testtestBEGIN
$����_�� !@std
testtestUPDATE `user` SET `name` = '1' WHERE `id` = '1' AND `name` = '??' LIMIT 1u�_J��_�XO�g��_O; !@std
testtestBEGIN�����_�� !@std
testtestUPDATE `user` SET `name` = 'ab' WHERE `id` = '1' AND `name` = '1' LIMIT 1��4|��_�ZKzY�[O�_Nj�j
复制代码其实这也是binlog日志的由来，为了数据压缩效率上的提升，binlog对一些标志位采用了特定的二进制的编码，同样可以使用上文提到的binlog维护工具-mysqlbinlog。 使用mysqlbinlog查看文件：
#201102 5:58:29 server id 1 end_log_pos 120 CRC32 0xe47fb0f4 Start: binlog v 4, server v 5.6.49-log created 201102 5:58:29 at startup
mysqlbinlog binlog.000003
#201103 5:50:16 server id 1 end_log_pos 663 CRC32 0x7d035a14 Query thread_id=7 exec_time=0 error_code=0
SET TIMESTAMP=1604382616/*!*/;
create database test
/*!*/;
# at 663
#201103 5:51:50 server id 1 end_log_pos 783 CRC32 0xf45ff780 Query thread_id=9 exec_time=0 error_code=0
use `test`/*!*/;
SET TIMESTAMP=1604382710/*!*/;
create table user(
id int,
name varchar(100)
)
/*!*/;
# at 783
#201103 5:55:34 server id 1 end_log_pos 862 CRC32 0xa4325bec Query thread_id=9 exec_time=0 error_code=0
SET TIMESTAMP=1604382934/*!*/;
BEGIN
/*!*/;
# at 862
#201103 5:55:34 server id 1 end_log_pos 972 CRC32 0x14052a6f Query thread_id=9 exec_time=0 error_code=0
SET TIMESTAMP=1604382934/*!*/;
insert into user values(1,"小明")
/*!*/;
# at 972
#201103 5:55:34 server id 1 end_log_pos 1003 CRC32 0x97d11933 Xid = 75
COMMIT/*!*/;
# at 1003
#201103 5:56:01 server id 1 end_log_pos 1082 CRC32 0xede6240a Query thread_id=9 exec_time=0 error_code=0
SET TIMESTAMP=1604382961/*!*/;
/*!\C latin1 *//*!*/;
复制代码可以看到，我们在配置主从同步时的sql都有在binlog中记录下来，因此，依靠binlog，mysql slave节点就可以复现出master节点的变更。
4. 总结
以上简单介绍了下binlog是什么，其次使用binlog实战了主从同步和数据恢复，最终简单介绍了下binlog的日志格式。
作者：绝了
链接：https://juejin.cn/post/6892397821152985102
来源：掘金
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。
