MySQl Study学习之--MySQl二进制日志管理
MySQL二进制日志（Binary Log）
a、它包括的内容及作用例如以下： 包括了全部更新了数据或者已经潜在更新了数据(比方没有匹配不论什么行的一个DELETE) 包括关于每一个更新数据库(DML)的语句的运行时间信息 不包括没有改动不论什么数据的语句，假设须要启用该选项，须要开启通用日志功能 主要目的是尽可能的将数据库恢复到数据库故障点，由于二进制日志包括备份后进行的全部更新 用于在主复制server上记录全部将发送给从server的语句 启用该选项数据库性能减少1%。但保障数据库完整性，对于关键数据库值得以性能换完整。有些类似于开启归档模式。
b、开启二进制日志的方法及属性 使用--log-bin[=file_name]选项或在配置文件里指定log-bin启动时，mysqld写入包括全部更新数据的SQL命令的日志文件。 对于未给出file_name值。默认名为-bin后面所跟的主机名。
在未指定绝对路径的情形下，缺省位置保存在数据文件夹下。 每一个二进制日志名会加入一个数字扩展名用于日志老化，因此不支持自己定义的扩展名。会被mysql数字扩展名动态替换。 若当前的日志大小达到max_binlog_size。则自己主动创建新的二进制日志。 对于大的事务，二进制日志会超过max_binlog_size设定的值。也即是事务只写入一个二进制日志。 由是可知，二进制日志文件大小接近，其size不是全然相等。这点不同于oracle。 二进制日志文件会有一个相应二进制日志索引文件，该文件包括全部的二进制日志，其文件名称与二进制日志同样，扩展名为.index 二进制索引文件通过--log-bin-index[=file_name]选项来指定 RESETMASTER语句将删除全部二进制日志文件。这将影响到从库。也能够用PURGE MASTER LOGS仅仅删除部分二进制文件。
二进制日志记录了全部对数据库运行更改的操作，二进制主要有下面两种作用：
1、恢复（recovery）2、复制（replication）
二进制日志的启动：配置參数log-bin[=name]，假设不指定name。则默认二进制日志文件名称为主机名，后缀名为二级制日志的序列号。所在路径为数据库所在文件夹。
以index为后缀的文件为二进制日志的索引文件，用来存储过程生产的二进制日志。
和二进制日志相关的參数：
max_binlog_size、binlog_cache_size、sync_binlog、binlog-do-db、binlog-ignore-db、log-slave-update、binlog_format
1、max_binlog_size，该參数指定了单个二进制日志文件的最大值，假设超过该值。则产生新的二进制日志文件，后缀名+1。并记录到.index文件，从Mysql5.0開始的默认值为1073741824。代表1G。
Mysql5.5的默认值
mysql> show variables like '%max_binlog%';+----------------------------+----------------------+| Variable_name | Value |+----------------------------+----------------------+| max_binlog_cache_size | 18446744073709547520 || max_binlog_size | 1073741824 || max_binlog_stmt_cache_size | 18446744073709547520 |+----------------------------+----------------------+3 rows in set (0.00 sec)
当使用事务的存储引擎InnoDB时。全部未提交的事务会记录到一个缓存中，等待事务提交时，直接将缓冲中的二进制日志写入二进制日志文件，而该缓冲的大小由binlog_cache_size决定，默认大小为32KB，此外，binlog_cache_size是基于session的。也就是。当一个线程開始一个事务时。mysql会自己主动分配一个大小为binlog_cache_size的缓存，因此该值得设置须要相当小心，能够通过show global status 查看binlog_cache_use、binlog_cache_disk_use的状态。能够推断当前binlog_cache_size的设置是否合适。
2、Binlog_cache_size的默认大小为32KB
mysql> show variables like 'binlog_cache%';+-------------------+-------+| Variable_name | Value |+-------------------+-------+| binlog_cache_size | 32768 |+-------------------+-------+1 row in set (0.00 sec)
3、參数sync_binlog=[N]表示每写缓存多少次就同步到磁盘，假设将N设置为1，则表示採用同步写磁盘的方式来写二进制日志。该參数非常重要，这个以后还会提到。值得注意的是，在将该參数设置为1时，也应该将innodb_support_xa设为1来解决。这能够确保二进制日志和InnoDB存储引擎数据文件的同步。
4、參数binlog-do-db和binlog-ignore-db表示须要写入或者忽略写入哪些库的日志，默认值为空，则表示将全部库的日志同步到二进制日志。
5、Log-slave-update该參数在搭建master=>slave=>slave的架构时，须要配置。
6、Binlog_format參数也特别重要。
从mysql5.1版本号開始引入这个參数。该參数能够设置的值有STATEMENT\、ROW、MIXED；
（1）STATEMENT格式和之前的mysql版本号一样，二进制日志文件记录的是日志的逻辑SQL语句。
（2）在ROW格式下，二进制日志记录的不再是简单的SQL语句了。而是记录表的行更改情况，此时能够将InnoDB的事务隔离基本设为READ COMMITTED，以获得更好的并发性。
（3）MIXED格式下。mysql默认採用的STATEMENT格式进行二进制日志文件的记录，可是在一些情况下会使用ROW格式。可能的情况包含：
1）表的存储引擎为NDB。这时对于表的DML操作都会以ROW格式记录
2）使用了UUID()、USER()、CURRENT_USER()、FOUND_ROWS()、ROW_COUNT()等不确定函数
3）使用了INSERT DELAY语句
4）使用了用户定义函数
5）使用了暂时表
案例分析：1、 二进制日志存放位置
[root@mysrv ~]# more /etc/my.cnf
[mysqld] datadir=/usr/local/mysql/data/mysqlmysql> show variables like '%datadir%'; +---------------+------------------------------+ | Variable_name | Value | +---------------+------------------------------+ | datadir | /usr/local/mysql/data/mysql/ | +---------------+------------------------------+ 1 row in set (0.00 sec) 查看日志信息：[root@mysrv ~]# cd /usr/local/mysql/data/mysql/[root@mysrv mysql]# ls -ltotal 29928-rw-rw---- 1 mysql mysql 56 Jun 30 17:12 auto.cnf-rw-rw---- 1 mysql mysql 18874368 Jul 8 15:17 ibdata1-rw-rw---- 1 mysql mysql 5242880 Jul 8 15:17 ib_logfile0-rw-rw---- 1 mysql mysql 5242880 Jun 30 17:12 ib_logfile1-rw-rw---- 1 mysql mysql 124 Jul 8 15:17 master.infodrwx--S--- 2 mysql mysql 4096 Jun 30 17:15 mysql-rw-rw---- 1 mysql mysql 114 Jun 30 17:15 mysql-bin.000001-rw-rw---- 1 mysql mysql 63438 Jun 30 17:15 mysql-bin.000002-rw-rw---- 1 mysql mysql 1096670 Jun 30 17:15 mysql-bin.000003-rw-rw---- 1 mysql mysql 252 Jun 30 17:26 mysql-bin.000004-rw-rw---- 1 mysql mysql 114 Jun 30 17:40 mysql-bin.000005-rw-rw---- 1 mysql mysql 133 Jun 30 17:51 mysql-bin.000006-rw-rw---- 1 mysql mysql 114 Jun 30 17:56 mysql-bin.000007-rw-rw---- 1 mysql mysql 114 Jul 8 15:17 mysql-bin.000008-rw-rw---- 1 mysql mysql 157 Jul 8 15:18 mysql-bin.000009-rw-rw---- 1 mysql mysql 157 Jul 8 15:20 mysql-bin.000010-rw-rw---- 1 mysql mysql 114 Jul 8 15:20 mysql-bin.000011-rw-rw---- 1 mysql mysql 209 Jul 8 15:20 mysql-bin.index-rw-rw---- 1 mysql mysql 24920 Jul 8 15:21 mysrv.err-rw-rw---- 1 mysql mysql 5 Jul 8 15:17 mysrv.pid-rw-rw---- 1 mysql mysql 163 Jul 8 15:18 mysrv-relay-bin.000003-rw-rw---- 1 mysql mysql 163 Jul 8 15:20 mysrv-relay-bin.000004-rw-rw---- 1 mysql mysql 114 Jul 8 15:20 mysrv-relay-bin.000005-rw-rw---- 1 mysql mysql 75 Jul 8 15:20 mysrv-relay-bin.indexdrwx--S--- 2 mysql mysql 4096 Jun 30 17:15 performance_schemadrwx--S--- 2 mysql mysql 4096 Jun 30 18:15 prod-rw-rw---- 1 mysql mysql 58 Jul 8 15:20 relay-log.infodrwx--S--- 2 mysql mysql 4096 Jun 30 17:15 test
mysrv-relay-bin.index。为记录日志文件的index文件
[root@mysrv mysql]# cat mysql-bin.index./mysql-bin.000001./mysql-bin.000002./mysql-bin.000003./mysql-bin.000004./mysql-bin.000005./mysql-bin.000006./mysql-bin.000007./mysql-bin.000008./mysql-bin.000009./mysql-bin.000010./mysql-bin.000011./mysql-bin.000012./mysql-bin.000013./mysql-bin.0000142、二进制日志文件的切换
运行flush logs产生新的二进制日志： mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000011 | 114 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) mysql> flush logs; Query OK, 0 rows affected (0.08 sec)
[root@mysrv mysql]# ls -ltotal 29936-rw-rw---- 1 mysql mysql 56 Jun 30 17:12 auto.cnf-rw-rw---- 1 mysql mysql 18874368 Jul 8 15:17 ibdata1-rw-rw---- 1 mysql mysql 5242880 Jul 8 15:17 ib_logfile0-rw-rw---- 1 mysql mysql 5242880 Jun 30 17:12 ib_logfile1-rw-rw---- 1 mysql mysql 124 Jul 8 15:17 master.infodrwx--S--- 2 mysql mysql 4096 Jun 30 17:15 mysql-rw-rw---- 1 mysql mysql 114 Jun 30 17:15 mysql-bin.000001-rw-rw---- 1 mysql mysql 63438 Jun 30 17:15 mysql-bin.000002-rw-rw---- 1 mysql mysql 1096670 Jun 30 17:15 mysql-bin.000003-rw-rw---- 1 mysql mysql 252 Jun 30 17:26 mysql-bin.000004-rw-rw---- 1 mysql mysql 114 Jun 30 17:40 mysql-bin.000005-rw-rw---- 1 mysql mysql 133 Jun 30 17:51 mysql-bin.000006-rw-rw---- 1 mysql mysql 114 Jun 30 17:56 mysql-bin.000007-rw-rw---- 1 mysql mysql 114 Jul 8 15:17 mysql-bin.000008-rw-rw---- 1 mysql mysql 157 Jul 8 15:18 mysql-bin.000009-rw-rw---- 1 mysql mysql 157 Jul 8 15:20 mysql-bin.000010-rw-rw---- 1 mysql mysql 157 Jul 8 15:24 mysql-bin.000011-rw-rw---- 1 mysql mysql 114 Jul 8 15:24 mysql-bin.000012-rw-rw---- 1 mysql mysql 228 Jul 8 15:24 mysql-bin.index-rw-rw---- 1 mysql mysql 25322 Jul 8 15:24 mysrv.err-rw-rw---- 1 mysql mysql 5 Jul 8 15:17 mysrv.pid-rw-rw---- 1 mysql mysql 163 Jul 8 15:18 mysrv-relay-bin.000003-rw-rw---- 1 mysql mysql 163 Jul 8 15:20 mysrv-relay-bin.000004-rw-rw---- 1 mysql mysql 163 Jul 8 15:24 mysrv-relay-bin.000005-rw-rw---- 1 mysql mysql 114 Jul 8 15:24 mysrv-relay-bin.000006-rw-rw---- 1 mysql mysql 100 Jul 8 15:24 mysrv-relay-bin.indexdrwx--S--- 2 mysql mysql 4096 Jun 30 17:15 performance_schemadrwx--S--- 2 mysql mysql 4096 Jun 30 18:15 prod-rw-rw---- 1 mysql mysql 58 Jul 8 15:24 relay-log.infodrwx--S--- 2 mysql mysql 4096 Jun 30 17:15 test採用mysqladmin切换：
[root@mysrv ~]# mysqladmin flush-logs -u root -p Enter password:
[root@mysrv mysql]# ls -ltotal 29944-rw-rw---- 1 mysql mysql 56 Jun 30 17:12 auto.cnf-rw-rw---- 1 mysql mysql 18874368 Jul 8 15:17 ibdata1-rw-rw---- 1 mysql mysql 5242880 Jul 8 15:17 ib_logfile0-rw-rw---- 1 mysql mysql 5242880 Jun 30 17:12 ib_logfile1-rw-rw---- 1 mysql mysql 124 Jul 8 15:17 master.infodrwx--S--- 2 mysql mysql 4096 Jun 30 17:15 mysql-rw-rw---- 1 mysql mysql 114 Jun 30 17:15 mysql-bin.000001-rw-rw---- 1 mysql mysql 63438 Jun 30 17:15 mysql-bin.000002-rw-rw---- 1 mysql mysql 1096670 Jun 30 17:15 mysql-bin.000003-rw-rw---- 1 mysql mysql 252 Jun 30 17:26 mysql-bin.000004-rw-rw---- 1 mysql mysql 114 Jun 30 17:40 mysql-bin.000005-rw-rw---- 1 mysql mysql 133 Jun 30 17:51 mysql-bin.000006-rw-rw---- 1 mysql mysql 114 Jun 30 17:56 mysql-bin.000007-rw-rw---- 1 mysql mysql 114 Jul 8 15:17 mysql-bin.000008-rw-rw---- 1 mysql mysql 157 Jul 8 15:18 mysql-bin.000009-rw-rw---- 1 mysql mysql 157 Jul 8 15:20 mysql-bin.000010-rw-rw---- 1 mysql mysql 157 Jul 8 15:24 mysql-bin.000011-rw-rw---- 1 mysql mysql 157 Jul 8 15:34 mysql-bin.000012-rw-rw---- 1 mysql mysql 114 Jul 8 15:34 mysql-bin.000013-rw-rw---- 1 mysql mysql 247 Jul 8 15:34 mysql-bin.index-rw-rw---- 1 mysql mysql 26536 Jul 8 15:34 mysrv.err-rw-rw---- 1 mysql mysql 5 Jul 8 15:17 mysrv.pid-rw-rw---- 1 mysql mysql 163 Jul 8 15:18 mysrv-relay-bin.000003-rw-rw---- 1 mysql mysql 163 Jul 8 15:20 mysrv-relay-bin.000004-rw-rw---- 1 mysql mysql 163 Jul 8 15:24 mysrv-relay-bin.000005-rw-rw---- 1 mysql mysql 163 Jul 8 15:34 mysrv-relay-bin.000006-rw-rw---- 1 mysql mysql 114 Jul 8 15:34 mysrv-relay-bin.000007-rw-rw---- 1 mysql mysql 125 Jul 8 15:34 mysrv-relay-bin.indexdrwx--S--- 2 mysql mysql 4096 Jun 30 17:15 performance_schemadrwx--S--- 2 mysql mysql 4096 Jun 30 18:15 prod-rw-rw---- 1 mysql mysql 58 Jul 8 15:34 relay-log.infodrwx--S--- 2 mysql mysql 4096 Jun 30 17:15 test[root@mysrv ~]# mysqladmin refresh -u root -p Enter password:
[root@mysrv mysql]# ls -ltotal 29952-rw-rw---- 1 mysql mysql 56 Jun 30 17:12 auto.cnf-rw-rw---- 1 mysql mysql 18874368 Jul 8 15:17 ibdata1-rw-rw---- 1 mysql mysql 5242880 Jul 8 15:17 ib_logfile0-rw-rw---- 1 mysql mysql 5242880 Jun 30 17:12 ib_logfile1-rw-rw---- 1 mysql mysql 124 Jul 8 15:17 master.infodrwx--S--- 2 mysql mysql 4096 Jun 30 17:15 mysql-rw-rw---- 1 mysql mysql 114 Jun 30 17:15 mysql-bin.000001-rw-rw---- 1 mysql mysql 63438 Jun 30 17:15 mysql-bin.000002-rw-rw---- 1 mysql mysql 1096670 Jun 30 17:15 mysql-bin.000003-rw-rw---- 1 mysql mysql 252 Jun 30 17:26 mysql-bin.000004-rw-rw---- 1 mysql mysql 114 Jun 30 17:40 mysql-bin.000005-rw-rw---- 1 mysql mysql 133 Jun 30 17:51 mysql-bin.000006-rw-rw---- 1 mysql mysql 114 Jun 30 17:56 mysql-bin.000007-rw-rw---- 1 mysql mysql 114 Jul 8 15:17 mysql-bin.000008-rw-rw---- 1 mysql mysql 157 Jul 8 15:18 mysql-bin.000009-rw-rw---- 1 mysql mysql 157 Jul 8 15:20 mysql-bin.000010-rw-rw---- 1 mysql mysql 157 Jul 8 15:24 mysql-bin.000011-rw-rw---- 1 mysql mysql 157 Jul 8 15:34 mysql-bin.000012-rw-rw---- 1 mysql mysql 157 Jul 8 15:36 mysql-bin.000013-rw-rw---- 1 mysql mysql 114 Jul 8 15:36 mysql-bin.000014-rw-rw---- 1 mysql mysql 266 Jul 8 15:36 mysql-bin.index-rw-rw---- 1 mysql mysql 26671 Jul 8 15:35 mysrv.err-rw-rw---- 1 mysql mysql 5 Jul 8 15:17 mysrv.pid-rw-rw---- 1 mysql mysql 163 Jul 8 15:18 mysrv-relay-bin.000003-rw-rw---- 1 mysql mysql 163 Jul 8 15:20 mysrv-relay-bin.000004-rw-rw---- 1 mysql mysql 163 Jul 8 15:24 mysrv-relay-bin.000005-rw-rw---- 1 mysql mysql 163 Jul 8 15:34 mysrv-relay-bin.000006-rw-rw---- 1 mysql mysql 163 Jul 8 15:36 mysrv-relay-bin.000007-rw-rw---- 1 mysql mysql 114 Jul 8 15:36 mysrv-relay-bin.000008-rw-rw---- 1 mysql mysql 150 Jul 8 15:36 mysrv-relay-bin.indexdrwx--S--- 2 mysql mysql 4096 Jun 30 17:15 performance_schemadrwx--S--- 2 mysql mysql 4096 Jun 30 18:15 prod-rw-rw---- 1 mysql mysql 58 Jul 8 15:36 relay-log.infodrwx--S--- 2 mysql mysql 4096 Jun 30 17:15 test3、查看和日志相关的參数：
mysql> show variables like '%log%';+-----------------------------------------+---------------------------------------------+| Variable_name | Value |+-----------------------------------------+---------------------------------------------+| back_log | 50 || binlog_cache_size | 32768 || binlog_checksum | NONE || binlog_direct_non_transactional_updates | OFF || binlog_format | MIXED || binlog_row_image | FULL || binlog_rows_query_log_events | OFF || binlog_stmt_cache_size | 32768 || expire_logs_days | 0 || general_log | OFF || general_log_file | /usr/local/mysql/data/mysql/mysrv.log || innodb_flush_log_at_trx_commit | 1 || innodb_locks_unsafe_for_binlog | OFF || innodb_log_buffer_size | 8388608 || innodb_log_file_size | 5242880 || innodb_log_files_in_group | 2 || innodb_log_group_home_dir | ./ || innodb_mirrored_log_groups | 1 || innodb_undo_logs | 128 || log_bin | ON ；是否开启二进制日志 || log_bin_basename | /usr/local/mysql/data/mysql/mysql-bin || log_bin_index | /usr/local/mysql/data/mysql/mysql-bin.index || log_bin_trust_function_creators | OFF || log_error | /usr/local/mysql/data/mysql/mysrv.err || log_output | FILE || log_queries_not_using_indexes | OFF || log_slave_updates | OFF || log_warnings | 1 || max_binlog_cache_size | 18446744073709547520 || max_binlog_size | 1073741824 || max_binlog_stmt_cache_size | 18446744073709547520 || max_relay_log_size | 0 || relay_log | || relay_log_basename | || relay_log_index | || relay_log_info_file | relay-log.info || relay_log_info_repository | FILE || relay_log_purge | ON || relay_log_recovery | OFF || relay_log_space_limit | 0 || slow_query_log | OFF || slow_query_log_file | /usr/local/mysql/data/mysql/mysrv-slow.log || sql_log_bin | ON || sql_log_off | OFF || sync_binlog | 0 || sync_relay_log | 0 || sync_relay_log_info | 0 |+-----------------------------------------+---------------------------------------------+47 rows in set (0.01 sec) mysql 二进制日志应用案例： 事务操作生成二进制日志： mysql> use prod; Database changed mysql> show tables; +----------------+ | Tables_in_prod | +----------------+ | t1 | +----------------+ 1 row in set (0.02 sec) mysql> select * from t1; +------+-------+ | id | name | +------+-------+ | 1 | tom | | 2 | scott | | 3 | jerry | +------+-------+ 3 rows in set (0.00 sec) mysql> insert into t1 values (4,'rose'); Query OK, 1 row affected (0.03 sec)
mysql> commit; Query OK, 0 rows affected (0.00 sec) 查看生成的二进制日志内容：
[root@mysrv mysql]# mysqlbinlog mysql-bin.000014 /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #150708 15:36:17 server id 2 end_log_pos 114 Start: binlog v 4, server v 5.6.4-m7-log created 150708 15:36:17 # Warning: this binlog is either in use or was not closed properly. BINLOG ' 8dKcVQ8CAAAAbgAAAHIAAAABAAQANS42LjQtbTctbG9nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVgAEGggAAAAICAgCAAAAANxYJ/w= '/*!*/; # at 114 #150708 15:39:44 server id 2 end_log_pos 189 Query thread_id=5 exec_time=0 error_code=0 SET TIMESTAMP=1436341184/*!*/; SET @@session.pseudo_thread_id=5/*!*/; 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=1, @@session.auto_increment_offset=1/*!*/; /*!\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/*!*/; BEGIN /*!*/; # at 189 #150708 15:39:44 server id 2 end_log_pos 291 Query thread_id=5 exec_time=0 error_code=0 use prod/*!*/; SET TIMESTAMP=1436341184/*!*/; insert into t1 values (4,'rose') /*!*/; # at 291 #150708 15:39:44 server id 2 end_log_pos 318 Xid = 23 COMMIT/*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; mysql> insert into t1 values (5,'john'); Query OK, 1 row affected (0.02 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) [root@mysrv mysql]# strings mysql-bin.000014 5.6.4-m7-log prod prod BEGIN prod prod insert into t1 values (4,'rose') prod prod BEGINw prod prod insert into t1 values (5,'john')w
[root@mysrv mysql]# mysqlbinlog mysql-bin.000014 /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #150708 15:36:17 server id 2 end_log_pos 114 Start: binlog v 4, server v 5.6.4-m7-log created 150708 15:36:17 # Warning: this binlog is either in use or was not closed properly. BINLOG ' 8dKcVQ8CAAAAbgAAAHIAAAABAAQANS42LjQtbTctbG9nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVgAEGggAAAAICAgCAAAAANxYJ/w= '/*!*/; # at 114 #150708 15:39:44 server id 2 end_log_pos 189 Query thread_id=5 exec_time=0 error_code=0 SET TIMESTAMP=1436341184/*!*/; SET @@session.pseudo_thread_id=5/*!*/; 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=1, @@session.auto_increment_offset=1/*!*/; /*!\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/*!*/; BEGIN /*!*/; # at 189 #150708 15:39:44 server id 2 end_log_pos 291 Query thread_id=5 exec_time=0 error_code=0 use prod/*!*/; SET TIMESTAMP=1436341184/*!*/; insert into t1 values (4,'rose') /*!*/; # at 291 #150708 15:39:44 server id 2 end_log_pos 318 Xid = 23 COMMIT/*!*/; # at 318 #150708 15:42:47 server id 2 end_log_pos 393 Query thread_id=5 exec_time=0 error_code=0 SET TIMESTAMP=1436341367/*!*/; BEGIN /*!*/; # at 393 #150708 15:42:47 server id 2 end_log_pos 495 Query thread_id=5 exec_time=0 error_code=0 SET TIMESTAMP=1436341367/*!*/; insert into t1 values (5,'john') /*!*/; # at 495 #150708 15:42:47 server id 2 end_log_pos 522 Xid = 25 COMMIT/*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; [root@mysrv mysql]# 查看生成的二进制日志： mysql> show binary logs;
+------------------+-----------+| Log_name | File_size |+------------------+-----------+| mysql-bin.000001 | 114 || mysql-bin.000002 | 63438 || mysql-bin.000003 | 1096670 || mysql-bin.000004 | 252 || mysql-bin.000005 | 114 || mysql-bin.000006 | 133 || mysql-bin.000007 | 114 || mysql-bin.000008 | 114 || mysql-bin.000009 | 157 || mysql-bin.000010 | 157 || mysql-bin.000011 | 157 || mysql-bin.000012 | 157 || mysql-bin.000013 | 157 || mysql-bin.000014 | 522 |+------------------+-----------+14 rows in set (0.00 sec)查看日志记录的事件：
mysql> show binlog events;+------------------+-----+-------------+-----------+-------------+-----------------------------------------+| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |+------------------+-----+-------------+-----------+-------------+-----------------------------------------+| mysql-bin.000001 | 4 | Format_desc | 1 | 114 | Server ver: 5.6.4-m7-log, Binlog ver: 4 |+------------------+-----+-------------+-----------+-------------+-----------------------------------------+1 row in set (0.06 sec)mysql> show binlog events in 'mysql-bin.000014';+------------------+-----+-------------+-----------+-------------+----------------------------------------------+| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |+------------------+-----+-------------+-----------+-------------+----------------------------------------------+| mysql-bin.000014 | 4 | Format_desc | 2 | 114 | Server ver: 5.6.4-m7-log, Binlog ver: 4 || mysql-bin.000014 | 114 | Query | 2 | 189 | BEGIN || mysql-bin.000014 | 189 | Query | 2 | 291 | use `prod`; insert into t1 values (4,'rose') || mysql-bin.000014 | 291 | Xid | 2 | 318 | COMMIT /* xid=23 */ || mysql-bin.000014 | 318 | Query | 2 | 393 | BEGIN || mysql-bin.000014 | 393 | Query | 2 | 495 | use `prod`; insert into t1 values (5,'john') || mysql-bin.000014 | 495 | Xid | 2 | 522 | COMMIT /* xid=25 */ |+------------------+-----+-------------+-----------+-------------+----------------------------------------------+7 rows in set (0.00 sec)数据恢复案例：
1、模拟数据环境 mysql> select * from t1; +------+-------+ | id | name | +------+-------+ | 1 | tom | | 2 | scott | | 3 | jerry | | 4 | rose | | 5 | john | +------+-------+ 5 rows in set (0.00 sec) 删除数据： mysql> delete from t1; Query OK, 5 rows affected (0.02 sec)
mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> select * from t1; Empty set (0.00 sec) 利用日志恢复。将日志生成文本文件进行分析： [root@mysrv mysql]# mysqlbinlog mysql-bin.000014 >/home/mysql/log14.txt [root@mysrv mysql]# cat log14.txt /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #150708 15:36:17 server id 2 end_log_pos 114 Start: binlog v 4, server v 5.6.4-m7-log created 150708 15:36:17 # Warning: this binlog is either in use or was not closed properly. BINLOG ' 8dKcVQ8CAAAAbgAAAHIAAAABAAQANS42LjQtbTctbG9nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVgAEGggAAAAICAgCAAAAANxYJ/w= '/*!*/; # at 114 #150708 15:39:44 server id 2 end_log_pos 189 Query thread_id=5 exec_time=0 error_code=0 SET TIMESTAMP=1436341184/*!*/; SET @@session.pseudo_thread_id=5/*!*/; 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=1, @@session.auto_increment_offset=1/*!*/; /*!\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/*!*/; BEGIN /*!*/; # at 189 #150708 15:39:44 server id 2 end_log_pos 291 Query thread_id=5 exec_time=0 error_code=0 use prod/*!*/; SET TIMESTAMP=1436341184/*!*/; insert into t1 values (4,'rose') /*!*/; # at 291 #150708 15:39:44 server id 2 end_log_pos 318 Xid = 23 COMMIT/*!*/; # at 318 #150708 15:42:47 server id 2 end_log_pos 393 Query thread_id=5 exec_time=0 error_code=0 SET TIMESTAMP=1436341367/*!*/; BEGIN /*!*/; # at 393 #150708 15:42:47 server id 2 end_log_pos 495 Query thread_id=5 exec_time=0 error_code=0 SET TIMESTAMP=1436341367/*!*/; insert into t1 values (5,'john') /*!*/; # at 495 #150708 15:42:47 server id 2 end_log_pos 522 Xid = 25 COMMIT/*!*/; # at 522 #150708 15:56:57 server id 2 end_log_pos 597 Query thread_id=5 exec_time=0 error_code=0 SET TIMESTAMP=1436342217/*!*/; BEGIN /*!*/; # at 597 ；。日志记录的操作事件的编号，假设要恢复前面的数据，须要在delete前停止 #150708 15:56:57 server id 2 end_log_pos 681 Query thread_id=5 exec_time=0 error_code=0 SET TIMESTAMP=1436342217/*!*/; delete from t1 /*!*/; # at 681 #150708 15:56:57 server id 2 end_log_pos 708 Xid = 31 COMMIT/*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; [root@mysrv mysql]# 利用二进制日志恢复：[root@mysrv mysql]# mysqlbinlog mysql-bin.000014 --stop-pos=597 |mysql -u root -p Enter password: ---在delete操作前停止日志的应用
查看已恢复的数据： mysql> select * from t1; +------+------+ | id | name | +------+------+ | 4 | rose | | 5 | john | +------+------+ 2 rows in set (0.00 sec) ---前面三行数据因为从master同步而来，在slave日志中没有记载。此次不再做恢复操作 ---数据恢复成功！