Safely delete binlog logs in MySQL, recover data using Binlog

When the database is in use, the default binlog is kept in one file (about 1.1G), resulting in a very large file, so you may need to delete some binlog log files when necessary:

To delete a log file:

Mode one:

[Root@master-mysql data]# grep-e "Expire_logs_days"/etc/my.cnf
Expire_logs_days = 7 #删除7天前的binlog记录

Mode two:

Mysql> RESET MASTER; #重置binlog
Mysql> PURGE MASTER LOGS to ' mysql-bin.000003 '; #删除mysql the log before-bin.000003
Mysql> PURGE MASTER LOGS before ' 2014-07-16 15:07:00 '; #删除2014-Binlog log before 07-16 15:07:00
Mysql> PURGE MASTER LOGS before Date_sub (now (), INTERVAL 3 day);

Recovery log File:

[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 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 (' Liang ');
mysql> INSERT INTO Test (Val,data) VALUES (' Jia ');
mysql> INSERT INTO Test (Val,data) VALUES (' 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 21:08 mysql-bin.000317 #刷新binlog新产生的日志, the other in turn;

mysql> INSERT INTO Test (Val,data) VALUES (' AAA ');
mysql> INSERT INTO Test (Val,data) VALUES ("BBB");
mysql> INSERT INTO Test (Val,data) VALUES ("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 ("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 (' dddd ');
mysql> INSERT INTO Test (Val,data) VALUES ("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 |
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 June 21:12 mysql-bin.000318
-RW-RW----. 1 MySQL MySQL 723 June 21:13 mysql-bin.000319
-RW-RW----. 1 MySQL MySQL 293 June 21:14 mysql-bin.000320
-RW-RW----. 1 MySQL MySQL 3697 June 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 CRC32 0x38d71bd6 start:binlog v 4, Server v 5.6.16-log created 140718 21:1 2: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)