zhimma's blog
0%

MySQL中binlog文件

Posted on Edited on In

[DCOT]

概念

MySQL Server 有四种类型的日志——Error Log、General Query Log、Binary Log 和 Slow Query Log。

Error Log：错误日志，记录 mysqld 的一些错误

General Query Log：一般查询日志，记录 mysqld 正在做的事情，比如客户端的连接和断开、来自客户端每条 Sql Statement 记录信息；如果你想准确知道客户端到底传了什么瞎 [哔哔] 玩意儿给服务端，这个日志就非常管用了，不过它非常影响性能。

Binlog：Mysql sever层维护的一种二进制日志,Binlog中包含了一些事件，这些事件描述了数据库的改动，如建表、数据改动等，也包括一些潜在改动，比如 DELETE FROM ran WHERE bing = luan，然而一条数据都没被删掉的这种情况。除非使用 Row-based logging，否则会包含所有改动数据的 SQL Statement。

Slow Query Log：慢查询日志，记录一些查询比较慢的 SQL 语句——这种日志非常常用，主要是给开发者调优用的。

用途

Binlog的作用主要有：

  • Replication（主从数据库）:在master端开启binary log后，log会记录所有数据库的改动，然后slave端获取这个Log文件内容就可以在slave端进行同样的操作。
  • 备份（数据恢复 ）：在某个时间点a做了一次备份，然后利用binary log记录从这个时间点a后的所有数据库的改动，然后下一次还原的时候，利用时间点a的备份文件和这个binary log文件，就可以将数据还原。

我们执行SELECT等不设计数据变更的语句是不会记录Binlog的，而涉及到数据更新则会记录。要注意的是，对支持事务的引擎如InnoDB而言，必须要提交了事务才会记录Binlog。Binlog是在事务最终commit前写入的，binlog什么时候刷新到磁盘跟参数sync_binlog相关。如果设置为0，则表示MySQL不控制binlog的刷新，由文件系统去控制它缓存的刷新，而如果设置为不为0的值则表示每sync_binlog次事务，MySQL调用文件系统的刷新操作刷新binlog到磁盘中。设为1是最安全的，在系统故障时最多丢失一个事务的更新，但是会对性能有所影响，一般情况下会设置为100或者0，牺牲一定的一致性来获取更好的性能。

Binlog的用法

常用命令

1.开启Binlog

通过配置/etc/my.cnf配置文件的log-bin选项：

1
2
[mysqld]
log-bin=mysql-bin

修改后重启MySql服务；

可以使用SET SQL_LOG_BIN=0命令停止使用日志文件，然后可以通过SET SQL_LOG_BIN=1命令来启用。

2.查看Binlog文件

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 |      1045 |
| mysql-bin.000002 |       201 |
| mysql-bin.000003 |       201 |
| mysql-bin.000004 |       201 |
| mysql-bin.000005 |       201 |
| mysql-bin.000006 |       201 |
| mysql-bin.000007 |       201 |
| mysql-bin.000008 |       201 |
| mysql-bin.000009 |     18949 |
| mysql-bin.000010 |       471 |
| mysql-bin.000011 |      1072 |
| mysql-bin.000012 |       177 |
| mysql-bin.000013 |      1171 |
| mysql-bin.000014 |      3548 |
+------------------+-----------+
14 rows in set (0.00 sec)

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      1045 |
| mysql-bin.000002 |       201 |
| mysql-bin.000003 |       201 |
| mysql-bin.000004 |       201 |
| mysql-bin.000005 |       201 |
| mysql-bin.000006 |       201 |
| mysql-bin.000007 |       201 |
| mysql-bin.000008 |       201 |
| mysql-bin.000009 |     18949 |
| mysql-bin.000010 |       471 |
| mysql-bin.000011 |      1072 |
| mysql-bin.000012 |       177 |
| mysql-bin.000013 |      1171 |
| mysql-bin.000014 |      3548 |
+------------------+-----------+
14 rows in set (0.00 sec)

3.查看Binlog记录时长,设置时长(过期自动删除)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> show variables like 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 7     |
+------------------+-------+
1 row in set (0.00 sec)

mysql> set global expire_logs_days=15;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 15    |
+------------------+-------+
1 row in set (0.00 sec)

4.删除Binlog

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
mysql> flush logs;
Query OK, 0 rows affected (0.35 sec)

mysql> reset master;
Query OK, 0 rows affected (0.35 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       154 |
+------------------+-----------+
1 row in set (0.00 sec)

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       154 |
+------------------+-----------+
1 row in set (0.00 sec)

查看Binlog文件目录
[root@b04f945297ac mysql]# ls -alh
. . .
drwxr-x--- 1 mysql mysql 4.0K Apr  8 04:12 mysql
-rw-r----- 1 mysql mysql 1.1K May 22 07:41 mysql-bin.000001
-rw-r----- 1 mysql mysql  201 May 22 07:41 mysql-bin.000002
-rw-r----- 1 mysql mysql  201 May 22 07:41 mysql-bin.000003
-rw-r----- 1 mysql mysql  201 May 22 07:41 mysql-bin.000004
-rw-r----- 1 mysql mysql  201 May 22 07:41 mysql-bin.000005
-rw-r----- 1 mysql mysql  201 May 22 07:41 mysql-bin.000006
-rw-r----- 1 mysql mysql  201 May 22 07:41 mysql-bin.000007
-rw-r----- 1 mysql mysql  201 May 22 07:41 mysql-bin.000008
-rw-r----- 1 mysql mysql  19K May 22 10:09 mysql-bin.000009
-rw-r----- 1 mysql mysql  471 May 23 01:28 mysql-bin.000010
-rw-r----- 1 mysql mysql 1.1K May 24 02:28 mysql-bin.000011
-rw-r----- 1 mysql mysql  177 May 24 02:28 mysql-bin.000012
-rw-r----- 1 mysql mysql 1.2K May 25 02:15 mysql-bin.000013
-rw-r----- 1 mysql mysql 2.0K May 25 06:36 mysql-bin.000014
-rw-r----- 1 mysql mysql  266 May 25 02:15 mysql-bin.index
srwxrwxrwx 1 mysql mysql    0 May 25 02:15 mysql.sock
-rw------- 1 mysql mysql    3 May 25 02:15 mysql.sock.lock
. . .

删除之后的目录
[root@b04f945297ac mysql]# ls -alh
total 313M
. . .
drwxr-x--- 1 mysql mysql 4.0K Apr  8 12:12 mysql
-rw-r----- 1 mysql mysql  154 May 25 15:03 mysql-bin.000001
-rw-r----- 1 mysql mysql   19 May 25 15:03 mysql-bin.index
srwxrwxrwx 1 mysql mysql    0 May 25 10:15 mysql.sock
-rw------- 1 mysql mysql    3 May 25 10:15 mysql.sock.lock
. . .

5.自动清理Binlog日志

如果堆积的binlog非常多，不要轻易设置改参数，可以使用purge命令 部分老化binlog

1
2
mysql> purge master logs before '2018-05-25 16:25:00';//删除指定日期前的日志索引中binlog日志文件
mysql> purge master logs to 'binlog.000001';//删除指定日志文件

Binlog日志格式

binary log可以通过mysqlbinlog命令来将log的信息打印出来，binlog模式总共可分为以下三种：

statement ,row,mixed

Statement:基于SQL语句的复制（statement-based replication, SBR）

每一条会修改数据的sql都会记录在binlog中。

优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO, 提高了性能。

缺点：由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行的时候相同的结果。另外mysql的复制，像一些特定函数的功能，slave可与master上要保持一致会有很多相关问题。

Row:基于行的复制（row-based replication, RBR）

5.1.5版本的MySQL才开始支持row level的复制,它不记录sql语句上下文相关信息，仅保存哪条记录被修改。

优点： binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以row的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题.

缺点:所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容

新版本的MySQL中对row level模式也被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录，如果sql语句确实就是update或者delete等修改数据的语句，那么还是会记录所有行的变更。

Mixed:混合模式复制（mixed-based replication, MBR）

从5.1.8版本开始，MySQL提供了Mixed格式，实际上就是Statement与Row的结合。
在Mixed模式下，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。

简而言之

  • statement：基于 SQL 语句的模式，binlog 数据量小，但是某些语句和函数在复制过程可能导致数据不一致甚至出错；
  • mixed：混合模式，根据语句来选用是 statement 还是 row 模式；
  • row：基于行的模式，记录的是行的完整变化。安全，但 binlog 会比其他两种模式大很多；

查看当前Binlog日志格式

1
2
3
4
5
6
7
8
mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)

设置Binlog日志格式

1
2
3
4
5
6
7
8
9
10
11
12
13
修改my.cnf
log-bin=mysql-bin  
#binlog_format=STATEMENT  
#binlog_format=ROW  
binlog_format=MIXED  
或者在运行时设置：
mysql> SET SESSION binlog_format = 'STATEMENT';  
mysql> SET SESSION binlog_format = 'ROW';  
mysql> SET SESSION binlog_format = 'MIXED';  
mysql> SET GLOBAL binlog_format = 'STATEMENT';  
mysql> SET GLOBAL binlog_format = 'ROW';  
mysql> SET GLOBAL binlog_format = 'MIXED';

查看Binlog日志文件内容

1.查看某个具体binlog文件的内容

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> show binlog events in "mysql-bin.000001";
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000001 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.21-log, Binlog ver: 4 |
| mysql-bin.000001 | 123 | Previous_gtids |         1 |         154 |                                       |
| mysql-bin.000001 | 154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000001 | 219 | Query          |         1 |         302 | BEGIN                                 |
| mysql-bin.000001 | 302 | Table_map      |         1 |         378 | table_id: 180 (tourism.user_tags)     |
| mysql-bin.000001 | 378 | Write_rows     |         1 |         445 | table_id: 180 flags: STMT_END_F       |
| mysql-bin.000001 | 445 | Xid            |         1 |         476 | COMMIT /* xid=71618 */                |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
7 rows in set (0.00 sec)

2.查看某个具体binlog文件的内容

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
[root@b04f945297ac mysql]# pwd
/var/lib/mysql
[root@b04f945297ac mysql]# mysqlbinlog mysql-bin.000001 
mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8mb4'

如果报错，加上--no-defaults参数运行即可

[root@b04f945297ac mysql]# mysqlbinlog --no-defaults mysql-bin.000001 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#180525 16:02:24 server id 1  end_log_pos 123 CRC32 0x62d58cd0 	Start: binlog v 4, server v 5.7.21-log created 180525 16:02:24 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
EMMHWw8BAAAAdwAAAHsAAAABAAQANS43LjIxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAQwwdbEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AdCM1WI=
'/*!*/;
# at 123
#180525 16:02:24 server id 1  end_log_pos 154 CRC32 0x57b5fe8d 	Previous-GTIDs
# [empty]
# at 154
#180525 16:02:48 server id 1  end_log_pos 219 CRC32 0xa3058217 	Anonymous_GTID	last_committed=0	sequence_number=1	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#180525 16:02:48 server id 1  end_log_pos 302 CRC32 0xa849ecb0 	Query	thread_id=633	exec_time=0	error_code=0
SET TIMESTAMP=1527235368/*!*/;
SET @@session.pseudo_thread_id=633/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1073741824/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=224,@@session.collation_connection=224,@@session.collation_server=8/*!*/;
SET @@session.time_zone='SYSTEM'/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 302
#180525 16:02:48 server id 1  end_log_pos 378 CRC32 0x66324940 	Table_map: `tourism`.`user_tags` mapped to number 180
# at 378
#180525 16:02:48 server id 1  end_log_pos 445 CRC32 0x25589cf0 	Write_rows: table id 180 flags: STMT_END_F

BINLOG '
KMMHWxMBAAAATAAAAHoBAAAAALQAAAAAAAEAB3RvdXJpc20ACXVzZXJfdGFncwALA/wPAQEPERER
EREKAlAA/AMAAAAAAMAHQEkyZg==
KMMHWx4BAAAAQwAAAL0BAAAAALQAAAAAAAEAAgAL///A/AMAAAAAAANjY2MBAAcAczowOiIiO1sI
M6hbCDOo8JxYJQ==
'/*!*/;
# at 445
#180525 16:02:48 server id 1  end_log_pos 476 CRC32 0xd1963b7d 	Xid = 71618
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

这里Binlog 显示和选择存储的格式相关

执行下面语句，查看对应Binlog记录格式

INSERT INTO staff (name) VALUES ('zhimma')

row格式对应格式：

1
2
3
4
5
BINLOG '
Cb8PWxMBAAAAOgAAAF0BAAAAAGQAAAAAAAEABHRlc3QABXN0YWZmAAYDDw8DAxIF/QL9AgAAkkVd
Dg==
Cb8PWx4BAAAAPwAAAJwBAAAAAGQAAAAAAAEAAgAG/8ADAAAABgB6aGltbWEAAAAAAAAAAAAAgAAA

statement格式对应格式：

1
2
3
4
5
use `test`/*!*/;
SET TIMESTAMP=1527758668/*!*/;
INSERT INTO `staff` (`name`) VALUES ('zhimma')
/*!*/;

mixd格式对应格式：

1
2
3
4
BINLOG '
S74PWw8BAAAAdwAAAHsAAAAAAAQANS43LjIxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABLvg9bEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AY4lTRU=

提取Binlog文件

提取指定的binlog日志

1
2
3
[root@b04f945297ac mysql]# mysqlbinlog --no-defaults  /var/lib/mysql/mysql-bin.000001 |grep INSERT  
SET INSERT_ID=4/*!*/;
INSERT INTO `staff` (`name`) VALUES ('zhimma')

提取指定position位置的binlog日志

1
2
3
[root@b04f945297ac mysql]# mysqlbinlog --no-defaults --start-position="154" --stop-position="481" /var/lib/mysql/mysql-bin.000001 | grep INSERT
SET INSERT_ID=5/*!*/;
INSERT INTO `staff` (`name`) VALUES ('zhimma')

提取指定position位置的binlog日志并输出到压缩文件

1
2
3
4
5
[root@b04f945297ac binlog]#  mysqlbinlog --no-defaults --start-position="154" --stop-position="481" /var/lib/mysql/mysql-bin.000001 |gzip > extra_01.sql.gz
[root@b04f945297ac binlog]# ls
extra_01.sql.gz
建议加上过滤
[root@b04f945297ac binlog]#  mysqlbinlog --no-defaults --start-position="154" --stop-position="481" /var/lib/mysql/mysql-bin.000001 | grep INSERT | gzip > extra_01.sql.gz

提取指定position位置的binlog日志导入数据库

1
[root@b04f945297ac binlog]#  mysqlbinlog --no-defaults --start-position="154" --stop-position="481" /var/lib/mysql/mysql-bin.000001 | mysql -uroot -p

提取指定时间区间的binlog并输出到日志文件

1
[root@b04f945297ac binlog]#  mysqlbinlog --no-defaults --start-datetime="2018-05-30 00:00:00" --stop-datetime="2018-06-30 00:00:00" /var/lib/mysql/mysql-bin.000001 --result-file=20180530_20180630.sql

提取指定位置的多个binlog日志文件

1
[root@b04f945297ac binlog]#  mysqlbinlog --no-defaults --start-datetime="2018-05-30 00:00:00" --stop-datetime="2018-06-30 00:00:00" /var/lib/mysql/mysql-bin.000001 /var/lib/mysql/mysql-bin.000002 --result-file=20180530_20180630.sql

提取指定数据库binlog并转换字符集到UTF8

1
[root@b04f945297ac binlog]#  mysqlbinlog --no-defaults --database=test --set-charset=utf8 --start-datetime="2018-05-30 00:00:00" --stop-datetime="2018-06-30 00:00:00" /var/lib/mysql/mysql-bin.000001 /var/lib/mysql/mysql-bin.000002 > test_20180530_20180630.sql

远程提取使用row格式的binlog日志并输出到本地文件

1
[root@b04f945297ac binlog]#  mysqlbinlog  --no-defaults -urobin -p -P3606 -h192.168.1.1 --read-from-remote-server -vv --database=test --set-charset=utf8 --start-datetime="2018-05-30 00:00:00" --stop-datetime="2018-06-30 00:00:00" /var/lib/mysql/mysql-bin.000001 > remote_20180530_20180630.sql

有了sql语句，数据就好恢复了

binlog2sql

点击查看文档

https://blog.csdn.net/shudaqi2010/article/details/54412895