MySQL/MariaDB: Purge the binary log

# cd /var/db/mysql
# ls -lha

[...]
-rw-rw----    1 mysql  mysql   1.0G Oct 28  2021 mysql-bin.000116
-rw-rw----    1 mysql  mysql   772M Nov  9  2021 mysql-bin.000117
-rw-rw----    1 mysql  mysql   1.0G Nov 22  2021 mysql-bin.000118
-rw-rw----    1 mysql  mysql   452M Nov 28  2021 mysql-bin.000119
-rw-rw----    1 mysql  mysql   327M Dec  4  2021 mysql-bin.000120
-rw-rw----    1 mysql  mysql   350M Dec  9  2021 mysql-bin.000121
-rw-rw----    1 mysql  mysql   1.0G Dec 26 11:20 mysql-bin.000122
-rw-rw----    1 mysql  mysql   1.0G Jan 14 18:44 mysql-bin.000123
-rw-rw----    1 mysql  mysql   331M Jan 18 00:11 mysql-bin.000124
-rw-rw----    1 mysql  mysql   2.3K Jan 14 18:44 mysql-bin.index
-rw-rw----    1 mysql  mysql    13B Jan 18 00:11 mysql-bin.state

# mysql -u root -pSECRET
Welcome to the MariaDB monitor.  Commands end with ; or \g.

root@localhost [(none)]> SHOW BINARY LOGS;
+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
[...]
| mysql-bin.000118 | 1073742239 |
| mysql-bin.000119 |  474005833 |
| mysql-bin.000120 |  343308184 |
| mysql-bin.000121 |  366573729 |
| mysql-bin.000122 | 1073742114 |
| mysql-bin.000123 | 1073745448 |
| mysql-bin.000124 |  347222446 |
| mysql-bin.000125 |     142214 |
+------------------+------------+

root@localhost [(none)]> PURGE BINARY LOGS TO 'mysql-bin.000123';
Query OK, 0 rows affected (5.691 sec)

root@localhost [(none)]> SHOW BINARY LOGS;
+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
| mysql-bin.000123 | 1073745448 |
| mysql-bin.000124 |  347222446 |
| mysql-bin.000125 |     165834 |
+------------------+------------+
3 rows in set (0.000 sec)

root@localhost [(none)]> set global expire_logs_days=28;
Query OK, 0 rows affected (0.000 sec)

#!/bin/bash

# This script assumes you have configured mysql to run without asking for username/password
# on both the master and slave hosts. (e.g. by putting credentials in ~/.my.cnf)

# Replace 'replication-server' with the hostname of your slave server.
#SECONDS=$(echo 'SHOW SLAVE STATUS\G' | ssh replication-server mysql | grep Seconds_Behind_Master | grep -o '[0-9]\+' )

# This performs the actual purge on the Master DB
# It calculates what date the slave has replicated to, and then subtracts an additional 4 weeks
# buffer just in case.
#echo "PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL 4 WEEK - INTERVAL $SECONDS SECOND)" | mysql

echo "PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL 4 WEEK )" | mysql

# mysqlcheck -o --all-databases

[...]
orange11sql2.be_groups                             OK
orange11sql2.be_sessions                           OK
orange11sql2.be_users                              OK
orange11sql2.cache_extensions                      OK
orange11sql2.cache_hash
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
orange11sql2.cache_imagesizes
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK