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)