How to delete MySQL binlog files if you don’t have disk space left
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.
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.
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
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.
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 https://www.otricks.com/responsiveness-in-reactive-programming/