How to flush bin.log files in MySQL which is consuming my disk space so high to the point website crashes

nabyeen 2020-08-31 06:21:43 UTC #1

Keywords: Nginx - AWS - Technical issue - Other
bnsupport ID: 11ca8c6e-2373-3ef9-9699-d040fe83a82d

Description:
On dally basis bin.log file is taking 1.1gb space yesterday site crashed because of disk full. Is there any command or method to purge all these files instantly ?

1.1G -rw-r----- 1 mysql mysql 1.1G Aug 24 14:19 binlog.000063
1.1G -rw-r----- 1 mysql mysql 1.1G Aug 25 08:25 binlog.000064
1.1G -rw-r----- 1 mysql mysql 1.1G Aug 26 10:18 binlog.000065
1.1G -rw-r----- 1 mysql mysql 1.1G Aug 27 12:17 binlog.000066
1.1G -rw-r----- 1 mysql mysql 1.1G Aug 28 13:02 binlog.000067
1.1G -rw-r----- 1 mysql mysql 1.1G Aug 29 14:29 binlog.000068
1.1G -rw-r----- 1 mysql mysql 1.1G Aug 30 15:15 binlog.000069
326M -rw-r----- 1 mysql mysql 326M Aug 31 01:00 binlog.000070
308M -rw-r----- 1 mysql mysql 308M Aug 31 06:06 binlog.000071

And I also wonder why is my site taking so much of space while the whole website size is around 800mb when I take backup

THis is the current size structure:

bitnami@ip-172-31-88-52:~$ sudo du -hs /opt/bitnami/* |sort -rh | head -35
8.4G /opt/bitnami/mysql
2.9G /opt/bitnami/apps
143M /opt/bitnami/common
86M /opt/bitnami/bnsupport
80M /opt/bitnami/php
60M /opt/bitnami/nginx
34M /opt/bitnami/letsencrypt
26M /opt/bitnami/bncert
4.2M /opt/bitnami/bnhelper
3.6M /opt/bitnami/varnish
3.4M /opt/bitnami/sqlite
3.3M /opt/bitnami/manager-linux-x64.run
3.0M /opt/bitnami/stats
116K /opt/bitnami/scripts
92K /opt/bitnami/var
52K /opt/bitnami/config
44K /opt/bitnami/letsencrypt
44K /opt/bitnami/ctlscript.sh
12K /opt/bitnami/README.txt
8.0K /opt/bitnami/img
8.0K /opt/bitnami/changelog.txt
4.0K /opt/bitnami/use_wordpresspro
4.0K /opt/bitnami/properties.ini
4.0K /opt/bitnami/bnsupport-regex.ini
0 /opt/bitnami/bnsupport-tool
0 /opt/bitnami/bnhelper-tool
0 /opt/bitnami/bncert-tool

BST code : 11ca8c6e-2373-3ef9-9699-d040fe83a82d

nabyeen 2020-08-31 07:28:23 UTC #2

Hello I flushed the bin.log files using
using
PURGE BINARY LOGS BEFORE '2020-08-31 00:00:00';

and freed almost 8 GB

But I am still confused if there is any misconfiguration in the server because when I migrated the website to local to test the total size is around 850 Mb, but in current server, it is taking 7.5gb.

bitnami@ip-172-31-88-52:~$ sudo df -h
Filesystem Size Used Avail Use% Mounted on
udev 488M 0 488M 0% /dev
tmpfs 100M 11M 89M 11% /run
/dev/xvda1 20G 7.5G 12G 39% /
tmpfs 496M 0 496M 0% /dev/shm
tmpfs 5.0M 0 5.0M 0% /run/lock
tmpfs 496M 0 496M 0% /sys/fs/cgroup
/dev/loop2 18M 18M 0 100% /snap/amazon-ssm-agent/1566
/dev/loop4 29M 29M 0 100% /snap/amazon-ssm-agent/2012
/dev/loop3 97M 97M 0 100% /snap/core/9665
tmpfs 100M 0 100M 0% /run/user/1000
/dev/loop0 97M 97M 0 100% /snap/core/9804

How to know if there are some resuduals which are of no use ?

Recent BST Key "d10e3437-9da2-bdee-30de-954482faff16"

davidg 2020-08-31 09:45:57 UTC #3

Hello @nabyeen,

I can see this in your my.cnf file:

binlog_expire_logs_seconds = 43200 seconds

You should use this instead:

binlog_expire_logs_seconds=43200

And then restart MySQL:

sudo /opt/bitnami/ctlscript.sh restart mysql

To double-check if this is properly configured, access MySQL console and execute:

select now(); show global variables like 'binlog_expire_logs_seconds';

I hope it helps

MySQL Is not running.?

What is consuming data?

Urgent : Error establishing a database connection

nabyeen 2020-08-31 10:19:56 UTC #4

Thankyou so much ! It worked

system 2020-09-14 10:20:13 UTC #5

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.

Bitnami provides free all-in-one installers, virtual machines and cloud images for popular open source applications. Get them now!