mysql开启binlog恢复数据_使用mysqlbinlog恢复数据

一、开启bin-log

1、安装mysql[root@zhen ~]# yum -y install mysql mysql-devel mysql-server

2、开启bin-log功能[root@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

登录数据库，执行：mysql> show variables like 'log_bin';

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

二、查看产生的日志文件

bin-log是二进制文件，不能用vim等文本编辑器打开。Mysql提供了两种方式查看，在查看之前先执行一些SQL让文件中有记录。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命令：[root@zhen ~]# /usr/bin/mysqlbinlog /var/lib/mysql/mysql_bin.000001

以在表中插入数据为例，来看看日志的格式：# 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。

可以根据记录的起始位置和时间来恢复数据。

第二种查看方式：mysql> show binlog events in "mysql_bin.000001";

用这种方式查看就简洁明了了。同样也能看到记录的起始位置，执行的SQL等信息。

三、使用bin-log恢复数据

先把刚刚在test库中创建的表students删除掉：mysql> drop table students;

Query OK, 0 rows affected (0.01 sec)

删除之后来恢复这个表，有下面两种方法：

①根据操作的时间来恢复。

使用mysqlbinlog查看日志文件，在记录中找到创建这个表的时间是160711 17:24:00，最后删除此表的时间是160711 19:10:38，利用这两个时间点来恢复此表。[root@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文件导入到数据库：[root@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;

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

[root@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;”来确定数据库当前使用的是哪个日志文件。