Linux上通过binlog文件恢复mysql数据库详细步骤
一、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)
由于之前我们一开始开启了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 /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日志也可以做增量备份。
以上所述是小编给大家介绍的linux上通过binlog文件恢复mysql数据库详细步骤，希望对大家有所帮助