mysql replacation_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)

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的日志格式。