阅读 1654

mysql binlog 基础 & 实战

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)

  1. statement base

日志中包含了引起数据变更的sql,例如insert、update、delete等。

缺点:只保留了sql变更,强依赖各个sql的上下文关系,强依赖主从间的完全一致,例如主从复制时可能会有函数表现不一致(例如sleep,current_timestamp)。

  1. row base(since mysql 5.1)

日志中包含了有效的行变更

缺点:保留了数据变更,会产生大量的数据文件,例如一条update语句产生的binlog可能都会是巨量的。

  1. 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进行通信。

主从同步

  1. 启动三台实例,进行相应的端口映射
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
复制代码

数据恢复

  1. 当前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的日志格式。

5. 参考文档

官方文档:dev.mysql.com/doc/interna…

下一篇:如何用go实现“container”

文章分类
后端