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

最新推荐文章于 2024-03-03 10:57:24 发布
最新推荐文章于 2024-03-03 10:57:24 发布
阅读量9.3k 收藏 5
点赞数
分类专栏： 数据库技术 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/lqx0405/article/details/46805067
版权
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_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/mysql

mysql> 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 -l
total 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.info
drwx--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.index
drwx--S--- 2 mysql mysql     4096 Jun 30 17:15 performance_schema
drwx--S--- 2 mysql mysql     4096 Jun 30 18:15 prod
-rw-rw---- 1 mysql mysql       58 Jul  8 15:20 relay-log.info
drwx--S--- 2 mysql mysql     4096 Jun 30 17:15 test
<strong>mysrv-relay-bin.index，为记录日志文件的index文件</strong>
[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.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) 

[root@mysrv mysql]# ls -l
total 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.info
drwx--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.index
drwx--S--- 2 mysql mysql     4096 Jun 30 17:15 performance_schema
drwx--S--- 2 mysql mysql     4096 Jun 30 18:15 prod
-rw-rw---- 1 mysql mysql       58 Jul  8 15:24 relay-log.info
drwx--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 -l
total 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.info
drwx--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.index
drwx--S--- 2 mysql mysql     4096 Jun 30 17:15 performance_schema
drwx--S--- 2 mysql mysql     4096 Jun 30 18:15 prod
-rw-rw---- 1 mysql mysql       58 Jul  8 15:34 relay-log.info
drwx--S--- 2 mysql mysql     4096 Jun 30 17:15 test

[root@mysrv ~]# mysqladmin refresh -u root -p
Enter password:  

[root@mysrv mysql]# ls -l
total 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.info
drwx--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.index
drwx--S--- 2 mysql mysql     4096 Jun 30 17:15 performance_schema
drwx--S--- 2 mysql mysql     4096 Jun 30 18:15 prod
-rw-rw---- 1 mysql mysql       58 Jul  8 15:36 relay-log.info
drwx--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)

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

[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日志中没有记载，此次不再做恢复操作

---数据恢复成功！


客居天涯
关注
  • 0
    点赞
  • 踩
  • 5
    收藏
    觉得还不错? 一键收藏
  • 4
    评论
专栏目录
进击的MySQL日志查询篇(5） 二进制日志
sz710211849的博客
02-08 1436
二进制日志内容是真的多啊
mysql 二进制文件总结
rainrun的博客
10-16 1316
二进制日志（Binary Log）也可叫作变更日志（Update Log），是 MySQL 中非常重要的日志。主要用于记录数据库的变化情况，即 SQL 语句的 DDL 和 DML 语句，不包含数据记录查询操作。 如果 MySQL 数据库意外停止，可以通过二进制日志文件来查看用户执行了哪些操作，对数据库服务器文件做了哪些修改，然后根据二进制日志文件中的记录来恢复数据库服务器。 默认情况下，二进制日志功能是关闭的。可以通过以下命令查看二进制日志是否开启，命令如下： mysql> show variable
mysql 二进制日志大小_mysql二进制日志相关参数
weixin_42510262的博客
01-26 338
1，binlog_cache_size和max_binlog_cache_size表示的为每个session的事物分配的缓存当插入或者修改数据的时候，不会立刻写入磁盘，而是会缓存起来，缓存的大小由binlog_cache_size 来控制mysql> show variables like '%binlog_cache%';+-----------------------+---------...
MySQL日志（二）—— 二进制日志binlog
前尘忆梦Memory的博客
08-26 1362
一、binlog介绍 mysql-binlog是MySQL数据库的二进制日志，记录了所有的DDL和DML语句（不包括select语句）,还包含语句所执行的消耗的时间，另外MySQL的二进制日志是事务安全型的。一般来说开启binlog日志会有一定的性能损耗。 二、binlog使用场景 无论是增量备份还是主从复制，都是需要开启mysql-binlog日志，并且在磁盘故障的时候可以利用mysql-binlog恢复数据。 1、主从复制： mysql replication在master端开启binlog,
MySQL二进制日志（bin_log)的作用和使用方法
最新发布
Timebro的博客
03-03 1139
MySQL的二进制日志（binary log，简称binlog）是MySQL数据库中的一个重要特性，它记录了所有对数据库执行更改的SQL语句（如INSERT、UPDATE、DELETE等），以及每个语句执行的确切时间。二进制日志是MySQL数据复制、数据恢复和审计分析的基础。
mysql日志文件空间估算_mysql日志文件过大导致磁盘空间不够的问题
weixin_42144366的博客
02-05 330
linux 通过 du -s -h ./*查看当前目录大小 文件大小逐级扫描发现mysql的路径下 文件过大集中在下图/alidata/server/mysql-5.5.37/datamysql-bin.000020mysql-bin.000019...占用了15G都是些数据库操作的日志 更新操作 服务器现在是打开一个文件内容如下PropertyID='4028828e5a9863d7015a9...
mysql-binlog-connector-java：MySQL二进制日志连接器
02-26
mysql-binlog-connector-java 注意：该存储库不再维护。 我建议迁移到 。 MySQL Binary Log连接器。 最初，项目是作为的分支开始的，但最终作为完整的重写而结束。 主要差异/特征： 自动binlog文件名/位置| GTID...
mysql二进制日志文件恢复数据库
12-15
 mysql二进制日志文件用来记录所有用户对数据库操作，即记录用户对数据库操作的sql语句。如果有此文件，当数据库发生意外时，可以通过此文件查看到用户在此文件记录的时间段内用户所做的操作，再和数据库备份配合...
MySQL启用二进制日志
01-07
1、创建存储mysql二进制日志目录 mysql> system mkdir -p /app/mysql/log/binary_log mysql> system chown -R mysql:mysql /app/mysql/log/binary_log 2、添加/etc/my.cnf 启动binary log 相关参数 log-bin=/app/...
CentOS-二进制安装mysql5.7
08-17
CentOS-二进制安装mysql5.7、CentOS-二进制安装mysql5.7、CentOS-二进制安装mysql5.7、CentOS-二进制安装mysql5.7、CentOS-二进制安装mysql5.7、CentOS-二进制安装mysql5.7、CentOS-二进制安装mysql5.7、CentOS-二...
mysql-5.7.38二进制包
09-14
mysql-5.7.38二进制包 mysql-5.7.38-el7-x86_64.tar.gz 官方下载地址：https://dev.mysql.com/downloads/mysql/
Mysql二进制日志（Binary Log）
hjkl52wh的博客
02-20 490
Mysql 数据库二进制日志（Binary log） 基本配置及二进制日志简介；
Mysql 二进制日志
u010648194的博客
03-24 6011
MySQL的二进制日志（binary log）是一个二进制文件，主要用于记录修改数据或有可能引起数据变更的MySQL语句。二进制日志（binary log）中记录了对MySQL数据库执行更改的所有操作，并且记录了语句发生时间、执行时长、操作数据等其它额外信息，但是它不记录SELECT、SHOW等那些不修改数据的SQL语句。二进制日志（binary log）主要用于数据库恢复和主从复制，以及审计（audit）操作。 一、二进制日志的参数配置 ① 开启二进制日志 show variables lik
MySQL数据库——MySQL二进制日志（Binary Log）详解
Itmastergo的博客
05-26 2839
二进制日志（Binary Log）也可叫作变更日志（Update Log），是 MySQL 中非常重要的日志。主要用于记录数据库的变化情况，即 SQL 语句的 DDL 和 DML 语句，不包含数据记录查询操作。
MySQL二进制日志详解 | binlog存储、解析、日志恢复
weixin_43816711的博客
01-17 1393
将mysqlbinlog的输出作为mysql命令的输入，就能重放binlog中记录的修改，这对于MySQL的即时点数据恢复时很有价值的。定义了单个文件的大小限制，如果二进制日志写入的内容大小超出给定值，日志就会发生滚动（关闭当前文件，重新打开一个新的日志文件）。⑦清空现有的二进制日志文件：该方法可以删除列于索引文件中的所有二进制日志，把二进制日志索引文件重新设置为空，并创建一个以.000001为后缀新的二进制日制文件，并删除过去生成的全部二进制日志。也就是说，这个文件是mysqld进程内部专用的。
MySQL日志 - Binary log二进制日志介绍
bisal的专栏
06-06 255
MySQL日志历史文章，《MySQL日志 - Slow Query Log慢查询日志》《MySQL日志 - Undo和Redo日志》《MySQL日志 - Undo回滚日志的介绍》《MySQL日志 - Relay Log中继日志的介绍》《MySQL日志 - Redo Log重做日志》《MySQL日志 - Error Log错误日志》《MySQL日志 - General Query Log》根据社群发...
MySQL数据库七种日志介绍
iuu77的博客
02-26 1931
bin log是一种数据库Server层（和存储引擎无关），以二进制形式存储在磁盘中的逻辑日志。bin log记录了数据库所有的DDL和DML操作（不包括SELECT 和 SHOW）bin log也叫做归档日志，因为他不像redo log一样循环写擦除之前的记录，而是会一直记录日志。如果单个日志文件大小超过 max_binlog_size，就会创建新的文件继续写入。bin log日志的内容格式其实就是执行SQL命令的反向逻辑，（和undo log有点类似）。
MySQL之二进制日志
weixin_52690231的博客
03-23 2299
MySQL之二进制日志
MySQL binlog(二进制日志)解析
lxw1844912514的博客
09-30 1304
binlog 顾名思义就是一种二进制日志，是一种与innodb引擎中redo/undo log完全不同的日志。它主要是用来记录对mysql数据更新或潜在发生更新的SQL语句，并以"事务"的形式保存在磁盘中。 binlog 主要作用 复制：MySQL Replication在Master端开启binlog，Master把它的二进制日志传递给slaves并回放来达到master-slave数据一致...
查看mysql二进制日志
05-25
例如，如果要查看名为 mysql-bin.000001 的二进制日志文件，可以使用以下命令： ``` mysqlbinlog mysql-bin.000001 ``` 这将会输出该二进制日志文件中所有的 SQL 语句。如果想要将输出保存到文件中，可以使用...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 4
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值