mysql binlog日志

  • Post author:
  • Post category:mysql
  • Post comments:0评论

一、简介

  binlog 即 binary log，二进制日志。这个日志文件以事件的形式记录了所有对数据操作的SQL语句(除了数据查询语句）信息。通过它我们可以实现数据恢复、增量备份、主主复制和主从复制等，对数据库来讲是非常重要的。
  事件（event）即二进制日志的最小记录单元。对于DDL和DCL而言，一个语句就是一个事件；而对于DML语句来讲，只记录已提交的事务，也就是说会有多个事件。
  以下内容基于mysql 5.7.28。
 

二、binlog日志的配置

修改/etc/my.cnf文件，在mysqld下添加以下配置。binlog日志存放位置强烈建议和数据目录分开。

[root@db03 ~]# mkdir /data/mysql57/binlog
[root@db03 ~]# chown -R mysql.mysql /data/mysql57/binlog/
server_id=1
log_bin=/data/mysql57/binlog/mysql-bin

配置说明：
 server_id: 指定id，设置多少没有关系，大于0整数。关键是在主从中，这个id必须不一样。
 log_bin: 开启并指定binlog日志位置。最后mysql-bin是binlog日志文件的前缀。

[root@db03 binlog]# ll
total 8
-rw-r----- 1 mysql mysql 154 Nov  3 06:54 mysql-bin.000001      # binlog日志文件
-rw-r----- 1 mysql mysql  38 Nov  3 06:54 mysql-bin.index       # 索引文件

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

 

三、binlog日志的记录格式

  binlog的格式有三种：STATEMENT、ROW、MIXED 。下面我们来了解下这三种格式的区别：
  STATMENT：基于SQL语句的复制(statement-based replication, SBR)，记录的是SQL语句本身，不记录SQL语句对应每行的数据变化，即你写的什么SQL语句就记录什么。这样子binlog的产生日志相对较少，减少了磁盘IO，提高了性能。但是它准确性差，因为记录的SQL语句本身，当SQL语句中用到了函数，例如now()、rand()、uuid()等，换个时间点值就不同，当你做恢复或者主从时就会出现数据不一致的情况。
  ROW：基于行的复制(row-based replication, RBR)，记录的是每行实际数据的变更。例如 “update stu set age=16” 这条语句意思是将stu表中所有行的age列的值修改为16，对应就有很多行数据的变更。如果是ROW格式，那么binlog记录的就不是这条SQL语句本身（DDL和DCL语句记录的是本身），而是对应到每行的实际数据变更操作，假如stu表有100行，那么就会记录100条操作。因此它会产生大量的日志，可读性差，但是它准确性强，能精准记录数据的变更。注意它只记录已提交的事务的日志。一般推荐使用的是row模式。
  MIXED：混合模式复制(mixed-based replication, MBR)，以上两种模式的混合使用，由MySQL根据执行的SQL语句选择日志的记录格式。
  日志格式的查看：默认使用的是ROW行模式。

mysql> select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| ROW             |
+-----------------+

 

四、binlog日志的查看

查看binlog日志配置信息：

mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------------+
| Variable_name                   | Value                                |
+---------------------------------+--------------------------------------+
| log_bin                         | ON                                   |
| log_bin_basename                | /data/mysql57/binlog/mysql-bin       |
| log_bin_index                   | /data/mysql57/binlog/mysql-bin.index |

查看所有的binlog日志文件：

mysql> flush logs;          # 生成一个新的binlog日志文件并使用它。数据库重启也会发生同样情况。
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       201 |
| mysql-bin.000002 |       154 |
+------------------+-----------+

查看当前正在使用的binlog日志文件：

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

查看binlog日志事件：

mysql> create database test1;
mysql> create database test2;
mysql> show binlog events in 'mysql-bin.000002';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000002 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.28-log, Binlog ver: 4 |
| mysql-bin.000002 | 123 | Previous_gtids |         1 |         154 |                                       |
| mysql-bin.000002 | 154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000002 | 219 | Query          |         1 |         316 | create database test1                 |
| mysql-bin.000002 | 316 | Anonymous_Gtid |         1 |         381 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000002 | 381 | Query          |         1 |         478 | create database test2                 |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+

前面2行是固定格式头部信息，后4行才是真正记录发生的事件。
至于第3、5行的gtid我们后面会介绍到，暂时无需理会。
输出信息含义：
 Pos         : 事件开始的position号，也可称位置点。
 End_log_pos : 事件结束的position号。
 Info        : 日志内容信息。

binlog日志内容查看：

mysql> use test1
mysql> create table stu(age int);
mysql> insert into stu values(1),(2),(3);
mysql> show binlog events in 'mysql-bin.000002';
+------------------+-----+----------------+-----------+-------------+----------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                   |
+------------------+-----+----------------+-----------+-------------+----------------------------------------+
.....省略
| mysql-bin.000002 | 478 | Anonymous_Gtid |         1 |         543 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'   |
| mysql-bin.000002 | 543 | Query          |         1 |         644 | use `test1`; create table stu(age int) |
| mysql-bin.000002 | 644 | Anonymous_Gtid |         1 |         709 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'   |
| mysql-bin.000002 | 709 | Query          |         1 |         782 | BEGIN                                  |
| mysql-bin.000002 | 782 | Table_map      |         1 |         829 | table_id: 108 (test1.stu)              |
| mysql-bin.000002 | 829 | Write_rows     |         1 |         879 | table_id: 108 flags: STMT_END_F        |
| mysql-bin.000002 | 879 | Xid            |         1 |         910 | COMMIT /* xid=26 */                    |
+------------------+-----+----------------+-----------+-------------+----------------------------------------+

  像上面因为格式我们无法查看具体插入的数据，为此我们可以使用mysqlbinlog命令去查看binlog日志文件里的内容。


[root@db03 binlog]# mysqlbinlog mysql-bin.000002 
......省略
# at 219
#201103  7:22:22 server id 1  end_log_pos 316 .......
......
create database test1
......
# at 543
#201103  7:40:22 server id 1  end_log_pos 644 .......
......
create table stu(age int)
......
# at 709
#201103  7:41:41 server id 1  end_log_pos 782 ......
BEGIN
# at 782
#201103  7:41:41 server id 1  end_log_pos 829 CRC32 0x185a896f  Table_map: `test1`.`stu` mapped to number 108
# at 829
#201103  7:41:41 server id 1  end_log_pos 879 CRC32 0x50fc363a  Write_rows: table id 108 flags: STMT_END_F
BINLOG '
BVChXxMBAAAALwAAAD0DAAAAAGwAAAAAAAEABXRlc3QxAANzdHUAAQMAAW+JWhg=
BVChXx4BAAAAMgAAAG8DAAAAAGwAAAAAAAEAAgAB//4BAAAA/gIAAAD+AwAAADo2/FA=
'/*!*/;
# at 879
#201103  7:41:41 server id 1  end_log_pos 910 CRC32 0xafcd9e7f  Xid = 26
COMMIT/*!*/;
......

  同样还是因为ROW格式，对于我们所做的具体DML操作还是看不出。可以加上以下参数去解析我们所做的DML操作。

[root@db03 binlog]# mysqlbinlog --base64-output=decode-rows -v mysql-bin.000002
......
# at 709
#201103  7:41:41 server id 1  end_log_pos 782 CRC32 0xc654ccb1  Query   thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1604407301/*!*/;
BEGIN
/*!*/;
# at 782
#201103  7:41:41 server id 1  end_log_pos 829 CRC32 0x185a896f  Table_map: `test1`.`stu` mapped to number 108
# at 829
#201103  7:41:41 server id 1  end_log_pos 879 CRC32 0x50fc363a  Write_rows: table id 108 flags: STMT_END_F
### INSERT INTO `test1`.`stu`
### SET
###   @1=1
### INSERT INTO `test1`.`stu`
### SET
###   @1=2
### INSERT INTO `test1`.`stu`
### SET
###   @1=3
# at 879
#201103  7:41:41 server id 1  end_log_pos 910 CRC32 0xafcd9e7f  Xid = 26
COMMIT/*!*/;
......

 

五、binlog日志的截取

按时间截取日志信息：

参数说明：
 --start-datetime=''     截取的其实时间
 --stop-datetime=''      截取的结束时间 ，不指定即到当前为止
注意：最多精确到秒，做精确截取会有问题。

[root@db03 binlog]# mysqlbinlog --start-datetime='2020-11-03 7:20:00' mysql-bin.000002 > /tmp/1.sql

按position号码截取日志：

参数说明：
 --start-position    截取的起始position号
 --stop-position     截取的结束position号

[root@db03 binlog]# mysqlbinlog --start-position=543 --stop-position=910 mysql-bin.000002 > /tmp/2.sql

 

六、模拟故障通过截取binlog日志恢复

mysql> flush logs;
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
mysql> create database test3;
mysql> create table test3.stu(id int);
mysql> insert into test3.stu values(1),(2);
mysql> drop database test;
mysql> show binlog events in 'mysql-bin.000003';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
......
| mysql-bin.000003 | 219 | Query          |         1 |         316 | create database test3                 |
......
| mysql-bin.000003 | 803 | Query          |         1 |         898 | drop database test3                   |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+

[root@db03 binlog]# mysqlbinlog --start-position=219 --stop-position=803 mysql-bin.000003 > /tmp/3.sql

mysql> set sql_log_bin=0;       # 关闭当前窗口的binlog日志记录
mysql> source /tmp/3.sql;       # 导入截取的日志恢复数据
mysql> select * from test3.stu;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+

 

七、binlog日志的删除

binlog日志的删除有多种方式，像简单粗暴的物理删除，我们肯定要避免使用。这里介绍以下三种：
一是定义过期时间，到了时间自动删除。

mysql> select @@expire_logs_days;
+--------------------+
| @@expire_logs_days |
+--------------------+
|                  0 |
+--------------------+
mysql> set global expire_logs_days=15;
mysql> select @@expire_logs_days;
+--------------------+
| @@expire_logs_days |
+--------------------+
|                 15 |
+--------------------+

这里过期时间可以依据我们全备的时间来，即一轮全备时间间隔+1 ，生产建议至少两轮全备时间间隔+1。
要是磁盘空间足，当然是多多益善，以防万一。

二是手动清理。

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       201 |
| mysql-bin.000002 |       201 |
| mysql-bin.000003 |       201 |
| mysql-bin.000004 |       201 |
| mysql-bin.000005 |       899 |
+------------------+-----------+

# 删除'mysql-bin.000005之前的binlog'日志文件
mysql> purge binary logs to 'mysql-bin.000005';
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000005 |       899 |
+------------------+-----------+

# 还可以以时间为目标删除
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       201 |
| mysql-bin.000002 |       201 |
| mysql-bin.000003 |       201 |
| mysql-bin.000004 |       201 |
| mysql-bin.000005 |       154 |
+------------------+-----------+
mysql> purge binary logs before '2020-11-03 23:10:00';
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000005 |       154 |
+------------------+-----------+

三就是重置日志文件了，慎用。

mysql> reset master;
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       154 |
+------------------+-----------+

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

 

八、binlog日志的远程备份

可以使用 mysqlbinlog 命令拉取位于远程服务器上的binlog日志文件。

[root@db01 binlog]# mysqlbinlog -R -h 10.0.0.53 -ucp -p mysqld-bin.000001

参数说明：
 -R      选项指示mysqlbinlog命令从远程服务器读取日志文件
 -h      指定远程服务器的ip地址
 -u      指定用户
 -p      将提示输入密码
 -P      指定端口
 --raw   不输出的本身日志内容了，而是拉取指定的binlog日志文件本身到当前目录。   
 --stop-never   等待来自服务器的更多数据，而不是在最后一个日志的末尾停止，直到服务器断开连接。

# 远程实时备份
[root@db01 backup]# mysqlbinlog -R -h 10.0.0.53 -ucp -p --raw --stop-never mysql-bin.000001 &

 

九、问题

1、假如要恢复的数据库是两年前已经创建的，如何恢复。

  如果要截取从数据库创建到当前的日志来恢复，听到两年就感觉头大海量的数据，当然也可能截取不到两年的，因为可能定义了自动删除。一般推荐使用最近的数据全备份+binlog进行恢复。

2、跨多个文件该怎么截取。

解决方案：
（1）对binlog日志文件分段截取追加方式存储。position号在不同的binlog日志文件是独立，而GTID是唯一的。
（2）通过GTID方式对多份日志进行截取和恢复。
（3）按时间截取，但有可能不准确，果断pass掉。

3、如果是数据库中的一张表被误删除。*

解决方案：
（1）利用三剑客等筛选过滤命令从日志中截取。不现实，麻烦还是麻烦，存在风险，万一过滤没过好，漏了什么或者多了什么。pass。
（2）找一个临时的库，恢复上一次的全备。从原库截取上一次全备到删库之前的binlog，恢复到临时库，然后将误删除的表单独导出，再导入到原生产环境。可行，但是慢。
（3）使用第三方工具，例如：binlog2sql等。

 
参考文章：https://www.jianshu.com/p/00c54d2832ed

发表回复

验证码： 86 − = 79