MariaDB’s point-in-time recovery (PITR) using binary logs is your safety net when a data-altering mistake happens, allowing you to rewind your database to exactly the moment before the incident.
Let’s see this in action. Imagine you have a products table.
CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
price DECIMAL(10, 2) NOT NULL
);
INSERT INTO products (name, price) VALUES ('Laptop', 1200.00);
INSERT INTO products (name, price) VALUES ('Keyboard', 75.00);
INSERT INTO products (name, price) VALUES ('Mouse', 25.00);
Now, a rogue DELETE statement:
-- Oops! This deleted everything!
DELETE FROM products WHERE price < 100.00;
Your products table is now empty. PITR is how you get those rows back.
At its core, PITR relies on two things: a full backup of your database at a specific point in time, and a continuous stream of all changes made after that backup, recorded in the binary logs. When you need to recover, you restore the full backup and then "replay" the changes from the binary logs up to the precise moment you want to restore to.
The key components are:
- Full Backup: A complete snapshot of your data. This is your starting point.
- Binary Logs (binlogs): These are transaction logs that record every data-modifying statement (and sometimes schema changes) executed on the server. They are essential for reconstructing the state of the database between full backups.
mysqlbinlogutility: This tool reads and processes the binary log files.mysqldumpor similar: Used to create the initial full backup.
To perform PITR, you’ll need:
- A recent full backup.
- All binary log files from the time of the backup up to the desired recovery point.
- The position in the binary log corresponding to the desired recovery point.
Let’s walk through the process.
First, ensure binary logging is enabled in your MariaDB configuration (my.cnf or my.ini). You’ll need log_bin set and binlog_format typically set to ROW or MIXED (ROW is generally preferred for PITR accuracy).
[mysqld]
log_bin = /var/lib/mysql/mysql-bin
binlog_format = ROW
server_id = 1
After making these changes, restart your MariaDB server.
Now, take a full backup. Using mysqldump is common:
mysqldump -u root -p --single-transaction --master-data=2 --flush-logs --all-databases > full_backup_20231027.sql
The --master-data=2 option is crucial. It adds a CHANGE MASTER TO statement to the dump file, which includes the current binary log file name and position at the time of the backup. You’ll need this to know where to start replaying. If you don’t use --master-data, you’ll have to manually find the starting binlog position later.
After taking the backup, your full_backup_20231027.sql file will contain something like:
--
-- Position to start replication from
--
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000015', MASTER_LOG_POS=12345;
--
Let’s say the accidental DELETE happened at 10:30 AM. You have your full backup taken at 10:00 AM, and you know the binary logs are being written to /var/lib/mysql/. You need to find the binlog file and position corresponding to 10:29:59 AM.
You can examine the binary logs using mysqlbinlog. If you know the binlog file and position from --master-data, you can start there. Otherwise, you might need to examine them sequentially:
mysqlbinlog /var/lib/mysql/mysql-bin.000015 | grep '2023-10-27 10:29:50'
This will show you statements around that time. You’re looking for the last statement before the problematic one. Let’s say you identify the correct position as mysql-bin.000015 at position 56789.
Now, restore the full backup to a new, temporary MariaDB instance (or a separate directory if you’re careful). Never restore a full backup over your live, working database.
# Assuming MariaDB data directory is /var/lib/mysql_restore
# Stop the temporary instance if running
# rm -rf /var/lib/mysql_restore/*
# mysql -u root -p < full_backup_20231027.sql
Once the full backup is restored, you’ll replay the binary logs from the identified position up to the point just before the error. You’ll use mysqlbinlog to pipe the relevant log entries into your temporary instance.
Let’s assume the error happened at mysql-bin.000016, position 1000. We want to restore to just before that.
# Stop the temporary MariaDB server
mysqlbinlog --stop-position=1000 /var/lib/mysql/mysql-bin.000016 | mysql -u root -p -h 127.0.0.1 --port=3306
If your error spanned multiple binlog files, you’d chain them:
mysqlbinlog --stop-position=1000 /var/lib/mysql/mysql-bin.000016 | \
mysqlbinlog --start-position=1001 --stop-position=5000 /var/lib/mysql/mysql-bin.000017 | \
mysql -u root -p -h 127.0.0.1 --port=3306
This process effectively "rewinds" your database to the state it was in at mysql-bin.000015:56789 and then reapplies all transactions except the problematic DELETE statement.
The --stop-never option with mysqlbinlog is useful if you want to apply logs until the very latest available, which is often what you want for PITR if you don’t know the exact time of failure.
mysqlbinlog --stop-never --start-position=56789 /var/lib/mysql/mysql-bin.000015 | mysql -u root -p -h 127.0.0.1 --port=3306
This command applies all events from position 56789 in mysql-bin.000015 until the end of that file, and then continues applying events from subsequent binary log files until the server is stopped or the logs run out.
The most commonly missed step is correctly identifying the binary log file and position just before the erroneous transaction. If you restore from too early or too late, you’ll either miss recent legitimate data or reapply the bad transaction.
Once your temporary instance is in the desired state, you can carefully compare it with your live data, potentially export the missing/corrected data, and then replace your live database with this recovered, correct version.
The next hurdle you’ll face is managing the lifecycle of your binary logs. If they aren’t purged, they can consume vast amounts of disk space.