Point-in-time recovery (PITR) helps to recover data from unintentional deletions and modifications.
Ways to recover the data
- Recover the complete database, backup or export it using a previous timestamp and then restore or import it into a new database. This is commonly used to recover from data corruption issues as we need to restore the database to a point in time before the damage happens.
- Restore a portion of the database, run an old read with a query-condition and timestamp from the past, then move the results back into the active database. This is commonly used in operation on an active database. Like this, if we mistakenly remove a row or wrongly alter a portion of data, we may restore it using this approach.
In this blog I will focus on second type, here is an example to recover the table from unintentional delete of a record.
For that I have created table customers and inserted some records checks status of the master to identify binlog position before deleting records.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | CREATE TABLE customers ( customer_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, email VARCHAR(100) NOT NULL UNIQUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- Insert into customers INSERT INTO customers (name, email) VALUES ('John Doe', 'johndoe@example.com'); INSERT INTO customers (name, email) VALUES ('Jane Smith', 'janesmith@example.com'); mysql> select * from customers; +-------------+------------+-----------------------+---------------------+ | customer_id | name | email | created_at | +-------------+------------+-----------------------+---------------------+ | 1 | John Doe | johndoe@example.com | 2024-10-18 12:13:28 | | 2 | Jane Smith | janesmith@example.com | 2024-10-18 12:13:29 | +-------------+------------+-----------------------+---------------------+ 2 rows in set (0.00 sec) mysql> show master status; +---------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+-------------------+ | binlog.000026 | 1194 | | | | +---------------+----------+--------------+------------------+-------------------+ mysql> DELETE FROM customers WHERE customer_id = 1; Query OK, 1 row affected (0.01 sec mysql> select * from customers; +-------------+------------+-----------------------+---------------------+ | customer_id | name | email | created_at | +-------------+------------+-----------------------+---------------------+ | 2 | Jane Smith | janesmith@example.com | 2024-10-18 12:13:29 | +-------------+------------+-----------------------+---------------------+ 1 row in set (0.00 sec) |
Steps for Point-in-time Recovery
Pre-requisites for point in time recovery
Edit the MySQL configuration file (my.cnf) to enable binary logging and set up a server ID. server-id is necessary for replication, and binary logging must be enabled to record the changes needed for PITR.
| 1 2 3 4 5 6 | # include server-id files from the config directory log_bin_trust_function_creators = ON local_infile = ON innodb_buffer_pool_size = 2147483648 bind-address =192.168.42.128 server-id = 1 log_bin = ON |
Restart the MySQL service to apply the changes
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | [root@Master etc]# systemctl stop mysqld.service [root@Master etc]# systemctl start mysqld.service [root@Master etc]# systemctl status mysqld.service ● mysqld.service - MySQL 8.0 database server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled) Active: active (running) since Fri 2024-10-18 11:48:11 EDT; 17min ago Process: 1948 ExecStartPost=/usr/libexec/mysql-check-upgrade (code=exited, status=0/SUCCESS) Process: 1006 ExecStartPre=/usr/libexec/mysql-prepare-db-dir mysqld.service (code=exited, status=0/SUCCESS) Process: 954 ExecStartPre=/usr/libexec/mysql-check-socket (code=exited, status=0/SUCCESS) Main PID: 1064 (mysqld) Status: "Server is operational" Tasks: 38 (limit: 10936) Memory: 492.9M CGroup: /system.slice/mysqld.service └─1064 /usr/libexec/mysqld --basedir=/usr Oct 18 11:47:57 Master systemd[1]: Starting MySQL 8.0 database server... Oct 18 11:48:11 Master systemd[1]: Started MySQL 8.0 database server. |
Verify the parameters
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | mysql> show master status; +---------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+-------------------+ | binlog.000026 | 1526 | | | | +---------------+----------+--------------+------------------+-------------------+ mysql> SHOW VARIABLES LIKE 'log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | ON | +---------------+-------+ 1 row in set (0.34 sec) mysql> SHOW VARIABLES LIKE 'binlog_format'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | binlog_format | ROW | +---------------+-------+ 1 row in set (0.01 sec) |
Take a Backup of the Database Using the mysqldump command for creating a logical backup of the database
| 1 | [root@Master centos]# mysqldump -u root -p --all-databases > /home/centos/backup.sql |
-h specifies the MySQL host.
-u specifies the MySQL user.
-p prompts for the password.
Identify Binary Log Files
Listing the binary log files to identify which logs need to be used for recovery. Checking on a path /var/lib/mysql/
| 1 | [root@Master mysql]# cat /var/lib/mysql/binlog.0000* |
Analyse the Binary Log in human-readable format using mysqlbinlog, this command shows the contents of the binary log, including SQL statements logged during transactions.
| 1 | root@Master mysql]# sudo mysqlbinlog /var/lib/mysql/binlog.000026 |
Recover Data
Using Binary Logs to recover up to a specific point, using the mysqlbinlog command with specific positions
–start-position and –stop-position define the range of the binary log that we want to use.
–skip-gtids skips GTIDs (Global Transaction IDs) if this is enabled in the configuration.
| 1 | [root@Master mysql]# mysqlbinlog /var/lib/mysql/binlog.0000* --start-position=235 --stop-position=1194 --skip-gtids > /home/centos/pitr.sql |
Apply the Recovered SQL Statements
Once we have extracted the SQL statements, apply them in our MySQL database
For Restoring the SQL transactions between the positions defined in the binary log, effectively recovering the database to the desired point in time.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | [root@Master mysql]# mysql -u root -p testdb < /home/centos/pitr.sql [root@Master mysql]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 25 Server version: 8.0.26 Source distribution Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> use testdb; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from customers; +-------------+------------+-----------------------+---------------------+ | customer_id | name | email | created_at | +-------------+------------+-----------------------+---------------------+ | 1 | John Doe | johndoe@example.com | 2024-10-18 12:13:28 | | 2 | Jane Smith | janesmith@example.com | 2024-10-18 12:13:29 | +-------------+------------+-----------------------+---------------------+ 2 rows in set (0.00 sec) |
Conclusion:
By following these steps, we can restore the MySQL database to a specific point in time using binary logs. This is useful in scenarios where we want to undo unintended operations or recover from a crash without losing all the transactions after the last backup.
