How to Perform MySQL Database Point-in-Time Recovery


How to Perform MySQL Database Point-in-Time Recovery


Restore the Database from last full backup ( the post discuss restoring just a single MySQL database only) you can adjust the commands according to your requirement.

Database anand was accidentally dropped and we have full dump and binary log backup which I am using to restore the database back


Database Anand is missing

show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| menagerie          |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

Restore single database (anand) from last fullbackup


mysql -u root -p < C:\mysql\backup\MySQL_Sunday_Full_Backup.sql

Enter password: *******

after successfull restore database anand is back again.

show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| anand              |
| menagerie          |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

use anand;

Database changed

show tables;
+-----------------+
| Tables_in_anand |
+-----------------+
| help            |
| t1              |
+-----------------+
2 rows in set (0.00 sec)


Ohh what's that I can see there are some tables still missing. Come on it is expected as the full backup I used to restore is a bit older and does not contain the changes made after fullbackup.

To bring forward the database and recover the missing data we must apply the incremental changes.
To identify which binary logs we Need for the same ,  just open the fullbackup dump and you will find line something like this.
--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='bin_log.000019', MASTER_LOG_POS=154;


This means we need binary log from bin_log.000019 till now (till point of time of recovery)


Applying Incremental Backup for database anand



mysqlbinlog bin_log.000019 bin_log.000020 bin_log.000021  bin_log.000022 bin_log.000023 bin_log.000024 bin_log.000025 |mysql anand -u root -p

Enter password: *******

Note:- did you notice I am passing all the binarylogs required for this recovery in one mysqlbinlog command. This is very importent Piece of Information. Please do not execute something like this. Ist dangerous.


shell> mysqlbinlog bin_log.000019 | mysql -u root -p

shell> mysqlbinlog bin_log.0000120| mysql -u root -p
.......................................................................................
.......................................................................................
.......................................................................................

For more Infrmation check out
https://dev.mysql.com/doc/refman/5.7/en/point-in-time-recovery.html

After successfully applying the incremental backup I can see all the tables and data in the table is back again.

I am happy.

use anand;
Database changed

show tables;
+-----------------+
| Tables_in_anand |
+-----------------+
| help            |
| t1              |
| t2              |
+-----------------+
3 rows in set (0.00 sec)

Good Luck ;

2 comments: