How can I purge MySQL binlogs if MySQL is turned off

Question

I am using Digital Ocean droplets for my WordPress Site. There are more than 60 binlog files in /var/lib/mysql. Most of the solutions say that I should purge them from MySQL. The storage is so full the Apache2 or MySQL are both off.

How can I get rid of these binlog files in this case in a way that doesn't lose my data? Running this

df -H --output=source,size,used,avail

Returns

Filesystem      Size  Used Avail
tmpfs           412M   43M  370M
/dev/vda1        63G   63G     0
tmpfs           2.1G     0  2.1G
tmpfs           5.3M     0  5.3M
/dev/vda15      110M  5.5M  104M
tmpfs           412M  4.1k  412M

Answer 1

1. Connect to your Digital Ocean droplet as root using SSH:

   ssh root@<server_ip_address>

2. Stop the MySQL service: Since the storage is full and both Apache2 and MySQL are off, you need to start by stopping the MySQL service to prevent any potential issues during the cleanup process.

   service mysql stop

3. Identify the most recent binlog file with the following command:

   mysql -u root -p -e "SHOW BINARY LOGS;"

4. Purge the binlog files: To remove the older binlog files, you can use the PURGE BINARY LOGS command in MySQL. However, since your storage is full, you'll need to modify the configuration to allow MySQL to start without loading the existing binlog files. Follow these steps:

a. Open the MySQL configuration file using a text editor. The file path may vary, but the default location is typically /etc/mysql/my.cnf or /etc/my.cnf.

b. Look for the [mysqld] section and add or modify the following line:

skip-log-bin

save the config file.

5. Strat the MySQL service

   service mysql start

MySQL will now start without loading the binlog files.

6. Connect to MySQL server

   mysql -u root -p

7. Purge the binlog files

PURGE BINARY LOGS TO 'binlog_filename';

8. Verify disk space:

   df -kh

Answer 2

To free up some space while MySQL is off, simply delete (rm) the oldest few of the binlogs.

But then, there could be further issues.

• Do you need binlogging for something -- Replication, Point-in-time-recovery, backups, etc? If you do, skip-log-bin would destroy what feature.
• Do you have expire_logs... set? If not, or set to high, new binlogs will reappear and the problem will recur. Change/set that setting in my.ini.
• Is Digital Ocean controlling start/stop/config/etc, but messing up this activity? (I don't have help for that.)
• How much of the 63G is being consumed by binlogs? If something else is growing rapidly, then that may be the real problem.

Answer 3

there is a file that records all the binlog, named binlog.index, you can edit this file, remove the first N lines of it, and then remove the correspending mysqlbinlog file.

then you can safely start the sever