1.开启bin-log

(1)在my.con配置文件里面

[mysqld] #选项添加

log-bin=mysql-bin #日志文件名称,未指定位置,默认数据文件位置

重启mysql服务


log_bin是生成的bin-log的文件名,后缀则是6位数字的编码,从000001开始,按照上面的配置,生成的文件则为: 

      mysql_bin.000001 

      mysql_bin.000002 

      ...... 

2.基本操作

(1)查看所有日志文件:

mysql> show binary logs;   或show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       120 |
+------------------+-----------+


(2)查看正在写入的日志文件:

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+


(3)查看当前binlog文件内容:

mysql> show binlog events;  #可以格式化输出 show binlog events\G; 或指定日志文件查看show binlog events in 'mysql-bin.000001';
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                  |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000001 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.25-log, Binlog ver: 4 |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+

注:

Log_name:此条log存在哪个文件中 

Pos:log在bin-log中的开始位置 

Event_type:log的类型信息 

Server_id:可以查看配置中的server_id,表示log是哪个服务器产生 

End_log_pos:log在bin-log中的结束位置 

Info:log的一些备注信息,可以直观的看出进行了什么操作 


(4)手动启用新的日志文件,一般备份完数据库后执行

mysql> show master status; 
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> flush logs; #结束正在写入日志文件
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

(5)删除所有二进制日志,并从新开始记录 

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> reset master; #重新开始
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

另外:

mysql> purge master logs to 'mysql-bin.000002';  #是将'mysql-bin.000002'编号之前的所有日志进行删除
mysql> purge master logs before 'yyyy-mm-dd hh:mm:ss' #是将在'yyyy-mm-dd hh:mm:ss'时间之前的所有日志进行删除


3.二进制日志文件导出

# mysqlbinlog --start-datetime="2015-07-02 11:25:56" --stop-datetime="2015-07-02 14:20:10" mysql-bin.000001 > /data/test01.log #按时间点导出
# mysqlbinlog --start-position=203  --stop-position=203 mysql-bin.000001 > /data/test02.log #按事件位置导出


4.恢复数据


强烈建议:做任何恢复之前都给数据库做一个完整备份,新建库进行恢复。


恢复

 bin-log是记录着mysql所有事件的操作,可以通过bin-log做完整恢复,基于时间点的恢复,和基于位置的恢复


(1)完整恢复,先执行上次完整备份恢复,再执行自上次备份后产生的二进制日志文件恢复

# mysql localhost mysql-bin.000001 | mysql -uroot -p

这样数据库就可以完全的恢复到崩溃前的完全状态


(2)基于时间点的恢复,如果确认误操作时间点为2015-06-04 10:00:00执行如下

# mysqlbinlog --stop-date='2015-06-04 9:59:59' mysql-bin.000001 | mysql -uroot -p


然后跳过误操作的时间点,继续执行后面的binlog

# mysqlbinlog --start-date='2015-06-04 10:01:00' mysql-bin.000001 | mysql -uroot -p


其中--stop-date='2015-06-04 9:59:59' 和 --start-date='2015-06-04 10:01:00' 


取两时间点

# mysqlbinlog --start-datetime="2015-07-02 11:25:56" --stop-datetime="2015-07-02 14:20:10" mysql-bin.000001 | mysql -u root -p


#注:其中的时间是你误操作的时间,而且这个时间点还可能涉及到的不只是误操作,也有可能有正确的操作也被跳过去了。那么执行位置恢复


基于位置恢复,通过查看日志文件信息,确认6259-6362为误操作点

# mysqlbinlog --stop-position=6259 mysql-bin.000001 | mysql -uroot -p #从1开始至6259的事件读,不包括6259事件

# mysqlbinlog --start-position=6363 mysql-bin.000001 | mysql -uroot -p #从6259的事件开始读

# 取两事件点
mysqlbinlog --start-position=5786 --stop-position=6254 mysql-bin.000001 | mysql -uroot -p