详解MySQL InnoDB管理与备份二进制日志的方法

发布时间:2019-10-14编辑:脚本学堂
本文介绍下,mysql innodb数据库中管理与备份二进制日志的具体方法,有兴趣的朋友可以参考学习下。

一,mysql binlog二进制日志的重要性
当保存了某个时间点的数据备份和所有从那时以后的二进制日志,就可以重放自从上次全备以来的二进制日志并"前滚"所有的变更。

二,二进制日志配置的最佳实践
对于 innodb 如果仅是启用二进制日志是不够、还需要其他措施来保证安全:

推荐配置:
sync_binlog = 1
表示采用同步写磁盘的方式来写二进制日志、这时写操作便绕开了OS的缓冲

该默认值为0
 
innodb_support_xa = 1
确保二进制日志和InnoDB 数据文件的同步

三,影响二进制日志备份策略的因素
如下图:
<a href=http://www.jb200.com/zt/mysqlerjinzhirizhi/ target=_blank class=infotextkey>mysql二进制日志</a>

四,二进制日志的格式
二进制日志的粒度是事件、每个事件都有固定的事件头、含:When、What、Who等
因其格式为二进制不可看、我们可借助 mysqlbinlog 查看其内容

例子:
 

复制代码 代码示例:
[root@jbxue data]$ mysqlbinlog -vv mysql-bin.000023 
/*!40019 SET @@session.max_insert_delayed_threads=0*/; 
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; 
DELIMITER /*!*/; 
# at 4 
#130515 12:35:29 server id 2  end_log_pos 107Start: binlog v 4, server v 5.5.16-log created 130515 12:35:29 at startup 
# Warning: this binlog is either in use or was not closed properly. 
ROLLBACK/*!*/; 
BINLOG ' 
kRCTUQ8CAAAAZwAAAGsAAAABAAQANS41LjE2LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 
AAAAAAAAAAAAAAAAAACREJNREzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA== 
'/*!*/; 
# at 107 
#130515 12:37:47 server id 2  end_log_pos 255Querythread_id=3exec_time=0error_code=0 
SET TIMESTAMP=1368592667/*!*/; 
SET @@session.pseudo_thread_id=3/*!*/; 
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; 
SET @@session.sql_mode=0/*!*/; 
SET @@session.auto_increment_increment=2, @@session.auto_increment_offset=2/*!*/; 
/*!C utf8 *//*!*/; 
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/; 
SET @@session.lc_time_names=0/*!*/; 
SET @@session.collation_database=DEFAULT/*!*/; 
alter database db_rocky character set = utf8mb4 COLLATE = utf8mb4_unicode_ci 
/*!*/; 
DELIMITER ; 
# End of log file 
ROLLBACK /* added by mysqlbinlog */; 
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; 

这里列了 2 个事件、来看第二个事件:
 

# at 107 
#130515 12:37:47 server id 2  end_log_pos 255Querythread_id=3exec_time=0error_code=0 

第一行表示该事件的起始位置:at 107
第二行包含如下几项:
1)、事件的日期和时间:130515 12:37:47
2)、服务器 ID、这对于防止复制之间无限循环和其他问题是非常有必要的
3)、下一个事件的开始位置:end_log_pos 255

注意:该值通常是不正确的。

因为、主库会复制事件到一个缓冲区、但这样做时,MySQL 并不知道下个日志事件的位置:
1)、事件类型、这里是 Query
2)、执行改事件的线程 ID、这点对审计蛮重要的
3)、语句的时间戳和写入二进制日志的时间差:exec_time
该值在复制落后的备库上会有很大偏差

4)、事件产生的错误代码

五,清除旧的二进制日志的方法
不要用 rm 删除日志、否则、将会导致 mysql-bin.index 状态文件与磁盘上的不一致。

定期清除旧的日志的cron 命令:
 

0 0 * * * /usr/bin/myql -e "PURGE MASTER LOGS BEFORE CURRENT_DATE - INTERVAL N DAY"