Mysql清理binlog日志

95 阅读 0 评论 63 点赞

我是靠谱客的博主曾经爆米花，这篇文章主要介绍Mysql清理binlog日志，现在分享给大家，希望可以做个参考。

1.在清理binlog日志之前，需要执行命令查看Master节点和slave节点https://www.cndba.cn/hbhe0316/article/22611

复制代码

1
2
3
4
5
6
7
8
9
10
11
mysql> show master status/G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 154
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

mysql> show slave status/G
Empty set (0.00 sec)

2.清理执行日期前的binlog日志，清理2021-11-27日 8:20:00前的日志

https://www.cndba.cn/hbhe0316/article/22611

复制代码

1
2
mysql> purge master logs before '2021-11-27 08:20:00';
Query OK, 0 rows affected, 1 warning (0.00 sec)

3.清理指定binlog文件https://www.cndba.cn/hbhe0316/article/22611https://www.cndba.cn/hbhe0316/article/22611

复制代码

1
2
mysql> purge master logs to 'mysql-bin.000001';
Query OK, 0 rows affected (0.01 sec)

4.手动执行flush logshttps://www.cndba.cn/hbhe0316/article/22611

https://www.cndba.cn/hbhe0316/article/22611

复制代码

1
2
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

5.切记不能直接在操作系统上删除binlog日志,下面通过一个实验来说明:

https://www.cndba.cn/hbhe0316/article/22611

复制代码

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       201 |
| mysql-bin.000002 |       201 |
| mysql-bin.000003 |       201 |
| mysql-bin.000004 |       201 |
| mysql-bin.000005 |       201 |
| mysql-bin.000006 |       201 |
| mysql-bin.000007 |       201 |
| mysql-bin.000008 |       201 |
| mysql-bin.000009 |       201 |
| mysql-bin.000010 |       154 |
+------------------+-----------+
10 rows in set (0.00 sec)

[root@mysql57 logs]# rm -rf mysql-bin.000001 mysql-bin.000002 mysql-bin.000003

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |         0 |
| mysql-bin.000002 |         0 |
| mysql-bin.000003 |         0 |
| mysql-bin.000004 |       201 |
| mysql-bin.000005 |       201 |
| mysql-bin.000006 |       201 |
| mysql-bin.000007 |       201 |
| mysql-bin.000008 |       201 |
| mysql-bin.000009 |       201 |
| mysql-bin.000010 |       154 |
+------------------+-----------+
10 rows in set (0.00 sec)

[root@mysql57 logs]# cat mysql-bin.index 
/logs/mysql-bin.000001
/logs/mysql-bin.000002
/logs/mysql-bin.000003
/logs/mysql-bin.000004
/logs/mysql-bin.000005
/logs/mysql-bin.000006
/logs/mysql-bin.000007
/logs/mysql-bin.000008
/logs/mysql-bin.000009
/logs/mysql-bin.000010

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       201 |
| mysql-bin.000002 |       201 |
| mysql-bin.000003 |       201 |
| mysql-bin.000004 |       201 |
| mysql-bin.000005 |       201 |
| mysql-bin.000006 |       201 |
| mysql-bin.000007 |       201 |
| mysql-bin.000008 |       201 |
| mysql-bin.000009 |       201 |
| mysql-bin.000010 |       154 |
+------------------+-----------+
10 rows in set (0.00 sec)

[root@mysql57 logs]# rm -rf mysql-bin.000001 mysql-bin.000002 mysql-bin.000003


mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |         0 |
| mysql-bin.000002 |         0 |
| mysql-bin.000003 |         0 |
| mysql-bin.000004 |       201 |
| mysql-bin.000005 |       201 |
| mysql-bin.000006 |       201 |
| mysql-bin.000007 |       201 |
| mysql-bin.000008 |       201 |
| mysql-bin.000009 |       201 |
| mysql-bin.000010 |       154 |
+------------------+-----------+
10 rows in set (0.00 sec)

[root@mysql57 logs]# cat mysql-bin.index 
/logs/mysql-bin.000001
/logs/mysql-bin.000002
/logs/mysql-bin.000003
/logs/mysql-bin.000004
/logs/mysql-bin.000005
/logs/mysql-bin.000006
/logs/mysql-bin.000007
/logs/mysql-bin.000008
/logs/mysql-bin.000009
/logs/mysql-bin.000010

重启mysql的时候,mysql日志也会抛错错误

复制代码

1
2
3
4
5
6
7
2021-11-27T00:34:43.403963Z 0 [ERROR] Failed to open log (file '/logs/mysql-bin.000001', errno 2)
2021-11-27T00:34:43.403982Z 0 [ERROR] Could not open log file
mysqld: File '/logs/mysql-bin.000002' not found (Errcode: 2 - No such file or directory)
2021-11-27T00:34:43.404675Z 0 [ERROR] Failed to open log (file '/logs/mysql-bin.000002', errno 2)
2021-11-27T00:34:43.404866Z 0 [ERROR] Could not open log file
mysqld: File '/logs/mysql-bin.000003' not found (Errcode: 2 - No such file or directory)
2021-11-27T00:34:43.405652Z 0 [ERROR] Failed to open log (file '/logs/mysql-bin.000003', errno 2)

打开mysql-bin.index，删除mysql-bin.000001、mysql-bin.000002、mysql-bin.000003三条记录

https://www.cndba.cn/hbhe0316/article/22611

复制代码

1
2
3
4
5
6
7
8
9
10
11
[root@mysql57 logs]# cat mysql-bin.index 
/logs/mysql-bin.000004
/logs/mysql-bin.000005
/logs/mysql-bin.000006
/logs/mysql-bin.000007
/logs/mysql-bin.000008
/logs/mysql-bin.000009
/logs/mysql-bin.000010
/logs/mysql-bin.000011
/logs/mysql-bin.000012
/logs/mysql-bin.000013

重启mysql，则显示正常

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
[root@mysql57 ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL.. SUCCESS! 

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000004 |       201 |
| mysql-bin.000005 |       201 |
| mysql-bin.000006 |       201 |
| mysql-bin.000007 |       201 |
| mysql-bin.000008 |       201 |
| mysql-bin.000009 |       201 |
| mysql-bin.000010 |       177 |
| mysql-bin.000011 |       177 |
| mysql-bin.000012 |       201 |
| mysql-bin.000013 |       177 |
| mysql-bin.000014 |       154 |
+------------------+-----------+
11 rows in set (0.00 sec)