MySql does some really stupid stuff, often I feel like it’s no MY-SQL it SOMEONE-ELSE-SQL. Maybe it has some valid reasoning behind it, which I don’t know completely yet. One issue which often people encountered is MySql binlog files.

These binlog files are very storage-intensive, most of the time for a small application each binlog file will be around 1.2GB.

Now imagine if you have 20GB disk-space it will eat up pretty much everything to the point where not a single kb is remaining for the server, if such thing happens, we even can’t run small commands like opening a file or editing a file etcetera.

As you can see in below image where It uses 100% of disk space 🙁

Some blog suggested that we can delete those files using myql> in terminal, however if you don’t have any space left we can’t even run sql in terminal.

The right way to remove binlog files.

If you are a root user to the system you can do following things to get it deleted in proper way.

login to the system via ssh and run below command, it will make you root user.

sudo -su

Run df -h to check current disk space information, it will show you something like below. check /dev/xvda1 section it consume all the space which I have it 🙁

Filesystem      Size  Used Avail Use% Mounted on
udev            236M     0  236M   0% /dev
tmpfs            49M  5.7M   44M  12% /run
/dev/xvda1       20G   20G     0 100% /
tmpfs           244M     0  244M   0% /dev/shm
tmpfs           5.0M     0  5.0M   0% /run/lock
tmpfs           244M     0  244M   0% /sys/fs/cgroup
/dev/loop0       92M   92M     0 100% /snap/core/8689
/dev/loop1       18M   18M     0 100% /snap/amazon-ssm-agent/1480
/dev/loop2       18M   18M     0 100% /snap/amazon-ssm-agent/1566
/dev/loop4       94M   94M     0 100% /snap/core/8935
tmpfs            49M     0   49M   0% /run/user/1000

To get more information about your database usage you can run du -hs /opt/bitnami/mysql/data for me, it’s 16 bg, which is way too much for small application.

16G	/opt/bitnami/mysql/data

To check all the big files (in space allocation) run below command, it will show list of culprits

du -hs /opt/bitnami/*/*/* |sort -rh | head -35

1.1G	/opt/bitnami/mysql/data/binlog.000017
1.1G	/opt/bitnami/mysql/data/binlog.000016
1.1G	/opt/bitnami/mysql/data/binlog.000015
1.1G	/opt/bitnami/mysql/data/binlog.000014
1.1G	/opt/bitnami/mysql/data/binlog.000013
1.1G	/opt/bitnami/mysql/data/binlog.000012
1.1G	/opt/bitnami/mysql/data/binlog.000011
1.1G	/opt/bitnami/mysql/data/binlog.000010
1.1G	/opt/bitnami/mysql/data/binlog.000009
1.1G	/opt/bitnami/mysql/data/binlog.000008
1.1G	/opt/bitnami/mysql/data/binlog.000007
1.1G	/opt/bitnami/mysql/data/binlog.000006
1.1G	/opt/bitnami/mysql/data/binlog.000003
1.0G	/opt/bitnami/mysql/data/binlog.000018
569M	/opt/bitnami/mysql/data/binlog.000004

At this point you even can’t open file via nano etc.txt or preform any other operation in the system as no space is left.

You can delete binlog.000001 to get some space, run rm binlog.000001 once it’s deleted you will be able to run nano command.

Now open binlog.index file using nano binlog.index and remove all the entries of binlog.0xxx from binlog.index file, once done save it and exit.

Run below to remove all fu(king binlog files at once, if you want you can remove mysqld.log files to as it might not be any use.

rm binlog.00*

After this if you run df -h you will get your space back. check /dev/xvda1 section 🙂

Filesystem      Size  Used Avail Use% Mounted on
udev            236M     0  236M   0% /dev
tmpfs            49M  5.7M   44M  12% /run
/dev/xvda1       20G  4.4G   15G  23% /
tmpfs           244M     0  244M   0% /dev/shm
tmpfs           5.0M     0  5.0M   0% /run/lock
tmpfs           244M     0  244M   0% /sys/fs/cgroup
/dev/loop0       92M   92M     0 100% /snap/core/8689
/dev/loop1       18M   18M     0 100% /snap/amazon-ssm-agent/1480
/dev/loop2       18M   18M     0 100% /snap/amazon-ssm-agent/1566
/dev/loop4       94M   94M     0 100% /snap/core/8935
tmpfs            49M     0   49M   0% /run/user/1000

Hope this will be helpful to someone.

I go beyond boundaries that trap me. Attachments and limitations in thinking are removed and I see people as actors playing roles sometimes negative and sometimes positive and scenes as passing phases, sometimes negative and sometimes positive. This experience translates into power in the consciousness as well as in words and actions.

You may check other informative article http://otricks.com/responsiveness-in-reactive-programming/