Linux服务器通过binlog日志文件恢复mysql数据库

binlog是二进制日志文件了这个可以记录到我们mysql数据库中每一天数据操作了,所以数据丢失了使用binlog来进行恢复是非常不错的办法了,在这里我们就来看在Linux 上通过binlog文件 恢复mysql 数据库例子吧,具体如下。

一、binlog 介绍

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

用途1:主从同步

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

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

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 事件位置作为开始。取值:=”3155″

示例:

[root@hcloud ~]# mysqlbinlog –start-position=”3155″ /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;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
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> /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< /data/mysqlbackup/Test_DB_0809-16_50.sql
再次查询一下:
mysql> select * from test.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.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.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.OneTb set name=’user10′;
Query OK, 6 rows affected (0.00 sec)
Rows matched: 6 Changed: 6 Warnings: 0
不行,上一步不够狠,这里再狠一点,把表都给删除
mysql> drop table test.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)

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

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

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

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

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

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

我们先用mysqlbinlog命令找到update 那条语句的位置,然后指定position 将mysql-bin.00001 导出来。
[root@hcloud ~]# mysqlbinlog /usr/local/mysql/data/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=”3155″ /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;
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|
| mysql |
+——————–+
3 rows in set (0.00 sec)

mysql> select * from test.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日志也可以做增量备份。

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注