mysql中安全删除binlog日志使用binlog恢复数据
发布时间：2022-06-29 20:19:25 所属栏目：MySql教程 来源：互联网
导读：mysql中binlog是二进制文件了,下面我们就来为各位介绍mysql中安全删除binlog日志、使用binlog恢复数据了,希望例子可以帮助到各位朋友哦. 在数据库正常使用的时候,默认binlog会一直放在一个文件中(大约1.1G),因此导致一个文件非常大,因此在必要的时候可能需要
|
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)。 （编辑：衡阳站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐
热点阅读