• 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

发表评论

验证码: 16 + = 24