在天堂 - 技术老男 ›

MYSQL ›
MySQL Binlog Server备份和二进制日志清理的几种方法

点击：947

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

mysql binlog server备份脚本

#!/bin/bash
BACKUP_BIN=/usr/bin/mysqlbinlog
LOCAL_BACKUP_DIR=/backup/xjddb_binlog/
BACKUP_LOG=/tmp/backup.log
REMOTE_HOST=rm-xxx.mysql.rds.aliyuncs.com
REMOTE_PORT=3306
REMOTE_USER=xxx
REMOTE_PASS=xxx
FIRST_BINLOG=mysql-bin.000020
SLAVE_SERVER_ID=1
# wait for 10s
SLEEP_SECONDS=10
cd ${LOCAL_BACKUP_DIR}
mysql --host=${REMOTE_HOST} --port=${REMOTE_PORT} --user=${REMOTE_USER} --password=${REMOTE_PASS} -e "show binary logs"

while :
do
  if [ `ls -A "${LOCAL_BACKUP_DIR}"|grep mysql-bin |wc -l` -eq 0 ];then
     LAST_FILE=${FIRST_BINLOG}
  else
     LAST_FILE=`ls -l ${LOCAL_BACKUP_DIR} |tail -n 1 |awk '{print $NF}'`
  fi
  echo Begin:$LAST_FILE
  ${BACKUP_BIN} --raw -R --stop-never --host=${REMOTE_HOST} --port=${REMOTE_PORT} --user=${REMOTE_USER} --password=${REMOTE_PASS}  --stop-never-slave-server-id=${SLAVE_SERVER_ID} ${LAST_FILE} --result-file=${LOCAL_BACKUP_DIR}
  echo "`date +"%Y/%m/%d %H:%M:%S"` mysqlbinlog is stoped,return code: $?" | tee -a ${BACKUP_LOG}
  echo "${SLEEP_SECONDS}s will continue !" | tee -a ${BACKUP_LOG}  
  sleep ${SLEEP_SECONDS}
done
EOF

源网址: http://www.fordba.com/mysql%E4%BA%8C%E8%BF%9B%E5%88%B6%E6%97%A5%E5%BF%97%E7%9A%84%E6%B8%85%E7%90%86.html

评论0

您还没有登录，请您注册 登录后发表评论。