当前位置: 代码迷 >> MySQL >> MySQl Study学习之-MySQl二进制日志管理
  详细解决方案

MySQl Study学习之-MySQl二进制日志管理

热度:114   发布时间:2016-05-05 16:56:43.0
MySQl Study学习之--MySQl二进制日志管理
MySQl Study学习之--MySQl二进制日志管理

MySQL二进制日志(Binary Log)


  a、它包含的内容及作用如下:

    包含了所有更新了数据或者已经潜在更新了数据(比如没有匹配任何行的一个DELETE)

    包含关于每个更新数据库(DML)的语句的执行时间信息

    不包含没有修改任何数据的语句,如果需要启用该选项,需要开启通用日志功能

    主要目的是尽可能的将数据库恢复到数据库故障点,因为二进制日志包含备份后进行的所有更新

    用于在主复制服务器上记录所有将发送给从服务器的语句

    启用该选项数据库性能降低1%,但保障数据库完整性,对于重要数据库值得以性能换完整。有些类似于
Oracle开启归档模式。
  

  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_usebinlog_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、 二进制日志存放位置


[[email protected] ~]# more /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data/mysql

mysql> show variables like '%datadir%';
+---------------+------------------------------+
| Variable_name | Value                        |
+---------------+------------------------------+
| datadir       | /usr/local/mysql/data/mysql/ |
+---------------+------------------------------+
1 row in set (0.00 sec)


查看日志信息:

[[email protected] ~]# cd /usr/local/mysql/data/mysql/[[email protected] 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
<strong>mysrv-relay-bin.index,为记录日志文件的index文件</strong>
[[email protected] 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.000014

2、二进制日志文件的切换

执行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)

[[email protected] 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切换:

[[email protected] ~]# mysqladmin flush-logs -u root -p
Enter password: 

[[email protected] 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

[[email protected] ~]# mysqladmin refresh -u root -p
Enter password: 


[[email protected] 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 test

3、查看和日志相关的参数:
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   <span style="color:#ff0000;">  ;是否开启二进制日志 </span>                                    || 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)

查看生成的二进制日志内容:

[[email protected] 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 [email protected]_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)

[[email protected] 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

[[email protected] 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 [email protected]_COMPLETION_TYPE*/;
[[email protected] 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)


利用日志恢复,将日志生成文本文件进行分析:
[[email protected] mysql]# mysqlbinlog mysql-bin.000014 >/home/mysql/log14.txt


[[email protected] 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 [email protected]_COMPLETION_TYPE*/;
[[email protected] mysql]# 

利用二进制日志恢复:
[[email protected] 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日志中没有记载,此次不再做恢复操作

---数据恢复成功!


版权声明:本文为博主原创文章,未经博主允许不得转载。

  相关解决方案