recover data from binary log

转自: http://www.0550go.com/database/mysql/recover-data-from-binary-log.html

要使用mysqlbinlog 恢复数据首先必须启用过binglog,通常我们会在配置文件中添加如下选项:

vi /etc/my.cnf
log-bin=mysql-bin

当然不是等到数据损坏的时候再启用binglog,那时候已经为时已晚!

从binlog恢复数据演练:

1、创建数据库:

mysql> create database test;
Query OK, 1 row affected (0.05 sec)
mysql> use test;
Database changed

2、创建表:

mysql> create table user(id int auto_increment not null primary key, age int(3),name varchar(20));
Query OK, 0 rows affected (0.25 sec)

3、相关操作:

mysql> insert into user(age,name)values(34,'Jack');
Query OK, 1 row affected (0.01 sec)
mysql> insert into user(age,name)values(34,'bob'); 
Query OK, 1 row affected (0.11 sec)
mysql> insert into user(age,name)values(34,'max');  
Query OK, 1 row affected (0.01 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.25 sec)
插入3条记录,并刷新一次logs
mysql> insert into user(age,name)values(34,'zhoufurong');
Query OK, 1 row affected (0.03 sec)
mysql> insert into user(age,name)values(34,'jerry');     
Query OK, 1 row affected (0.04 sec)
mysql> insert into user(age,name)values(34,'mashou');
Query OK, 1 row affected (0.01 sec)
mysql> insert into user(age,name)values(34,'mead');  
Query OK, 1 row affected (0.07 sec)
mysql> delete from user where id between 5 and 7;
Query OK, 3 rows affected (0.05 sec)
mysql> insert into user(age,name)values(40, 'aaa');
Query OK, 1 row affected (0.06 sec)
mysql> insert into user(age,name)values(40, 'bbb');
Query OK, 1 row affected (0.02 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.17 sec)
插入4条记录,并删除记录5-7,然后又插入2条记录

mysql> insert into user(age,name)values(40, 'ccc');
Query OK, 1 row affected (0.06 sec)
mysql> insert into user(age,name)values(40, 'ddd');
Query OK, 1 row affected (0.04 sec)
mysql> drop table user;
Query OK, 0 rows affected (0.14 sec)
mysql> show tables;
Empty set (0.00 sec)
插入2条记录,删除整个表

4、恢复数据:

总共插入了11条记录,中途删除了2条记录,最后丢弃了整张表,首先要做的我们要查询每步操作的起始pos和停目pos,因为我们要恢复所有的数据,所以中间要跳过delete操作和drop操作

首先,我们要查看binlog具体信息,通过下面命令:

mysqlbinlog mysql-bin.000001 > 1.txt
# cat 1.txt 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#150416 16:15:41 server id 1  end_log_pos 107   Start: binlog v 4, server v 5.5.34-log created 150416 16:15:41 at startup
ROLLBACK/*!*/;
BINLOG '
rW8vVQ8BAAAAZwAAAGsAAAAAAAQANS41LjM0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACtby9VEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107
#150416 16:16:02 server id 1  end_log_pos 190   Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1429172162/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1437073440/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create database test
/*!*/;
# at 190
#150416 16:16:45 server id 1  end_log_pos 343   Query   thread_id=1     exec_time=0     error_code=0
use `test`/*!*/;
SET TIMESTAMP=1429172205/*!*/;
create table user(id int auto_increment not null primary key, age int(3),name varchar(20))
/*!*/;
# at 343
#150416 16:18:14 server id 1  end_log_pos 411   Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1429172294/*!*/;
BEGIN
/*!*/;
# at 411
# at 458
#150416 16:18:14 server id 1  end_log_pos 458   Table_map: `test`.`user` mapped to number 33
#150416 16:18:14 server id 1  end_log_pos 501   Write_rows: table id 33 flags: STMT_END_F

BINLOG '
RnAvVRMBAAAALwAAAMoBAAAAACEAAAAAAAEABHRlc3QABHVzZXIAAwMDDwI8AAY=
RnAvVRcBAAAAKwAAAPUBAAAAACEAAAAAAAEAA//4AQAAACIAAAAESmFjaw==
'/*!*/;
# at 501
#150416 16:18:14 server id 1  end_log_pos 528   Xid = 10
COMMIT/*!*/;
# at 528
#150416 16:18:24 server id 1  end_log_pos 596   Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1429172304/*!*/;
BEGIN
/*!*/;
# at 596
# at 643
#150416 16:18:24 server id 1  end_log_pos 643   Table_map: `test`.`user` mapped to number 33
#150416 16:18:24 server id 1  end_log_pos 685   Write_rows: table id 33 flags: STMT_END_F

BINLOG '
UHAvVRMBAAAALwAAAIMCAAAAACEAAAAAAAEABHRlc3QABHVzZXIAAwMDDwI8AAY=
UHAvVRcBAAAAKgAAAK0CAAAAACEAAAAAAAEAA//4AgAAACIAAAADYm9i
'/*!*/;
# at 685
#150416 16:18:24 server id 1  end_log_pos 712   Xid = 11
COMMIT/*!*/;
# at 712
#150416 16:18:31 server id 1  end_log_pos 780   Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1429172311/*!*/;
BEGIN
/*!*/;
# at 780
# at 827
#150416 16:18:31 server id 1  end_log_pos 827   Table_map: `test`.`user` mapped to number 33
#150416 16:18:31 server id 1  end_log_pos 869   Write_rows: table id 33 flags: STMT_END_F

BINLOG '
V3AvVRMBAAAALwAAADsDAAAAACEAAAAAAAEABHRlc3QABHVzZXIAAwMDDwI8AAY=
V3AvVRcBAAAAKgAAAGUDAAAAACEAAAAAAAEAA//4AwAAACIAAAADbWF4
'/*!*/;
# at 869
#150416 16:18:31 server id 1  end_log_pos 896   Xid = 12
COMMIT/*!*/;
# at 896
#150416 16:18:36 server id 1  end_log_pos 939   Rotate to mysql-bin.000002  pos: 4
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

或者:

mysqlbinlog mysql-bin.000001 | more

当数据量不是很大的情况下,你可以这样操作:

mysql> show binlog events in 'mysql-bin.000001';
+------------------+-----+-------------+-----------+-------------+--------------------------------------------------------------------------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                                                                                   |
+------------------+-----+-------------+-----------+-------------+--------------------------------------------------------------------------------------------------------+
| mysql-bin.000001 |   4 | Format_desc |         1 |         107 | Server ver: 5.5.34-log, Binlog ver: 4                                                                  |
| mysql-bin.000001 | 107 | Query       |         1 |         190 | create database test                                                                                   |
| mysql-bin.000001 | 190 | Query       |         1 |         343 | use `test`; create table user(id int auto_increment not null primary key, age int(3),name varchar(20)) |
| mysql-bin.000001 | 343 | Query       |         1 |         411 | BEGIN                                                                                                  |
| mysql-bin.000001 | 411 | Table_map   |         1 |         458 | table_id: 33 (test.user)                                                                               |
| mysql-bin.000001 | 458 | Write_rows  |         1 |         501 | table_id: 33 flags: STMT_END_F                                                                         |
| mysql-bin.000001 | 501 | Xid         |         1 |         528 | COMMIT /* xid=10 */                                                                                    |
| mysql-bin.000001 | 528 | Query       |         1 |         596 | BEGIN                                                                                                  |
| mysql-bin.000001 | 596 | Table_map   |         1 |         643 | table_id: 33 (test.user)                                                                               |
| mysql-bin.000001 | 643 | Write_rows  |         1 |         685 | table_id: 33 flags: STMT_END_F                                                                         |
| mysql-bin.000001 | 685 | Xid         |         1 |         712 | COMMIT /* xid=11 */                                                                                    |
| mysql-bin.000001 | 712 | Query       |         1 |         780 | BEGIN                                                                                                  |
| mysql-bin.000001 | 780 | Table_map   |         1 |         827 | table_id: 33 (test.user)                                                                               |
| mysql-bin.000001 | 827 | Write_rows  |         1 |         869 | table_id: 33 flags: STMT_END_F                                                                         |
| mysql-bin.000001 | 869 | Xid         |         1 |         896 | COMMIT /* xid=12 */                                                                                    |
| mysql-bin.000001 | 896 | Rotate      |         1 |         939 | mysql-bin.000002;pos=4                                                                                 |
+------------------+-----+-------------+-----------+-------------+--------------------------------------------------------------------------------------------------------+

从这个日志可以看到,mysql-bin.000001 里面是创建数据库,创建表,插入三条记录操作,并无删除记录操作!因为只删除了表,所以可以忽略库的恢复!

# mysqlbinlog --database=test mysql-bin.000001 --start-position=190 --stop-position=896 | mysql -uroot -p123456

查看数据库,表回来了!

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| user           |
+----------------+
1 row in set (0.00 sec)

mysql> select * from user;
+----+------+------+
| id | age  | name |
+----+------+------+
|  1 |   34 | Jack |
|  2 |   34 | bob  |
|  3 |   34 | max  |
+----+------+------+
3 rows in set (0.00 sec)

查看mysql-bin.000002日志:

mysql> show binlog events in 'mysql-bin.000002';
+------------------+------+-------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos  | Event_type  | Server_id | End_log_pos | Info                                  |
+------------------+------+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000002 |    4 | Format_desc |         1 |         107 | Server ver: 5.5.34-log, Binlog ver: 4 |
| mysql-bin.000002 |  107 | Query       |         1 |         175 | BEGIN                                 |
| mysql-bin.000002 |  175 | Table_map   |         1 |         222 | table_id: 33 (test.user)              |
| mysql-bin.000002 |  222 | Write_rows  |         1 |         271 | table_id: 33 flags: STMT_END_F        |
| mysql-bin.000002 |  271 | Xid         |         1 |         298 | COMMIT /* xid=14 */                   |
| mysql-bin.000002 |  298 | Query       |         1 |         366 | BEGIN                                 |
| mysql-bin.000002 |  366 | Table_map   |         1 |         413 | table_id: 33 (test.user)              |
| mysql-bin.000002 |  413 | Write_rows  |         1 |         457 | table_id: 33 flags: STMT_END_F        |
| mysql-bin.000002 |  457 | Xid         |         1 |         484 | COMMIT /* xid=15 */                   |
| mysql-bin.000002 |  484 | Query       |         1 |         552 | BEGIN                                 |
| mysql-bin.000002 |  552 | Table_map   |         1 |         599 | table_id: 33 (test.user)              |
| mysql-bin.000002 |  599 | Write_rows  |         1 |         644 | table_id: 33 flags: STMT_END_F        |
| mysql-bin.000002 |  644 | Xid         |         1 |         671 | COMMIT /* xid=16 */                   |
| mysql-bin.000002 |  671 | Query       |         1 |         739 | BEGIN                                 |
| mysql-bin.000002 |  739 | Table_map   |         1 |         786 | table_id: 33 (test.user)              |
| mysql-bin.000002 |  786 | Write_rows  |         1 |         829 | table_id: 33 flags: STMT_END_F        |
| mysql-bin.000002 |  829 | Xid         |         1 |         856 | COMMIT /* xid=17 */                   |
| mysql-bin.000002 |  856 | Query       |         1 |         924 | BEGIN                                 |
| mysql-bin.000002 |  924 | Table_map   |         1 |         971 | table_id: 33 (test.user)              |
| mysql-bin.000002 |  971 | Delete_rows |         1 |        1045 | table_id: 33 flags: STMT_END_F        |
| mysql-bin.000002 | 1045 | Xid         |         1 |        1072 | COMMIT /* xid=18 */                   |
| mysql-bin.000002 | 1072 | Query       |         1 |        1140 | BEGIN                                 |
| mysql-bin.000002 | 1140 | Table_map   |         1 |        1187 | table_id: 33 (test.user)              |
| mysql-bin.000002 | 1187 | Write_rows  |         1 |        1229 | table_id: 33 flags: STMT_END_F        |
| mysql-bin.000002 | 1229 | Xid         |         1 |        1256 | COMMIT /* xid=19 */                   |
| mysql-bin.000002 | 1256 | Query       |         1 |        1324 | BEGIN                                 |
| mysql-bin.000002 | 1324 | Table_map   |         1 |        1371 | table_id: 33 (test.user)              |
| mysql-bin.000002 | 1371 | Write_rows  |         1 |        1413 | table_id: 33 flags: STMT_END_F        |
| mysql-bin.000002 | 1413 | Xid         |         1 |        1440 | COMMIT /* xid=20 */                   |
| mysql-bin.000002 | 1440 | Rotate      |         1 |        1483 | mysql-bin.000003;pos=4                |
+------------------+------+-------------+-----------+-------------+---------------------------------------+
30 rows in set (0.00 sec)

mysql-bin.000002日志,在这个日志中,有删除记录操作,所以要想全部恢复回来数据,一条不差,就得跳过delte_rows这步:

# mysqlbinlog --database=test  mysql-bin.000002 --start-position=4 --stop-position=971 | mysql -uroot -p123456 

mysql> select * from user;
+----+------+------------+
| id | age  | name       |
+----+------+------------+
|  1 |   34 | Jack       |
|  2 |   34 | bob        |
|  3 |   34 | max        |
|  4 |   34 | zhoufurong |
|  5 |   34 | jerry      |
|  6 |   34 | mashou     |
|  7 |   34 | mead       |
+----+------+------------+
7 rows in set (0.00 sec)

# mysqlbinlog --database=test  mysql-bin.000002 --start-position=1045 --stop-position=1483 | mysql -uroot -p123456
mysql> select * from user;
+----+------+------------+
| id | age  | name       |
+----+------+------------+
|  1 |   34 | Jack       |
|  2 |   34 | bob        |
|  3 |   34 | max        |
|  4 |   34 | zhoufurong |
|  5 |   34 | jerry      |
|  6 |   34 | mashou     |
|  7 |   34 | mead       |
|  8 |   40 | aaa        |
|  9 |   40 | bbb        |
+----+------+------------+
9 rows in set (0.00 sec)

查看mysql-bin.000003:

mysql> show binlog events in 'mysql-bin.000003';
+------------------+-----+-------------+-----------+-------------+---------------------------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                                    |
+------------------+-----+-------------+-----------+-------------+---------------------------------------------------------+
| mysql-bin.000003 |   4 | Format_desc |         1 |         107 | Server ver: 5.5.34-log, Binlog ver: 4                   |
| mysql-bin.000003 | 107 | Query       |         1 |         175 | BEGIN                                                   |
| mysql-bin.000003 | 175 | Table_map   |         1 |         222 | table_id: 33 (test.user)                                |
| mysql-bin.000003 | 222 | Write_rows  |         1 |         264 | table_id: 33 flags: STMT_END_F                          |
| mysql-bin.000003 | 264 | Xid         |         1 |         291 | COMMIT /* xid=22 */                                     |
| mysql-bin.000003 | 291 | Query       |         1 |         359 | BEGIN                                                   |
| mysql-bin.000003 | 359 | Table_map   |         1 |         406 | table_id: 33 (test.user)                                |
| mysql-bin.000003 | 406 | Write_rows  |         1 |         448 | table_id: 33 flags: STMT_END_F                          |
| mysql-bin.000003 | 448 | Xid         |         1 |         475 | COMMIT /* xid=23 */                                     |
| mysql-bin.000003 | 475 | Query       |         1 |         581 | use `test`; DROP TABLE `user` /* generated by server */ |
+------------------+-----+-------------+-----------+-------------+---------------------------------------------------------+
10 rows in set (0.00 sec)

mysql-bin.000003 有一个drop table操作,所以在恢复这个文件是跳过drop操作就好:

# mysqlbinlog --database=test  mysql-bin.000003 --start-position=4 --stop-position=475 | mysql -uroot -p123456
mysql> select * from user;
+----+------+------------+
| id | age  | name       |
+----+------+------------+
|  1 |   34 | Jack       |
|  2 |   34 | bob        |
|  3 |   34 | max        |
|  4 |   34 | zhoufurong |
|  5 |   34 | jerry      |
|  6 |   34 | mashou     |
|  7 |   34 | mead       |
|  8 |   40 | aaa        |
|  9 |   40 | bbb        |
| 10 |   40 | ccc        |
| 11 |   40 | ddd        |
+----+------+------------+
11 rows in set (0.00 sec)

 

数据全部取回!

注意:在进行恢复前,一定要将flush logs产生一个新的log文件,这样做是恢复操作将写入一个新的binlog,不会写入老的日志文件中,如若不然记录会接着老的日志往下写,到时查询起来就会比较凌乱了!

当日志非常多时,上面的恢复可能不太适合了,那么binlog可以按时间进行恢复:

/mysql/bin/mysqlbinlog --database=bbs --start-datetime="2013-01-22 05:00:00" --stop-datetime="2013-01-22 09:00:00" /mysql/data/mysql-bin.000001 | /mysql/bin/mysql -u root -p123456 -f

注释:
–database=bbs // 需要恢复的数据库
–start-datetime=”2013-01-22 05:00:00″ // 恢复数据起始时间
–stop-datetime=”2013-01-22 09:00:00″ // 恢复数据截至时间
/mysql/data/mysql-bin.000001 // 引用的日志文件,多个日志就多增加几条
/mysql/bin/mysql -u root -p123456 // 登录Mysql帐号密码
-f // 忽略错误,否则遇到相同条目恢复就会终止

需要注意的问题:

mysqlbinlog命令只能恢复binglog日志里有的数据
一般我们都使用expire-logs-days来限制日志保存的天数,以防止日志文件不断增大撑爆硬盘。
假设 expire-logs-days = 7
那么7天之前的数据如果被误删除将无法通过此命令恢复,比如discuz论坛,你要是误删除了某个版块,而这个版块创建已经有半年,显然版块以及版块内的所有帖子,无法使用近期的binglog日志恢复。
遇到这种情况,要想恢复数据,必须配合每天定时备份的数据来进行
假设数据是每天早上5点定时打包备份,早上9点误删了某个版块
首先恢复打包备份的数据:

service mysql stop
cd /mysql/data
tar zxvf /mysql/dbbackup/bbs20130122.tar.gz
service mysql start
/mysql/bin/mysqlbinlog --database=bbs --start-datetime="2013-01-22 5:00:00" --stop-datetime="2013-01-22 9:00:00" /mysql/data/mysql-bin.000001 | /mysql/bin/mysql -u root -p123456 -f

 

 

 

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注