开启mysql的binlog日志&查看$备份

绝地灬酷狼 2022-03-27 05:45 321阅读 0赞

# 1、5.7版本前 #

修改my.cnf文件，添加log-bin=my-bin(my.cnf的位置根据自己安装的路径查看)  
\# vi /usr/local/mysql/my.cnf  
log-bin=my-bin            \#my-bin就是bin-log文件的名字，可以根据各自情况自定义文件名

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

或自定义存放bin-log的位置，默认情况下是在mysql data目录下，如  
log-bin=/mysql/data/my-bin  
注意：/mysql/data文件夹的权限需要mysql的。  
修改后需要重启mysql  
\# service mysql restart

查看是否开启  
mysql> show variables like 'log\_bin%';

# ![20190112110712375.png][]   2、5.7及以后的版本  #

在5.6的版本情况下需要多添加一个参数  
server-id=123456789        \#123456789是唯一的值就好

# 3、查看日志文件 #

(1)查看所有日志文件:  
mysql> show binary logs;   或show master logs;  
![20190112111050301.png][]  
(2)查看正在写入的日志文件：  
mysql> show master status;  
![20190112111106438.png][]  
(3)查看当前binlog文件内容：  
mysql> show binlog events;  \#可以格式化输出 show binlog events\\G; 或指定日志文件查看show binlog events in 'my-bin.000001';  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTM4MDk3Mg_size_16_color_FFFFFF_t_70][]  
注：  
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;   
![20190112111106438.png][]  
  
mysql> flush logs; \#结束正在写入日志文件  
![20190112111350317.png][]  
mysql> show master status;  
\+------------------+----------+--------------+------------------+-------------------+  
| File             | Position | Binlog\_Do\_DB | Binlog\_Ignore\_DB | Executed\_Gtid\_Set |  
\+------------------+----------+--------------+------------------+-------------------+  
| mysql-bin.000002 |      120 |              |                  |                   |  
\+------------------+----------+--------------+------------------+-------------------+  
(5)删除所有二进制日志，并从新开始记录  
mysql> show master status;  
![20190112111401987.png][]  
1 row in set (0.00 sec)  
mysql> reset master; \#重新开始  
![20190112111418801.png][]c)  
mysql> show master status;  
![20190112111432184.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'时间之前

#    4.二进制日志文件导出 #

\# mysqlbinlog  binlog.000001 >1.sql \#到处成sql格式  
\# mysqlbinlog --start-datetime="2018-03-20 11:25:56" --stop-datetime="2018-03-20 14:20:10" mysql-bin.000001 > /data/test01.log \#按时间点导出  
\# mysqlbinlog --start-position=203  --stop-position=203 mysql-bin.000001 > /data/test02.log \#按事件位置导出

# 5.恢复数据 #

强烈建议：做任何恢复之前都给数据库做一个完整备份，新建库进行恢复。  
恢复  
 bin-log是记录着mysql所有事件的操作，可以通过bin-log做完整恢复，基于时间点的恢复，和基于位置的恢复  
(1)基于时间点的恢复，如果确认误操作时间点为2018-03-20 10:00:00执行如下  
\# mysqlbinlog --stop-date='2018-03-02 9:59:59' mysql-bin.000001 | mysql -uroot -p  
然后跳过误操作的时间点，继续执行后面的binlog  
\# mysqlbinlog --start-date='2018-03-20 10:01:00' mysql-bin.000001 | mysql -uroot -p  
其中--stop-date='2018-03-20 9:59:59' 和 --start-date='2018-03-20 10:01:00'   
取两时间点  
\# mysqlbinlog --start-datetime="2018-03-20 11:25:56" --stop-datetime="2018-03-20 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  
到此为止基本就可以恢复被误删的数据了。  
当然我们平时还是要管理好数据库权限，避免萌新们误删数据带来不必要的麻烦。当我们操作生产环境上的正式数据库时候一点要在操作之前先备份，提前做好准备。避免给正式数据库造成不必要的数据污染。

[20190112110712375.png]: /images/20220327/74cca45c9a534c43b2b24cdc2808dfe6.png
[20190112111050301.png]: /images/20220327/ce0542419557448fa263b4c587ac7541.png
[20190112111106438.png]: /images/20220327/aabdf3040183407399fa17ea10ae1a55.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTM4MDk3Mg_size_16_color_FFFFFF_t_70]: /images/20220327/d1680b1ed1594391b8862ff57e686afc.png
[20190112111350317.png]: /images/20220327/8437c9e16c2a4130bd9cac0fbffc5b58.png
[20190112111401987.png]: /images/20220327/9aa1404970f845ba8d9fef6dec12f6b7.png
[20190112111418801.png]: /images/20220327/7fc2d71505d140198dbd8e90844b9595.png
[20190112111432184.png]: /images/20220327/9fc9714a44cd4f5289c8aa31c7e77e57.png