MySQL二进制日志操作

二进制日志概念

记录对数据发生或潜在发生更改的SQL语句，并且是以二进制格式保存的日志

使用用途

查看数据库变更历史
数据库增量备份
数据库灾难恢复
MySQL复制（主从、主主复制）

二进制日志性能影响

日志即影响MySQL性能又占用大量磁盘空间。因此，往往需要做采样分析时才会打开
即使做采样分析，也最好仅在一台测试机上开启
二进制日志由于用途广泛，大多数情况下会开启。需要制定合理的备份计划和管理策略

开启二进制日志方法一：不重启修改二进制日志配置

SET @@global.log_bin=1;
SET @@global.binlog_szie=37268;（单位：bytes）
其他参数可以通过以下命令查阅：SHOW VARIABLES LIKE "%bin%";

1234567891011121314151617181920212223242526272829303132 mysql> show variables like "%bin%"; +-----------------------------------------+------------------------------------------+ | Variable_name | Value | +-----------------------------------------+------------------------------------------+ | bind_address | * | | binlog_cache_size | 32768 | | binlog_checksum | CRC32 | | binlog_direct_non_transactional_updates | OFF | | binlog_error_action | IGNORE_ERROR | | binlog_format | STATEMENT | | binlog_gtid_simple_recovery | OFF | | binlog_max_flush_queue_time | 0 | | binlog_order_commits | ON | | binlog_row_image | FULL | | binlog_rows_query_log_events | OFF | | binlog_stmt_cache_size | 32768 | | binlogging_impossible_mode | IGNORE_ERROR | | innodb_api_enable_binlog | OFF | | innodb_locks_unsafe_for_binlog | OFF | | log_bin | ON | | log_bin_basename | /data/3306/mysql-bin/mysql-bin | | log_bin_index | /data/3306/mysql-bin/mysql-bin.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | max_binlog_cache_size | 18446744073709547520 | | max_binlog_size | 1073741824 | | max_binlog_stmt_cache_size | 18446744073709547520 | | simplified_binlog_gtid_recovery | OFF | | sql_log_bin | ON | | sync_binlog | 0 | +-----------------------------------------+------------------------------------------+ 26 rows in set （0.00 sec）

常用二进制日志相关配置参数

log-bin=

#开启并指定二进制日志保存路劲及文件名，不设置则使用默认值。默认存放位置为数据库文件所目录下，名称为hostname-bin.xxxxx

max-binlog-size=500m

#设置单个二进制日志文件的最大值，默认1G，最大1G

binlog-do-db与binlog-ignore-db

#指定二进制日志文件记录哪些数据库操作

binlog-cache-size=100m

#设置二进制日志缓存大小

sync-binlog=N

#每隔N秒将缓存中的二进制日志记录写回硬盘。默认为0。不过，你经常会陷入group commit函数与I/O之间二选一的矛盾。如果在replication环境中，由于考虑到耐久性和一致性，则需要设置为1。同时，还需要设置innodb_flush_log_at_trx_commit=1以及innodb-support-ax=1（默认已开启）暂停二进制日志

SET sql_log_bin={0|1};

查看二进制日志

mysqlbinlog host2-bin.000001

创建一个数据库并做相应修改，并查看二进制日志的变化mysql> create database t1;mysql> create table tt1（id int,name varchar（20）,birthday date）;mysql> insert into tt1（id,name,birthday） values（"1","demi","1991-10-12"）;mysql> insert into tt1（id,name,birthday） values（"2","hoai","1992-02-20"）;

12345678910 mysql> select * from tt1; +------+------+------------+ | id | name | birthday | +------+------+------------+ | 1 | demi | 1991-10-12 | | 2 | hoai | 1992-02-20 | | 3 | wiss | 1991-07-14 | | 4 | kime | 1993-06-18 | +------+------+------------+ 4 rows in set （0.00 sec）

mysql> update tt1 set birthday="1991-06-28" where id=4;

12345678910 mysql> select * from tt1; +------+------+------------+ | id | name | birthday | +------+------+------------+ | 1 | demi | 1991-10-12 | | 2 | hoai | 1992-02-20 | | 3 | wiss | 1991-07-14 | | 4 | kime | 1991-06-28 | +------+------+------------+ 4 rows in set （0.00 sec）

使用mysqlbinlog命令查看二进制日志的变化

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103 [root@slave1 ~]# mysqlbinlog /data/3306/mysql-bin/mysql-bin.000011 /*！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 #160314 18:34:51 server id 3 end_log_pos 120 CRC32 0xfd6a32fe Start: binlog v 4, server v 5.6.27-log created 160314 18:34:51 at startup # Warning: this binlog is either in use or was not closed properly. ROLLBACK/*！*/; BINLOG " y5PmVg8DAAAAdAAAAHgAAAABAAQANS42LjI3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAADLk+ZWEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAf4y av0= "/*！*/; # at 120 #160314 21:03:56 server id 3 end_log_pos 208 CRC32 0x12f2847a Query thread_id=4 exec_time=0 error_code=0 SET TIMESTAMP=1457960636/*！*/; SET @@session.pseudo_thread_id=4/*！*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*！*/; SET @@session.sql_mode=1075838976/*！*/; 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 t1 /*！*/; # at 208 #160314 21:08:36 server id 3 end_log_pos 333 CRC32 0x806b0266 Query thread_id=4 exec_time=0 error_code=0 use `t1`/*！*/; SET TIMESTAMP=1457960916/*！*/; create table tt1（id int,name varchar（20）,birthday date） /*！*/; # at 333 #160314 21:10:05 server id 3 end_log_pos 408 CRC32 0xc4950130 Query thread_id=4 exec_time=0 error_code=0 SET TIMESTAMP=1457961005/*！*/; BEGIN /*！*/; # at 408 #160314 21:10:05 server id 3 end_log_pos 543 CRC32 0xab0bc88f Query thread_id=4 exec_time=0 error_code=0 SET TIMESTAMP=1457961005/*！*/; insert into tt1（id,name,birthday） values（"1","demi","1991-10-12"） /*！*/; # at 543 #160314 21:10:05 server id 3 end_log_pos 574 CRC32 0xacd9a973 Xid = 27 COMMIT/*！*/; # at 574 #160314 21:10:40 server id 3 end_log_pos 649 CRC32 0xb6e8ef83 Query thread_id=4 exec_time=0 error_code=0 SET TIMESTAMP=1457961040/*！*/; BEGIN /*！*/; # at 649 #160314 21:10:40 server id 3 end_log_pos 784 CRC32 0xef33bae2 Query thread_id=4 exec_time=0 error_code=0 SET TIMESTAMP=1457961040/*！*/; insert into tt1（id,name,birthday） values（"2","hoai","1992-02-20"） /*！*/; # at 784 #160314 21:10:40 server id 3 end_log_pos 815 CRC32 0xc1ec540f Xid = 29 COMMIT/*！*/; # at 815 #160314 21:11:00 server id 3 end_log_pos 890 CRC32 0xb4e3c45e Query thread_id=4 exec_time=0 error_code=0 SET TIMESTAMP=1457961060/*！*/; BEGIN /*！*/; # at 890 #160314 21:11:00 server id 3 end_log_pos 1025 CRC32 0xf8584b1d Query thread_id=4 exec_time=0 error_code=0 SET TIMESTAMP=1457961060/*！*/; insert into tt1（id,name,birthday） values（"3","wiss","1991-07-14"） /*！*/; # at 1025 #160314 21:11:00 server id 3 end_log_pos 1056 CRC32 0xe75877f8 Xid = 30 COMMIT/*！*/; # at 1056 #160314 21:11:33 server id 3 end_log_pos 1131 CRC32 0x12b7b345 Query thread_id=4 exec_time=0 error_code=0 SET TIMESTAMP=1457961093/*！*/; BEGIN /*！*/; # at 1131 #160314 21:11:33 server id 3 end_log_pos 1266 CRC32 0xab6f89cc Query thread_id=4 exec_time=0 error_code=0 SET TIMESTAMP=1457961093/*！*/; insert into tt1（id,name,birthday） values（"4","kime","1993-06-18"） /*！*/; # at 1266 #160314 21:11:33 server id 3 end_log_pos 1297 CRC32 0x2e4dbdb4 Xid = 31 COMMIT/*！*/; # at 1297 #160314 21:14:04 server id 3 end_log_pos 1372 CRC32 0x571a3dda Query thread_id=4 exec_time=0 error_code=0 SET TIMESTAMP=1457961244/*！*/; BEGIN /*！*/; # at 1372 #160314 21:14:04 server id 3 end_log_pos 1489 CRC32 0xaee0efb9 Query thread_id=4 exec_time=0 error_code=0 SET TIMESTAMP=1457961244/*！*/; update tt1 set birthday="1991-06-28" where id=4 /*！*/; # at 1489 #160314 21:14:04 server id 3 end_log_pos 1520 CRC32 0xa7faed24 Xid = 33 COMMIT/*！*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*！50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*！50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

修改二进制日志记录配置为ROW方式（ROW记录的二进制信息最为完整）mysql> set @@global.binlog_format=ROW;mysql> set binlog_format=ROW;

修改mysql数据

1234567891011121314 mysql> update tt1 set birthday="1992-12-20" where id=1; Query OK, 1 row affected （0.00 sec） Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from tt1; +------+------+------------+ | id | name | birthday | +------+------+------------+ | 1 | demi | 1992-12-20 | | 2 | hoai | 1992-02-20 | | 3 | wiss | 1991-07-14 | | 4 | kime | 1991-06-28 | +------+------+------------+ 4 rows in set （0.00 sec）

查看二进制日志的变化[root@slave1 ~]# mysqlbinlog /data/3306/mysql-bin/mysql-bin.000011

12345678910111213 BINLOG " 07zmVhMDAAAAMAAAAGYGAAAAAEcAAAAAAAEAAnQxAAN0dDEAAwMPCgI8AAe2N8/Z07zmVh8DAAAAPgAAAKQGAAAAAEcAAAAAAAEAAgAD///4AQAAAARkZW1pTI8P+AEAAAAEZGVtaZSR D3vrjpU= "/*！*/; # at 1700 #160314 21:29:55 server id 3 end_log_pos 1731 CRC32 0xff29a00b Xid = 41 COMMIT/*！*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*！50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*！50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

[root@slave1 ~]# mysqlbinlog /data/3306/mysql-bin/mysql-bin.000011 -v#加-v解析二进制日志

12345678910111213141516171819202122 BINLOG " 07zmVhMDAAAAMAAAAGYGAAAAAEcAAAAAAAEAAnQxAAN0dDEAAwMPCgI8AAe2N8/Z07zmVh8DAAAAPgAAAKQGAAAAAEcAAAAAAAEAAgAD///4AQAAAARkZW1pTI8P+AEAAAAEZGVtaZSR D3vrjpU= "/*！*/; ### UPDATE `t1`.`tt1` ### WHERE ### @1=1 ### @2="demi" ### @3="1991:10:12" ### SET ### @1=1 ### @2="demi" ### @3="1992:12:20" # at 1700 #160314 21:29:55 server id 3 end_log_pos 1731 CRC32 0xff29a00b Xid = 41 COMMIT/*！*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*！50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*！50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

mysqlbinlog命令参数

--start-datetime=name
--stop-datetime=name
-j, --start-position=#
--stop-position=#（指定范围查看二进制日志和--start-position一起使用）

使用二进制日志恢复数据#mysqlbinlog host-bin.000001 | mysql#mysqlbinlog host-bin.000001 > backup.sql#mysqlbinlog host-bin.000002 >> backup.sql#mysql dbname < backup.sql删除二进制日志

二进制日志会不断的增长，并产生多个文件。因此，需要制定备份计划和管理策略。无用的二进制日志要记得及时删除。
删除二进制日志的方法：

1、操作系统命令直接删除 2、RESET MASTER 3、PURGE 格式：
PURGE {MASTER|BINARY} LOGS TO "log_name" PURGE {MASTER|BINARY} LOGS BEFORE "date" 1、使用RESET MASTER命令删除二进制文件

查看二进制文件

[root@slave1 ~]# ls /data/3306/mysql-bin/mysql-bin.000001 mysql-bin.000003 mysql-bin.000005 mysql-bin.000007 mysql-bin.000009 mysql-bin.000011mysql-bin.000002 mysql-bin.000004 mysql-bin.000006 mysql-bin.000008 mysql-bin.000010 mysql-bin.index

执行reset master命令删除二进制日志文件

12	`mysql> reset master;` `Query OK, 0 rows affected （0.01 sec）`

查看结果

[root@slave1 ~]# ls /data/3306/mysql-bin/mysql-bin.000001 mysql-bin.index2、使用PURGE命令指定删除某个二进制文件之前的二进制文件[root@slave2 ~]# ls /data/3306/mysql-bin/mysql-bin.000001 mysql-bin.000003 mysql-bin.000005 mysql-bin.000007 mysql-bin.000009mysql-bin.000002 mysql-bin.000004 mysql-bin.000006 mysql-bin.000008 mysql-bin.index

12	`mysql> purge master logs to` `"mysql-bin.000005";` `Query OK, 0 rows affected （0.00 sec）`

[root@slave2 ~]# ls /data/3306/mysql-bin/mysql-bin.000005 mysql-bin.000006 mysql-bin.000007 mysql-bin.000008 mysql-bin.000009 mysql-bin.index
3、使用PURGE命令删除指定日期之前的二进制文件[root@slave2 ~]# date "+%F %T"2016-03-14 22:12:41删除5天前的全部二进制日志

12	`mysql> purge master logs before` `"2016-03-10 00:00:00";` `Query OK, 0 rows affected （0.02 sec）`

查看结果[root@slave2 ~]# ls /data/3306/mysql-bin/mysql-bin.000007 mysql-bin.000008 mysql-bin.000009 mysql-bin.index删除2016-03-14之前的全部二进制日志

12	`mysql> purge master logs before` `"2016-03-14 00:00:00";` `Query OK, 0 rows affected （0.00 sec）`

查看结果[root@slave2 ~]# ls /data/3306/mysql-bin/mysql-bin.000008 mysql-bin.000009 mysql-bin.index查看binlog日志mysql> show binary logs;

删除10天前的二进制日志文件mysql> purge master logs before date_sub（current_date,interval 10 day）;设置自动清理二进制日志方法

设置binlog的过期时间为5天mysql> set global expire_logs_days=5;
刷新设置mysql> flush logs;
修改my.cnf配置，增加二进制日志过期参数（使重启后仍然生效）expire_logs_days = 5

本文永久更新链接地址