MySQL binlog日志操作详解
MySQL的二进制日志可以说是MySQL最重要的日志了，它记录了所有的DDL和DML(除了数据查询语句)语句，以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。
binlog日志的作用
其一：MySQL Replication在Master端开启binlog，Mster把它的二进制日志传递给slaves来达到master-slave数据一致的目的。
其二：自然就是数据恢复了，通过使用mysqlbinlog工具来使恢复数据。
MySQL二进制日志包括两类文件：二进制日志索引文件（文件名后缀为.index）用于记录所有的二进制文件，二进制日志文件（文件名后缀为.00000*）记录数据库所有的DDL和DML(除了数据查询语句)语句事件。
一、开启binlog日志
vim /etc/my.cnf [mysqld] log_bin=mysql-bin
重启mysqld服务使配置生效
/etc/init.d/mysqld restart
二、登录mysql服务器，通过mysql的变量配置表，查看二进制日志是否已开启.
mysql> show variables like 'log_%'; +----------------------------------------+--------------------------------+ | Variable_name | Value | +----------------------------------------+--------------------------------+ | log_bin | ON | | log_bin_basename | /var/lib/mysql/mysql-bin | | log_bin_index | /var/lib/mysql/mysql-bin.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | log_error | /var/log/mysqld.log | | log_output | FILE | | log_queries_not_using_indexes | OFF | | log_slave_updates | OFF | | log_slow_admin_statements | OFF | | log_slow_slave_statements | OFF | | log_throttle_queries_not_using_indexes | 0 | | log_warnings | 1 | +----------------------------------------+--------------------------------+ 13 rows in set (0.00 sec)
三、查看某个binlog日志内容，常用有两种方式：
1.使用mysqlbinlog自带查看命令法：
binlog是二进制文件，普通文件查看器cat more vim 等打开都是乱码，必须使用自带的 mysqlbinlog 命令查看.
[root@localhost mysql]# mysqlbinlog mysql-bin.000003
2.使用show binlog events 查看binlog日志.
mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
选项解析：
IN 'log_name' 指定要查询的binlog文件名(不指定就是第一个binlog文件)
FROM pos 指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)
LIMIT [offset,] 偏移量(不指定就是0)
row_count 查询总条数(不指定就是所有行)
截取部分查询结果：
mysql> show binlog events in 'mysql-bin.000003'\G
*************************** 1. row ***************************
Log_name: mysql-bin.000003 ----> 查询的binlog日志文件名
Pos: 4 ----> pos起始点:
Event_type: Format_desc ----> 事件类型
Server_id: 1 ----> 标识是由哪台服务器执行的
End_log_pos: 120 ----> pos结束点:120(即：下行的pos起始点)
Info: use `juzidb`; INSERT INTO `tb1` VALUES (1,'UFO') ---> 执行的sql语句
*************************** 2. row ***************************
Log_name: mysql-bin.000003
Pos: 542
Event_type: Query
Server_id: 1
End_log_pos: 625
Info: BEGIN
*************************** 3. row ***************************
Log_name: mysql-bin.000003
Pos: 946
Event_type: Query
Server_id: 1
End_log_pos: 1044
Info: drop database juzidb
这条语句可以将指定的binlog日志文件，分成有效事件行的方式返回，并可使用limit指定pos点的起始偏移，查询条数；
A.查询第一个(即mysql-bin.000001)的binlog日志：
mysql> show binlog events\G
B.指定查询 mysql-bin.00002 这个文件:
mysql> show binlog events in 'mysql-bin.000002'\G
C.指定查询 mysql-bin.000002 这个文件，从pos点:534620开始查起：
mysql> show binlog events in 'mysql-bin.000002' from 534620\G
D.指定查询 mysql-bin.000002 这个文件，从pos点:534620开始查起，查询10条
mysql> show binlog events in 'mysql-bin.000002' from 534620 limit 10\G
E.指定查询 mysql-bin.000002 这个文件，从pos点:534620开始查起，偏移2行，查询10条
mysql> show binlog events in 'mysql-bin.000002' from 534620 limit 2,10\G
四、 通过全备和binlog日志恢复数据实例：
1.假设现在是凌晨4:00，我的计划任务开始执行一次完整的数据库备份：
将juzidb数据库备份到 /tmp/bak_juzidb.sql 文件中：
[root@localhost tmp]# mysqldump -uroot -p123456 -lF --log-error=/tmp/myDump.err -B juzidb > /tmp/bak_juzidb.sql
......
大约过若干分钟，全备份就完成了，我不用担心数据丢失了，因为我有备份了，嘎嘎~~~
由于我使用了-F选项，当备份工作刚开始时系统会刷新log日志，产生新的binlog日志来记录备份之后的数据库“增删改”操作，查看一下：
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000007 | 120 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
也就是说， mysql-bin.000007 是用来记录4:00之后对数据库的所有“增删改”操作
2.早9:30上班了，业务的需求会对数据库进行各种“增删改”操作~~~~~~~
比如：创建一个tb1表并插入、修改了数据等等:
mysql>mysql> use juzidb;
mysql>CREATE TABLE IF NOT EXISTS `tb1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(16) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
导入数据:
mysql> insert into juzidb.tb1(name) values('DBA'),('CBA'),('NBA'),('UFO');
查看数据
mysql> select * from tb1; +----+------+ | id | name | +----+------+ | 1 | DBA | | 2 | CBA | | 3 | NBA | | 4 | UFO | +----+------+ 4 rows in set (0.00 sec)
中午时分又执行了修改数据操作
mysql> mysql> update juzidb.tb1 set name='BBA' where id=4;
修改后的结果：
mysql> select * from tb1; +----+------+ | id | name | +----+------+ | 1 | DBA | | 2 | CBA | | 3 | NBA | | 4 | BBA | +----+------+ 4 rows in set (0.00 sec)
假设此时是下午，脑子一热莫名地执行了一条悲催的SQL语句，整个数据库都没了：
mysql> drop database juzidb;
然后，就没然后了，凌乱一会，开始恢复吧！！
先仔细查看最后一个binlog日志，并记录下关键的pos点，到底是哪个pos点的操作导致了数据库的破坏(通常在最后几步)；
备份一下最后一个binlog日志文件：
cp -v /var/lib/mysql/mysql-bin.000007 /tmp/
此时执行一次刷新日志索引操作，重新开始新的binlog日志记录文件，理论说 mysql-bin.000007 这个文件不会再有后续写入了(便于我们分析原因及查找pos点)，以后所有数据库操作都会写入到下一个日志文件；
mysql> flush logs; Query OK, 0 rows affected (0.08 sec) mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000008 | 120 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
3.读取binlog日志，分析问题
方式一：使用mysqlbinlog读取binlog日志：
/usr/bin/mysqlbinlog /var/lib/mysql/mysql-bin.000007
方式二：登录服务器，并查看(推荐)：
以下为末尾片段：
mysql> show binlog events in 'mysql-bin.000007'\G
*************************** 1. row ***************************
Log_name: mysql-bin.000007
Pos: 4
Event_type: Format_desc
Server_id: 1
End_log_pos: 120
Info: Server ver: 5.6.36-log, Binlog ver: 4
*************************** 2. row ***************************
Log_name: mysql-bin.000007
Pos: 120
Event_type: Query
Server_id: 1
End_log_pos: 386
Info: use `juzidb`; CREATE TABLE IF NOT EXISTS `tb1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(16) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
*************************** 3. row ***************************
Log_name: mysql-bin.000007
Pos: 386
Event_type: Query
Server_id: 1
End_log_pos: 469
Info: BEGIN
*************************** 4. row ***************************
Log_name: mysql-bin.000007
Pos: 469
Event_type: Intvar
Server_id: 1
End_log_pos: 501
Info: INSERT_ID=1
*************************** 5. row ***************************
Log_name: mysql-bin.000007
Pos: 501
Event_type: Query
Server_id: 1
End_log_pos: 645
Info: use `juzidb`; insert into juzidb.tb1(name) values('DBA'),('CBA'),('NBA'),('UFO')
*************************** 6. row ***************************
Log_name: mysql-bin.000007
Pos: 645
Event_type: Xid
Server_id: 1
End_log_pos: 676
Info: COMMIT /* xid=571661 */
*************************** 7. row ***************************
Log_name: mysql-bin.000007
Pos: 676
Event_type: Query
Server_id: 1
End_log_pos: 759
Info: BEGIN
*************************** 8. row ***************************
Log_name: mysql-bin.000007
Pos: 759
Event_type: Query
Server_id: 1
End_log_pos: 880
Info: use `juzidb`; update juzidb.tb1 set name='BBA' where id=4
*************************** 9. row ***************************
Log_name: mysql-bin.000007
Pos: 880
Event_type: Xid
Server_id: 1
End_log_pos: 911
Info: COMMIT /* xid=571663 */
*************************** 10. row ***************************
Log_name: mysql-bin.000007
Pos: 911
Event_type: Query
Server_id: 1
End_log_pos: 1009
Info: drop database juzidb
*************************** 11. row ***************************
Log_name: mysql-bin.000007
Pos: 1009
Event_type: Rotate
Server_id: 1
End_log_pos: 1056
Info: mysql-bin.000008;pos=4
11 rows in set (0.00 sec)
通过分析，造成数据库破坏的pos点区间是介于 911--1009 之间，只要恢复到1009前就可。
4.现在先把凌晨备份的数据恢复:
mysql -uroot -p123456 -v < /tmp/bak_juzidb.sql
注: 至此截至当日凌晨(4:00)前的备份数据都恢复了。
但今天一整天(4:00--现在)的数据肿么办呢？就得从前文提到的 mysql-bin.000007 新日志做文章了......
5.从binlog日志恢复数据
恢复语法格式：
# mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码 数据库名
常用选项：
--start-position=953 起始pos点
--stop-position=1437 结束pos点
--start-datetime="2017-6-20 13:18:54" 起始时间点
--stop-datetime="2017-6-20 13:21:53" 结束时间点
--database=juzidb 指定只恢复juzidb数据库(一台主机上往往有多个数据库，只限本地log日志)
不常用选项：
-u --user=name Connect to the remote server as username.连接到远程主机的用户名
-p --password[=name] Password to connect to remote server.连接到远程主机的密码
-h --host=name Get the binlog from server.从远程主机上获取binlog日志
--read-from-remote-server Read binary logs from a MySQL server.从某个MySQL服务器上读取binlog日志
小结：实际是将读出的binlog日志内容，通过管道符传递给mysql命令。这些命令、文件尽量写成绝对路径；
方法1：通过binlog日志mysql-bin.000007直接恢复.
使用-d参数表示从binlog中只导出juzidb库的数据.
mysqlbinlog mysql-bin.000007 -d juzidb > mysql-bin.sql 然后vim mysql-bin.sql文件，找到误操作的mysql语句，一定要删除.我们这里误操作的sql为drop database juzidb，找到并删除即可. 然后倒入/tmp/mysql-bin.sql文件. mysql -uroot -p123456 -v < /tmp/mysql-bin.sql
另一种指定位置点导出sql的方法(-r和重定向是一个作用.)：
mysqlbinlog mysqlbin.000020 --start-position=365 --stop-postition=456 -r pos.sql
方法2：指定pos点恢复数据(部分恢复):
A.指定pos结束点恢复某段时间的数据
@ --stop-position=759 pos结束点 注：此pos结束点介于“导入实验数据”与更新 name='UFO'之间，这样可以恢复到更改name='BBA'之前的“导入测试数据” mysqlbinlog --stop-position=759 --database=juzidb mysql-bin.000007 | mysql -uroot -p123456 -v juzidb
在另一终端登录查看结果(成功恢复了)：
mysql> select * from tb1; +----+------+ | id | name | +----+------+ | 1 | DBA | | 2 | CBA | | 3 | NBA | | 4 | UFO | +----+------+ 4 rows in set (0.00 sec)
B.指定Pos点区间恢复(部分恢复)：
更新name='BBA'这条数据, Info: use `juzidb`; update juzidb.tb1 set name='BBA' where id=4 mysqlbinlog --start-position=759 --stop-position=880 --database=juzidb mysql-bin.000007 | mysql -uroot -p123456 -v juzidb
在另一终端登录查看结果(成功恢复了)：
mysql> select * from tb1; +----+------+ | id | name | +----+------+ | 1 | DBA | | 2 | CBA | | 3 | NBA | | 4 | BBA | +----+------+ 4 rows in set (0.00 sec)
事务区间：INFO信息为两个BEGIN 之间的为按事务区间分 如：Pos: 469 End_log_pos: 759
日志区间：日志区间为Pos: 759 到 End_log_pos: 880
除了用Pos点的办法进行数据恢复，也可以通过指定时间区间进行恢复，按时间恢复需要用mysqlbinlog命令读取binlog日志内容，找时间节点。
例如：
@ --start-datetime="2017-6-20 13:18:54" 起始时间点
@ --stop-datetime="2013-16-20 14:21:53" 结束时间点
通过起始时间段则可以这样恢复数据
mysqlbinlog --start-datetime="2017-6-20 13:18:54" --stop-datetime="2013-16-20 14:21:53" --database=juzidb mysql-bin.000007 | mysql -uroot -p123456 -v juzidb
总结：所谓恢复，就是让MySQL将保存在binlog日志中指定段落区间的sql语句逐个重新执行一次而已。
--master-data参数解释：
--master-data
该选项将binlog的位置和文件名追加到输出文件中。如果为1，将会输出CHANGE MASTER 命令；如果为2，输出的CHANGE MASTER命令前添加注释信息。该选项将打开--lock-all-tables
选项，除非--single-transaction也被指定（在这种情况下，全局读锁在开始导出时获得很短的时间；其他内容参考下面的--single-transaction选项）。
该选项自动关闭--lock-tables选项。
--master-data=1 从库: 告诉从库，从主库的那个位置开始同步. --master-data=2 注释中记录了获取binlog文件和位置点
myisam备份场景:
myisam备份是要锁表，不然数据可能会不一致.
mysqldump -uroot -p123456 -A -B --master-data=2 -x|gzip >/opt/all.sql.gz
innodb备份场景:
mysqldump -uroot -p123456 -A -B --master-data=2 --single-transaction|gzip >/opt/all.sql.gz for innoDB: mysqldump --user=root --all-databases --flush-privileges --single-transaction --master-data=1 --flush-logs --triggers --routines --events --hex-blob > /tmp/full_dump.sql
五、常用binlog日志操作命令
1.查看所有binlog日志列表
mysql> show master logs;
2.查看master状态，即最后(最新)一个binlog日志的编号名称，及其最后一个操作事件pos结束点(Position)值
mysql> show master status;
3.刷新log日志，自此刻开始产生一个新编号的binlog日志文件
mysql> flush logs;
注：每当mysqld服务重启时，会自动执行此命令，刷新binlog日志；在mysqldump备份数据时加 -F 选项也会刷新binlog日志；
4.binlog日志的删除
binlog的删除可以手工删除或自动删除
自动删除binlog
通过binlog参数（expire_logs_days ）来实现mysql自动删除binlog
mysql> show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 167 | | mysql-bin.000002 | 531688179 | | mysql-bin.000003 | 1091 | | mysql-bin.000004 | 360363501 | | mysql-bin.000005 | 1798 | | mysql-bin.000006 | 577 | | mysql-bin.000007 | 1056 | | mysql-bin.000008 | 720729027 | | mysql-bin.000009 | 3010 | +------------------+-----------+ 9 rows in set (0.00 sec) mysql> show variables like 'expire_logs_days'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | expire_logs_days | 0 | +------------------+-------+ 1 row in set (0.00 sec) mysql> set global expire_logs_days=7; Query OK, 0 rows affected (0.00 sec) mysql>
手工删除binlog
mysql> reset master; //清空所有的binlog日志 mysql> reset slave; //清空slave的中继日志 mysql> purge master logs before '2017-03-30 00:20:00'; //删除指定日期以前的日志索引中binlog日志文件 mysql> purge master logs to 'mysql-bin.000009'; //删除指定日志文件的日志索引之前的日志文件 或者直接用操作系统命令直接删除
5.显示当前注册的slave的列表。不以--report-host=slave_name选项为开头的slave不会显示在本列表中
mysql> show slave hosts;
MySQLbinlog的三种日志模式：
1.Statement Level模式： 每一条会修改数据的sql都会记录到master的bin-log中,slave在赋值的时候sql进程会解析成和原理master端执行过的相同的sql来再次执行. 日志量小,节约IO，提高性能 2.Row Level模式: 日志中会记录成每一行数据被修改的形式,然后在slave端再对相同的数据进行修改 记录下每一行数据修改的细节，解决函数、存储过程等的复制问题. 日志量大. 3.Mixed(混合)模式: 实际就是前两种模式的结合,在Mixed模式下，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式, 也就是在Statement和Row之间选择一种. 修改日志模式的方式： 在配置文件中修改： log-bin=mysql-bin binlog_format='STATEMENT' binlog_format='ROW' binlog_format='MIXED' 在线修改立即生效方法： SET SESSION binlog_format = 'STATEMENT'; SET SESSION binlog_format = 'ROW'; SET SESSION binlog_format = 'MIXED'; 全局生效： SET GLOBAL binlog_format = 'STATEMENT'; SET GLOBAL binlog_format = 'ROW'; SET GLOBAL binlog_format = 'MIXED';
参考文档： http://www.cnblogs.com/martinzhang/p/3454358.html
