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)
您可能感兴趣的文章：
如何在mysqldump时加上binlog日志的位置号
自动与手动清理mysql-binlog日志的方法
mysql删除binlog日志及使用日志恢复数据的方法
自动清理binlog日志与手动删除binlog日志的方法
删除Mysql中binlog日志的二种方法
使用binlog恢复mysql数据的简单例子
mysql设置自动清理binlog日志的方法
解决开启bin-log日志mysql报错的问题
学习mysql binlog日志清理
mysql 主从同步一例