MySQL 开启二进制文件(windows)

默认情况下MySQL是未开启二进制文件的。
所以查看以下二进制日志的时候，会报错(1381 You are not using binary logging )

mysql> show binary logs; 或show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 120 |
+------------------+-----------+
那么，我们查看看，log_bin这个变量是否有开启？

mysql>show variables like 'log_bin';
显示 log_bin off

如何解决？

1.需要在my.ini 中配置开启log_bin

[mysqld]
log_bin = mysql_bin #日志文件名称，未指定位置，默认数据文件位置
这里还可以配置不同服务器的id
server_id = 2; # 不同服务器可以叫不同的数字1,2,3....

image.png

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

2.重启mysql服务器

再查看log_bin是否开启
如下图，ON说明配置成功！

image.png

3. 查看所有日志文件

image.png

4.查看正在写入的二进制文件

image.png

5.查看二进制文件内容

可以格式化输出 show binlog events\G; 或指定日志文件查看show binlog events in 'mysql-bin.000001';

image.png
image.png

注：
Log_name:此条log存在哪个文件中
Pos:log在bin-log中的开始位置
Event_type:log的类型信息
Server_id:可以查看配置中的server_id,表示log是哪个服务器产生
End_log_pos：log在bin-log中的结束位置
Info:log的一些备注信息，可以直观的看出进行了什么操作

MySQL二进制文件默认路径查找
mysql>show variables like 'datadir';

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

mysql>flush logs; #结束正在写入的日志文件

image.png

7.删除所有二进制日志，并从新开始记录

mysql>reset master; #重新开始

image.png

另外：
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'时间之前的所有日志进行删除

8.二进制日志文件导出

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 #按事件位置导出

image.png

#######9.恢复数据

强烈建议：做任何恢复之前都给数据库做一个完整备份，新建库进行恢复。
恢复
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