您有个知识币

【退出】

使用 MYSQLBINLOG 来恢复数据

上一篇文章写了备份,这篇文章写写如何恢复。这里不再自己写,从网上拷一篇过来,原因是给上篇文章一个恢复方案,另一个原因是别人已经写的很好了。

BINLOG就是一个记录SQL语句的过程,和普通的LOG一样。不过只是她是二进制存储,普通的是十进制存储罢了。

1、配置文件里要写的东西:

[mysqld]

log-bin=mysql-bin(名字可以改成自己的,如果不改名字的话,默认是以主机名字命名)

重新启动MSYQL服务。

二进制文件里面的东西显示的就是执行所有语句的详细记录,当然一些语句不被记录在内,要了解详细的,见手册页。

2、查看自己的BINLOG的名字是什么

show binlog events;

query result(1 records)

Log_name

Pos

Event_type

Server_id

End_log_pos

Info

yueliangdao_binglog.000001

4

Format_desc

1

106

Server ver: 5.1.22-rc-community-log, Binlog ver: 4

3、我做了几次操作后,她就记录了下来

又一次 show binlog events 的结果。

query result(4 records)

 

Log_name

Pos

Event_type

Server_id

End_log_pos

Info

yueliangdao_binglog.000001

4

Format_desc

1

106

Server ver: 5.1.22-rc-community-log, Binlog ver: 4

yueliangdao_binglog.000001

106

Intvar

1

134

INSERT_ID=1

yueliangdao_binglog.000001

134

Query

1

254

use `test`; create table a1(id int not null auto_increment primary key, str varchar(1000)) engine=myisam

yueliangdao_binglog.000001

254

Query

1

330

use `test`; insert into a1(str) values ('I love you'),('You love me')

yueliangdao_binglog.000001

330

Query

1

485

use `test`; drop table a1

4、用mysqlbinlog 工具来显示记录的二进制结果,然后导入到文本文件,为了以后的恢复。

详细过程如下:

D:\LAMP\MYSQL5\data>mysqlbinlog --start-position=4 --stop-position=106 yueliangd

ao_binglog.000001 > c:\\test1.txt

test1.txt的文件内容:/*!40019 SET @@session.max_insert_delayed_threads=0*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#7122 16:9:18 server id 1 end_log_pos 106     Start: binlog v 4, server v 5.1.22-rc-community-log created 7122 16:9:18 at startup

# Warning: this binlog was not closed properly. Most probably mysqld crashed writing it.

ROLLBACK/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

第二行的记录:

D:\LAMP\MYSQL5\data>mysqlbinlog --start-position=106 --stop-position=134 yuelian

gdao_binglog.000001 > c:\\test1.txt

test1.txt内容如下:

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 106

#7122 16:22:36 server id 1 end_log_pos 134     Intvar

SET INSERT_ID=1/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

第三行记录:

D:\LAMP\MYSQL5\data>mysqlbinlog --start-position=134 --stop-position=254 yuelian

gdao_binglog.000001 > c:\\test1.txt

内容:

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 134

#7122 16:55:31 server id 1 end_log_pos 254     Query    thread_id=1    exec_time=0    error_code=0

use test/*!*/;

SET TIMESTAMP=1196585731/*!*/;

SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/;

SET @@session.sql_mode=1344274432/*!*/;

/*!\C utf8 *//*!*/;

SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;

create table a1(id int not null auto_increment primary key,

str varchar(1000)) engine=myisam/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

第四行的记录:

D:\LAMP\MYSQL5\data>mysqlbinlog --start-position=254 --stop-position=330 yuelian

gdao_binglog.000001 > c:\\test1.txt

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 254

#7122 16:22:36 server id 1 end_log_pos 330     Query    thread_id=1    exec_time=0    error_code=0

use test/*!*/;

SET TIMESTAMP=1196583756/*!*/;

SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/;

SET @@session.sql_mode=1344274432/*!*/;

/*!\C utf8 *//*!*/;

SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;

use `test`; insert into a1(str) values ('I love you'),('You love me')/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

5、查看这些东西是为了恢复数据,而不是为了好玩。所以我们最中还是为了要导入结果到MYSQL中。

D:\LAMP\MYSQL5\data>mysqlbinlog --start-position=134 --stop-position=330 yuelian

gdao_binglog.000001 | mysql -uroot -p

或者

D:\LAMP\MYSQL5\data>mysqlbinlog --start-position=134 --stop-position=330 yuelian

gdao_binglog.000001 >test1.txt

进入MYSQL导入

mysql> source c:\\test1.txt

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Database changed

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Charset changed

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

6、查看数据:

mysql> show tables;

+----------------+

| Tables_in_test |

+----------------+

| a1             |

+----------------+

1 row in set (0.01 sec)

mysql> select * from a1;

+----+-------------+

| id | str         |

+----+-------------+

| 1 | I love you |

| 2 | You love me |

+----+-------------+

2 rows in set (0.00 sec)

将一个mysqlbinlog文件导为sql文件

cd  cd /usr/local/mysql

./mysqlbinlog /usr/local/mysql/data/mysql-bin.000001 > /opt/001.sql

将mysql-bin.000001日志文件导成001.sql

可以在mysqlbinlog语句中通过--start-date和--stop-date选项指定DATETIME格式的起止时间

./mysqlbinlog --stop-date="2009-04-10 17:41:28" /usr/local/mysql/data/mysql-bin.000002 > /opt/004.sql

将mysql-bin.000002文件中截止到2009-04-10 17:41:28的日志导成004.sql

./mysqlbinlog --start-date="2009-04-10 17:30:05" --stop-date="2009-04-10 17:41:28" /usr/local/mysql/data/mysql-bin.000002  /usr/local/mysql/data/mysql-bin.0000023> /opt/004.sql

----如果有多个binlog文件,中间用空格隔开,打上完全路径

./mysqlbinlog --start-date="2009-04-10 17:30:05" --stop-date="2009-04-10 17:41:28" /usr/local/mysql/data/mysql-bin.000002 |mysql -u root -p123456

或者  source /opt/004.sql

将mysql-bin.000002日志文件中从2009-04-10 17:30:05到2008-04-10 17:41:28截止的sql语句导入到mysql中

本文固定链接: http://www.webzhishi.com/mysqlbinlog/ | web知识网

使用 MYSQLBINLOG 来恢复数据:等您坐沙发呢!

发表评论