How to Perform Point-in-Time Recovery (PITR) in MySQL to Restore Corrupted Databases?

Point-in-time recovery is a type of backup recovery that helps restore data changes up to a specific moment, after a full backup restore. Based on binary logging, it helps restore the database to a more recent time, just before the problem or issue has occurred. For more clarity, let’s take an example:

“If your MySQL Server experienced a sudden system crash at 3 PM on Tuesday, which resulted in corruption of tables in the database. Since the crash happened at 3 PM, you can restore the database to the point of time just before the crash occurred. If the Binary logging was enabled, then you can perform the point-in-time recovery to a state before the crash occurred.”

However, to restore database using PITR, you must have the binary log files. These files are created only after a full backup. All changes made to the database after the full backup are recorded in these binary log files. These files are usually present in the last incremental backup in the series of backups.

In this article, we will learn different ways to perform point-in-time recovery to restore MySQL databases.

Prerequisites for Point-in-Time Recovery in MySQL

For point-in-time recovery in MySQL Server, consider the following:

• Binary log files are present in the backup file by default. But if you have created the backup using the –use-tts option, the binary log files will not be generated.
• Make sure you have a readable full backup file. The binary log file is often included in the incremental backup file, you can use it for point-in-time recovery.
• Make sure the Binary logging is enabled.
• Ensure you have all the permissions to restore backup. You can run SHOW PRIVELEGES statement to check the permissions.

Ways to Perform Point-in-Time Recovery to Restore Databases

You can perform point-in-time recovery using the below methods.

Method 1: Using Binary Log (Binlog) File

Binary log file is included in your last backup file. It contains events that record database changes. It describes modifications to table data and operations to create tables. It includes DELETE statements that you have used to make changes. It also contains the duration of statements taken to update data in the database.

Steps to perform point-in-time recovery using Binary Log file:

First, ensure that the binary logging is enabled on your MySQL Server. To check the status of binary logging, you can execute the below command:

If the command displays the value of log_bin file as ON, it means the binary logging is enabled. Now, follow the below steps to restore data from the binary log:

• Find the name and location of current binary log files. By default, the binary log files are located in the data directory. However, you can place these files in a different location using the –log-bin option. You can run the below statement to see the list of all binary log files:

• If you want to know the status of binary log files on server, then run the below command:

• To find the name of current binary log file on your MySQL Server, you can run the following command:

• Next, use the mysqlbinlog utility to replay binary log events. Make sure you have all privileges, like BINLOG_ADMIN privilege or SUPER, REPLICATIION_APPLIER, to execute log events. Here is the command to run the mysqlbinlog utility:

• You can check the data in binary log files and select the individual content to apply. Run the below command:

Alternatively, you can apply the events directly from the binary log file in the mysql command using the below command:

• Next, use the below command to restore the MySQL database using the binlog file:

• To restore MySQL database using multiple binlog files, run the below command:

Method 2: Using Event Positions

You can also use event positions within the binary log to perform the point-in-time recovery in MySQL. In this, you have to check the binary log precisely to know the exact position and event, and then use it to apply the binary log events. To check the event positions, you can use mysqlbinlog utility.

Steps to perform point-in-time recovery using Event Positions

Following are the steps to use event positions within binary log for point-in-time recovery:

• First, restore the last full backup and the series of backups.
• Next, use the mysqlbinlog utility to find the binary log event position corresponding to the point-in-time. Find the starting and stopping positions using the --start-position and --stop-position options, respectively. For this, run the below command:

• Next, apply the binary log events to server, starting from that position.

This command replays all the changes done to the database up to the specified point-in-time. It recovers all the transactions from the starting position to the stop position. It recovers your corrupted database to the point-in-time of interest, right before the MySQL database was crashed.

Limitations of Point-in-Time Recovery

The point-in-time recovery feature in MySQL can help you restore the corrupt database to a specific moment in the past. However, it requires the binary logging feature to be enabled and a recent readable backup. The point-in-time recovery can fail, if the binary log files get corrupted.

Quick Solution to Restore Corrupted Databases

If your backup file or binary log file is corrupted or binary logging is disabled, you can restore the corrupt MySQL database using specialized MySQL recovery tools, like Stellar Repair for MySQL. This tool can recover data from corrupted database quickly. It can repair corrupt databases created in both InnoDB and MyISAM storage engines. The tool can restore all the data from corrupted database to a new database with complete precision. You can also save the repaired database file in multiple formats, such as MariaDB, SQL Script, CSV, XLS, and HTML. It allows restoring the MySQL database created on Windows and Linux systems.

Conclusion

Above, we have discussed two methods to restore corrupt MySQL database using point-in-recovery. But to restore the database using these methods requires binary log file and full database backup. If your backup file is not readable, then you can repair the corrupted MySQL database using Stellar Repair for MySQL. It can quickly and effectively repair and restore the corrupted MySQL database with complete integrity. Also, it allows you to perform specific recovery of MySQL objects, including partition tables, table space, etc. The tool supports MySQL 8.0.40 and lower versions.