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的默认值
|
1
2
3
4
5
6
7
8
9
|
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
|
1
2
3
4
5
6
7
|
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)
查看日志信息：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
[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
|
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)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
[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
|
[root@mysrv ~]# mysqladmin flush-logs -u root -p
Enter password:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
[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:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
|
[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
[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
[root@mysrv mysql]#
3、查看和日志相关的参数：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
|
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 二进制日志应用案例：
事务操作，生成二进制日志：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
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)
|
查看二进制日志内容：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
|
[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*/
;
|
查看日志事件：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
|
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、模拟数据环境
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
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)
|
通过二进制日志恢复：
dump日志文件：
[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 ;;日志记录操作事件编号
#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]#
将数据恢复到delete操作之前（编号：597）：
[root@mysrv mysql]# mysqlbinlog mysql-bin.000014 --stop-pos=597 |mysql -u root -p
Enter password:
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 4 | rose |
| 5 | john |
+------+------+
2 rows in set (0.00 sec)
---通过日志恢复了两行数据，其余数据恢复略 ！
