使用mysqlbinlog恢复数据

34323

一、开启bin-log

1、安装mysql

1	[iyunv@zhen ~]# yum -y install mysql mysql-devel mysql-server

2、开启bin-log功能

1 2 3 4 5 6 7 8

[iyunv@zhen ~]# vi /etc/my.cnf       1 [mysqld]       2 datadir=/var/lib/mysql       3 socket=/var/lib/mysql/mysql.sock       4 user=mysql       5 # Disabling symbolic-links is recommended to prevent assorted security risks       6 symbolic-links=0       7 log_bin=mysql_bin

配置保存之后重启mysqld。
bin-log是执行SQL时产生的日志文件，只不过该日志是二进制。作用有两个：
①数据恢复；
②配置数据库主从，用于slave可以从master同步数据。
3、确认是否开启bin-log
登录数据库，执行：

1	mysql> show variables like 'log_bin';

看到确实是开启的，这样会在数据库的datadir目录下生成格式为mysql_bin.000001的日志文件：

二、查看产生的日志文件
bin-log是二进制文件，不能用vim等文本编辑器打开。Mysql提供了两种方式查看，在查看之前先执行一些SQL让文件中有记录。

1 2 3 4

mysql> use test; mysql> create table students( id int(10) primary key auto_increment,name varchar(255)); mysql> insert into students (id,name) values (1,'zhangsan'),(2,'lisi'); mysql> select * from students;

第一种查看方式：mysql自带的mysqlbinlog命令：

1	[iyunv@zhen ~]# /usr/bin/mysqlbinlog /var/lib/mysql/mysql_bin.000001

以在表中插入数据为例，来看看日志的格式：

1 2 3 4 5

# at 248 #160711 17:29:00 server id 1  end_log_pos 373 Querythread_id=3exec_time=0error_code=0 SET TIMESTAMP=1468272540/*!*/; insert into students (id,name) values (1,'zhangsan'),(2,'lisi') /*!*/;

“at 248”表示这条记录的起始位置（position）
“160711 17:29:00”表示记录产生的时间
“server id 1”表示是哪个服务器产生的记录
“end_log_pos 373”表示这条记录的终止位置
最后还能看到执行的SQL。
可以根据记录的起始位置和时间来恢复数据。
第二种查看方式：

1	mysql> show binlog events in "mysql_bin.000001";

用这种方式查看就简洁明了了。同样也能看到记录的起始位置，执行的SQL等信息。
三、使用bin-log恢复数据
先把刚刚在test库中创建的表students删除掉：

1 2	mysql> drop table students; Query OK, 0 rows affected (0.01 sec)

删除之后来恢复这个表，有下面两种方法：
①根据操作的时间来恢复。
使用mysqlbinlog查看日志文件，在记录中找到创建这个表的时间是160711 17:24:00，最后删除此表的时间是160711 19:10:38，利用这两个时间点来恢复此表。

1	[iyunv@zhen ~]# /usr/bin/mysqlbinlog --start-date="2016-07-11 17:24:00" --stop-date="2016-07-11 19:10:38" /var/lib/mysql/mysql_bin.000001 | /usr/bin/mysql -uroot -p

如果没有设定数据库密码，则/usr/bin/mysql
亦可以先导出为sql文件，再将sql文件导入到数据库：

1 2	[iyunv@zhen ~]# /usr/bin/mysqlbinlog --start-date="2016-07-11 17:24:00" --stop-date="2016-07-11 19:10:38" /var/lib/mysql/mysql_bin.000001 > /tmp/1.sql mysql> source /tmp/1.sql;

②根据操作的起止位置来恢复。

1	[iyunv@zhen ~]# /usr/bin/mysqlbinlog --start-position=106 --stop-position=373 /var/lib/mysql/mysql_bin.000001 | /usr/bin/mysql -uroot -p

四、小结
1、为了能在发生灾难时恢复数据库，要现在配置文件中添加log_bin=mysql_bin。“mysql_bin”是日志文件的格式，可以自定义；
2、使用“mysql> show master status;”来确定数据库当前使用的是哪个日志文件。