mysq-binlog

Auth: Jin
Date: 2014-04-23
参考: http://dev.mysql.com/doc/refman/5.1/en/replication-options-binary-log.html

一、介绍
binlog日志用于记录所有更新了数据或者已经潜在更新了数据（例如，没有匹配任何行的一个DELETE）的所有语句。语句以“事件”的形式保存，它描述数据更改。
1.作用
1).二进制日志的主要目的是在恢复使能够最大可能地更新数据库，因为二进制日志包含备份后进行的所有更新。
2).二进制日志还用于在主复制服务器上记录所有将发送给从服务器的语句。
2.不良影响
运行服务器时若启用二进制日志则性能大约慢1%。
3.日志文件更换策略：
使用索引来循环文件,在以下条件将循环至下一个索引
1)服务器重启
2)服务器被更新
3)日志到达了最大日志长度 max_binlog_size
4)日志被刷新 mysql> flush logs;
4.支持的存储引擎 (binlog和存储引擎无关)
MyISAM/InnoDB/ndbcluster/memory
memory引擎测试 http://www.cnblogs.com/diege/p/3683987.html
binlog+innodb组合

二、开启binlog
1、配置打开binlog
#vim /etc/my.cnf
log-bin=mysql-bin #方法目录和文件前置
log-bin=/data/logs/mysql/mysql-bin #放到指定目录 （如果使用innodbckaup备份，还是放在一起）
log_bin_index #设置此参数是指定二进制索引文件的路径与名称，不用设置默认即可
#binlog_format="STATEMENT" #基于SQL语句的复制(statement-based replication, SBR).没有明确配置时,默认为STATEMENT.
#binlog_format="ROW" #基于行的复制(row-based replication, RBR)
binlog_format="MIXED" #混合模式复制(mixed-based replication, MBR)
#可以在运行时动态修改binlog的格式 mysql> SET SESSION binlog_format = 'STATEMENT';
#二进制日志binlog_format参数说明 http://google3030.blog.163.com/blog/static/161724465201057114557138/

max_binlog_size=500M # 每个Binlog最大值，默认值是1GB
max_binlog_files=5 #5.5.27-29.0 这个版本可以设置binlog文件的数量
expire_logs_day=1 #删除多少天以前的binlog 可以在线调整set global expire_logs_days=3;调整后把配置文件也修改

binlog_cache_size=3M #事务进行过程中存储二进制日志的缓存大小,系统会为每个session分配指定大小的空间
#可以通过状态变量binlog_cache_use和binlog_cache_disk_use来帮助测试。
#binlog_cache_use：使用二进制日志缓存的事务数量 单位是?
#binlog_cache_disk_use:使用二进制日志缓存但超过binlog_cache_size值并使用临时文件来保存事务中的语句的事务数量
max_binlog_cache_size=10M #表示的时二进制日志最大可使用的内存,如果数值国小会导致系统报错.
#The maximum and default values are 4GB on 32-bit platforms and 16EB (exabytes) on 64-bit platforms.

log-bin-trust-function-creators[={0|1}]
二进制日志打开时有效,该参数决定存储函数创建者是否可以创建会导致不安全的时间记录到二进制日志的存储函数。
当为0时,此时这也是默认值,除了拥有super权限除了create routine或alter routine权限.同时也会强制函数必须和deterministic或reads sql data又或者no sql的字符集一致.
当设为1时MySQL不会去限制这些,这个变量同样作用于触发器
--sync-binlog=n #在进行n次事务提交后,MySQL将执行一次磁盘同步指令讲binlog文件的缓存写到磁盘,
当为0时(默认)MySQL总是将binlog_cache写到binlog文件,由system fs来决定什么时候来同步到磁盘.
该参数对MySQL性能有较大影响,简朝阳<MySQL性能调优与架构设计>中谈到1和0两个值之间性能能有5以上倍的差距

Replication中还以通过一下选项来减少binlog数据量,来达到提到效率的目的,前两个用在Master端,后六个是用在Slave端的
--binlog-do-db二进制日志记录的数据库(多个数据库用,分隔)
--binlog-ignore-db二进制日忽略的数据库(多个数据库用,分隔)
#但是如果在操作数据库之前，不使用use $dbname 那么所有的SQL都不会记录
#如果使用了use $dbname,那么判断规则取决于这里的$dbname，而不是SQL中操作的库
#记录到二进制日志知的内容配置 ，不用配置，默认全部，replication slave有控制复制库的参数

在replication的slave端还有一下6个参数
--replication-do-db设定需要复制的数据库(多个数据库用,分隔) (原来我以为是在master端控制复制的)
--replication-ignore-db设定忽略复制的数据库(多个数据库用,分隔)
--replication-do-table设定需要复制的表(多个表用,分隔)
--replication-ignore-table设定忽略复制的表(多个表用,分隔)
--replication-wild-do-table同replication-do-table功能一样,但是可以加通配符
--replication-wild-ignore-table同replication-ignore-table功能一样,但是可以加通配符

2、重启生效

三、清理binlog
mysql> SHOW master LOGS;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 1469 |
| mysql-bin.000002 | 256 |
| mysql-bin.000003 | 332 |
+------------------+-----------+
3 rows in set (0.00 sec)

2.手动清理日志
1)清理指定时间前的日志
PURGE {MASTER | BINARY} LOGS BEFORE ‘date’ #清除多少日期前的日志
例如：
PURGE MASTER LOGS BEFORE '2008-06-22 13:00:00';
PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 3 DAY);#现在的时间为基准，往前三天的前的日志清
2）全部清空
reset后 File和Position都重新计算
mysql> reset master;
Query OK, 0 rows affected (0.03 sec)
mysql> SHOW master LOGS;
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 106 |
+------------------+-----------+
1 row in set (0.00 sec)
mysql> SHOW master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 106 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
3）系统自动清理
expire_logs_day=1

四.解析binlog
binlog文件为二进制文件，不能直接阅读，需要用mysqlbinlog工具解析成文本
mysqlbinlog工具 可以解析本地文件，也可以解析远程服务器
(一)解析后格式说明
# mysqlbinlog /var/lib/mysql/mysql-bin.000002
# at 196
#140423 22:52:47 server id 33061 end_log_pos 418 Query thread_id=5 exec_time=2 error_code=0
SET TIMESTAMP=1398264767/*!*/;
CREATE TABLE `t5` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(25) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`) ) ENGINE=MEMORY
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
说明如下：
文件中的开始位置，结束位置
位于文件中的位置: at 196说明“事件”的起点，是以第196字节开始.end_log_pos 418说明以第418字节结束.
事件发生的时间戳：140423 22:52:47
服务器的标识：server id 33061
事件类型：Query/Table_map/Update_rows/Write_rows等
执行事件的线程编号：thread_id=5
事件执行时间: exec_time=2
错误码：error_code=0
下面就是SQL语句了，第一条在总是设置unix timestamp
SET TIMESTAMP=1398264767/*!*/;

(二)mysqlbinlog工具常用参数
--result-file=name, -r name 将输出指向给定的文件
--short-form，-s 只显示日志中包含的语句，不显示其它信息
--start-datetime=datetime
从二进制日志中第1个日期时间等于或晚于datetime参量的事件开始读取。datetime值相对于运行mysqlbinlog的机器上的本地时区。
该值格式应符合DATETIME或TIMESTAMP数据类型。
--stop-datetime=datetime 从二进制日志中第1个日期时间等于或晚于datetime参量的事件起停止读。
--start-position=N 从二进制日志中第1个位置等于N参量时的事件开始读。 #和at 196有关
--stop-position=N 从二进制日志中第1个位置等于和大于N参量时的事件起停止读。#和end_log_pos 418有关
--offset=N，-o N 跳过前N个条目。

五、通过binlog恢复数据
(一)简单测试
测试需要，我先清空原来的binlog
mysql> reset master;
1.创建数据
mysql> CREATE TABLE `t4` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(25) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`) ) ENGINE=MEMORY;
mysql> insert into t4(name) value('diege'),('diege1');
2.删除数据
mysql> drop table t4;
3.恢复
mysqlbinlog /var/lib/mysql/mysql-bin.000003 |mysql dbtest
mysql> select * from t4;
+----+--------+
| id | name |
+----+--------+
| 1 | diege1 |
| 2 | diege |
+----+--------+
2 rows in set (0.00 sec
恢复成功

(二)解析导出成文本文件修改后再恢复
--result-file=name, -r name
1.用途：
如果你需要先修改的日志中语句，可以将mysqlbinlog的输出重新指向一个文本文件。(例如，想删除由于某种原因而不想执行的语句)。
编辑好文件后，将它输入到mysql程序并执行它包含的语句。
2.操作
mysqlbinlog /var/lib/mysql/mysql-bin.000001 --result-file=binlog.sql
或者
mysqlbinlog /var/lib/mysql/mysql-bin.000001 > ./binlog.sql
mysqlbinlog /var/lib/mysql/mysql-bin.000001 >> ./binlog2.sql
手动修改后导入数据库
shell> mysql -e "source ./binlog.sql"

(三).解析后直接导入数据库
1.用途
无需修改binlog导出语句，直接拿来恢复
2.操作
mysqlbinlog hostname-bin.000001 | mysql
错误操作
shell> mysqlbinlog hostname-bin.000001 | mysql # DANGER!!
shell> mysqlbinlog hostname-bin.000002 | mysql # DANGER!!
使用与服务器的不同连接来处理二进制日志时，如果第1个日志文件包含一个CREATE TEMPORARY TABLE语句，
第2个日志包含一个使用该临时表的语句，则会造成问题。当第1个mysql进程结束时，服务器撤销临时表。
当第2个mysql进程想使用该表时，服务器报告 “不知道该表”。
通过多个binlog文件恢复的正确方法：
如果MySQL服务器上有多个要执行的二进制日志，安全的方法是在一个连接中处理它们
shell> mysqlbinlog hostname-bin.000001 hostname-bin.000002 | mysql

(四).详细控制位置恢复
mysqlbinlog --start-position=4 --stop-position=106 /var/lib/mysql/mysql-bin.000002 --short-form
mysqlbinlog --start-position=4 --stop-position=106 /var/lib/mysql/mysql-bin.000002 --result-file=binlog.sql
mysqlbinlog --start-position=4 --stop-position=106 /var/lib/mysql/mysql-bin.000002 |mysql -h127.0.0.1 -uroot -p

(五).详细控制时间恢复
mysqlbinlog --start-datetime="2014-04-23 21:00:00" --stop-datetime="2014-04-23 23:00:00" ./mysql-bin.000001 --short-form
mysqlbinlog --start-datetime="2014-04-23 21:00:00" --stop-datetime="2014-04-23 23:00:00" ./mysql-bin.000001 --result-file=binlog.sql
mysqlbinlog --start-datetime="2014-04-23 21:00:00" --stop-datetime="2014-04-23 23:00:00" ./mysql-bin.000001|mysql -h127.0.0.1 -uroot -p
http://dev.mysql.com/doc/refman/5.1/en/replication-options-binary-log.html

六.二进制日志不准确的处理
--sync-binlog=n #在进行n次事务提交后,MySQL将执行一次磁盘同步指令讲binlog文件的缓存写到磁盘,
当为0时(默认)MySQL总是将binlog_cache写到binlog文件,由system fs来决定什么时候来同步到磁盘.
该参数对MySQL性能有较大影响,简朝阳<MySQL性能调优与架构设计>中谈到1和0两个值之间性能能有5以上倍的差距

默认情况下，并不是每次写入时都将二进制日志与硬盘同步。因此如果操作系统或机器(不仅仅是MySQL服务器)崩溃，有可能二进制日志中最后的语句丢失。
要想防止这种情况，你可以使用sync_binlog全局变量(1是最安全的值，但也是最慢的，默认为0)，使二进制日志在每N次二进制日志写入后与硬盘同步。
即使sync_binlog设置为1,出现崩溃时，也有可能表内容和二进制日志内容之间存在不一致性。

如果崩溃恢复时MySQL服务器发现二进制日志变短了(即至少缺少一个成功提交的InnoDB事务)，
如果sync_binlog=1并且硬盘/文件系统的确能根据需要进行同步(有些不需要)则不会发生，则输出错误消息 (“二进制日志<名>比期望的要小”)。
在这种情况下，二进制日志不准确，复制应从主服务器的数据快照开始。