PDA

View Full Version : 2 questions: Single DB restore possible & Possible to restore to different DB name?



motin
May 21st, 2007, 05:48 AM
I love ZRM's ability to restore database data to a specific time in history! By doing full backups every week and incremental backups every hour it seems to be possible to get some good sleep knowing the data is safely backed up.

However, a secured backup doesn't do much good in a crisis if the specific restoration process is unknown. I read this on the issue http://www.howtoforge.com/point_in_time_restoration_mysql_zrm , but I still have to get my head straight on these two issues:

1. Is it possible to restore a single database from a full (all-databases) backup? If yes - what parameter(s) is(are) needed where...?

2. How does one restore a database into the current server data under a different database name without shutting down the server?

By having the answers to these questions, I'll be able to write a nice restoration shell-script that takes two parameters: database name and timestamp. The goal after running the script is to have a fully restored database added alongside of the existing databases, with the name databasename_TIMESTAMP.

From there, it is a simple matter of analyzing, comparing and/or restoring anything using simple SQL commands like, "INSERT INTO databasename SELECT * FROM databasename_TIMESTAMP WHERE id < 1000" and similar. Restoring the whole database back to the snapshot is as easy as dropping the current one and renaming the snapshot etc.

It seems like the ultimate backup/restore procedure in my eyes, so hopefully it is possible to do what I ask about above. I will of course publish the finished script here for others to enjoy. Any tips appreciated!

Cheers,

Fredrik

paddy
May 21st, 2007, 03:45 PM
I love ZRM's ability to restore database data to a specific time in history! By doing full backups every week and incremental backups every hour it seems to be possible to get some good sleep knowing the data is safely backed up.

However, a secured backup doesn't do much good in a crisis if the specific restoration process is unknown. I read this on the issue http://www.howtoforge.com/point_in_time_restoration_mysql_zrm , but I still have to get my head straight on these two issues:

1. Is it possible to restore a single database from a full (all-databases) backup? If yes - what parameter(s) is(are) needed where...?



Yes. You can. See http://mysqlbackup.zmanda.com/index.php/Selective_Recovery#Restoring_One_or_More_Databases _from_a_Full_or_Incremental_Backup




2. How does one restore a database into the current server data under a different database name without shutting down the server?



This will work if the database backup is logical. You can look at the backup image and change the database names.

Paddy

motin
May 21st, 2007, 11:47 PM
This will work if the database backup is logical. You can look at the backup image and change the database names.

Paddy


Yes. You can. See http://mysqlbackup.zmanda.com/index.php/Selective_Recovery#Restoring_One_or_More_Databases _from_a_Full_or_Incremental_Backup

Aha ok, so basically I should use sed to transform the stored backup, replacing `databasename` with `databasename_timestamp`.

Then I'll have to change the database name in "index". Lastly, I then follow the above instructions to restore the individual database, using the new timestamped name as selector.

Thanks Paddy, I'll report back with my experience on this matter!

motin
May 22nd, 2007, 12:05 AM
Hmmm. But the need of a logical (full) backup makes it no longer possible to perform point in time restorations under a different database name.

Any idea of how that would be possible?

kkg
May 23rd, 2007, 01:50 AM
Hmmm. But the need of a logical (full) backup makes it no longer possible to perform point in time restorations under a different database name.

Any idea of how that would be possible?


Currently restoring from incremental backups to a different database name is not supported.

BTW you can restores to a completely different mysql server if that will suit your requirements. All you need to do is to first apply the full backup and all of the incremental.

--kkg

motin
May 31st, 2007, 12:21 PM
UNFINISHED POST BELOW - I realize that until ZRM has this functionality built-in - the making and execution of the procedure below is going to take a lot of effort without being as stable nor tested as the built-in functions. It seems a lot easier and safer to take a snapshot of the database as it is, rename it temporarily, restore using standard restoration process to a specific time (http://www.howtoforge.com/point_in_time_restoration_mysql_zrm), then switch the names.

To do this without shutting down the server, one can use two mysql server hosts.

BELOW IS UNFINISHED. Feel free to take off where I left but I believe this case is solved with the above solution at least for me...

Scenario
Someone accidentally ran some catastrophic UPDATE query affecting all records in the members table with id < 1000 at May 30th 1:38. The server is busy and the database large but the records needs to be restored nevertheless, so we need to restore the database to the server under a different name, with the contents of how it was at May 30th 1:37, without shutting the server down.

Backup strategy
Prior to the catastrophe, we set up a weeklycollection backup set that allows for weekly individual off-site archiving and point in time restores.


# Weekly full database backups
0 1 * * 0 /usr/bin/zrm-pre-scheduler --action backup --backup-set weeklycollection --backup-level 0 --interval weekly
0 2 * * * /usr/bin/mysql-zrm --action purge

# Incremental database backups every hour
30 * * * * /usr/bin/mysql-zrm-scheduler --now --backup-set weeklycollection --backup-level 1


Proposed solution
1. Check what backups are available:

mysql-zrm-reporter -show restore-info --where backup-set=weeklycollection | less

2. The output:


weeklycollection Wed 30 May 2007 02:30:01 1 /var/lib/mysql-zrm/weeklycollection/2 Backup succeeded ----
AM CEST 0070530023001
weeklycollection Wed 30 May 2007 01:30:01 1 /var/lib/mysql-zrm/weeklycollection/2 Backup succeeded ----
AM CEST 0070530013001
weeklycollection Wed 30 May 2007 12:30:01 1 /var/lib/mysql-zrm/weeklycollection/2 Backup succeeded ----
AM CEST 0070530003001
weeklycollection Tue 29 May 2007 11:30:02 1 /var/lib/mysql-zrm/weeklycollection/2 Backup succeeded ----
PM CEST 0070529233002
weeklycollection Tue 29 May 2007 10:30:01 1 /var/lib/mysql-zrm/weeklycollection/2 Backup succeeded ----
PM CEST 0070529223001
weeklycollection Tue 29 May 2007 09:30:02 1 /var/lib/mysql-zrm/weeklycollection/2 Backup succeeded ----
PM CEST 0070529213002
weeklycollection Tue 29 May 2007 08:30:02 1 /var/lib/mysql-zrm/weeklycollection/2 Backup succeeded ----
PM CEST 0070529203002
weeklycollection Tue 29 May 2007 07:30:01 1 /var/lib/mysql-zrm/weeklycollection/2 Backup succeeded ----
PM CEST 0070529193001
weeklycollection Tue 29 May 2007 06:30:02 1 /var/lib/mysql-zrm/weeklycollection/2 Backup succeeded ----
PM CEST 0070529183002
weeklycollection Tue 29 May 2007 05:30:02 1 /var/lib/mysql-zrm/weeklycollection/2 Backup succeeded ----
PM CEST 0070529173002
weeklycollection Tue 29 May 2007 04:30:01 1 /var/lib/mysql-zrm/weeklycollection/2 Backup succeeded ----
PM CEST 0070529163001
weeklycollection Tue 29 May 2007 03:30:02 1 /var/lib/mysql-zrm/weeklycollection/2 Backup succeeded ----
PM CEST 0070529153002
weeklycollection Tue 29 May 2007 03:10:43 1 /var/lib/mysql-zrm/weeklycollection/2 Backup succeeded ----
PM CEST 0070529151043
weeklycollection Tue 29 May 2007 03:06:16 0 /var/lib/mysql-zrm/weeklycollection/2 Backup succeeded ----
PM CEST 0070529150616


We found we have a full backup from May 29th and then hourly incremental backups up to May 30th 2:30. Great! A point in time restoration back to May 30th 1:37 is possible.

3. Prepare the logical full backup:

export DB=members
export RESTORE_DATE=20070330023002
export FULL_BACKUP_DATE=20070529150616
export FULL_BACKUP_FOLDER=/var/lib/mysql-zrm/weeklycollection/$DATE
export RESTORE_FOLDER=${FULL_BACKUP_FOLDER}_restorethis
mkdir $RESTORE_FOLDER
cat ${FULL_BACKUP_FOLDER}/backup-data | gunzip | sed -r "s/\`$DB\`/\`${DB}_$DATE\`/g" | gzip > /var/lib/mysql-zrm/weeklycollection/$DATE_restorethis/backup-data
cat ${FULL_BACKUP_FOLDER}/index | sed -r "s/$DB\s/${DB}_$DATE /g" > /var/lib/mysql-zrm/weeklycollection/$DATE_restorethis/index

4. Convert the incremental binary log backups to logical format

export SPACE_SEPARATED_DATES_OF_INCREMENTAL_BACKUPS=20070 529153002 20070529151043
export INCREMENTALS_FOLDER=${RESTORE_FOLDER}/incrementals
mkdir $INCREMENTALS_FOLDER
mkdir
for INCDATE in $SPACE_SEPARATED_DATES_OF_INCREMENTAL_BACKUPS
do
mysql-zrm --action parse-binlogs --source-directory=/var/lib/mysql-zrm/weeklycollection/$INCDATE > $INCREMENTALS_FOLDER/$INCDATE.dump
done
# Perform sed-voodoo here to output queries only from dumpfiles


5. Remove the parts from the latest incremental backup that contains the erroneous UPDATE statement and later (should be at around 1:37)

6. Restore from the full backup:

mysql-zrm --action restore --backup-set weeklycollection \
--source-directory /var/lib/mysql-zrm/weeklycollection/20070529150616 \
--no-mysql-shutdown \
--databases "members_20070529150616"

7. Restore the converted incremental backups:
http://mysqlbackup.zmanda.com/index.php/Selective_Recovery#Restoring_One_or_More_Databases _from_a_Full_or_Incremental_Backup

8. Verify the restored database: http://mysqlbackup.zmanda.com/index.php/Verifying_Restoration