Contents
How to do point in time recovery of MySQL data?
First, you have to pick a backup to restore. If the backup is compatible with PITR, an option will be presented to perform a Point-In-Time Recovery. You will have two options for that – “Time Based” and “Position Based”. Let’s discuss the difference between those two options.
How are binary logs used in MySQL replication?
Luckily, MySQL has such a mechanism for storing changes – binary logs. You may know those logs are used for replication – MySQL uses them to store all of the changes which happened on the master, and a slave uses them to replay those changes and apply them to its dataset.
When to use a point in time recovery?
Typically, you will use backups to recover from different types of cases: accidental DROP TABLE or DELETE without a WHERE clause, or with a WHERE clause that was not specific enough. Is restoring from backup not enough? What does it have to be point-in-time? We have to keep in mind that a backup is a snapshot of data taken at a given point in time.
Which is MySQL Backup is compatible with Pitr?
First of all, you have to take a backup which is compatible with PITR. Such backup has to be full, complete and consistent. For xtrabackup, as long it contains full dataset (you didn’t include just a subset of schemas), it will be PITR-compatible. For mysqldump, there is an option to make it PITR-compatible.
When to do point in time data recovery?
We have to keep in mind that a backup is a snapshot of data taken at a given point in time. If you take a backup at 1:00 am and a table was removed accidently at 11:00 am, you can restore your data up to 1:00 am but what about changes which happened between 1:00 am and 11:00 am?
How to restore full physical backup of MySQL?
You can restore full physical backup on a separate host and then use mysqldump to dump the missing data and load it up on the production server. Keep in mind that when you restore the backup on your cluster, you will have to rebuild nodes other than the one you recovered.