MySQL 通过Binlog恢复数据

发表于 | 分类于 ，
字数统计: 2.8k | 阅读时长 ≈ 12

Binlog 日志的作用

一、Binlog 日志的作用

Binlog日志，即binary log，是二进制日志文件，有两个作用，一个是增量备份，另一个是主从复制，即主节点维护一个binlog日志文件，从节点从binlog中同步数据，也可以通过binlog日志来恢复数据。

二、Binlog 三种模式

Statement Level 模式 5.5默认模式
Row Level 模式 5.7默认模式
Mixed 模式（混合模式）

2.1 Statement Level模式

记录每一条修改数据的SQL语句（批量修改时，记录的不是单条SQL语句，而是批量修改的SQL语句事件）。

优点：statement模式记录的更改的SQ语句事件，并非每条更改记录，所以大大减少了binlog日志量，节约磁盘IO，提高性能。

缺点：statement level下对一些特殊功能的复制效果不是很好，比如：函数、存储过程的复制。由于row level是基于每一行的变化来记录的，所以不会出现类似问题

2.2 Row Level模式

记录的方式是行，即如果批量修改数据，记录的不是批量修改的SQL语句事件，而是每条记录被更改的SQL语句，因此，ROW模式的binlog日志文件会变得很“重”。

优点：row level的binlog日志内容会非常清楚的记录下每一行数据被修改的细节。而且不会出现某些特定情况下存储过程或function，以及trigger的调用和触发器无法被正确复制的问题。

缺点：row level下，所有执行的语句当记录到日志中的时候，都以每行记录的修改来记录，这样可能会产生大量的日志内容，产生的binlog日志量是惊人的。批量修改几百万条数据，那么记录几百万行……

2.3 Mixed模式（混合模式）

实际上就是前两种模式的结合，在mixed模式下，mysql会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也是在statement和row之间选择一种。

企业场景如何选择binlog的模式

  1. 如果生产中使用MySQL的特殊功能相对少（存储过程、触发器、函数）。选择默认的语句模式，Statement Level。
  2. 如果生产中使用MySQL的特殊功能较多的，可以选择Mixed模式。
  3. 如果生产中使用MySQL的特殊功能较多，又希望数据最大化一致，此时最好Row level模式；但是要注意，该模式的binlog非常“沉重”。

例如：

Statement Level模式

1
2
3
100w条记录
updatetest set name='tintin';
binilog 里面就只用一条 update top1 set name='tony';

ROW 行级模式

1
2
3
100w条记录
updatetest set name='tintin';
binilog 里面就用100w条update top1 set name='tony';语句

MIXED混合模式

1
2
3
100w条记录
updatetest set name='tintin';
binilog 里面就只用 update top1 set name='tony';

对于函数、触发器、存储过程会自动的使用row-level模式

三、修改 Binlog 模式

查看安装的mysql是否开启binlog日志功能

1
2
3
4
5
6
7
8
9
10
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                            |
+---------------------------------+--------------------------------+

查看 Binlog 的模式

1
2
3
4
5
6
mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+

开启 Binlog 并设置 Binlog模式

1
2
3
4
5
6
7
8
9
10
# vim /etc/my.cnf

[mysqld]
log-bin = mysql-bin
server-id=1234567      #mysql 5.7以后需要设置server-id，可以随便填
log-bin=/var/lib/mysql/mysql-bin

binlog_format = ROW
#binlog_format = STATEMENT
#binlog_format = MIXED

四、查看 Binlog

Binlog是二进制文件，普通文件查看器cat、more、vi 等都无法打开，必须使用自带的 mysqlbinlog 命令查看

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
# mysqlbinlog --no-defaults --base64-output='decode-rows' -v /var/lib/mysql/mysql-bin.000001

…………………………………………………………
# at 8962
#210909 16:38:20 server id 1234567  end_log_pos 9028 CRC32 0xf7a591bb 	Update_rows: table id 117 flags: STMT_END_F
### UPDATE `Transaction`.`class`
### WHERE
###   @1=5
###   @2='e'
###   @3=9999
### SET
###   @1=5
###   @2='e'
###   @3=101
# at 9028
#210909 16:38:20 server id 1234567  end_log_pos 9059 CRC32 0x7c40c1d7 	Xid = 474
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

at 后面的数字就是开始的 pos 点，end_log_pos 后面的数字则是结束的pos点

也可以使用 mysql 查询语句查看

语法:

show binlog events [IN ‘log_name’] [FROM pos] [LIMIT [offset,] row_count];

IN ‘log_name’： 指定要查询的binlog文件名(不指定就是第一个binlog文件)；
FROM pos：指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)；
LIMIT [offset,]：偏移量(不指定就是0)；
row_count：查询总条数(不指定就是所有行)。

查看总共有几个binlog文件

1
2
3
4
5
6
7
8
9
mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |     16781 |
| mysql-bin.000002 |      3541 |
| mysql-bin.000003 |     12984 |
| mysql-bin.000004 |      9059 |
+------------------+-----------+

查看Binlog

1
2
3
4
5
6
7
8
9
mysql> show binlog events in 'mysql-bin.000004' limit 5;
+---------------+-----+----------------+-----------+-------------+--------------------------  --------------+
| Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                                     |
+---------------+-----+----------------+-----------+-------------+--------------  --------------------------+
| mysql-bin.000004 |   4 | Format_desc    |   1234567 |         123 | Server ver: 5.7.35-log, Binlog ver: 4 |
| mysql-bin.000004 | 123 | Previous_gtids |   1234567 |         154 |                                       |
| mysql-bin.000004 | 154 | Anonymous_Gtid |   1234567 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000004 | 219 | Query          |   1234567 |         334 | create database Transaction           |
| mysql-bin.000004 | 334 | Anonymous_Gtid |   1234567 |         399 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |

五、通过Binlog恢复数据

恢复命令的语法格式：

mysqlbinlog –start-position=121 –stop-position=257 -d 数据库名 /tmp/mysql-bin.000004|mysql -u用户名 -p’密码’ 数据库名| mysql -u用户名 -p密码 数据库名

常用参数选项：

–start-position=121 起始pos点
–stop-position=257 结束pos点
–start-datetime=”2018-7-25 10:18:18” 起始时间点
–stop-datetime=”2018-7-25 10:28:46” 结束时间点
–d xxx 指定只恢复xxx数据库

实际是将读出的binlog日志内容，通过管道符传递给mysql命令。这些命令、文件尽量写成绝对路径；

数据库只读模式

在对生产数据库进行恢复操作前，如果业务允许尽量先将数据库设置为只读模式

1
mysql> set global read_only=1;

这样可以避免在恢复过程中普通用户又对数据进行修改，造成数据二次污染

在恢复完后再关闭数据库只读模式

1
mysql> set global read_only=0;

5.1 恢复全量数据

使用.sql文件全量恢复（记得剔除掉问题语句比如drop）

1
2
3
4
# mysqlbinlog /opt/backup/mysql-bin.000004 > /opt/backup/000004.sql
# vi /opt/backup/000004.sql    #删除里面的drop语句
# 删掉drop语句前后的# at 到 /*！*/之间的内容
# mysql -uroot -p'123456' -v < /opt/backup/000004.sql

5.2 全量备份 + binlog还原

例如现在有一个学生表 student

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> select * from student;
+-----+--------+------+------+
| sid | sname  | sage | ssex |
+-----+--------+------+------+
| 101 | 龙大   |   18 | 男   |
| 102 | 熊二   |   19 | 男   |
| 103 | 张三   |   18 | 男   |
| 104 | 李四   |   19 | 女   |
| 105 | 王五   |   20 | 男   |
| 106 | 李华   |   19 | 男   |
| 107 | 李红   |   19 | 女   |
| 108 | 李明   |   20 | 男   |
| 109 | 贝贝   |   19 | 女   |
| 110 | 娜娜   |   20 | 女   |
+-----+--------+------+------+

不小心将 sname 为 张三 的数据删掉了

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> delete from student where sname='张三';
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+-----+--------+------+------+
| sid | sname  | sage | ssex |
+-----+--------+------+------+
| 101 | 龙大   |   18 | 男   |
| 102 | 熊二   |   19 | 男   |
| 104 | 李四   |   19 | 女   |
| 105 | 王五   |   20 | 男   |
| 106 | 李华   |   19 | 男   |
| 107 | 李红   |   19 | 女   |
| 108 | 李明   |   20 | 男   |
| 109 | 贝贝   |   19 | 女   |
| 110 | 娜娜   |   20 | 女   |
+-----+--------+------+------+

现在开始恢复数据的步骤

查询最新的binlog日志，binlog存放路径

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |     16781 |
| mysql-bin.000002 |      3541 |
| mysql-bin.000003 |     12984 |
| mysql-bin.000004 |      9059 |
+------------------+-----------+

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                            |
+---------------------------------+--------------------------------+

解析binlog日志，根据刚刚执行的SQL语句查找到pos的起始和结束值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# mysqlbinlog --no-defaults --base64-output='decode-rows' -v /var/lib/mysql/mysql-bin.000004

………………………………………………
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
BEGIN
/*!*/;
# at 12812
#210909 17:55:15 server id 1234567  end_log_pos 12871 CRC32 0x7cda6824 	Table_map: `school`.`student` mapped to number 119
# at 12871
#210909 17:55:15 server id 1234567  end_log_pos 12926 CRC32 0x4ba774d2 	Delete_rows: table id 119 flags: STMT_END_F
### DELETE FROM `school`.`student`
### WHERE
###   @1=103
###   @2='张三'
###   @3=18
###   @4='男'
# at 12926
#210909 17:55:15 server id 1234567  end_log_pos 12957 CRC32 0x20e34aff 	Xid = 565
COMMIT/*!*/;

刚才删除的 张三 的SQL在pos 12812开启了事务，该 pos 12812就是我们要还原的结束点

第一步：找到离刚刚删除 张三 最近的 pos 12812

第二步：将距离最近全量备份数据到现在的binlog 拷贝到其它目录

第三步：将最近的全量备份还原到数据库，并查看post节点

1
# mysql -uroot -p'123456' < /opt/backup/all_databases.sql

找出全量备份的pos结束点

1
2
3
# cat /opt/backup/all_databases.sql|grep -i 'CHANGE MASTER TO MASTER_LOG_FILE'

-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000017', MASTER_LOG_POS=10729;

第四步：还原binlog pos 10729到 pos 12812的数据

1
# mysqlbinlog --start-position=10729 --stop-position=12812  -d school /var/lib/mysql/mysql-bin.000004|mysql -uroot -p'123456' school

最后查看数据已恢复

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> select * from student;
+-----+--------+------+------+
| sid | sname  | sage | ssex |
+-----+--------+------+------+
| 101 | 龙大   |   18 | 男   |
| 102 | 熊二   |   19 | 男   |
| 104 | 李四   |   19 | 女   |
| 105 | 王五   |   20 | 男   |
| 106 | 李华   |   19 | 男   |
| 107 | 李红   |   19 | 女   |
| 108 | 李明   |   20 | 男   |
| 109 | 贝贝   |   19 | 女   |
| 110 | 娜娜   |   20 | 女   |
+-----+--------+------+------+

5.3 binlog 还原

这个得找到离删除 张三 最近的数据修改，然后才能还原到张三删除前的数据

还是刚刚的student，以继续往上查找binlog

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
# mysqlbinlog --no-defaults --base64-output='decode-rows' -v /var/lib/mysql/mysql-bin.000004

………………………………………………………………
# at 10466
#210909 17:49:32 server id 1234567  end_log_pos 10540 CRC32 0xdfe334ef  Query   thread_id=28    exec_time=0 error_code=0
SET TIMESTAMP=1631180972/*!*/;
BEGIN
/*!*/;
# at 10540
#210909 17:49:32 server id 1234567  end_log_pos 10599 CRC32 0x73d9dece  Table_map: `school`.`student` mapped to number 119
# at 10599
#210909 17:49:32 server id 1234567  end_log_pos 10654 CRC32 0x90c1eed2  Write_rows: table id 119 flags: STMT_END_F
### INSERT INTO `school`.`student`
### SET
###   @1=103
###   @2='张三'
###   @3=18
###   @4='男'
# at 10654
#210909 17:49:32 server id 1234567  end_log_pos 10685 CRC32 0x281d9ace  Xid = 528
COMMIT/*!*/;
# at 10685
#210909 17:49:32 server id 1234567  end_log_pos 10750 CRC32 0x28ec22f6  Anonymous_GTID  last_committed=36   sequence_number=37   rbr_only=yes

找到了离delete 张三 前最近的数据改动是 insert 插入的张三数据，起始pos 10540 结束 pos 10685

那么就可以使用该起始和结束pos来还原

1
# mysqlbinlog --start-position=10540 --stop-position=10685  -d school /var/lib/mysql/mysql-bin.000004|mysql -uroot -p'123456' school

最后查看数据已恢复

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> select * from student;
+-----+--------+------+------+
| sid | sname  | sage | ssex |
+-----+--------+------+------+
| 101 | 龙大   |   18 | 男   |
| 102 | 熊二   |   19 | 男   |
| 103 | 张三   |   18 | 男   |
| 104 | 李四   |   19 | 女   |
| 105 | 王五   |   20 | 男   |
| 106 | 李华   |   19 | 男   |
| 107 | 李红   |   19 | 女   |
| 108 | 李明   |   20 | 男   |
| 109 | 贝贝   |   19 | 女   |
| 110 | 娜娜   |   20 | 女   |
+-----+--------+------+------+

5.4 恢复单个表的数据

通常在从全库备份恢复中可以指定--one-database简写-o，参数来指定要恢复的库

1
# mysql -uroot -p'123456' -o school < /opt/backup/all_databases.sql

那么如何从全库备份中抽取某张表呢，全库恢复，再恢复某张表小库还可以，大库就很麻烦了，那我们可以利用正则表达式来进行快速抽取，具体实现方法如下

先从全库备份中抽取出student表的表结构

1
2
3
4
5
6
7
8
9
10
11
12
13
# sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `student`/!d;q' /opt/backup/all_databases.sql

DROP TABLE IF EXISTS `student`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `student` (
  `sid` int(11) NOT NULL,
  `sname` varchar(30) DEFAULT NULL,
  `sage` int(11) DEFAULT NULL,
  `ssex` varchar(8) DEFAULT NULL,
  PRIMARY KEY (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

再从全库备份中抽取出student表的内容

1
2
# grep 'INSERT INTO `student`' /opt/backup/all_databases.sql
INSERT INTO `student` VALUES (101,'龙大',18,'男'),(102,'熊二',19,'男'),(103,'张三',18,'男'),(104,'李四',19,'女'),(105,'王五',20,'男'),(106,'李华',19,'男'),(107,'李红',19,'女'),(108,'李明',20,'男'),(109,'贝贝',19,'女'),(110,'娜娜',20,'女');

然后分别将DDL和DML写入数据库中，接着再通过 binlog恢复到两个pos点之间的数据即可完成单个表的数据恢复

-------------本文结束感谢您的阅读-------------
分享到： 收藏夹 复制网址 邮件 微信 QQ空间 腾讯微博 豆瓣 一键分享 更多