MySQLInnoDB管理和备份二进制日志_MySQL

MySQL InnoDB管理和备份二进制日志

㈠ 二进制日志的重要性

如果有某个时间点的数据备份和所有从那时以后的二进制日志

就可以重放自从上次全备以来的二进制日志并"前滚"所有的变更

㈡ 二进制日志配置的最佳实践

对于 InnoDB 如果仅是启用二进制日志是不够、还需要其他措施来保证安全：

推荐配置如下：

● sync_binlog = 1

表示采用同步写磁盘的方式来写二进制日志、这时写操作便绕开了OS的缓冲

该默认值为0

● innodb_support_xa = 1

确保二进制日志和InnoDB 数据文件的同步

㈢ 影响二进制日志备份策略的因素

如下图：

㈣ 二进制日志的格式

二进制日志的粒度是事件、每个事件都有固定的事件头、含：When、What、Who等

因其格式为二进制不可看、我们可借助 mysqlbinlog 查看其内容

下面是一个例子：

[plain]

[mysql@even 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 107 Start: 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 255 Query thread_id=3 exec_time=0 error_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 个事件、来看第二个事件：

[plain]

# at 107

#130515 12:37:47 server id 2 end_log_pos 255 Query thread_id=3 exec_time=0 error_code=0

第一行表示该事件的起始位置：at 107

第二行包含如下几项：

● 事件的日期和时间：130515 12:37:47

● 服务器 ID、这对于防止复制之间无限循环和其他问题是非常有必要的

● 下一个事件的开始位置：end_log_pos 255

注意了、该值通常是不正确的

因为、主库会复制事件到一个缓冲区、但这样做时

MySQL 并不知道下个日志事件的位置

● 事件类型、这里是 Query

● 执行改事件的线程 ID、这点对审计蛮重要的

● 语句的时间戳和写入二进制日志的时间差：exec_time

该值在复制落后的备库上会有很大偏差

● 事件产生的错误代码

㈤ 清除老的二进制日志的方法

不要用 rm 删除日志、否则、将会导致 mysql-bin.index 状态文件与磁盘上的不一致

应该用类似下面的 cron 命令：

[plain]

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