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的全库导入。