How to configure MariaDB binary logging for point-in-time recovery

Configure the MariaDB server to record all data changes in binary log files. This setup enables point-in-time recovery, allowing you to restore a database to a specific second after a crash or data corruption. These steps target MariaDB 11.4 running on Ubuntu 24.04 or any Linux distribution with a standard systemd installation.

Prerequisites

• MariaDB Server 11.4 installed and running.
• Root or sudo privileges to edit the configuration file.
• Access to the MariaDB shell (mysql) or command line.
• At least 2 GB of free disk space for initial log file growth.

Step 1: Edit the MariaDB configuration file

Open the main configuration file located at /etc/my.cnf. Add the binary logging directives to the [mysqld] section. Ensure you do not duplicate existing settings if they are already present in the file.

sudo nano /etc/my.cnf

Add the following lines to the end of the file. These settings enable logging, set the log file name, and define the maximum size for each log file.

[mysqld]
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
binlog_expire_logs_seconds = 2592000
max_binlog_size = 100M
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

The log_bin directive specifies the path and filename for the binary log. The binlog_format must be ROW to support point-in-time recovery for all data types. The binlog_expire_logs_seconds setting automatically removes old logs after 30 days to prevent disk exhaustion. The max_binlog_size limits each log file to 100 MB. The sync_binlog setting ensures logs are written to disk after every transaction. The innodb_flush_log_at_trx_commit setting ensures data durability by flushing logs to disk after every transaction.

Step 2: Restart the MariaDB service

Apply the new configuration by restarting the MariaDB service. This action forces the server to read the updated settings from the configuration file.

sudo systemctl restart mariadb

Verify that the service started without errors. If the service fails to start, check the logs at /var/log/mysql/error.log for syntax errors in the configuration file.

Step 3: Verify binary logging is active

Connect to the MariaDB shell to check the current status of the binary log. Run the SHOW VARIABLES command to confirm that logging is enabled and the format is ROW.

mysql -u root -p -e "SHOW VARIABLES LIKE 'log_bin';"

You should see output similar to this:

log_bin | ON

Check the log file size and name to confirm the server is writing to the correct location.

mysql -u root -p -e "SHOW VARIABLES LIKE 'log_bin_basename';"

Expected output:

log_bin_basename | /var/log/mysql/mysql-bin.000001

Step 4: Create a test transaction

Perform a simple INSERT operation to generate a new binary log entry. This confirms the server is actively recording changes.

mysql -u root -p
MariaDB [(none)]> USE test_db;
MariaDB [test_db]> CREATE TABLE IF NOT EXISTS recovery_test (id INT PRIMARY KEY, data VARCHAR(100));
MariaDB [test_db]> INSERT INTO recovery_test (id, data) VALUES (1, 'Initial Data');
MariaDB [test_db]> SHOW BINARY LOGS;

The SHOW BINARY LOGS command lists all available log files. You should see a new file listed, typically named mysql-bin.000001 or similar.

File_name | Size | Position | File
----------|------|----------|------------------
mysql-bin.000001 | 1024 | 4 | mysql-bin.000001

Verify the installation

Run a final check to ensure the binary logs are being rotated correctly and contain data. Execute the following command to list logs with their sizes.

mysql -u root -p -e "SHOW BINARY LOGS;"

If the list shows multiple files with increasing positions, the rotation logic is working. If only one file exists and it is small, the test transaction may not have triggered a rotation yet. This is normal behavior until the file reaches the max_binlog_size limit.

Troubleshooting

Error: "Can't create/write to file '/var/log/mysql/mysql-bin.log'"

This error indicates the directory does not exist or lacks write permissions. Create the directory and set ownership to the MariaDB user.

sudo mkdir -p /var/log/mysql
sudo chown mysql:mysql /var/log/mysql

Error: "Invalid binlog_format specified"

This occurs if you set binlog_format to STATEMENT or MIXED when using certain data types. Always use ROW format for point-in-time recovery. Revert to ROW format in the configuration file.

Error: "The server is running with the GTID mode ON but the log_bin is not set"

This happens if you enable GTID without enabling binary logging. Ensure log_bin is set before enabling GTID mode. Restart the server after changing the configuration.

Error: "Table is marked as crashed"

If a table is marked as crashed, binary logging may have failed during the crash. Run the repair command to fix the table structure before resuming logging.

mysql -u root -p
MariaDB [test_db]> CHECK TABLE recovery_test;
MariaDB [test_db]> REPAIR TABLE recovery_test;

Log rotation issues

If you want to manage log rotation manually, use the FLUSH LOGS command. This forces the server to close the current log file and start a new one. Use this command only when you have a backup strategy in place.

mysql -u root -p -e "FLUSH LOGS;"

Verify log file existence

Check the filesystem directly to ensure the log file exists and is growing.

ls -lh /var/log/mysql/mysql-bin.*

If the file is missing, the server may have failed to start or the path in the configuration file is incorrect. Verify the path matches the directory created in the prerequisites.

Check for syntax errors

If the service fails to start, check the error log for syntax errors.

sudo tail -n 50 /var/log/mysql/error.log

Look for lines containing "ERROR" or "Syntax error". Correct the configuration file and restart the service.

Ensure disk space is available

Binary logs consume disk space. Monitor the size of the log files to prevent disk exhaustion.

du -sh /var/log/mysql/mysql-bin.*

If the total size approaches your disk limit, consider increasing the binlog_expire_logs_seconds value or reducing the max_binlog_size to trigger rotation sooner.