IT Cooking

Success is just one script away

How to Prevent MySQL MariaDB Binary Log Growth?

1 min read
MySQL binary logs demesurably growing? Here is how to prevent MySQL/MAriaDB from stuffing your file system with useless logs.
how to prevent mysql mariadb binary log growth

how to prevent mysql mariadb binary log growth

An online WooCommerce store I created suddenly started experiencing file system full. The cause ? MySQL binary logs demesurably growing up to 30GB, while the database is only 150MB… Makes no sense, does it? Here is how to prevent MySQL/MAriaDB from stuffing your file system with useless logs.

Commands provided are for docker containers, just remove the docker part if your MySQL server is local.

How To Purge MySQL Binary Logs

First things first: the remedy when your file system is full: purge all binary logs:

docker exec itcooking-db mysql -e 'RESET MASTER;'

This works if you have enabled passwordless login for mysql root… Otherwise, add -uUSER -pPASSWORD to the command.

Bonus: how to Enable MySQL Root Passwordless login

First, make sure you have the unix_socket plugin:

docker exec itcooking-db mysql -uroot -pPASSWORD -t -e "SELECT PLUGIN_NAME, PLUGIN_STATUS, PLUGIN_DESCRIPTION FROM information_schema.PLUGINS ORDER BYPLUGIN_NAME;"

| THREAD_POOL_WAITS | ACTIVE | Provides wait counters for threadpool. |
| unix_socket       | ACTIVE | Unix Socket based authentication       | <--
| USER_STATISTICS   | ACTIVE | User Statistics                        |
| user_variables    | ACTIVE | User-defined variables                 |
| wsrep             | ACTIVE | Wsrep replication plugin               |
+-------------------+------- +----------------------------------------+

Second, verify that you have more than 1 way to access your database as root, in case you botch the switch!

docker exec itcooking-db mysql -uroot -pPASSWORD-t -e "SELECT user, host, plugin FROM mysql.user WHERE user='root';"

| User        | Host      | plugin                |
| mariadb.sys | localhost | mysql_native_password |
| root        | %         | mysql_native_password |<-- good! accessible from outside
| root        | localhost | mysql_native_password |<-- we will modify this one

If you have at least 2 admin users, let’s go and make root@localhost passwordless! Execute these commands in one go:

docker exec itcooking-db mysql -uroot -pPASSWORD -t -e "CREATE USER root@localhost IDENTIFIED VIA unix_socket; GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED VIA unix_socket; FLUSH PRIVILEGES;"

If you botch it, or if you try to execute one after another, root@localhost will lose access and all its permissions! And I can tell you that without another root or admin user, it’s impossible to revert it. I tried everything, and there is no way you can revert a user from using unix_socket to mysql_native_password without another user with admin privileges. MySQL is not Oracle, the way single access mode works is different, and if the user you want to fix has a login plugin enabled, good luck.

Result if all is good:

| User        | Host      | plugin                |
| mariadb.sys | localhost | mysql_native_password |
| root        | %         | mysql_native_password |
| root        | localhost | unix_socket           |<-- yay!


What Are These Binary Logs and How to Visalize them?

Now that you took care of the file system full, let’s review what these logs are, where they are and how to see what’s going on.

The binary log contains “events” that describe database changes such as table creation operations or changes to table data. It also contains events for statements that potentially could have made changes (for example, a DELETE which matched no rows), unless row-based logging is used. The binary log also contains information about how long each statement took that updated data. The binary log has two important purposes:

  • For replication, the binary log on a replication source server provides a record of the data changes to be sent to replicas. The source sends the information contained in its binary log to its replicas, which reproduce those transactions to make the same data changes that were made on the source.
  • Certain data recovery operations require use of the binary log. After a backup has been restored, the events in the binary log that were recorded after the backup was made are re-executed. These events bring databases up to date from the point of the backup.

MariaDB or MySQL: it’s the same, they are located here: /docker/mariadbpath/log/mysql

-rw-rw----  1 opc opc 107761422 2024-04-30 14:41:38 mariadb-bin.000307
-rw-rw----  1 opc opc 107857304 2024-04-30 14:44:13 mariadb-bin.000308
-rw-rw----  1 opc opc 107971895 2024-04-30 14:46:36 mariadb-bin.000309
-rw-rw----  1 opc opc 108080174 2024-04-30 14:48:59 mariadb-bin.000310
-rw-rw----  1 opc opc 108191883 2024-04-30 14:51:21 mariadb-bin.000311
-rw-rw----  1 opc opc 108323045 2024-04-30 14:59:23 mariadb-bin.000312
-rw-rw----  1 opc opc  77022695 2024-05-01 20:43:46 mariadb-bin.000313
-rw-rw----  1 opc opc      7622 2024-04-30 14:59:23 mariadb-bin.index

To make sure they are used by MySQL indeed, here is the command:

docker exec itcooking-db mysql -e 'show BINARY LOGS;'

Log_name           File_size
mariadb-bin.000313     77090155
mariadb-bin.000314          367
mariadb-bin.000315      2872380
mariadb-bin.000316    110038501

In this example, they are all 100MB in size, and go back up to a number of days defined in custom.cnf:

expire_logs_days        = 1
max_binlog_size         = 100M

As you can see, there is no way to disable them, or limit them to a certain number. It’s by day, and the minimum is 1. You can perfectly grow above your file system space in just one day with WooCommerce.

No way to fix this, other then digging for hours or days in your plugins, to see what is causing this… Or is there?

Solution to Stop Having Binary Log Grow Like Crazy

Since MySQL 8.0.1, while expire_log_days still works, the canonical way to setup expiration is by changing binlog_expire_logs_seconds, which, as you may guess, has second resolution. The default value since MySQL 8.0.11 is 2592000 (= 30 days), but can be configured as usual on the configuration file to any other period in seconds!

First, verify your MariaDB or MySQL version:

docker exec itcooking-db mysql -t -e "select @@version as version;"

| version             |
| 10.11.6-MariaDB-log | <-- we good!

binlog_expire_logs_seconds is available since:

binlog_expire_logs_seconds takes precedence over expire_log_days, so just add it in custom.cnf:

binlog_expire_logs_seconds = 3600

Then restart MariaDB/MySQL! DONE!!


Leave a Reply

Your email address will not be published. Required fields are marked *