MySQL Point in Time Recovery: How To Configure And How Does it Work?

 MySQL Point in Time Recovery: How To Configure And How Does it Work?

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 binar log status:-

We need login to mysql server and run given below command:-

show global variables like 'log_bin';

Show output for enable mysql-bin log.

Creating a new sample database & taking a full backup:-

We are going to login in mysql databse and creating new database

and table.given below command:-

After that we are going to insert record in table:-

Finally, take a full backup of the mydb database and

store the dump file in the ~/backup/ directory:

mysql -u root -p > ~/backup/PITR.sql

Now Something change in PITR databases:-

First We need to connected to  mysql databse and use PITR database.

after that we are going to insert some record.

After that we are going to delete all record from contact table.

Now we are going to check mysql bin log position.

using given below command:-

mysql> show master status;

Output:-

check the time when we delete all rows from the contacts table in

the binary log file using the mysqlbinlog utility program:

Given below command:-

mysqlbinlog  --verbose /var/lib/mysql/mysql-bin.000001 |

grep -i -C 10 "DELETE FROM `PITR`.`contacts`"

Now we are going to drop database.

After that we going to check mysql bin-log postion and name.

given below command:-

Another way login to mysql and run below command:-

Finally we are going to restore mysql log with position.

given below snapshot:-