How to configure MariaDB binlog for point-in-time recovery

Configure binary logging in MariaDB 11.4 to enable point-in-time recovery. You will set global variables, restart the server, and verify the log is active. These steps apply to a fresh install on any supported Linux distribution.

Prerequisites

• MariaDB 11.4 installed and running.
• Root or a user with GRANT ALL PRIVILEGES ON *.*.
• Access to the MariaDB configuration file (my.cnf or my.ini).
• Enough disk space for binary log files.

Step 1: Create a new binary log file

Start a new binary log file to ensure a clean state before enabling logging. This prevents mixing old and new log files during the configuration change.

STOP BINLOG;

RESET MASTER;

START BINLOG;

Step 2: Set binary logging variables

Enable binary logging and set the format to ROW. ROW format is recommended for point-in-time recovery because it captures actual row changes rather than SQL statements. Set the log file name prefix and the number of files to keep before rotation.

SET GLOBAL log_bin = 'ON';

SET GLOBAL binlog_format = 'ROW';

SET GLOBAL binlog_expire_logs_seconds = 2592000;

SET GLOBAL binlog_stmt_cache_size = 131072;

SET GLOBAL binlog_cache_size = 32768;

SET GLOBAL binlog_stmt_format = 'ROW';

Step 3: Configure flush_log_triggers

Enable flush_log_triggers to ensure binary logs are flushed to disk after each transaction. This reduces the risk of data loss if the server crashes. Set the variable to 1 to enable it.

SET GLOBAL sync_binlog = 1;

SET GLOBAL flush_log_triggers = 1;

Step 4: Restart the MariaDB server

Restart the MariaDB service to apply the new settings. On systemd-based systems like Ubuntu or AlmaLinux, use systemctl. On other systems, use the appropriate service command.

systemctl restart mariadb

Wait for the service to start and check the status.

systemctl status mariadb

Step 5: Verify the binary log is active

Check the binary log file name and the current position. Use the SHOW VARIABLES command to confirm the settings are active.

SHOW VARIABLES LIKE 'log_bin';

SHOW VARIABLES LIKE 'binlog_format';

SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';

SHOW VARIABLES LIKE 'sync_binlog';

SHOW VARIABLES LIKE 'flush_log_triggers';

The output should show 'ON' for log_bin and 'ROW' for binlog_format. The binlog_expire_logs_seconds should be set to 2592000 (30 days).

SHOW BINARY LOGS;

This command lists all binary log files. You should see a file like 'bin.000001' with a position of 4.

SHOW MASTER STATUS;

This command shows the current binary log file and position. The output should look like this:

File: bin.000001
Position: 4
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:

Verify the installation

Perform a test write to ensure the binary log is capturing changes. Insert a row into a table and check the log file.

CREATE TABLE test_recovery (id INT, name VARCHAR(50));

INSERT INTO test_recovery (id, name) VALUES (1, 'test');

SHOW BINARY LOGS;

The output should show the new log file and the updated position.

File: bin.000001
Position: 1234

Check the log file content to ensure the row change is recorded.

mysqlbinlog --start-position=1234 bin.000001

The output should show the INSERT statement for the test row.

Troubleshooting

If you see "Unknown system variable 'log_bin'", check the MariaDB version. This variable is available in MariaDB 10.2 and later.

If you see "Error: Can't create/write to file 'bin.000001'", check the disk space and file permissions. Ensure the MariaDB user can write to the log directory.

If you see "Unknown system variable 'binlog_expire_logs_seconds'", update the MariaDB version. This variable is available in MariaDB 10.3 and later.

If you see "Row event not found in binlog", ensure the table structure matches the log file. This error occurs if the table schema changes after the log is written.