How to recover point in time recovery in MariaDB / MySQL databases

Backing up and restoring a MySQL database is a critical part of database management. This guide explains how to take a full backup, restore it, and recover to a specific point in time using binary logs. All steps are accompanied by simple examples.

Following are the steps involved in process:

1. Step 1: Check if Binary Logs Are Enabled
2. Step 2: Take a Full Backup
3. Step 3: Restore the Full Backup
4. Step 4: Identify the Binary Logs for Recovery
5. Step 5: Perform Point-in-Time Recovery
6. Step 6: Verify the Restoration

Step 1: Check if Binary Logs Are Enabled

Binary logs are required for point-in-time recovery. Verify if they are enabled:

--- Login to MySQL command prompt:
mysql -u root -p

--Check the binary log status:
SHOW VARIABLES LIKE 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+

Step 2: Take a Full Backup

Use mysqldump to take a full backup and include binary log information.

mysqldump -u root -p --databases your_database_name --flush-logs --master-data=2 > /path/to/full_backup.sql

Note:
--flush-logs: Ensures binary logs start cleanly.
--master-data=2: Includes binary log details as a comment in the backup file.

Open the backup file and find the binary log coordinates:

-- On linux machine:
head -n 50 /path/to/full_backup.sql

Example: show like below:
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;

Save all the files generated after this mentioned file included this.

Step 3: Restore the Full Backup

mysql -u root -p your_database_name < /path/to/full_backup.sql

Step 4: Identify the Binary Logs for Recovery

-- List the binary logs available:
mysql -u root -p -e "SHOW BINARY LOGS;"

+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
| mysql-bin.000001 | 104857600  |
| mysql-bin.000002 | 52428800   |
+------------------+------------+

-- Review the logs to determine the recovery range:
mysqlbinlog --start-position=107 /var/log/mysql/mysql-bin.000001 | less

-- Use timestamps or positions to identify the required logs for your recovery point.

Step 5: Perform Point-in-Time Recovery

Recover the database up to a specific time or event using binary logs.

-- Use timestamps:
mysqlbinlog --start-datetime="2024-12-22 09:00:00" --stop-datetime="2024-12-22 12:00:00" /var/log/mysql/mysql-bin.000001 > binlog_recovery.sql

-- Apply the binary log to the database:
mysql -u root -p your_database_name < binlog_recovery.sql

-- Alternatively, use positions:
mysqlbinlog --start-position=107 --stop-position=2000 /var/log/mysql/mysql-bin.000001 | mysql -u root -p your_database_name

Step 6: Verify the Restoration

Check the data in the database to confirm:

mysql -u root -p -e "SELECT * FROM your_database_name.some_table;"