深入解析 MySQL binlog
2020-03-28 本文已影响0人
技术灭霸
一、介绍
MySQL的二进制日志可以说是MySQL最重要的日志了，它记录了所有的DDL和DML(除了数据查询语句)语句，以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。
binlog是Mysql sever层维护的一种二进制日志，与innodb引擎中的redo/undo log是完全不同的日志；其主要是用来记录对mysql数据更新或潜在发生更新的SQL语句，并以"事务"的形式保存在磁盘中；
作用：
- 复制：MySQL Replication在Master端开启binlog，Master把它的二进制日志传递给slaves并回放来达到master-slave数据一致的目的
- 数据恢复：通过mysqlbinlog工具恢复数据
- 增量备份
二、binlog管理
1、开启binlog日志
vi编辑打开mysql配置文件
# vi /usr/local/mysql/etc/my.cnf
在[mysqld] 区块
设置/添加 log-bin=mysql-bin 确认是打开状态(值 mysql-bin 是日志的基本名或前缀名)；
重启mysqld服务使配置生效
# pkill mysqld
# /usr/local/mysql/bin/mysqld_safe --user=mysql &
2、reset master 清空binlog日志文件
三、binlog内容
默认情况下binlog日志是二进制格式，无法直接查看。可使用两种方式进行查看
1、 mysqlbinlog
mysqlbinlog: /usr/bin/mysqlbinlog mysql-bin.000007
- mysqlbinlog是mysql官方提供的一个binlog查看工具，
- 也可使用–read-from-remote-server从远程服务器读取二进制日志，
- 还可使用--start-position --stop-position、--start-time= --stop-time精确解析binlog日志
截取位置1190-1352 binlog如下：
# at 1190 //事件的起点
#171223 21:56:26 server id 123 end_log_pos 1190 CRC32 0xf75c94a7 Intvar
SET INSERT_ID=2/*!*/;
#171223 21:56:26 server id 123 end_log_pos 1352 CRC32 0xefa42fea Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1514123786/*!*/; //开始事务的时间起点 (每个at即为一个event)
insert into tb_person set name="name__2", address="beijing", sex="man", other="nothing" //sql语句
/*!*/;
# at 1352
#171223 21:56:26 server id 123 end_log_pos 1383 CRC32 0x72c565d3 Xid = 5 //执行时间，及位置戳，Xid:事件指示提交的XA事务
2、直命令行解析
SHOW BINLOG EVENTS
- [IN 'log_name'] //要查询的binlog文件名
- [FROM pos]
- [LIMIT [offset,] row_count]
1190-135如下：mysql> show binlog events in 'mysql-bin.000007' from 1190 limit 2\G
*************************** 13. row ***************************
Log_name: mysql-bin.000007
Pos: 1190
Event_type: Query //事件类型
Server_id: 123
End_log_pos: 1352 //结束pose点，下个事件的起点
Info: use `test`; insert into tb_person set name="name__2", address="beijing", sex="man", other="nothing"
*************************** 14. row ***************************
Log_name: mysql-bin.000007
Pos: 1352
Event_type: Xid
Server_id: 123
End_log_pos: 1383
Info: COMMIT /* xid=51 */
四、binlog格式
Mysql binlog日志有ROW,Statement,MiXED三种格式：
- statement模式下，每一条会修改数据的sql都会记录在binlog中。不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。由于sql的执行是有上下文的，因此在保存的时候需要保存相关的信息，同时还有一些使用了函数之类的语句无法被记录复制。
- row级别下，不记录sql语句上下文相关信息，仅保存哪条记录被修改。记录单元为每一行的改动，基本是可以全部记下来但是由于很多操作，会导致大量行的改动(比如alter table)，因此这种模式的文件保存的信息太多，日志量太大。
- mixed，一种折中的方案，普通操作使用statement记录，当无法使用statement的时候使用row。
可通过my.cnf配置文件及 ==set global binlog_format='ROW/STATEMENT/MIXED'== 进行修改
命令行 ==show variables like 'binlog_format'== 命令查看binglog格式
此外，新版的MySQL中对row级别也做了一些优化，当表结构发生变化的时候，会记录语句而不是逐行记录。
五、使用binlog实现主从复制
复制是mysql最重要的功能之一，mysql集群的高可用、负载均衡和读写分离都是基于复制来实现的；
从5.6开始复制有两种实现方式，基于binlog和基于GTID（全局事务标示符）；
本文接下来将介绍基于binlog的一主一从复制；其复制的基本过程如下：
- Master将数据改变记录到二进制日志(binary log)中
- Slave上面的IO进程连接上Master，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的日志内容
- Master接收到来自Slave的IO进程的请求后，负责复制的IO进程会根据请求信息读取日志指定位置之后的日志信息，返回给Slave的IO进程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息已经到Master端的bin-log文件的名称以及bin-log的位置
- Slave的IO进程接收到信息后，将接收到的日志内容依次添加到Slave端的relay-log文件的最末端，并将读取到的Master端的 bin-log的文件名和位置记录到master-info文件中，以便在下一次读取的时候能够清楚的告诉Master从某个bin-log的哪个位置开始往后的日志内容
- Slave的Sql进程检测到relay-log中新增加了内容后，会马上解析relay-log的内容成为在Master端真实执行时候的那些可执行的内容，并在自身执行
接下来使用实例演示基于binlog的主从复制：
1、配置master
主要包括设置复制账号，并授予REPLICATION SLAVE权限，具体信息会存储在于master.info文件中，及开启binlog；
mysql> CREATE USER 'test'@'%' IDENTIFIED BY '123456';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'test'@'%';
mysql> show variables like "log_bin";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
查看master当前binlogmysql状态：mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
建表插入数据：
CREATE TABLE `tb_person` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(36) NOT NULL,
`address` varchar(36) NOT NULL DEFAULT '',
`sex` varchar(12) NOT NULL DEFAULT 'Man' ,
`other` varchar(256) NOT NULL ,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
insert into tb_person set name="name1", address="beijing", sex="man", other="nothing";
insert into tb_person set name="name2", address="beijing", sex="man", other="nothing";
insert into tb_person set name="name3", address="beijing", sex="man", other="nothing";
insert into tb_person set name="name4", address="beijing", sex="man", other="nothing";
2、配置slave
Slave的配置类似master，需额外设置relay_log参数，slave没有必要开启二进制日志，如果slave为其它slave的master，须设置bin_log
3、连接master
mysql> CHANGE MASTER TO
MASTER_HOST='10.108.111.14',
MASTER_USER='test',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=120;
4、show slave status;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: ---------------------------- slave io状态，表示还未启动
Master_Host: 10.108.111.14
Master_User: test
Master_Port: 20126
Connect_Retry: 60 ------------------------- master宕机或连接丢失从服务器线程重新尝试连接主服务器之前睡眠时间
Master_Log_File: mysql-bin.000003 ------------ 当前读取master binlog文件
Read_Master_Log_Pos: 120 ------------------------- slave读取master binlog文件位置
Relay_Log_File: relay-bin.000001 ------------ 回放binlog
Relay_Log_Pos: 4 -------------------------- 回放relay log位置
Relay_Master_Log_File: mysql-bin.000003 ------------ 回放log对应maser binlog文件
Slave_IO_Running: No
Slave_SQL_Running: No
Exec_Master_Log_Pos: 0 --------------------------- 相对于master从库的sql线程执行到的位置
Seconds_Behind_Master: NULL
Slave_IO_State, Slave_IO_Running, 和Slave_SQL_Running为NO说明slave还没有开始复制过程。
5、启动复制
start slave
6、再次观察slave状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event -- 等待master新的event
Master_Host: 10.108.111.14
Master_User: test
Master_Port: 20126
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 3469 ---------------------------- 3469 等于Exec_Master_Log_Pos，已完成回放
Relay_Log_File: relay-bin.000002 ||
Relay_Log_Pos: 1423 ||
Relay_Master_Log_File: mysql-bin.000003 ||
Slave_IO_Running: Yes ||
Slave_SQL_Running: Yes ||
Exec_Master_Log_Pos: 3469 -----------------------------3469 等于slave读取master binlog位置，已完成回放
Seconds_Behind_Master: 0
可看到slave的I/O和SQL线程都已经开始运行，而且Seconds_Behind_Master=0。Relay_Log_Pos增加，意味着一些事件被获取并执行了。
最后看下如何正确判断SLAVE的延迟情况，判定slave是否追上master的binlog：
- 首先看 Relay_Master_Log_File 和 Maser_Log_File 是否有差异；
- 如果Relay_Master_Log_File 和 Master_Log_File 是一样的话，再来看Exec_Master_Log_Pos 和 Read_Master_Log_Pos 的差异，对比SQL线程比IO线程慢了多少个binlog事件；
- 如果Relay_Master_Log_File 和 Master_Log_File 不一样，那说明延迟可能较大，需要从MASTER上取得binlog status，判断当前的binlog和MASTER上的差距；
- 如果以上都不能发现问题，可使用pt_heartbeat工具来监控主备复制的延迟。
7、查询slave数据，主从一致
mysql> select * from tb_person;
+----+-------+---------+-----+---------+
| id | name | address | sex | other |
+----+-------+---------+-----+---------+
| 5 | name4 | beijing | man | nothing |
| 6 | name2 | beijing | man | nothing |
| 7 | name1 | beijing | man | nothing |
| 8 | name3 | beijing | man | nothing |
+----+-------+---------+-----+---------+
五、使用binlog实现恢复数据
恢复是binlog的两大主要作用之一，接下来通过实例演示如何利用binlog恢复数据：
1、首先，看下当前binlog位置
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000008 | 1847 | | | |
+------------------+----------+--------------+------------------+-------------------+
2、向表tb_person中插入两条记录：
insert into tb_person set name="person_1", address="beijing", sex="man", other="test-1";
insert into tb_person set name="person_2", address="beijing", sex="man", other="test-2";
3、记录当前binlog位置：
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000008 | 2585 | | | |
+------------------+----------+--------------+------------------+-------------------+
4、查询数据
mysql> select * from tb_person where name ="person_2" or name="person_1";
+----+----------+---------+-----+--------+
| id | name | address | sex | other |
+----+----------+---------+-----+--------+
| 6 | person_1 | beijing | man | test-1 |
| 7 | person_2 | beijing | man | test-2 |
+----+----------+---------+-----+--------+
5、删除一条: delete from tb_person where name ="person_2";
mysql> select * from tb_person where name ="person_2" or name="person_1";
+----+----------+---------+-----+--------+
| id | name | address | sex | other |
+----+----------+---------+-----+--------+
| 6 | person_1 | beijing | man | test-1 |
+----+----------+---------+-----+--------+
6、 binlog恢复（指定pos点恢复/部分恢复）
mysqlbinlog --start-position=1847 --stop-position=2585 mysql-bin.000008 > test.sql
mysql> source /var/lib/mysql/3306/test.sql
7、数据恢复完成
mysql> select * from tb_person where name ="person_2" or name="person_1";
+----+----------+---------+-----+--------+
| id | name | address | sex | other |
+----+----------+---------+-----+--------+
| 6 | person_1 | beijing | man | test-1 |
| 7 | person_2 | beijing | man | test-2 |
+----+----------+---------+-----+--------+
8、总结
恢复，就是让mysql将保存在binlog日志中指定段落区间的sql语句逐个重新执行一次而已
七、redo log和binlog区别
- redo log是属于innoDB层面，binlog属于MySQL Server层面的，这样在数据库用别的存储引擎时可以达到一致性的要求。
- redo log是物理日志，记录该数据页更新的内容；binlog是逻辑日志，记录的是这个更新语句的原始逻辑
- redo log是循环写，日志空间大小固定；binlog是追加写，是指一份写到一定大小的时候会更换下一个文件，不会覆盖。
- redo log作为异常宕机或者介质故障后的数据恢复使用，binlog可以作为恢复数据使用，主从复制搭建。
