MySQL Point-in-time Recovery

Summary: in this tutorial, you will learn about the MySQL Point-in-time recovery concept and how to restore a database to a specific time in the past.

Introduction to MySQL point-in-time recovery

The point-in-time recovery allows you to restore a MySQL database to a specific time in the past. The point-in-time recovery relies on two key components:

Full backup: This serves as the foundation for recovery, providing the starting state of the database.
Binary logs: These binary log files record all changes made to the database, allowing you to replay those changes to a desired point.

If you don’t have a full backup or binary logs enabled, you cannot carry the point-in-time recovery.

We’ll illustrate how to perform the point-in-time recovery in MySQL to recover a database to a specific point in time.

Checking binary log status and taking a full backup of a database

First, open Command Prompt on Windows or Terminal on Unix-like systems.

Next, connect to the MySQL Server:

mysql -u root -pCode language: SQL (Structured Query Language) (sql)

Then, check if the binary log is enabled by showing the value of the log_bin variable:

show global variables like 'log_bin';Code language: SQL (Structured Query Language) (sql)

Output:


+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.01 sec)Code language: SQL (Structured Query Language) (sql)

The output indicates that the binary log is currently enabled. If it is not, you need to enable the binary log.

After that, exit the mysql program:

exitCode language: SQL (Structured Query Language) (sql)

Creating a new sample database & taking a full backup

First, reconnect to the MySQL server database:

mysql -u root -p

Second, create a new database called mydb:

CREATE DATABASE mydb;

Third, change the current database to mydb:

USE mydb;Code language: PHP (php)

Fourth, create a new table called contacts in the mydb database:

CREATE TABLE IF NOT EXISTS contacts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL
);Code language: PHP (php)

Fifth, insert three rows into the contacts table:

INSERT INTO contacts (first_name, last_name, email) 
VALUES
    ('John', 'Doe', '[email protected]'),
    ('Jane', 'Smith', '[email protected]'),
    ('Bob', 'Johnson', '[email protected]');Code language: JavaScript (javascript)

Sixth, exit the mysql program:

exitCode language: PHP (php)

Finally, take a full backup of the mydb database and store the dump file in the ~/backup/ directory:

mysql -u root -p > ~/backup/mydb.sqlCode language: JavaScript (javascript)

Making changes to the database

First, reconnect to the MySQL server:

mysql -u root -pCode language: SQL (Structured Query Language) (sql)

Second, change the current database to mydb:

USE mydb;Code language: SQL (Structured Query Language) (sql)

Third, insert a new row into the mydb database:

INSERT INTO contacts(first_name, last_name, email)
VALUES('Bob','Climo', '[email protected]');Code language: JavaScript (javascript)

Fourth, delete a contact with the id 1 but forget to add a WHERE clause, hence, the statement deletes all rows from the contacts table:

DELETE FROM contacts;Code language: SQL (Structured Query Language) (sql)

Since we delete all rows from the contacts table unintentionally, we want to recover the contacts table.

Fifth, show the current position of the binary log:

SHOW MASTER STATUS;Code language: SQL (Structured Query Language) (sql)

Output:


+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000001 |     4952 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)

The current binary log file is binlog.000001.

Sixth, exit the mysql program:

exitCode language: SQL (Structured Query Language) (sql)

Seventh, check the time when we delete all rows from the contacts table in the binary log file using the mysqlbinlog utility program:

 mysqlbinlog  --verbose /var/lib/mysql/binlog.000001 | grep -i -C 10 "delete table"Code language: SQL (Structured Query Language) (sql)

The output looks like this:

--
#231225 20:26:10 server id 1  end_log_pos 4921 CRC32 0x53432937         Delete_rows: table id 494 flags: STMT_END_F

BINLOG '
8oKJZRMBAAAARAAAAHISAAAAAO4BAAAAAAEABG15ZGIACGNvbnRhY3RzAAQDDw8PBvwD/AP8AwAB
AQACA/z/ADxQW1A=
8oKJZSABAAAAxwAAADkTAAAAAO4BAAAAAAEAAgAE/wABAAAABABKb2huAwBEb2UUAGpvaG4uZG9l
QGV4YW1wbGUuY29tAAIAAAAEAEphbmUFAFNtaXRoFgBqYW5lLnNtaXRoQGV4YW1wbGUuY29tAAMA
AAADAEJvYgcASm9obnNvbhcAYm9iLmpvaG5zb25AZXhhbXBsZS5jb20ABAAAAAMAQm9iBQBDbGlt
bxUAYm9iLmNsaW1vQGV4YW1wbGUuY29tNylDUw==
'/*!*/;
### DELETE FROM `mydb`.`contacts`
### WHERE
###   @1=1
###   @2='John'
###   @3='Doe'
###   @4='[email protected]'
### DELETE FROM `mydb`.`contacts`
### WHERE
###   @1=2
###   @2='Jane'
###   @3='Smith'
###   @4='[email protected]'
### DELETE FROM `mydb`.`contacts`
### WHERE
###   @1=3
###   @2='Bob'
###   @3='Johnson'
###   @4='[email protected]'
### DELETE FROM `mydb`.`contacts`
### WHERE
###   @1=4
###   @2='Bob'
###   @3='Climo'
###   @4='[email protected]'
# at 4921
#231225 20:26:10 server id 1  end_log_pos 4952 CRC32 0x634fce0a         Xid = 3194
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
Code language: SQL (Structured Query Language) (sql)

The output shows that at 2023-12-25 20:26:10 we delete all rows from the contacts table. Therefore, we need to recover the database up to 2023-12-25 20:26:10.

Performing a point-in-time recovery

First, reconnect to the MySQL server:

mysql -u root -pCode language: SQL (Structured Query Language) (sql)

Second, drop the mydb database and recreate it:

DROP DATABASE mydb;
CREATE DATABASE mydb;Code language: SQL (Structured Query Language) (sql)

Third, exit mysql program:

exitCode language: SQL (Structured Query Language) (sql)

Fourth, restore the mydb database from the full backup:

mysql -u root -p mydb < ~/backup/mydb.sqlCode language: SQL (Structured Query Language) (sql)

Fifth, recover the rows from the contacts table from the binary log file using the mysqlbinlog utility program:

mysqlbinlog --stop-datetime="2023-12-25 20:26:10" --verbose /var/lib/mysql/binlog.000001 | mysql -u root -p Code language: SQL (Structured Query Language) (sql)

This command reads the MySQL binary log file (/var/lib/mysql/binlog.000001), stopping at a specified datetime (2023-12-25 20:26:10), displays the contents with additional verbosity, and then pipes that output to the mysql command, which executes the SQL statements recorded in the binary log on a MySQL server, using the specified user (root) and prompting for a password.

Let’s breakd down the command:

mysqlbinlog:
- mysqlbinlog is a command-line utility provided by MySQL for processing and displaying the contents of binary log files.
- --stop-datetime="2023-12-25 20:26:10": This option specifies a datetime value and mysqlbinlog will process the binary log until it reaches the specified datetime. In this case, it stops processing at December 25, 2023, 20:26:10.
- --verbose: This option causes mysqlbinlog to display additional information along with the actual log contents. It can be useful for debugging and understanding the log entries.
- /var/lib/mysql/binlog.000001: This is the path to the binary log file that mysqlbinlog will process.
| (pipe):
- The pipe symbol (|) is used to redirect the output of the first command (mysqlbinlog) to the input of the second command (mysql).
mysql:
- mysql is another command-line utility used to execute SQL statements based on the output of mysqlbinlog.
- -u root -p: These are options for specifying the MySQL user (root in this case) and prompting for a password (-p).

Sixth, connect to MySQL server:

mysql -u root -p

Seventh, switch the current database to mydb:

use mydb;Code language: PHP (php)

Finally, retrieve data from the contacts table:

SELECT * FROM contacts;

Output:


+----+------------+-----------+-------------------------+
| id | first_name | last_name | email                   |
+----+------------+-----------+-------------------------+
|  1 | John       | Doe       | [email protected]    |
|  2 | Jane       | Smith     | [email protected]  |
|  3 | Bob        | Johnson   | [email protected] |
|  4 | Bob        | Climo     | [email protected]   |
+----+------------+-----------+-------------------------+
4 rows in set (0.00 sec)Code language: JavaScript (javascript)

The output indicates that we have successfully recover the rows from the contacts table right before we issued the DELETE statement that removed all the rows from the table.

Finally, exit the mysql program:

exitCode language: PHP (php)

Summary

Use point-in-time recovery to restore a MySQL database to a specific time in the past.
Restore a database from a full backup and restore to a specific time from the binary log log to perform a point-in-time recovery.

Was this tutorial helpful?