mysqlbinlog恢复数据-update20140820

mysqlbinlog恢复数据

BINLOG就是一个记录SQL语句的过程，和普通的LOG一样。只是它是二进制存储，普通的是十进制存储。

==============================================================================

1.启动二进制日志记录，默认mysql关闭binlog。
# vim /etc/my.cnf，修改或添加

#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 作用：

如果my.cnf中设置了default-character-set选项，不加--np-defaults  会报错  mysqlbinlog: unknown variable 'default-character-set=utf8'

这是mysql使用mysqlbinlog的bug:

使用mysqlbinlog工具查看二进制日志时会重新读取的mysql的配置文件my.cnf(windows下是my.ini),而不是服务器已经加 载进内存的配置文件。也就是说只要修改并保存了my.cnf文件，而不需要重起mysql服务器，则使用mysqlbinlog查看时修改后的 my.cnf配置文件对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使之生效，想要重启后生效要配置到my.cnf。

2.测试

SQL code

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41

mysql> create table test(id int auto_increment not null primary key,              val int,data varchar(20));   mysql> insert into test(val,data) values(10,'liang'); Query OK, 1 row affected (0.03 sec)   mysql> insert into test(val,data) values(20,'jia'); Query OK, 1 row affected (0.08 sec)   mysql> insert into test(val,data) values(30,'hui'); Query OK, 1 row affected (0.03 sec)   mysql> flush logs;   --产生第二个日志文件 Query OK, 0 rows affected (0.09 sec)   mysql> insert into test(val,data) values(40,'aaa'); Query OK, 1 row affected (0.05 sec)   mysql> insert into test(val,data) values(50,'bbb'); Query OK, 1 row affected (0.03 sec)   mysql> insert into test(val,data) values(60,'ccc'); Query OK, 1 row affected (0.03 sec)   mysql> delete from test where id between 4 and 5;  --删除记录 Query OK, 2 rows affected (0.05 sec)   mysql> insert into test(val,data) values(70,'ddd'); Query OK, 1 row affected (0.03 sec)   mysql> flush logs;          --产生第三个文件文件 Query OK, 0 rows affected (0.11 sec)   mysql> insert into test(val,data) values(80,'dddd'); Query OK, 1 row affected (0.05 sec)   mysql> insert into test(val,data) values(90,'eeee'); Query OK, 1 row affected (0.03 sec)  drop table test;       --删除表 Query OK, 0 row affected (0.05 sec)

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=0
SET TIMESTAMP=1378577717/*!*/;
delete from 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到当前的表中，

导入时报错：导入的文件太大了，要设置配置项max_allowed_packet，方法参看http://www.cnblogs.com/leezhxing/p/3925332.html。

搞定，数据回来了。