mysqlbinlog恢复备份例子

二进制日志增量备份数据库使用的工具是:mysqlbinlog;

这个是在5.1版之后就可以用。

简单的配置,先去mysql的目录:C:\Program Files\MySQL\MySQL Server 5.5就是my.ini所在的目录;

修改my.ini:

[mysqld]
log-bin=‘d:/log/mylog‘ //LOG输出的目录,一定要存在,不然MYSQL重启报错;
binlog_format = ‘MIXED‘ //二进制日志的格式有3种:

 

Mysql binlog日志有三种格式,分别为Statement,MiXED,以及ROW!

1.Statement:每一条会修改数据的sql都会记录在binlog中。

优点:不需要记录每一行的变化,减少了binlog日志量,节约了IO,提高性能。(相比row能节约多少性能与日志量,这个取决于应用的SQL情况,正常同一条记录修改或者插入row格式所产生的日志量还小于Statement产生的日志量,但是考虑到如果带条件的update操作,以及整表删除,alter表等操作,ROW格式会产生大量日志,因此在考虑是否使用ROW格式日志时应该跟据应用的实际情况,其所产生的日志量会增加多少,以及带来的IO性能问题。)

缺点:由于记录的只是执行语句,为了这些语句能在slave上正确运行,因此还必须记录每条语句在执行的时候的一些相关信息,以保证所有语句能在slave得到和在master端执行时候相同 的结果。另外mysql 的复制,像一些特定函数功能,slave可与master上要保持一致会有很多相关问题(如sleep()函数, last_insert_id(),以及user-defined functions(udf)会出现问题).

使用以下函数的语句也无法被复制:

* LOAD_FILE()

* UUID()

* USER()

* FOUND_ROWS()

* SYSDATE() (除非启动时启用了 --sysdate-is-now 选项)

同时在INSERT ...SELECT 会产生比 RBR 更多的行级锁

2.Row:不记录sql语句上下文相关信息,仅保存哪条记录被修改。

优点: binlog中可以不记录执行的sql语句的上下文相关的信息,仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程,或function,以及trigger的调用和触发无法被正确复制的问题

缺点:所有的执行的语句当记录到日志中的时候,都将以每行记录的修改来记录,这样可能会产生大量的日志内容,比如一条update语句,修改多条记录,则binlog中每一条修改都会有记录,这样造成binlog日志量会很大,特别是当执行alter table之类的语句的时候,由于表结构修改,每条记录都发生改变,那么该表每一条记录都会记录到日志中。

3.Mixedlevel: 是以上两种level的混合使用,一般的语句修改使用statment格式保存binlog,如一些函数,statement无法完成主从复制的操作,则采用row格式保存binlog,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式,也就是在Statement和Row之间选择一种.新版本的MySQL中队row level模式也被做了优化,并不是所有的修改都会以row level来记录,像遇到表结构变更的时候就会以statement模式来记录。至于update或者delete等修改数据的语句,还是会记录所有行的变更。

 

 

成功重启后会在D:\log发现有2个新文件:mylog.index,mylog.000001

使用mysqlbinlog可以查看日志:

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysqlbinlog d:\log\mylog.000016就可以在控制台看。

也可以输出到文本中看;

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysqlbinlog d:\log\mylog.000016>e:\log.txt

 

内容:

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#140917 16:51:04 server id 1  end_log_pos 107  Start: binlog v 4, server v 5.5.29-log created 140917 16:51:04
# Warning: this binlog is either in use or was not closed properly.
BINLOG ‘
eEsZVA8BAAAAZwAAAGsAAAABAAQANS41LjI5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
‘/*!*/;
# at 107
#140917 16:51:50 server id 1  end_log_pos 175  Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1410943910/*!*/;
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=1344274432/*!*/;
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/*!*/;
BEGIN
/*!*/;
# at 175
#140917 16:51:50 server id 1  end_log_pos 262  Query thread_id=1 exec_time=0 error_code=0
use `test`/*!*/;
SET TIMESTAMP=1410943910/*!*/;
insert into t values (3)
/*!*/;
# at 262
#140917 16:51:50 server id 1  end_log_pos 289  Xid = 11
COMMIT/*!*/;
# at 289
#140917 16:52:52 server id 1  end_log_pos 357  Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1410943972/*!*/;
BEGIN
/*!*/;
# at 357
#140917 16:52:52 server id 1  end_log_pos 444  Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1410943972/*!*/;
insert into t values (4)
/*!*/;
# at 444
#140917 16:52:52 server id 1  end_log_pos 471  Xid = 12
COMMIT/*!*/;
# at 471
#140917 16:53:31 server id 1  end_log_pos 574  Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1410944011/*!*/;
DROP TABLE `t` /* generated by server */
/*!*/;
# at 574
#140917 16:54:40 server id 1  end_log_pos 675  Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1410944080/*!*/;
CREATE TABLE `t` (

`id`  int NULL

)
/*!*/;
# at 675
#140917 16:51:50 server id 1  end_log_pos 743  Query thread_id=1 exec_time=404 error_code=0
SET TIMESTAMP=1410943910/*!*/;
BEGIN
/*!*/;
# at 743
#140917 16:51:50 server id 1  end_log_pos 830  Query thread_id=1 exec_time=404 error_code=0
SET TIMESTAMP=1410943910/*!*/;
insert into t values (3)
/*!*/;
# at 830
#140917 16:51:50 server id 1  end_log_pos 857  Xid = 58
COMMIT/*!*/;
# at 857
#140917 16:52:52 server id 1  end_log_pos 925  Query thread_id=1 exec_time=342 error_code=0
SET TIMESTAMP=1410943972/*!*/;
BEGIN
/*!*/;
# at 925
#140917 16:52:52 server id 1  end_log_pos 1012  Query thread_id=1 exec_time=342 error_code=0
SET TIMESTAMP=1410943972/*!*/;
insert into t values (4)
/*!*/;
# at 1012
#140917 16:52:52 server id 1  end_log_pos 1039  Xid = 63
COMMIT/*!*/;
# at 1039
#140917 16:51:50 server id 1  end_log_pos 1107  Query thread_id=1 exec_time=440 error_code=0
SET TIMESTAMP=1410943910/*!*/;
BEGIN
/*!*/;
# at 1107
#140917 16:51:50 server id 1  end_log_pos 1194  Query thread_id=1 exec_time=440 error_code=0
SET TIMESTAMP=1410943910/*!*/;
insert into t values (3)
/*!*/;
# at 1194
#140917 16:51:50 server id 1  end_log_pos 1221  Xid = 89
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

 

然后,可以试着开始恢复:

在675我创建了一个TABLE T;

现在我输入:

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysqlbinlog --stop-position=574 d:\l
og\mylog.000016|mysql -uroot -proot

然后在查看;

发现T已经删除了。恢复成功;

注意:

在每个操作INSERT,UPDATE,DELTE,DROP,CREATE都会保存在二进制日志中,

在你INSERT完之后就可以查日志看到,不用FLUSH LOGS;

 

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。