When space starts running low on a MySQL server or MariaDB you may need to purge binary logs. To do that, simply connect to the server and use the PURGE BINARY LOGS command:
mysql> PURGE BINARY LOGS BEFORE 'yyyy-mm-dd hh:mm:ss'; For example:
mysql> PURGE BINARY LOGS BEFORE '2023-03-02 22:46:26'; This command will purge all binary logs from before the specified date. Another method is to use the log file’s name. For example:
mysql> PURGE BINARY LOGS TO 'ns30XXXX-bin.009836';Still, you may find yourself in a more serious situation: there is no longer enough space on your storage to carry out this operation. In most cases, on ext4, the tune2fs command can be a lifesaver. By default, 5% of the space is reserved for root, and you can use tune2fs to change that value on the fly:
02:39:52-root@buster:~$ df -h |grep sda1
/dev/sda1 15G 4.9G 9.1G 36% /
02:39:54-root@buster:~$ tune2fs -m 1 /dev/sda1
tune2fs 1.44.5 (15-Dec-2018)
Setting reserved blocks percentage to 1% (39439 blocks)
02:40:02-root@buster:~$ df -h |grep sda1
/dev/sda1 15G 4.9G 9.7G 34% /
02:40:05-root@buster:~$
However, if you cannot free up space any other way, or for some other reason cannot log in to the MySQL / MariaDB server, you will have to manually delete the binary log files. For this example, we will assume your log files are in /var/lib/mysql: you can then use the following method to solve the problem.
Step 1: stop MySQL or MariaDB (the syntax may depend on your Linux distribution version). For example:
service mysql stop Step 2: Delete binary log files and remove their names from the MySQL or MariaDB binary log index.
Lost? This “brute-force” command line will count and delete 50% of the binary log files to free up space, and remove them from the MySQL or MariaDB binary log index.
cd /var/lib/mysql && a=$(ls | grep -v relay | grep bin.index) && b=$(wc -l < $a); c=$((b/2)); for file in $(head -n $c $a | cut -d "/" -f2); do rm "$file" ; done ; sed -i '1,'"$c"'d' $a
Step 3: Start MySQL or MariaDB
service mysql start Step 4: check that there are no problems
MariaDB [(none)]> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000013 | 365 |
| mysql-bin.000014 | 365 |
| mysql-bin.000015 | 365 |
| mysql-bin.000016 | 342 |
+------------------+-----------+
4 rows in set (0.000 sec)
MariaDB [(none)]> If you see a file with a value of 0, something is wrong: you will need to stop MySQL again and remove the file from the index manually. If everything is OK, you can now use “PURGE BINARY LOGS BEFORE” in the usual way to delete more logs if needed.
Enjoy!
Would you rather not do this yourself, and/or never have to deal with it again for the rest of your life? Contact ScalarX ;)
Christophe Casalegno
You can follow me on: Telegram | YouTube | Twitter | Facebook | LinkedIn | Twitch
4 comments