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