MySQL · 2017-02-09 0

MySQL二进制日志的清理

1.扯淡
我们在mysql运维过程中,经常遇到磁盘爆满,登陆server一看发现binlog占了80%的空间,鸡冻的同学就放大招:reset master。结果就石化了,忘了还有从库,从库复制报出1236的复制错误。我每个月至少遇到两三次租户干出这种事情,今天我们就来扯扯,我们平时binlog怎么清理,以及常用配置。

2.清理binlog的方法
(1).PURGE MASTER LOGS
(2).手动删除
(3).expire_logs_days
(4).reset master
我们常用的清理binlog的方法无外乎以上几种,那么他们分别用于哪些方面,以及怎么用。
方法一:PURGE LOGS
这种方法是直接选择我们需要purge掉的binlog,其实不到万不得已,一般不会手动去做这个事情,只有当磁盘爆满,可能会用到这个命令。
语法如下:

PURGE { BINARY | MASTER } LOGS
    { TO 'log_name' | BEFORE datetime_expr }

BINARY 和 MASTER 是同义词,随意用,但是我们习惯性的用BINARY。那么后面的TO和BEFORE有啥区别呢。我们平时最常用的无非就是以下两种形式:

(1).PURGE BINARY LOGS TO 'mysql-bin.010';
(2).PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';

PURGE BINARY LOGS TO ‘mysql-bin.010’;表示删除mysql-bin.010之前的binlog,但是不包括mysql-bin.010。
例如:

root@localhost [(none)] 09:51:14>>>show binary logs;
+---------------+------------+
| Log_name      | File_size  |
+---------------+------------+
| binlog.000001 |        230 |
| binlog.000002 |   48974213 |
| binlog.000003 |        147 |
| binlog.000004 | 1073760531 |
| binlog.000005 |  588731794 |
| binlog.000006 | 1073932489 |
| binlog.000007 | 1075224499 |
| binlog.000008 | 1074166473 |
| binlog.000009 |  582769947 |
| binlog.000010 |   18175185 |
| binlog.000011 |   13169077 |
| binlog.000012 |  157464362 |
| binlog.000013 | 1074160831 |
| binlog.000014 |  506689596 |
+---------------+------------+
14 rows in set (0.00 sec)

我们执行如下语句:

root@localhost [(none)] 09:54:58>>>PURGE BINARY LOGS TO 'binlog.000002';
Query OK, 0 rows affected (0.02 sec)

那么清理掉的binlog就是binlog.000001,

root@localhost [(none)] 10:00:55>>>show binary logs;                    
+---------------+------------+
| Log_name      | File_size  |
+---------------+------------+
| binlog.000002 |   48974213 |
| binlog.000003 |        147 |
| binlog.000004 | 1073760531 |
| binlog.000005 |  588731794 |
| binlog.000006 | 1073932489 |
| binlog.000007 | 1075224499 |
| binlog.000008 | 1074166473 |
| binlog.000009 |  582769947 |
| binlog.000010 |   18175185 |
| binlog.000011 |   13169077 |
| binlog.000012 |  157464362 |
| binlog.000013 | 1074160831 |
| binlog.000014 |  506689596 |
+---------------+------------+
13 rows in set (0.00 sec)

PURGE BINARY LOGS BEFORE ‘2008-04-02 22:46:26’;表示删除2008-04-02 22:46:26这个时间点以前的binlog,这时我们就好奇了,这个时间点在某个binlog的中间,那么是不是这条命令可以掐掉一部分binlog呢?我们做个试验就好了。
假如有以下Binlog:

[mysql@10-10-193-126 mysql01]$ ls -l
total 13887200
-rw-r----- 1 mysql mysql      11984 Feb  9 09:46 10-10-193-126.err
-rw-rw---- 1 mysql mysql          5 Feb  9 09:45 10-10-193-126.pid
-rw-rw---- 1 mysql mysql   48974213 Feb  8 21:41 binlog.000002
-rw-rw---- 1 mysql mysql        147 Feb  8 21:41 binlog.000003
-rw-rw---- 1 mysql mysql 1073760531 Feb  9 09:43 binlog.000004
-rw-rw---- 1 mysql mysql  588731794 Feb  9 09:44 binlog.000005
-rw-rw---- 1 mysql mysql 1073932489 Feb  9 09:47 binlog.000006
-rw-rw---- 1 mysql mysql 1075224499 Feb  9 09:48 binlog.000007
-rw-rw---- 1 mysql mysql 1074166473 Feb  9 09:49 binlog.000008
-rw-rw---- 1 mysql mysql  582769947 Feb  9 09:50 binlog.000009
-rw-rw---- 1 mysql mysql   18175185 Feb  9 09:50 binlog.000010
-rw-rw---- 1 mysql mysql   13169077 Feb  9 09:50 binlog.000011
-rw-rw---- 1 mysql mysql  157464362 Feb  9 09:50 binlog.000012
-rw-rw---- 1 mysql mysql 1074160831 Feb  9 09:51 binlog.000013
-rw-rw---- 1 mysql mysql  506689596 Feb  9 09:52 binlog.000014
-rw-rw---- 1 mysql mysql        208 Feb  9 10:00 binlog.index
drwx------ 2 mysql mysql      16384 Feb  9 09:52 db01
-rw-rw---- 1 mysql mysql 6922698752 Feb  9 09:52 ibdata1
-rw-rw---- 1 mysql mysql    5242880 Feb  9 09:52 ib_logfile0
-rw-rw---- 1 mysql mysql    5242880 Feb  9 09:52 ib_logfile1
drwx------ 2 mysql mysql       4096 Feb  8 21:19 mysql
srwxrwxrwx 1 mysql mysql          0 Feb  9 09:45 mysql.sock
drwx------ 2 mysql mysql       4096 Feb  8 21:19 performance_schema
drwx------ 2 mysql mysql          6 Feb  8 21:19 test
[mysql@10-10-193-126 mysql01]$ mysqlbinlog -vvv binlog.000004 |more
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#170208 21:41:44 server id 1  end_log_pos 107   Start: binlog v 4, server v 5.5.53-log created 170208 21:41:44
BINLOG '
GCCbWA8BAAAAZwAAAGsAAAAAAAQANS41LjUzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107
#170209  9:42:43 server id 1  end_log_pos 188   Query   thread_id=60    exec_time=1     error_code=0
SET TIMESTAMP=1486604563/*!*/;
SET @@session.pseudo_thread_id=60/*!*/;
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/*!*/;
drop database db01
/*!*/;
# at 188
#170209  9:42:48 server id 1  end_log_pos 271   Query   thread_id=60    exec_time=0     error_code=0
SET TIMESTAMP=1486604568/*!*/;
create database db01
/*!*/;
# at 271
#170209  9:42:59 server id 1  end_log_pos 571   Query   thread_id=66    exec_time=0     error_code=0
use `db01`/*!*/;
SET TIMESTAMP=1486604579/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
CREATE TABLE sbtest17 (
id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
k INTEGER UNSIGNED DEFAULT '0' NOT NULL,
c CHAR(120) DEFAULT '' NOT NULL,
pad CHAR(60) DEFAULT '' NOT NULL,
PRIMARY KEY (id)
) /*! ENGINE = innodb MAX_ROWS = 1000000 */
/*!*/;
# at 571
#170209  9:42:59 server id 1  end_log_pos 870   Query   thread_id=73    exec_time=0     error_code=0
SET TIMESTAMP=1486604579/*!*/;

我现在想把170209 9:42:59之前的binlog全部清理掉(170209 9:42:59在binlog.000004),于是可以执行这么条语句:

root@localhost [(none)] 10:00:56>>>PURGE BINARY LOGS BEFORE '2017-02-09 09:42:59';
Query OK, 0 rows affected (0.00 sec)

于是得到以下结果:

[mysql@10-10-193-126 mysql01]$ ls -l
total 13839368
-rw-r----- 1 mysql mysql      11984 Feb  9 09:46 10-10-193-126.err
-rw-rw---- 1 mysql mysql          5 Feb  9 09:45 10-10-193-126.pid
-rw-rw---- 1 mysql mysql 1073760531 Feb  9 09:43 binlog.000004
-rw-rw---- 1 mysql mysql  588731794 Feb  9 09:44 binlog.000005
-rw-rw---- 1 mysql mysql 1073932489 Feb  9 09:47 binlog.000006
-rw-rw---- 1 mysql mysql 1075224499 Feb  9 09:48 binlog.000007
-rw-rw---- 1 mysql mysql 1074166473 Feb  9 09:49 binlog.000008
-rw-rw---- 1 mysql mysql  582769947 Feb  9 09:50 binlog.000009
-rw-rw---- 1 mysql mysql   18175185 Feb  9 09:50 binlog.000010
-rw-rw---- 1 mysql mysql   13169077 Feb  9 09:50 binlog.000011
-rw-rw---- 1 mysql mysql  157464362 Feb  9 09:50 binlog.000012
-rw-rw---- 1 mysql mysql 1074160831 Feb  9 09:51 binlog.000013
-rw-rw---- 1 mysql mysql  506689596 Feb  9 09:52 binlog.000014
-rw-rw---- 1 mysql mysql        176 Feb  9 10:09 binlog.index
drwx------ 2 mysql mysql      16384 Feb  9 09:52 db01
-rw-rw---- 1 mysql mysql 6922698752 Feb  9 09:52 ibdata1
-rw-rw---- 1 mysql mysql    5242880 Feb  9 09:52 ib_logfile0
-rw-rw---- 1 mysql mysql    5242880 Feb  9 09:52 ib_logfile1
drwx------ 2 mysql mysql       4096 Feb  8 21:19 mysql
srwxrwxrwx 1 mysql mysql          0 Feb  9 09:45 mysql.sock
drwx------ 2 mysql mysql       4096 Feb  8 21:19 performance_schema
drwx------ 2 mysql mysql          6 Feb  8 21:19 test
[mysql@10-10-193-126 mysql01]$ mysqlbinlog -vvv binlog.000004 |more
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#170208 21:41:44 server id 1  end_log_pos 107   Start: binlog v 4, server v 5.5.53-log created 170208 21:41:44
BINLOG '
GCCbWA8BAAAAZwAAAGsAAAAAAAQANS41LjUzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107
#170209  9:42:43 server id 1  end_log_pos 188   Query   thread_id=60    exec_time=1     error_code=0
SET TIMESTAMP=1486604563/*!*/;
SET @@session.pseudo_thread_id=60/*!*/;
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/*!*/;
drop database db01
/*!*/;
# at 188
#170209  9:42:48 server id 1  end_log_pos 271   Query   thread_id=60    exec_time=0     error_code=0
SET TIMESTAMP=1486604568/*!*/;
create database db01
/*!*/;
# at 271
#170209  9:42:59 server id 1  end_log_pos 571   Query   thread_id=66    exec_time=0     error_code=0
use `db01`/*!*/;
SET TIMESTAMP=1486604579/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
CREATE TABLE sbtest17 (
id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
k INTEGER UNSIGNED DEFAULT '0' NOT NULL,
c CHAR(120) DEFAULT '' NOT NULL,
pad CHAR(60) DEFAULT '' NOT NULL,
PRIMARY KEY (id)
) /*! ENGINE = innodb MAX_ROWS = 1000000 */
/*!*/;
# at 571
#170209  9:42:59 server id 1  end_log_pos 870   Query   thread_id=73    exec_time=0     error_code=0
SET TIMESTAMP=1486604579/*!*/;

结果并不是我们期望的那样子,把binlog掐掉一部分,只是将时间点所在binlog以前的binlog文件删掉了。那么这条命令的意义何在呢?我们可以设想这种场景,我们的磁盘快满了,binlog占了绝大部分磁盘,这时主库还有部分binlog没有发送给备库,如果莽撞的删除所有binlog并不合适,会把从库搞坏,如果我们心中有数,一般主库不会积累两个小时的binlog不发送从库,那么也就是说我们只要保留两个小时的binlog,从库也就安全,这时,基于时间点的purge binlop作用就发挥出来了,它可以自动帮我们找到时间点所在binlog文件,并帮我们做出安全的清理,保证时间点以后的binlog都在。
方法二:手动删除
手动删除binlog的方式未免太除暴,我们也可以试试啊,也是一种手段嘛。
A.确认需要删除的Binlog文件

[mysql@10-10-193-126 mysql01]$ ls -l
total 13839368
-rw-r----- 1 mysql mysql      11984 Feb  9 09:46 10-10-193-126.err
-rw-rw---- 1 mysql mysql          5 Feb  9 09:45 10-10-193-126.pid
-rw-rw---- 1 mysql mysql 1073760531 Feb  9 09:43 binlog.000004
-rw-rw---- 1 mysql mysql  588731794 Feb  9 09:44 binlog.000005
-rw-rw---- 1 mysql mysql 1073932489 Feb  9 09:47 binlog.000006
-rw-rw---- 1 mysql mysql 1075224499 Feb  9 09:48 binlog.000007
-rw-rw---- 1 mysql mysql 1074166473 Feb  9 09:49 binlog.000008
-rw-rw---- 1 mysql mysql  582769947 Feb  9 09:50 binlog.000009
-rw-rw---- 1 mysql mysql   18175185 Feb  9 09:50 binlog.000010
-rw-rw---- 1 mysql mysql   13169077 Feb  9 09:50 binlog.000011
-rw-rw---- 1 mysql mysql  157464362 Feb  9 09:50 binlog.000012
-rw-rw---- 1 mysql mysql 1074160831 Feb  9 09:51 binlog.000013
-rw-rw---- 1 mysql mysql  506689596 Feb  9 09:52 binlog.000014
-rw-rw---- 1 mysql mysql        176 Feb  9 10:09 binlog.index
drwx------ 2 mysql mysql      16384 Feb  9 09:52 db01
-rw-rw---- 1 mysql mysql 6922698752 Feb  9 09:52 ibdata1
-rw-rw---- 1 mysql mysql    5242880 Feb  9 09:52 ib_logfile0
-rw-rw---- 1 mysql mysql    5242880 Feb  9 09:52 ib_logfile1
drwx------ 2 mysql mysql       4096 Feb  8 21:19 mysql
srwxrwxrwx 1 mysql mysql          0 Feb  9 09:45 mysql.sock
drwx------ 2 mysql mysql       4096 Feb  8 21:19 performance_schema
drwx------ 2 mysql mysql          6 Feb  8 21:19 test
root@localhost [(none)] 10:09:35>>>show binary logs;
+---------------+------------+
| Log_name      | File_size  |
+---------------+------------+
| binlog.000004 | 1073760531 |
| binlog.000005 |  588731794 |
| binlog.000006 | 1073932489 |
| binlog.000007 | 1075224499 |
| binlog.000008 | 1074166473 |
| binlog.000009 |  582769947 |
| binlog.000010 |   18175185 |
| binlog.000011 |   13169077 |
| binlog.000012 |  157464362 |
| binlog.000013 | 1074160831 |
| binlog.000014 |  506689596 |
+---------------+------------+
11 rows in set (0.00 sec)

B.我们需要删除binlog.000004号binlog。那我们在shell命令行删除即可:

[mysql@10-10-193-126 mysql01]$ rm -rf binlog.000004

C.编辑binlog的index文件
[mysql@10-10-193-126 mysql01]$ vim binlog.index

删掉binlog.000004这行
./binlog.000005
./binlog.000006
./binlog.000007
./binlog.000008
./binlog.000009
./binlog.000010
./binlog.000011
./binlog.000012
./binlog.000013
./binlog.000014

C.flush一下(如果不flush,信息不会更新)

root@localhost [(none)] 10:28:22>>>show binary logs;
+---------------+------------+
| Log_name      | File_size  |
+---------------+------------+
| binlog.000004 |          0 |
| binlog.000005 |  588731794 |
| binlog.000006 | 1073932489 |
| binlog.000007 | 1075224499 |
| binlog.000008 | 1074166473 |
| binlog.000009 |  582769947 |
| binlog.000010 |   18175185 |
| binlog.000011 |   13169077 |
| binlog.000012 |  157464362 |
| binlog.000013 | 1074160831 |
| binlog.000014 |  506689596 |
+---------------+------------+
11 rows in set (0.00 sec)

root@localhost [(none)] 10:32:55>>>flush logs;
Query OK, 0 rows affected (0.02 sec)

root@localhost [(none)] 10:33:06>>>show binary logs;
+---------------+------------+
| Log_name      | File_size  |
+---------------+------------+
| binlog.000005 |  588731794 |
| binlog.000006 | 1073932489 |
| binlog.000007 | 1075224499 |
| binlog.000008 | 1074166473 |
| binlog.000009 |  582769947 |
| binlog.000010 |   18175185 |
| binlog.000011 |   13169077 |
| binlog.000012 |  157464362 |
| binlog.000013 | 1074160831 |
| binlog.000014 |  506689636 |
| binlog.000015 |        107 |
+---------------+------------+
11 rows in set (0.00 sec)

方法三:指定过期天数
expire_logs_days参数可以指定保留binlog的天数。一般的,1.这个参数只要保证binlog能够都发送到从库,2.并且binlog要保留时间要大于两次备份时间。一般第一条很容易满足,如果数据库压力大到积压几天都没发送到从库,那么数据库离死也不远了。第二条需要根据库大小来定,一般小库,一天一备,超大库一周一次或者半月一次全备。
为什么binlog保留要大于两次备份,如果数据库彻底坏了,我们可以通过备份+binlog把数据滚到最新。
那么那些情况下回触发mysql去清理expire_logs_days天之前的binlog呢?
A.重启mysql
B.手动执行flush logs;
C.当binlog的尺寸大到max_binlog_size,发生一次binlog切换
方法四:reset master
这种方式,最好是不用,就算没有从库,也最好不用。很多时候大家把它用在修复主从,或者带GTID的全库导入。