mysql中安全删除binlog日志、使用binlog恢复数据
发布:smiling 来源: PHP粉丝网 添加日期:2015-04-18 10:15:14 浏览: 评论:0
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)
Tags: mysql删除日志 binlog日志
推荐文章
热门文章
最新评论文章
- 写给考虑创业的年轻程序员(10)
- PHP新手上路(一)(7)
- 惹恼程序员的十件事(5)
- PHP邮件发送例子,已测试成功(5)
- 致初学者:PHP比ASP优秀的七个理由(4)
- PHP会被淘汰吗?(4)
- PHP新手上路(四)(4)
- 如何去学习PHP?(2)
- 简单入门级php分页代码(2)
- php中邮箱email 电话等格式的验证(2)