mysql中安全删除binlog日志、使用binlog恢复数据

2024-07-24 12:39:40

字体：大中小

来源：转载

供稿：网友

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)