mysql中安全刪除binlog日志、使用binlog恢復數據

mysql中binlog是二進制文件了，下面我們就來為各位介紹mysql中安全刪除binlog日志、使用binlog恢復數據了，希望例子可以幫助到各位朋友哦．

在數據庫正常使用的時候，默認binlog會一直放在一個文件中（大約1.1G），因此導致一個文件非常大，因此在必要的時候可能需要刪除一些binlog日志文件：

刪除日志文件：

方式一：

[root@Master-Mysql data]# grep -E "expire_logs_days" /etc/my.cnf
expire_logs_days = 7    #刪除7天前的binlog記錄

方式二：

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);

恢復日志文件：

[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)