Linux 上通过binlog文件恢复MySQL数据库详细步骤

共计 9524 个字符，预计需要花费 24 分钟才能阅读完成。

一、binlog 介绍

服务器的二进制日志记录着该数据库的所有增删改的操作日志(前提是要在自己的服务器上开启 binlog)，还包括了这些操作的执行时间。为了显示这些二进制内容，我们可以使用 MySQLbinlog 命令来查看。

用途 1：主从同步

用途 2：恢复数据库（也是线上出现一次数据库文件丢失后，才对这个有所了解并学习的）

mysqlbinlog 命令用法：shell> mysqlbinlog [options] log_file …

<!–[if !supportLists]–>1) mysqlbinlog 选项示例

常见的选项有以下几个：

–start-datetime

从二进制日志中读取指定等于时间戳或者晚于本地计算机的时间。取值如：=”1470733768″ 或者 =”2016-08-09 5:09:28″

示例:

[root@hcloud ~]# mysqlbinlog --start-datetime="2016-08-09 5:05:27" /var/lib/mysql/mysql-bin.000001

–stop-datetime

从二进制日志中读取指定小于时间戳或者等于本地计算机的时间取值和上述一样

–start-position

从二进制日志中读取指定 position 事件位置作为开始。取值：=”2698″

示例：

[root@hcloud ~]# mysqlbinlog --start-position="2698" /var/lib/mysql/mysql-bin.000001

–stop-position

从二进制日志中读取指定 position 事件位置作为事件截至。取值：=”2698″

二、环境准备以及备份恢复

1) 安装好 mysql 后，检查开启 binlog

mysql> SHOW BINARY LOGS;

ERROR 1381 (HY000): You are not using binary logging

：上面提示说明没有服务器开启 binlog

修改 /etc/my.cnf

在 mysqld 选项中添加一行内容如下:

log-bin=mysql-bin

默认如果不给值的话，log-bin 的会以 mysqld-bin 为索引，创建 mysqld-bin.00001 等

重启 mysqld 即可。

2) 检查下 binlog

mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 106 |
+------------------+-----------+
1 row in set (0.00 sec)

3) 先创建一些原始数据。

mysql> create database Test_DB;
Query OK, 1 row affected (0.00 sec)

mysql> use Test_DB;
Database changed

mysql> CREATE TABLE OneTb(id INT(10) NOT NULL,name varchar(20),age INT(10));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into OneTb values (1,’user1′,18);
mysql> insert into OneTb values (2,’user2′,19);
insert into OneTb values (3,’user3′,20);

检查下数据：

mysql> select * from OneTb;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | user1 |   18 |
|  2 | user2 |   19 |
|  3 | user3 |   20 |
+----+-------+------+
3 rows in set (0.00 sec)

4) 备份还原 (完整备份以及还原)

这里我们模拟一下做下每天的完整备份数据库任务。

[root@hcloud ~]# mysqldump -uroot -p Test_DB > /data/mysqlbackup/Test_DB_0809-16:50.sql
Enter password:

模拟下操作失误, 将数据修改错误了。

mysql> update OneTb set age = 15;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0

mysql> select * from OneTb;
+—-+——-+——+
| id | name | age |
+—-+——-+——+
| 1 | user1 | 15 |
| 2 | user2 | 15 |
| 3 | user3 | 15 |
+—-+——-+——+
3 rows in set (0.00 sec)

现在我们使用传统的方式来进行恢复还原。

[root@hcloud ~]# mysql -uroot -p Test_DB < /data/mysqlbackup/Test_DB_0809-16\:50.sql

再次查询一下：

mysql> select * from Test_DB.OneTb;
+—-+——-+——+
| id | name | age |
+—-+——-+——+
| 1 | user1 | 18 |
| 2 | user2 | 19 |
| 3 | user3 | 20 |
+—-+——-+——+
3 rows in set (0.00 sec)

可以看到数据都已经还原回来。

5) 利用 binlog 模拟还原

在原表的基础上在创建几条数据。

mysql> insert into Test_DB.OneTb values(4,'user4',21),(5,'user5',22),(6,'user6',23);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from Test_DB.OneTb;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | user1 |   18 |
|  2 | user2 |   19 |
|  3 | user3 |   20 |
|  4 | user4 |   21 |
|  5 | user5 |   22 |
|  6 | user6 |   23 |
+----+-------+------+
6 rows in set (0.00 sec)

如果这个时候我们把数据不小心修改了或者把库删除掉了，导致数据全部丢失，这个时候如果再用之前最新的备份文件 Test_DB_0809-16:50.sql，去恢复数据的话，那么将会丢掉备份之后新插入的数据。

注意：如果真的使用最近的一次备份文件去做的话，一定是在万不得已的情况（比如 binlog 被删除，整个硬盘挂掉、、、想想都可怕。。。）。

模拟误操作，批量更改下用户的名字。

mysql> update Test_DB.OneTb set name='user10';
Query OK, 6 rows affected (0.00 sec)
Rows matched: 6  Changed: 6  Warnings: 0

不行，上一步不够狠，这里再狠一点，把表都给删除

mysql> drop table Test_DB.OneTb;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    3
Current database: *** NONE ***

Query OK, 0 rows affected (0.00 sec)

更多详情见请继续阅读下一页的精彩内容：http://www.linuxidc.com/Linux/2016-08/134162p2.htm

由于之前我们一开始开启了 binlog 日志选项，用 binlog 恢复数据库。下面从 binlog 入手，先检查一下 binlog 文件，目前我的 MySQL 服务自开启 binlog 后重启了两次，所以有 2 个 binlog 文件（每重启一次，便会重新生成一个 binlog 文件，还有一种情况就是运行了 FLUSH LOGS 命令也会重建一个）;

mysql-bin.index 文件中记录的是：自 log-bin 选项开启后，记录的所有的二进制日志清单列表。

Linux 上通过 binlog 文件恢复 MySQL 数据库详细步骤

注意：在实际生产环境中，如果遇到需要恢复数据库的情况，不要让用户能访问到数据库，以避免新的数据插入进来，以及在主从的环境下，关闭主从。

使用 mysqlbinlog 命令可以查看 binlog 文件. 我们看下最新的文件 mysql-bin.00002

Linux 上通过 binlog 文件恢复 MySQL 数据库详细步骤

从最后可以看出有删除的操作。但是我们不能完全的恢复，因为最后还有删除的操作。

现在我的思路就是，先将第一个 binlog 和第二个 binlog 文件导出来à利用指定的 position 位置的方式（过滤掉删除表操作和 update Test_DB.OneTb set name=’user10′; 这条语句），导出 2 个 sql 语句，最后我们将 2 个 sql 合成一个 sql，导入到数据库中即可。

我们先用 mysqlbinlog 命令找到 update 那条语句的位置，然后指定 position 将 mysql-bin.00001 导出来。

[root@hcloud ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000001

….

#160809  5:09:28 server id 1  end_log_pos 2698      Query       thread_id=17   exec_time=0    error_code=0

SET TIMESTAMP=1470733768/*!*/;

SET @@session.foreign_key_checks=1, @@session.unique_checks=1/*!*/;

SET @@session.sql_mode=0/*!*/;

/*!\C latin1 *//*!*/;

SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;

insert into Test_DB.OneTb values(4,'user4',21),(5,'user5',22),(6,'user6',23)

/*!*/;

# at 2698

#160809  5:19:49 server id 1  end_log_pos 2795      Query       thread_id=17   exec_time=0    error_code=0

SET TIMESTAMP=1470734389/*!*/;

update Test_DB.OneTb set name='user10'

/*!*/;

# at 2795

#160809  5:30:38 server id 1  end_log_pos 2814      Stop

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

从上面可以看到我们在做插入正常数据后的 position 是 2698，那么使用下面的命令导出 sql

[root@hcloud ~]# mysqlbinlog --stop-position="2698" /var/lib/mysql/mysql-bin.000001 > Backup_1.sql

然后导出 mysql-bin.00002 的 sql 语句（注：由于演示操作，该文件只有一个drop 表操作，所以不做处理，但是在实际环境中，由于中途可能会有重启数据库操作，那时就需要检测最新的 binlog 有没有业务需要的语句。）

sql 语句已经导出来了。我们可以利用该语句直接恢复所有正常的数据。

注：本次恢复没有利用到之前的完整备份，因为我是开启 binlog 后，然后才做的所有建库建表操作，第一个 binlog 文件里已经记录了所有的数据库操作，所以不需要使用之前的完整备份（另外：实际的生产环境，还是需要利用到完整备份的，因为线上环境可能会有 N 多个 binlog 文件，所以需要利用到完整备份和最新的 binlog 文件来结合恢复）

开始恢复前，我们将原有的 Test_DB 数据库也给干掉吧。毕竟我们的 binlog 中有创建操作

mysql> DROP DATABASE Test_DB;
Query OK, 0 rows affected (0.03 sec)

恢复数据库时还可以利用在登陆 mysql 后，用 source 命令导入 sql 语句，这里暂不介绍

[root@hcloud ~]# mysql -uroot -p < Backup_1.sql 
Enter password:

恢复完成后，我们检查下表的数据是否完整

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| Test_DB            |
| mysql              |
+--------------------+
3 rows in set (0.00 sec)

mysql> select * from Test_DB.OneTb;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | user1 |   18 |
|  2 | user2 |   19 |
|  3 | user3 |   20 |
|  4 | user4 |   21 |
|  5 | user5 |   22 |
|  6 | user6 |   23 |
+----+-------+------+
6 rows in set (0.00 sec)

Ok 完整的都恢复过来了。

三、总结

1) 恢复方式

a) 利用最新一次的完整备份加 binlog 指定事件起始时间和终止时间或者 position 恢复数据库

b) 利用所有 binlog 指定事件起始位置和终止时间来合并 sql 文件恢复数据库（此方法要确保 binlog 文件的完整）

c) 利用 mysqldump 使用完整恢复。（在确保最新一次的完整备份后的数据不重要，允许丢掉的情况下，直接恢复。该方法最简单、效率最高）

2) 附: 官方建议的备份原则（为了能睡个好觉…. 嗯，是的）

a) 在 mysql 安装好并运行时，就始终开启 log-bin 选项，该日志文件位于 datadir 目录下，也要确保该目录所在存储介质是安全的。

b) 定期做完整的 mysql 备份。

c) 定期使用 FlUSH LOGS 或者 mysqladmin flush-logs，该操作会关闭当前的二进制日志文件，并新建一个 binlog 日志文件。(和重启 mysql 后新建的 binlog 操作一样)。以备份 binlog 日志，利用 binlog 日志也可以做增量备份。

参考文章：

mysql 官方文档：http://dev.mysql.com/doc/refman/5.6/en/point-in-time-recovery.html

本文永久更新链接地址：http://www.linuxidc.com/Linux/2016-08/134162.htm