简介:
MySQL的二进制日志可以说或是MySQL最重要的日志了,它记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二进制日志是失误安全型的.
MySQL的二进制日志的作用是显而易见的,可以方便的备份这些日志以便做数据恢复,也可以作为主从复制的同步文件,然而二进制日志的大小可能会根据不同的需求而存在麻烦,所以让日志回滚是必须的,当然MySQL已经为我们提供了二进制回滚的功能,那就是max_binlog_size参数。
默认MySQL的二进制日志达到1G后就会自动回滚,如果我们想要更小的二进制日志,可以使用max_binlog_size参数来设置。测试使用max_binlog_size=200M,具体应用就是在配置文件里添加这个参数max_binlog_size=200M,官方文档是这样解释的:
mysqld在每个二进制日志名后面添加一个数字扩展名。每次你启动服务器或刷新日志时该数字则增加。如果当前的日志大小达到 max_binlog_size,还会自动创建新的二进制日志。如果你正使用大的事务,二进制日志还会超过 max_binlog_size:事务全写入一个二进制日志中,绝对不要写入不同的二进制日志中。也就是说,在达到max_binlog_size的时候,如果正在处理一个大的事务,那么二进制日志会在处理完这个事务后才会回滚,所以该二进制日志可能会大于所设定的max_binlog_size。
在主从复制的应用中,可能我们不希望二进制日志过大,因为日志过大可能会影响日志的执行效率,适当调整max_binlog_size的值还是非常有意义的,当然还要记得定期清理长时间不用的日志哦,要不然你的硬盘空间很快就会被用完的。
开启mysql二进制日志:
编辑my.cnf,添加
|
1
2
3
4
5
6
|
[root@ogg mysql]# cat /etc/my.cnf
# binary logging is required
for
replication
log-bin = /
var
/lib/mysql/mysql-bin
# binary logging format - mixed recommended
#binlog_format=mixed
binlog_format=row
|
开启日志后需要myssqladmin flush logs才能生效。需要注意的是log-bin指定扩展名是无效的,当mysql创建二进制日志文件时,首先创建一个以“mysql_log_bin”为名称,以“.index”为后缀的文件;再创建一个以“mysql_log_bin”为名称,以 “.000001”为后缀的文件。当mysql服务重新启动一次以“.000001”为后缀的文件会增加一个,并且后缀名加1递增;如果日志长度超过了 max_binlog_size的上限(默认是1G)也会创建一个新的日志文件;使用flush logs(mysql命令符)或者执行mysqladmin –u –p flush-logs(windows命令提示符)也会创建一个新的日志文件。
案 例分析:
|
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
|
事务做DML操作:
mysql>
delete
from
test2
where
id=
60
;
Query OK,
1
row affected (
0.01
sec)
mysql> commit;
Query OK,
0
rows affected (
0.00
sec)
mysql>
insert
into
test2 values (
60
,
'jack'
);
Query OK,
1
row affected (
0.00
sec)
mysql> commit;
Query OK,
0
rows affected (
0.00
sec)
mysql> update test2 set name=
'hellen'
where
id=
50
;
Query OK,
1
row affected (
0.01
sec)
Rows matched:
1
Changed:
1
Warnings:
0
mysql> commit;
Query OK,
0
rows affected (
0.00
sec)
查看日志:
[root@ogg mysql]# mysqlbinlog mysql-bin
.000008
-d test
/*!40019 SET @@session.max_insert_delayed_threads=0*/
;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/
;
DELIMITER
/*!*/
;
#
at
4
#
140928
13
:
44
:
37
server id
1
end_log_pos
114
Start: binlog v
4
, server v
5.6.
4
-m7-log created
140928
13
:
44
:
37
at
startup
# Warning:
this
binlog is either
in
use
or
was
not
closed properly.
ROLLBACK
/*!*/
;
BINLOG '
RaAnVA8BAAAAbgAAAHIAAAABAAQANS42LjQtbTctbG9nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABFoCdUEzgNAAgAEgAEBAQEEgAAVgAEGggAAAAICAgCAAAAADM7vj8=
'
/*!*/
;
#
at
114
#
140928
13
:
45
:
40
server id
1
end_log_pos
182
Query thread_id=
3
exec_time=
0
error_code=
0
SET TIMESTAMP=
1411883140
/*!*/
;
SET @@session.pseudo_thread_id=
3
/*!*/
;
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 latin1 */
/*!*/
;
SET @@session.character_set_client=
8
,@@session.collation_connection=
8
,@@session.collation_server=
8
/*!*/
;
SET @@session.lc_time_names=
0
/*!*/
;
SET @@session.collation_database=DEFAULT
/*!*/
;
BEGIN
/*!*/
;
#
at
182
#
at
229
#
140928
13
:
45
:
40
server id
1
end_log_pos
229
Table_map: `test`.`test2` mapped to number
66
#
140928
13
:
45
:
40
server id
1
end_log_pos
268
Delete_rows: table id
66
flags: STMT_END_F
BINLOG '
hKAnVBMBAAAALwAAAOUAAAAAAEIAAAAAAAEABHRlc3QABXRlc3QyAAID/gL+CgM=
hKAnVBkBAAAAJwAAAAwBAAAAAEIAAAAAAAEAAv/8PAAAAARqYWNr
'
/*!*/
;
#
at
268
#
140928
13
:
45
:
40
server id
1
end_log_pos
295
Xid =
11
COMMIT
/*!*/
;
#
at
295
#
140928
13
:
46
:
39
server id
1
end_log_pos
363
Query thread_id=
3
exec_time=
0
error_code=
0
SET TIMESTAMP=
1411883199
/*!*/
;
BEGIN
/*!*/
;
#
at
363
#
at
410
#
140928
13
:
46
:
39
server id
1
end_log_pos
410
Table_map: `test`.`test2` mapped to number
66
#
140928
13
:
46
:
39
server id
1
end_log_pos
449
Write_rows: table id
66
flags: STMT_END_F
BINLOG '
v6AnVBMBAAAALwAAAJoBAAAAAEIAAAAAAAEABHRlc3QABXRlc3QyAAID/gL+CgM=
v6AnVBcBAAAAJwAAAMEBAAAAAEIAAAAAAAEAAv/8PAAAAARqYWNr
'
/*!*/
;
#
at
449
#
140928
13
:
46
:
39
server id
1
end_log_pos
476
Xid =
14
COMMIT
/*!*/
;
#
at
476
#
140928
13
:
49
:
22
server id
1
end_log_pos
544
Query thread_id=
3
exec_time=
0
error_code=
0
SET TIMESTAMP=
1411883362
/*!*/
;
BEGIN
/*!*/
;
#
at
544
#
at
591
#
140928
13
:
49
:
22
server id
1
end_log_pos
591
Table_map: `test`.`test2` mapped to number
66
#
140928
13
:
49
:
22
server id
1
end_log_pos
643
Update_rows: table id
66
flags: STMT_END_F
BINLOG '
YqEnVBMBAAAALwAAAE8CAAAAAEIAAAAAAAEABHRlc3QABXRlc3QyAAID/gL+CgM=
YqEnVBgBAAAANAAAAIMCAAAAAEIAAAAAAAEAAv
///DIAAAAEam9obvwyAAAABmhlbGxlbg==
'
/*!*/
;
#
at
643
#
140928
13
:
49
:
22
server id
1
end_log_pos
670
Xid =
16
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
|
[root@ogg mysql]# pwd
/
var
/lib/mysql
[root@ogg mysql]# ls -l
total
28820
-rw-rw----
1
mysql mysql
56
Sep
17
14
:
03
auto.cnf
-rw-rw----
1
mysql mysql
18874368
Sep
28
15
:
23
ibdata1
-rw-rw----
1
mysql mysql
5242880
Sep
28
15
:
23
ib_logfile0
-rw-rw----
1
mysql mysql
5242880
Sep
17
13
:
51
ib_logfile1
-rw-rw----
1
mysql mysql
32
Sep
26
17
:
35
master-log-bin.index
drwx------
2
mysql root
4096
Sep
17
13
:
51
mysql
-rw-rw----
1
mysql mysql
133
Sep
28
13
:
14
mysql-bin
.000001
-rw-rw----
1
mysql mysql
574
Sep
28
13
:
22
mysql-bin
.000002
-rw-rw----
1
mysql mysql
417
Sep
28
13
:
27
mysql-bin
.000003
-rw-rw----
1
mysql mysql
315
Sep
28
13
:
29
mysql-bin
.000004
-rw-rw----
1
mysql mysql
314
Sep
28
13
:
31
mysql-bin
.000005
-rw-rw----
1
mysql mysql
133
Sep
28
13
:
33
mysql-bin
.000006
-rw-rw----
1
mysql mysql
314
Sep
28
13
:
44
mysql-bin
.000007
-rw-rw----
1
mysql mysql
9539
Sep
28
15
:
23
mysql-bin
.000008
-rw-rw----
1
mysql mysql
256
Sep
28
13
:
44
mysql-bin.index
-rw-rw----
1
mysql root
77132
Sep
28
15
:
13
ogg.err
-rw-rw----
1
mysql mysql
5
Sep
28
13
:
44
ogg.pid
drwx------
2
mysql mysql
4096
Sep
17
13
:
51
performance_schema
drwx------
2
mysql root
4096
Sep
26
15
:
33
test
|
由于日志是以二进制方式存储的,不能直接读取,需要使用mysql自带的mysqlbinlog工具来进行查看
mysqlbinlog mysql-bin.000002 -d test
mysqlbinlog有一些选项可以使用,简单说明常用选项:
-d,--database=name :指定数据库名称,只列出指定数据库的操作.
-D, --disable-log-bin :执行恢复的时候,禁止二进制日志.可以防止同一台MySQL加上-t时进入死循环
-o,--offset=n :忽略掉日志前n行命令
-r,--result-file=name :将输出日志到指定文件
-R, --read-from-remote-server :从一个MySQL服务器上读取二进制
-s,--short-form :显示简单格式,省略一些信息
-S, --socket=name :socket文件连接path.
-t, --to-last-log :和-R一起使用,在二进制日志结束的时候并不会停止,而是在MySQL服务器最后生成的binlog结束,如果输出和输入都在一台MySQL上可能会导致死循环.
--set-charset=char-name :在输出文本格式的时候,在第一行加上set names char-name.
--start-datetime=# --stop-datetime=# :指定输出起始日期的日志.
--start-position=# --stop-position=# :指定起始日志的位置.
清理:
删除全部二进制日志:
reset master
删除部分日志:
PURGE MASTER LOGS TO & PURGE MASTER LOGS BEFORE
PURGE MASTER LOGS TO 'mysql-bin.******'命令,是将'******'编号之前的所有日志进行删除
PURGE MASTER LOGS BEFORE 'yyyy-mm-dd hh:mm:ss'命令,是将在'yyyy-mm-dd hh:mm:ss'时间之前的所有日志进行删除
设置日志过期时间:
修改my.cnf
expire_log_day=5
这里设置保存5天的日志,超过5天的日志会被自动删除
恢复:
完全恢复:
mysqlbinlog mysql-bin.00001|mysql -uroot -p
基于时间点的恢复:
如果误删了一张表,使用完全恢复是没有用的,因为日志里同样也保留着删除的sql语句,所以我们需要恢复到误操作前的状态,然后跳过误操作的语句。
假如我在20:00误删了一张表,可以使用以下语句恢复:
mysqlbinlog --stop-date='2012-06-05 19:59:59' /var/log/mysql-bin.000001 | mysql -uroot -p
跳过误删除的时间点,再执行:
mysqlbinlog --start-date='2012-06-05 20:01:00' /var/log/mysql-bin.000001 | mysql -uroot -p
基于位置点的恢复:
基于位置点的恢复可以得到更为精确的数据。

如上图,drop table test这条语句的起始位置是889107,终止位置是889189,那么我们可以使用于以下语句进行恢复:
mysqlbinlog --stop-position='889107' /var/lib/mysql/mysql-bin.000001|mysql -uroot -p
mysqlbinlog --start-position='889189' /var/lib/mysql/mysql-bin.000001|mysql -uroot -p
有时有可能因为系统版本的问题,以上方法行不通,可以将二进制导出到一个sql文件中,再直接根据sql语句进行恢复
mysqlbinlog mysqlbinlog.000001 >log.sql
完全恢复案例:
|
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
|
mysql> select *
from
test2;
+------+-------+
| id | name |
+------+-------+
|
10
| tom |
|
20
| jerry |
恢复以前的数据:
[root@ogg mysql]# mysqlbinlog /
var
/lib/mysql/mysql-bin
.000003
|mysql -uroot -poracle
恢复后查询:
mysql> select *
from
test2;
+------+-------+
| id | name |
+------+-------+
|
10
| tom |
|
20
| jerry |
|
60
| jack |
|
60
| jack |
|
60
| jack |
|
60
| jack |
|
60
| jack |
|
50
| john |
|
40
| ellen |
|
30
| rose |
+------+-------+
10
rows
in
set (
0.00
sec)
|
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)使用了临时表
binary log相关变量和参数
命令行参数
--log-bin [=file_name]
设置此参数表示启用binlog功能,并制定路径名称。
--log-bin-index[=file]
设置此参数是指定二进制索引文件的路径与名称。
--max_binlog_size
Binlog最大值,最大和默认值是1GB,该设置并不能严格控制Binlog的大小,尤其是Binlog比较靠近最大值而又遇到一个比较大事务时,
为了保证事务的完整性,不可能做切换日志的动作,只能将该事务的所有SQL都记录进当前日志,直到事务结束。
--binlog-do-db=db_name
此参数表示只记录指定数据库的二进制日志
--binlog-ignore-db=db_name
此参数表示不记录指定的数据库的二进制日志
系统变量
log_bin
binlog_cache_size
此参数表示binlog使用的内存大小,可以通过状态变量binlog_cache_use和binlog_cache_disk_use来帮助测试。
max_binlog_cache_size
此参数表示binlog使用的内存最大的尺寸
binlog_cache_use
使用二进制日志缓存的事务数量
binlog_cache_disk_use
使用二进制日志缓存但超过binlog_cache_size值并使用临时文件来保存事务中的语句的事务数量。
binlog_do_db
binlog_ignore_db
sync_binlog
这个参数直接影响mysql的性能和完整性。
sync_binlog=0:
当事务提交后,Mysql仅仅是将binlog_cache中的数据写入binlog文件,但不执行fsync之类的磁盘,同步指令通知文件系统将缓存刷新到磁盘,而让Filesystem自行决定什么时候来做同步,这个是性能最好的。
sync_binlog=0,在进行n次事务提交以后,Mysql将执行一次fsync之类的磁盘同步指令,通知文件系统将Binlog文件缓存刷新到磁盘。
Mysql中默认的设置是sync_binlog=0,即不做任何强制性的磁盘刷新指令,这时性能是最好的,但风险也是最大的。一旦系统Crash,在文件系统缓存中的所有Binlog信息都会丢失。