[MySql]mysql维护-mysql开启binlog教程

[MySql]mysql维护-mysql开启binlog教程


每一个工程师特别是网络工程师基本会遇到一个问题:自己在测试的时候稍微一个不小心把生产数据库里面的数据给删除了该咋办?比如忘了加上一个限制条件把整个User表给update了,这事大了,找部门经理?他可能比你还不懂。一个一个修改?这效率也太低了再说你咋记得之前的数据是怎样的呢?

所以MySQL提供了一个功能:binlog。顾名思义就是二级制的日志,这里记录了你每个操作,如果误删除了一个什么样的东西,没事,可以找回来的。

一、查看是否开启了log_bin

show variables like ‘log_%’;

二、如果没有开启bin_log:

vim /etc/my.cnf

这里在网上找到的资料中,由于MySQL的版本的不同,配置log_bin的方法也不大一样。我按照网上的方式进行配置完成以后导致我的MySQL不能正确启动,看了my.cnf的注释中,只要把log_bin前面的注释去掉就算开启了,最后我得出的开启的方式竟然是这么简单。

如图是我的MySQL版本号:

当按照第一步查询出来的结果是ON的时候就说明我们的MySQL bin_log已经开启了。接下来就可以通过工具来恢复我们的数据了。

三、利用bin_log来恢复数据

首先我们需要做准备工作:

  1. 创建imopei_test数据库;

  2. 创建User表格

  3. 插入数据

  4. 刷新日志

  5. 查看bin_log文件

  6. 完整备份test数据库

  7. 删除表中插入的部分数据

  8. 删除数据库

  9. 通过bin_log来恢复数据库数据

1-3步在这里就不做演示了。


use imopei_test; CREATE TABLE imopei_user(id int(4), name VARCHAR(50)); INSERT INTO imopei_user VALUES(1, '狗蛋'); INSERT INTO imopei_user VALUES(2, '狗剩'); SELECT * FROM imopei_user;

4. 刷新日志

没有刷新日志之前,我们只有一个log_bin。

show master logs;

刷新之后

flush logs;

show master logs;

可以看到我们多出了一个后缀为000002的bin_log文件。

此时我们再插入两条数据,让其他生成第三个bin_log文件

INSERT INTO imopei_user VALUES(3, ‘狗娃’);

INSERT INTO imopei_user VALUES(4, ‘狗子’);

此时我们再做一次刷新bin_log的动作,现在我们就有了三个bin_log文件了。

5. 我们可以查看binlog中的内容如下:

因为我没有指定bin_log的文件的位置,所以我的bin_log文件配置根据show variables like ‘log_%’;查询出来的结果是在/var/lib/mysql/里面。所以我们现在切换到这个目录底下来:cd /var/lib/mysql/

图中所示就是我们刚刚操作过程中生成的bin_log文件

现在我们可以通过/usr/local/mysql/bin/mysqlbinlog里面的命令来查询他。但是在这里我出现了一个问题:

这时候有两种方法可以解决:

一是在MySQL的配置/etc/my.cnf中将default-character-set=utf8 修改为 character-set-server = utf8,但是这需要重启MySQL服务,如果你的MySQL服务正在忙,那这样的代价会比较大。

二是用mysqlbinlog –no-defaults mysql-bin.000004 命令打开

我使用第二种方式解决:

从图中可以发现,我们创建表以及插入数据的动作都被记录下来了,到了这里我发现我已经成功一半了。

6. 完整备份数据库方法:

利用mysqldump:

mysqldump -uroot -p123456 imopei_test >/usr/local/src/back/imopei.test.sql

日常报错……

解决:修改my.cnf中,socket = 后面修改成途中报错的位置。

再运行一次上面的备份命令,运行成功。

已经完整的备份:

7. 删除部分数据:

DELETE FROM imopei_user WHERE id = 3;


# 刷新日志 flush logs; show master logs;

8. 删除数据库

drop database imopei_test;


# 刷新日志 flush logs; show master logs;

已经出现了8个日志文件了。

查看第七个的时候已经发现,删除imopei_test表的时候已经被记录了

end_log_pos就是MySQL记录这个操作的一个位置的节点,在我们下面恢复数据库的时候会有帮助的。

9. 通过bin_log来恢复数据库数据

做了那么久的准备工作,终于来到这一步了,可想而知这一步才是我们生产环境中经常需要使用的。

通常来讲我们一般情况下是误删数据,而不会删掉整个表格(如果你连表格都删掉了也真的是太不小心了,要不就是你没什么经验…)

鉴于刚刚在上面有一步我们把整个表格干掉了,所以现在我们需要来恢复他的表格结构以及全部的数据,刚刚我们已经备份了所有的数据了,现在我们把它还原回来。

/usr/local/mysql/bin/mysql -uroot -p123456 imopei_test</usr/local/src/back/imopei_test.sql

数据已经完整恢复了:

– 不指定恢复时间和位置进行数据恢复

现在我查看生成的第6个日志文件中发现,这个文件记录的是删除第3个USER的数据库的状态,所以现在我们需要把数据库还原到这个状态来。

我们使用以下命令来恢复:


mysqlbinlog --no-defaults /var/lib/mysql/iZwz95e88wxcryr7f5g91aZ-bin.000006 |mysql -uroot -p123456

已经可以看出来数据库恢复到删除第3条记录的状态了。

10. 通过bin_log指定时间或者指定位置来恢复数据库到某种状态

以上第9的例子中,是通过恢复所有数据的样子再来进行恢复操作的,在市级生产中实用性并不是很想,因为很多时候我们并不知道什么时候删除了重要的数据,以致可以让我们在误操作之前做一次完整的备份。所以就有了通过时间或者位置进行恢复的功能。而时间或者位置的关系中,因为很多时候我们在一个时间里面可能会操作多条数据,所以建议是,尽量使用位置来进行数据的恢复。

首先我假装不小心把数据库表清空:

这时候我需要进入MySQL,更新我的日志。

查看近几个日志文档,发现在iZwz95e88wxcryr7f5g91aZ-bin.000014这个日志里面

所以通过制定时间或者位置来恢复

通过时间进行恢复:


mysqlbinlog --stop-datetime='2016-12-11 15:36:07' /var/lib/mysql/iZwz95e88wxcryr7f5g91aZ-bin.000014 |mysql -uroot -p123456

通过位置进行恢复:


mysqlbinlog --stop-position=2116 /var/lib/mysql/iZwz95e88wxcryr7f5g91aZ-bin.000014 |mysql -uroot -p123456

数据回来了~现在可以假装无事发生,去喝一杯咖啡了。

这个过程中遇到的问题:

我是按照教程里面走的,准备要回去第三部删除一个数据那里,可是死活也不能回去。我发现可能是因为之前删掉了表格的缘故导致无法回滚到上一个表格的状态。所以一般来说如果我们误删了或者清空了一个表格只要按照我上面的方式寻找到删除表格SQL的上一个点,就可以吧数据给回滚了。

通过bin_log恢复数据库部分参考资料:http://www.ilanni.com/?spm=5176.100239.blogcont43163.11.esLZoi&p=7911

出现mysqlbinlog: unknown variable ‘default-character-set=utf8’参考资料:http://www.cnblogs.com/cobbliu/p/4311926.html

出现mysqldump: Got error: 2002: Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2) when trying to connect参考资料:http://www.jb51.net/article/56952.htm

Weidan

评论已关闭。