0

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

3 Answers 3

0
  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.

  1. Strat the MySQL service

    service mysql start

MySQL will now start without loading the binlog files.

  1. Connect to MySQL server

    mysql -u root -p

  2. Purge the binlog files

PURGE BINARY LOGS TO 'binlog_filename';

  1. Verify disk space:

    df -kh

4
  • Hello, when I run mysql -u root -p -e "SHOW BINARY LOGS;" I get the error Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
    – Sky Lurk
    Commented Jun 7, 2023 at 10:22
  • Check MySQL configurations check if mysqld.sock is correctly configured Look for the following line in the [mysqld] section: socket = /var/run/mysqld/mysqld.sock the restart MySQL services: service mysql restart BTW, did you follow the steps that I answered?
    – dbafix
    Commented Jun 7, 2023 at 10:35
  • Yes, I followed the steps. but when I get to starting mysql, that is where it refuses and says no space.
    – Sky Lurk
    Commented Jun 7, 2023 at 11:31
  • Please check the config file.
    – dbafix
    Commented Jun 7, 2023 at 11:35
0

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.
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.