mysqlbinlog恢复备份例子

浏览数：38 / 时间：2015年06月12日

二进制日志增量备份数据库使用的工具是: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*/;