道者编程

Mysql binlog日志恢复增量数据以及安全模式限制

二进制日志是mysql非常重要的日志,不仅记录了MYSQL操作的全部过程,而且还能使用二进制日志恢复数据,以及主从同步都依赖二进制日志。开启binlog日志,大概损失1%的性能。

1:数据库备份:定时备份+ binlog日志恢复增量数据。

binlog日志管理查看以前的章节:mysql日志

2:实战模拟:


aaa这个表里面有5条数据,现在我们删掉2条,看怎么回复。


id为1和3的已经删除了

恢复:

查看当前使用的日志

mysql> show master status;


查看该日志:

mysqlbinlog --no-defaults /var/log/mysql/my-bin.000002 | more


可以看到这里执行了删除,那么要恢复只有找这之前的语句,往上面找:


以上红线部分就是我们要恢复的两条语句,id为1和id为3;找这两个位置点就可以恢复,但中间有一条不不需要恢复的,也就是划蓝线的部分,这时候我们需要分别恢复这两条数据。

mysqlbinlog工具看起来不太直观,我们用mysql命令查看:


事物区间:查看Info字段的BEGIN和COMMIT,BEGIN:开始;COMMIT:结束,这就是一个事物区间

从上图可以看到:

恢复id为1的事物区间是:204到502

恢复id为3的事物区间是:908到1097

执行恢复,我们用位置点恢复,位置点是pos和end_log_pos;mysqlbinlog工具的位置点是at;先恢复id为1的数据:

mysqlbinlog --no-defaults /var/log/mysql/my-bin.000002 --start-position 204 --stop-position=502 | mysql -uroot -p123
查看是否恢复成功


可以看到id为1的数据回来了。

接着恢复ID为3的数据

mysqlbinlog --no-defaults /var/log/mysql/my-bin.000002 --start-position 802 --stop-position=1097 | mysql -uroot -p123

可以看到数据都回来了。

也可以根据时间点来恢复,--start-datetime --stop-datetime

注意:从这里可以看到,如果要恢复数据,必须要找到之前的sql语句,比如恢复删除的数据,必须找到这条插入的数据,那么问题就来了,如果数据是去年插入的,今年无意中删除了?咋办?找binlog日志?一年的日志肯定很大,这样会找死人,而且以前的日志有可能都删了。

方案:要解决上的问题,需要每天进行一次定时全部备份,备份的时候一定要刷新日志,这样就重新生成了一个binlog日志文件,从这个日志文件中找到删除的记录,如果文件中没有添加的记录,那么就在定时备份的文件里找到相应的sql,然后恢复即可。

3:设置safe_updates 安全模式

在开发的时候,因为程序员或者DBA失误,update和delete没加条件,直接导致一个表的数据和删除,针对这种情况mysql提供了方案,设置safe_updates=1即可。两种设置方式:

(1):进入mysql控制台设置:

mysql>set global sql_safe_updates = 1
global为全局设置,设置后对所有用户有效,当前链接的用户,需要先退出来。set设置后重启失效,每次重启都需要重新设置比较麻烦。

(2):在my.cnf设置(永久设置)

直接在mysqld段设置会报错,mysql段设置也不起作用,可以通过init_connect设置,重启生效。

[mysqld]
init_connect='SET SQL_SAFE_UPDATES=1'

注意:此种设置对拥有super权限的没有效果,也就是如果用户是root权限,那么该设置没效果。开发过程中推荐给用户不同的项目分于不同的账号权限。

查看是否设置成功:

show variables like '%sql_safe%'

开启safe_updates后,程序要注意几点:

1:update/delete 操作的时候,必须要带where,如果不带,mysql会报错且不执行。

2:where条件必须要有索引,如果没有索引,那么必须带limit才可以执行。


最新评论:
我要评论:

看不清楚