Improve By Learning

Manage Binglog retention in Maria/MySQL

Posted on Mar 15, 2024

In Maria DB or MySQL DB we can manage update Binlog retention by updating the global property named expire_logs_days

What is binlog ?

  • binlog is a file where all the sql statements that were executed against the data in the database were record.
  • using this binlog file, you can see what are the SQL queries executed against your database.
  • And this binlog file can be used for replication or to perform PITR(point in time restore.)

What is the format ?

binlog can be in 3 formats - Row Based - For a SQL query, where are all the impacted rows, will be recorded and same will be copied over on the secondary DBIs - No much load on secondary DBI because you are updating the final result already - Not a very good approach if you have executed a query that just updates 1M rows - Statement Based - Good for queries where the impact would be huge like 1M rows - bad for a very long query but impact limited to few 100 rows - Mixed - Generally most of the companies prefers this. - Leaving MySQL to decide for the current SQL query, the binlog entry should be row based/statement based. - space concise.

What is this retention ?

We don’t need the old binlog files.From time to time we can remove the old bin log depends upon your backup strategy and secondary database setup. By setting a global value called expire_logs_days we are setting by when MySQL/Maria can remove these binlog files automatically.

You can get current value of your expire_logs_days as below

MariaDB [(none)]> select @@expire_logs_days;
+--------------------+
| @@expire_logs_days |
+--------------------+
|                  5 |
+--------------------+
1 row in set (0.001 sec)

MariaDB [(none)]> set global expire_logs_days=4 ;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> select @@expire_logs_days;
+--------------------+
| @@expire_logs_days |
+--------------------+
|                  4 |
+--------------------+
1 row in set (0.001 sec)

MariaDB [(none)]>

So, all the binlog files with age of 4 days will be deleted automatically.

Location in the Filesystem

From the DB Home folder, go to log -> binlog

/log/binlog]# ls
mysql-binlog.000001  mysql-binlog.000003  mysql-binlog.000005  mysql-relay.000007  mysql-relay.index
mysql-binlog.000002  mysql-binlog.000004  mysql-binlog.index   mysql-relay.000008  mysql-relay.info

Hope it helps.