一、简介
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