How to Prevent MySQL MariaDB Binary Log Growth?
1 min read
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;'
-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!!
