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 back up: 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.
To allow a server to be restored to a point-in-time, binary logging must be enabled on it,
which is the default setting for MySQL 8.4:
SHOW GLOBAL VARIABLES LIKE 'log_bin';
To restore data from the binary log, you must know the name and location of the current binary
log files. By default, the server creates binary log files in the data directory, but a path name can
be specified with the --log-bin option to place the files in a different location. To see a listing of
all binary log files, use this statement:
SHOW BINARY LOGS;
To determine the name of the current binary log file, issue the following statement:
SHOW BINARY LOG STATUS;
The mysqlbinlog utility converts the events in the binary log files from binary format to text so
that they can be viewed or applied. mysqlbinlog has options for selecting sections of the binary
log based on event times or position of events within the log.
mysqlbinlog binlog_files
save the output in a file
mysqlbinlog binlog_files > out_file
If you have more than one binary log:
mysqlbinlog binlog.000001 binlog.000002
Creating a new sample database & taking a full backup:
CREATE DATABASE mydb;
USE mydb;
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);
INSERT INTO contacts (first_name, last_name, email) VALUES('John', 'Doe',
'
[email protected]'),('Jane', 'Smith', '
[email protected]'),('Bob', 'Johnson',
'
[email protected]');
Take a full backup of the mydb database and store the dump file in the backup directory:
mysqldump -u root -p mydb > /db_backup/mydb_dump.sql
Making changes to the database:
insert a new row into the mydb database:
SELECT now(); #--start_datetime
INSERT INTO contacts(first_name, last_name, email)VALUES('Naveen','Gomangi',
'
[email protected]');
delete a contact with the id 1 but forget to add a WHERE clause, hence, the statement
deletes all rows from the contact’s table:
DELETE FROM contacts;
SELECT now(); #--stop_datetime
Since we delete all rows from the contacts table unintentionally, we want to recover the
contacts’ table.
show the current position of the binary log:
SHOW BINARY LOG STATUS;
In this case we can use current log file for PITR.
PITR Using Event DateTime:
Step1) Restore the last full backup created before the point-in-time recovery:
DROP DATABASE mydb;
CREATE DATABASE mydb;
mysql -u root -p mydb < /db_backup/mydb_dump.sql
Step2) Find the precise binary log event datetime:
mysqlbinlog --start-datetime="2024-12-20 09:08:25" --stop-datetime="2024-12-20 09:13:33"
/var/lib/mysql/binlog.000002 |grep -i -C 15 "delete"
From the output of mysqlbinlog, the data is available till “2024-12-20 9:10:38”
Step3) Apply the events in binary log file to the server.
mysqlbinlog --stop-datetime="2024-12-20 9:10:38" --verbose /var/lib/mysql/binlog.000002
|mysql -u root -p
PITR Using Event Position:
Step1) Take a full backup of the mydb database and store the dump file in the backup
directory:
Step2) Making changes to the database:
Let’s insert some data into the table and drop the table
INSERT INTO contacts(first_name,last_name,email) VALUES('Rohit','Sharma','[email protected]');
INSERT INTO contacts(first_name,last_name,email) VALUES('Virat','Kohli','[email protected]');
DROP TABLE contacts;
Check current binary log:
Step3) Find the event position in binary log file:
mysqlbinlog --start-datetime="2024-12-21 19:41:39" --stop-datetime=”2024-12-21 19:45:00”
/var/lib/mysql/binlog.000003 |grep -C 15 "DROP"
Drop table event occurs after event position at 3021. (--stop-position)
Step4) Drop the mydb database and restore the backup
DROP DATABASE mydb;
CREATE DATABASE mydb;
Mysql -u root -p mydb < /db_backup/mydb_dump_22dec24.sql
Step5) To recovery newly inserted data, apply the events in binary log file to the server.
mysqlbinlog --stop-position=3021 /var/lib/mysql/binlog.000003 | mysql -u root -p
Note:
Only use the --start-datetime and --stop-datetime options to help you find the actual event
positions of interest. Using the two options to specify the range of binary log segment to apply is
not recommended: there is a higher risk of missing binary log events when using the options.
Use --start-position and --stop-position instead.
