Safely delete binlog logs in MySQL, recover data using Binlog

Source: Internet
Author: User
Tags crc32 datetime flush create database

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)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.