mysql binlog update"/>
mysql binlog update
mysqlbinlog恢复数据
BINLOG就是一个记录SQL语句的过程,和普通的LOG一样。只是它是二进制存储,普通的是十进制存储。
==============================================================================
1.启动二进制日志记录,默认mysql关闭binlog。# vim /etc/myf,修改或添加
#log-bin = mysql-bin (此处等号后边名字可以自定义)
重启mysql
bin-log日志的存储位置:
如果log-bin配置项没指定绝对路径,则在配置的datadir目录下,不指定的话默认和数据在一起,不方便管理。
可以自己指定如log-bin=/var/lib/mysql/binlog/mysql-bin
我的datadir=/var/lib/mysql,指定上边的目录就是在mysql下建立个binlog文件夹,注意要先建立此文件件,并且注意权限,之后才可以指定成上边的存贮位置。
查看bin-log是否开启:
Show variables like "%log_bin%";
查看bin-log日志文件名:
show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 149 |
| mysql-bin.000002 | 106 |
+------------------+-----------+
新生成一个bin-log日志(即再发生的sql操作将会写入这个bin-log里):
flush logs;
查看当前bin-log日志:
show master status;
清空bin-log日志:
reset master; (一般在数据库进行完一次完整的备份时就清空一次bin-log日志)
查看某个日志:
mysqlbinlog --no-defaults mysql-bin.000001 [ | more] 此命令在命令行下执行报错可以到 /usr/local/mysql/bin/下执行。
--no-defaults 作用:
如果myf中设置了default-character-set选项,不加--np-defaults 会报错 mysqlbinlog: unknown variable 'default-character-set=utf8'
这是mysql使用mysqlbinlog的bug:
使用mysqlbinlog工具查看二进制日志时会重新读取的mysql的配置文件myf(windows下是my.ini),而不是服务器已经加 载进内存的配置文件。也就是说只要修改并保存了myf文件,而不需要重起mysql服务器,则使用mysqlbinlog查看时修改后的 myf配置文件对mysqlbinlog而言已经生效。所以这里可以使用此方法:把client选项组中default-character- set=utf8选项屏蔽掉,然后运行mysqlbinlog工具,则不会产生任何问题了。当然记得在不使用mysqlbinlog工具时把选项恢复。
使用--no-defaults 就不用修改上边的配置了,所以还是使用这个选项比较方便。
以下是mysqlbinlog后跟的主要参数:
--stop-position="100"
--start-position="50"
--stop-date="2012-01-04 21:17:50"
--start-date="2012-01-04 19:10:10"
binlog日志删除:
binlog日志会使文件增长非常快,很快会占满磁盘空间,所以要定期删除。
mysql>show variables like ‘%expire_logs_days%’; #默认是0,既不过期
mysql>set global expire_logs_days=7; #保存7天,设置全局参数(set global)是方便不重启mysql使之生效,想要重启后生效要配置到myf。
2.测试
SQL code
OK,现在测试数据已经建好了,要的就是将test表的数据全部恢复出来。
先用mysqlbinlog工具将日志文件生成txt文件出来分析。
#mysqlbinlog mysql-bin.000001 > 001.txt
#mysqlbinlog mysql-bin.000002 > 002.txt
#mysqlbinlog mysql-bin..000003 > 003.txt
注:如果报mysqlbinlog命令不存在,需要进入到/usr/local/mysql/bin下执行,
[bin] mysqlbinlog ../var/bin-log.000001 > /tmp/log1.txt //指定bin-log的相对路径
因为我们需要重做第一个日志文件的所有操作,所以这里只需要将第一个日志文件全恢复就行了。
mysqlbinlog mysql-bin.000001 | mysql -uroot –p123 [databasename] //可以指定数据库
Ok,接着,我们需要分析的是第二个日志文件。为什么要分析它呢,因为它中途执行了一个操作是DELETE,因为我们要做的是恢复全部数据,也就是我们不希望去重做这个语句。所以在这里我们要想办法去绕开它。我们先打开002.txt文件来分析一下。
# at 546#130908 2:15:17 server id 128 end_log_pos 654 Query thread_id=19 exec_time=0 error_code=0SET TIMESTAMP=1378577717/*!*/;
deletefrom gl_whos_online where full_name=2
/*!*/;
在这个文件中,我们可以看到DELETE的操作的起始位置是546,终止位置是654.
那么我们只要重做第二个日志文件的开头到546的操作,然后再从654到末尾的操作,
我们就可以把数据给恢复回来,而不会DELETE数据。所以执行两个命令:
#mysqlbinlog mysql-bin.000002 --stop-pos=546 | mysql -uroot -p123
#mysqlbinlog mysql-bin.000002 --start-pos=654 | mysql -uroot -p 123
OK,现在第二个日志文件的数据了。
第三个日志文件也是同理,只要找到DROP TABLE的位置,就可以了。
#mysqlbinlog mysql-bin.000003 --stop-pos=574 | mysql -uroot –p123
最终,全部数据都回来了。
总结:bin-log日志就是记录了操作数据库的语句,用bin-log恢复数据就是把曾执行的语句有选择的再执行一遍,执行需要的,像delete什么的就跳过。
我第一次按操作把表drop后执行binlog恢复总是报错:指定的表不存在,
因为我恢复的日志中没有创建表的语句,汗!
2014-08-20号添加内容
=====================================================================================
今天把数据库中的前三天的数据不小心删了,想要通过binlog日志恢复,这个表的数据时通过每天的定时任务插入的。
我删除了17,18,19号的数据,想通过上边的方法恢复,但是不可行,因为binlog文件太大了,要从这个表的建表语句恢复到我执行的delete语句,要执行几个binlog,文件很大不说,关键的问题是里边不光是这一个表的语句,还包含了其他表的语句。
关键时刻,帆哥出马:
思路:
1,先从binlog日志中找出17,18,19号的插入语句,因为表的数据通过每天的crontab(每天9:00执行)插入的,可以根据crontab的执行时间剔除掉binlog中的不需要的数据。
mysqlbinlog --no-defaults --start-date="2014-08-17 09:00:00" --stop-date="2014-08-17 09:01:00" mysql-bin.000005 >>mylog.txt
mysqlbinlog--no-defaults --start-date="2014-08-18 09:00:00" --stop-date="2014-08-18 09:01:00" mysql-bin.000005 >>mylog.txt
mysqlbinlog--no-defaults --start-date="2014-08-19 09:00:00" --stop-date="2014-08-19 09:01:00" mysql-bin.000005 >> mylog.txt
查看crontab执行时间:最好执行的脚本在结束后echo开始时间和结束时间到指定log,以便查看每天执行情况。
2,通过上边的三条语句把17,18,19三天的插入语句都放入到了mylog.txt中,然后过滤掉操作的不是当前表名的行,不是insert into的行。
cat mylog.txt | grep 'INSERT INTO' | grep 'tablename' > newlog.sql
3, 导入newlog.sql到当前的表中,
搞定,数据回来了。
更多推荐
mysql binlog update
发布评论