Ryan

清理MySQL Binlog
刚才用Bitwarden保存登录密码死活不能提交,由于服务是自建的,和博客放在一起,所以就上去看一下什么情况。结果...
扫描右侧二维码阅读全文
07
2019/07

清理MySQL Binlog

刚才用Bitwarden保存登录密码死活不能提交,由于服务是自建的,和博客放在一起,所以就上去看一下什么情况。结果发现是硬盘满了。

看看谁在占用空间

首先把/tmp清理一下。结果发现/tmp也就空出了几十M的空间。看看谁占用的最多吧

# du -g . | sort -rn | head -1
./data/mysql 5g

原来是MySQL啊,不用说的肯定是binlog

清理MySQL Binlog

1、查看binlong

mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000023 | 517313385 | No        |
| binlog.000024 |  16047139 | No        |
| binlog.000028 |     50380 | No        |
| binlog.000029 |  92094579 | No        |
| binlog.000030 |    119016 | No        |
| binlog.000031 |    635284 | No        |
| binlog.000032 | 619770141 | No        |
| binlog.000033 |    303159 | No        |
| binlog.000034 |  28446218 | No        |
| binlog.000035 |  43999108 | No        |
| binlog.000036 |  23087626 | No        |
| binlog.000037 |    243305 | No        |
| binlog.000038 |    428940 | No        |
| binlog.000039 |   4878625 | No        |
| binlog.000040 |    921874 | No        |
| binlog.000041 | 776327916 | No        |
| binlog.000042 |  15039544 | No        |
| binlog.000043 |    299041 | No        |
| binlog.000044 |       155 | No        |
| binlog.000045 | 378732429 | No        |
| binlog.000046 |       178 | No        |
| binlog.000047 |       178 | No        |
| binlog.000048 |  60495166 | No        |
| binlog.000049 |       178 | No        |
| binlog.000050 |  53353961 | No        |
| binlog.000051 |     13166 | No        |
| binlog.000052 | 653486090 | No        |
+---------------+-----------+-----------+
27 rows in set (0.04 sec)

2、清理指定binlog

mysql> purge binary logs to 'binlog.000050'
Query OK, 0 rows affected (0.27 sec)

3、再次查看binlog

mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000050 |  53353961 | No        |
| binlog.000051 |     13166 | No        |
| binlog.000052 | 653522220 | No        |
+---------------+-----------+-----------+
2 rows in set (0.01 sec)

4、清理所有binlog

mysql> reset master;
Query OK, 0 rows affected (0.80 sec)

5、再次查看,清理过程中新的又来了。

mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000028 |       155 | No        |
+---------------+-----------+-----------+
1 row in set (0.00 sec)

6、可以通过修改参数关闭binlog
修改my.cnf,注释log-bin

#log-bin=mysql-bi

重启MySQL就可以了。

后记

硬盘只有8G,太小了,博主看了看VPS控制面板,发现是40G的,然后fdisk看了一下,我日,数据盘没挂...

搬瓦工年付$187机房套餐补货了,电信联通优化,512M内存/500G流量/1G带宽,建站稳定,优惠码:BWH1ZBPVK,【点击购买】!
搬瓦工年付$28CN2高速线路,512M内存/500G流量/1G带宽,电信联通优化,延迟低,速度快,建站稳定,优惠码同上,【点击购买】!
Last modification:July 7th, 2019 at 06:33 pm
If you think my article is useful to you, please feel free to appreciate

Leave a Comment

2 comments

  1. 张波博客

    8个G的数据?数据怎么这么多呀

    1. Ryan
      @张波博客

      MySQL的二进制日志就占了5G多了