在数据库正常使用的时候，默认binlog会一直放在一个文件中（大约1.1G），因此导致一个文件非常大，因此在必要的时候可能需要删除一些binlog日志文件：
删除日志文件：文 章 源 自 note.t4x.orgByrd's Blog-https://note.t4x.org/database/delete-restoration-log-bin/
方式一：
|
0 1 |
[root@Master-Mysql data]# grep -E "expire_logs_days" /etc/my.cnf expire_logs_days = 7 #删除7天前的binlog记录 |
方式二：
|
0 1 2 3 |
mysql> RESET MASTER; #重置binlog mysql> PURGE MASTER LOGS TO 'mysql-bin.000003'; #删除mysql-bin.000003之前的日志 mysql> PURGE MASTER LOGS BEFORE '2014-07-16 15:07:00'; #删除2014-07-16 15:07:00之前的binlog日志 mysql> PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 3 DAY); |
恢复日志文件：
|
0 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 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 |
[root@Master-Mysql ~]# /usr/local/mysql/bin/mysql -uroot -p'' -e 'SHOW BINLOG EVENTS \G' Enter password: *************************** 1. row *************************** [root@Master-Mysql ~]# /etc/init.d/mysqld restart [root@Master-Mysql ~]# ll /usr/local/mysql/data/ -rw-rw----. 1 mysql mysql 120 Jul 18 21:01 mysql-bin.000316 #重新启动生成的binlog [root@Master-Mysql ~]# /usr/local/mysql/bin/mysql -uroot mysql> create database hahaha; mysql> use hahaha; mysql> create table test(id int auto_increment not null primary key, -> val int,data varchar(20)); mysql> insert into test(val,data) values(10,'liang'); mysql> insert into test(val,data) values(20,'jia'); mysql> insert into test(val,data) values(30,'hui'); mysql> show tables; +------------------+ | Tables_in_hahaha | +------------------+ | test | +------------------+ 1 row in set (0.00 sec) mysql> select * from test; +----+------+-------+ | id | val | data | +----+------+-------+ | 1 | 10 | liang | | 3 | 20 | jia | | 5 | 30 | hui | +----+------+-------+ mysql> flush logs; #mysql-bin.000317 [root@Master-Mysql ~]# ll /usr/local/mysql/data/ -rw-rw----. 1 mysql mysql 120 Jul 18 21:08 mysql-bin.000317 #刷新binlog新产生的日志，其他依次类推； mysql> insert into test(val,data) values(40,'aaa'); mysql> insert into test(val,data) values(50,'bbb'); mysql> insert into test(val,data) values(60,'ccc'); mysql> select * from test; +----+------+-------+ | id | val | data | +----+------+-------+ | 1 | 10 | liang | | 3 | 20 | jia | | 5 | 30 | hui | | 7 | 40 | aaa | | 9 | 50 | bbb | | 11 | 60 | ccc | +----+------+-------+ mysql> delete from test where id between 4 and 5; mysql> select * from test; +----+------+-------+ | id | val | data | +----+------+-------+ | 1 | 10 | liang | | 3 | 20 | jia | | 7 | 40 | aaa | | 9 | 50 | bbb | | 11 | 60 | ccc | +----+------+-------+ mysql> insert into test(val,data) values(70,'ddd'); mysql> select * from test; +----+------+-------+ | id | val | data | +----+------+-------+ | 1 | 10 | liang | | 3 | 20 | jia | | 7 | 40 | aaa | | 9 | 50 | bbb | | 11 | 60 | ccc | | 13 | 70 | ddd | +----+------+-------+ mysql> flush logs; #mysql-bin.000318 mysql> insert into test(val,data) values(80,'dddd'); mysql> insert into test(val,data) values(90,'eeee'); mysql> flush logs; #mysql-bin.000319 mysql> drop table test; mysql> flush logs; #mysql-bin.000320 mysql> drop database hahaha; ####################以上为测试数据#################### [root@Master-Mysql ~]# /usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/mysql-bin.000317 | /usr/local/mysql/bin/mysql -uroot mysql> show databases; mysql> show databases; +--------------------+ | Database | +--------------------+ | hahaha | 14 rows in set (0.00 sec) mysql> select * from test; +----+------+-------+ | id | val | data | +----+------+-------+ | 1 | 10 | liang | | 3 | 20 | jia | | 5 | 30 | hui | +----+------+-------+ 3 rows in set (0.00 sec) [root@Master-Mysql ~]# /usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/mysql-bin.000318 -d hahaha| /usr/local/mysql/bin/mysql -uroot mysql> select * from test; +----+------+-------+ | id | val | data | +----+------+-------+ | 1 | 10 | liang | | 3 | 20 | jia | | 7 | 40 | aaa | | 9 | 50 | bbb | | 11 | 60 | ccc | | 13 | 70 | ddd | +----+------+-------+ 6 rows in set (0.00 sec) ######################################################## [root@Master-Mysql ~]# ll /usr/local/mysql/data/ -rw-rw----. 1 mysql mysql 1518 Jul 18 21:12 mysql-bin.000318 -rw-rw----. 1 mysql mysql 723 Jul 18 21:13 mysql-bin.000319 -rw-rw----. 1 mysql mysql 293 Jul 18 21:14 mysql-bin.000320 -rw-rw----. 1 mysql mysql 3697 Jul 18 21:26 mysql-bin.000321 [root@Master-Mysql ~]# /usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/mysql-bin.000318 | grep end_log_pos #140718 21:12:23 server id 1 end_log_pos 120 CRC32 0x38d71bd6 Start: binlog v 4, server v 5.6.16-log created 140718 21:12:23 #140718 21:13:40 server id 1 end_log_pos 723 CRC32 0xcaa8ac7f Rotate to mysql-bin.000320 pos: 4 [root@Master-Mysql ~]# /usr/local/mysql/bin/mysqlbinlog --start-position=120 --stop-position=1518 /usr/local/mysql/data/mysql-bin.000319 -d hahaha|/usr/local/mysql/bin/mysql -uroot mysql> select * from test; +----+------+-------+ | id | val | data | +----+------+-------+ | 1 | 10 | liang | | 3 | 20 | jia | | 7 | 40 | aaa | | 9 | 50 | bbb | | 11 | 60 | ccc | | 13 | 70 | ddd | | 15 | 80 | dddd | | 17 | 90 | eeee | +----+------+-------+ 8 rows in set (0.00 sec) ######################################################## [root@Master-Mysql ~]# /usr/local/mysql/bin/mysqlbinlog --start-datetime="2014-07-18 21:13:59" --stop-datetime="2014-07-18 21:14:03" /usr/local/mysql/data/mysql-bin.000320 -d hahaha|/usr/local/mysql/bin/mysql -uroot mysql> show tables; Empty set (0.00 sec) |
申明：本文由BYRD原创(基于Mysql5.6.16)，未经许可禁止转载！文 章 源 自 note.t4x.orgByrd's Blog-https://note.t4x.org/database/delete-restoration-log-bin/ 文 章 源 自 note.t4x.orgByrd's Blog-https://note.t4x.org/database/delete-restoration-log-bin/
申明：除非注明Byrd's Blog内容均为原创,未经许可禁止转载!详情请阅读版权申明!
