PDA

View Full Version : Schedule a Restore?



db-backer
March 2nd, 2008, 04:39 AM
Hi,

I managed to survive 3 days on 8-hour shifts of reading the MySQL-ZRM documentation and 2 days on 8-hour shifts of implementing our simple backup system. :) Kudos to the Zmanda team!

Now my problem is with our restore system. It's actually pretty simple: schedule a restore from a backup taken daily at 1:00 AM. Is this possible? If I can schedule a customized backup by creating a backup-set, can I schedule a restore?


************************************************** ***

Setup
Please refer to this image (http://img266.imageshack.us/img266/5980/dbsetupyw7.png) to get a glimpse of our setup. We have 1 host that does the backup (backup host), 1 database server, and 1 Web server that gets 1 specific database from the backup host. All the backup runs and restorations are done from the backup host. The backup host has a MySQL database too.


Task
I want to be able to restore locally and to a remote host using the DATABASE that I just backed up from the database server. I've had no problems doing this manually because I only have to look at /var/lib/mysql-zrm/ for the valid backup directories that I can use and then issue a mysql-zrm-restore command to restore to a remote host like in the example below:

[root@backup-server]# mysql-zrm-restore --source-directory /var/lib/mysql-zrm/remote-backup/20080302010001/ --databases "lottamembers" --copy-plugin /usr/share/mysql-zrm/plugins/ssh-copy.pl --host 12.345.678.900 --ssh-user mysql --user backup-user --password 123pass
But I need to restore the database to the Web server, 12.345.678.900 daily at 1:30 AM or 30 minutes after the backup has been performed. Since the directories generated at /var/lib/mysql-zrm/remote-backup/ are dynamic, how would I be able to restore an updated copy every time the restore is run from cron?


Backup Configuration (Remote Backup via SSH)
backup-level = 0
backup-mode = logical
retention-policy = 1D (1 day)
databases = lottamembers lottausers lottavip lottafriends lotta enemies
user = "backup-user"
password = "123pass"
host = 12.345.678.901 (database server IP)
copy-plugin=/usr/share/mysql-zrm/plugins/ssh-copy.pl
ssh-user="mysql"

kkg
March 2nd, 2008, 11:03 PM
Hi,

I managed to survive 3 days on 8-hour shifts of reading the MySQL-ZRM documentation and 2 days on 8-hour shifts of implementing our simple backup system. :) Kudos to the Zmanda team!

Now my problem is with our restore system. It's actually pretty simple: schedule a restore from a backup taken daily at 1:00 AM. Is this possible? If I can schedule a customized backup by creating a backup-set, can I schedule a restore?


************************************************** ***

Setup
Please refer to this image (http://img266.imageshack.us/img266/5980/dbsetupyw7.png) to get a glimpse of our setup. We have 1 host that does the backup (backup host), 1 database server, and 1 Web server that gets 1 specific database from the backup host. All the backup runs and restorations are done from the backup host. The backup host has a MySQL database too.


Task
I want to be able to restore locally and to a remote host using the DATABASE that I just backed up from the database server. I've had no problems doing this manually because I only have to look at /var/lib/mysql-zrm/ for the valid backup directories that I can use and then issue a mysql-zrm-restore command to restore to a remote host like in the example below:

But I need to restore the database to the Web server, 12.345.678.900 daily at 1:30 AM or 30 minutes after the backup has been performed. Since the directories generated at /var/lib/mysql-zrm/remote-backup/ are dynamic, how would I be able to restore an updated copy every time the restore is run from cron?


Backup Configuration (Remote Backup via SSH)
backup-level = 0
backup-mode = logical
retention-policy = 1D (1 day)
databases = lottamembers lottausers lottavip lottafriends lotta enemies
user = "backup-user"
password = "123pass"
host = 12.345.678.901 (database server IP)
copy-plugin=/usr/share/mysql-zrm/plugins/ssh-copy.pl
ssh-user="mysql"

You can get the directory where the last backup was done in /etc/mysql-zrm/remote-backup/last_backup. So if you plan to do the restore about 30 mins after the backup and you do not run any more backups in between you could do something like the following in a bash shell.

mysql-zrm-restore --source-directory `cat /etc/mysql-zrm/remote-backup/last_backup` --databases "lottamembers" --copy-plugin /usr/share/mysql-zrm/plugins/ssh-copy.pl --host 12.345.678.900 --ssh-user mysql --user backup-user --password 123pass

In case you need to do the restore from a backup that was done earlier than the last backup, you can use the mysql-zrm-reporter utility to list the backups and then choose which backup to restore from. This will probably involve some scripting.

--kkg

db-backer
March 6th, 2008, 04:33 AM
Thanks for your timely response, kkg! I really appreciate it.

I have a question. Is that "last_backup" some kind of a parameter in mysql-zrm that recognizes the name of the directory that was created during the last backup run?

I plan to put this in a shell script just so that I can have another level of security (through obscurity) by not exposing the password in cron:


mysql-zrm-restore --source-directory `cat /etc/mysql-zrm/remote-backup/last_backup` --databases "lottamembers" --copy-plugin /usr/share/mysql-zrm/plugins/ssh-copy.pl --host 12.345.678.900 --ssh-user mysql --user backup-user --password 123pass


I have little experience in shell scripting, so what would you advice if I turn the command above into a shell script?

kkg
March 7th, 2008, 01:19 AM
Thanks for your timely response, kkg! I really appreciate it.

I have a question. Is that "last_backup" some kind of a parameter in mysql-zrm that recognizes the name of the directory that was created during the last backup run?

I plan to put this in a shell script just so that I can have another level of security (through obscurity) by not exposing the password in cron:


I have little experience in shell scripting, so what would you advice if I turn the command above into a shell script?

All of the options can be put in the /etc/mysql-zrm/remote-backup/mysql-zrm.conf. ZRM will pick all of the options from there.

Please note that last_backup is a file that sits inside the /etc/mysql-zrm/remote-backup directory and is updated everytime a backup is run so that it points ot the latest backup.
In my example all I am doing is sourcing the contents of last_backup to ZRM.
So what I would suggest is
to put the following into the /etc/mysql-zrm/remote-backup/mysql-zrm.conf
databases=lottamembers
copy-plugin=/usr/share/mysql-zrm/plugins/ssh-copy.pl
host=12.345.678.900
ssh-user=mysql
user=backup-user
password=123pass

and put the following in the crontab

mysql-zrm-restore --source-directory `cat /etc/mysql-zrm/remote-backup/last_backup`

--kkg

db-backer
March 8th, 2008, 12:11 PM
Once again, thanks for your response, kkg.

I'm beginning to understand what you're saying, but please correct me if I'm wrong. Based on the conditions of our backup system, that is, backup once via SSH then restore to a remote location via SSH daily at 1:30 AM, then I can indeed schedule a restore easily through "last_backup".

You suggested this command to be placed in cron:


mysql-zrm-restore --source-directory `cat /etc/mysql-zrm/remote-backup/last_backup'


I haven't tested your suggestion yet because I have way too many tasks to accomplish in one shift, but for the sake of archiving... Can I also add a "--databases lottamembers" parameter to the command you suggested? ....cause the backup-set, remote-backup backs up multiple databases... not just lottamembers.

kkg
March 9th, 2008, 09:28 PM
Once again, thanks for your response, kkg.

I'm beginning to understand what you're saying, but please correct me if I'm wrong. Based on the conditions of our backup system, that is, backup once via SSH then restore to a remote location via SSH daily at 1:30 AM, then I can indeed schedule a restore easily through "last_backup".

You suggested this command to be placed in cron:


I haven't tested your suggestion yet because I have way too many tasks to accomplish in one shift, but for the sake of archiving... Can I also add a "--databases lottamembers" parameter to the command you suggested? ....cause the backup-set, remote-backup backs up multiple databases... not just lottamembers.

Yes you can. BTW please note that you need to use ` character and not the ' character to quote.

mysql-zrm-restore --source-directory `cat /etc/mysql-zrm/remote-backup/last_backup`

db-backer
March 10th, 2008, 02:46 PM
Thank you so much for your help, kkg. I'll surely put your suggestions to good use. Thanks as well for noting that character. I could've used ' . :)