Zmanda KB
   

Browse by Category    

How do I restore my MySQL Database to another server?

Product: Zmanda Recovery Manager For MySQL (ZRM)
Version: 3.0

Link to more info:

MySQL Disaster Recovery using ZRM For MySQL

Assumptions:

1. MySQL backup has been restored to disk from tape.
2. Destination MySQL Server has MySQL installed/configured/running.
3. Destination MySQL Server has ZRM for MySQL installed/configured.

Copy backup and configuration files for database to the destination MySQL server. For this example the name of our backupset is called dailyrun.

-bash-3.1# cd /etc/mysql-zrm/dailyrun/
-bash-3.1# ls -lh *.conf
-rwxr-x--- 1 root root 8.8K Nov 7 11:56 mysql-zrm.conf
-bash-3.1# scp mysql-zrm.conf root@fc5xfsvm:mysql-zrm.conf
root@fc5xfsvm's password:
mysql-zrm.conf

100% 8983 8.8KB/s 00:00
-bash-3.1# cd /var/lib/mysql-zrm/dailyrun/
-bash-3.1# ls -lh
total 4.0K
drwx------ 2 root root 4.0K Nov 7 12:00 20061107115700
-bash-3.1# tar -cvf lastdbbackup.tar 20061107115700/
20061107115700/
20061107115700/index
20061107115700/backup-data
-bash-3.1# scp lastdbbackup.tar root@fc5xfsvm:lastdbbackup.tar
root@fc5xfsvm's password:
lastdbbackup.tar

Login to source MySQL Server and copy ZRM backup and configuration files to appropriate locations.

-bash-3.1# ssh fc5xfsvm
root@fc5xfsvm's password:
Last login: Tue Nov 7 14:49:15 2006 from 192.168.184.1
[root@fc5xfsvm ~]# ls -lh

-rw-r--r-- 1 root root 105M Nov 7 14:51 lastdbbackup.tar
-rwxr-x--- 1 root root 8.8K Nov 7 14:49 mysql-zrm.conf

[root@fc5xfsvm ~]# mkdir /etc/mysql-zrm/dailyrun/
[root@fc5xfsvm ~]# mkdir /var/lib/mysql-zrm/dailyrun/

[root@fc5xfsvm ~]# cp mysql-zrm.conf /etc/mysql-zrm/dailyrun/
[root@fc5xfsvm ~]# cp lastdbbackup.tar /var/lib/mysql-zrm/dailyrun/

[root@fc5xfsvm ~]# cd /var/lib/mysql-zrm/dailyrun/

[root@fc5xfsvm dailyrun]# ls -lh *.tar
-rw-r--r-- 1 root root 105M Nov 7 14:51 lastdbbackup.tar
[root@fc5xfsvm dailyrun]# tar -xvf lastdbbackup.tar
20061107115700/
20061107115700/index
20061107115700/backup-data

Log into destination MySQL server and create backup-user with appropriate permissions.

mysql> grant create, insert, update, drop, lock tables, select, grant option, super, replication client, reload, shutdown on *.* to 'backup-user'@'localhost' identified by 'pass123';

Initialize Restore.

[root@fc5xfsvm mysql-zrm]# mysql-zrm --action restore --backup-set dailyrun --source-directory /var/lib/mysql-zrm/dailyrun/20061107115700/

Tue Nov 7 12:53:01 2006: INFO: Input Parameters Used {
Tue Nov 7 12:53:01 2006: INFO: verbose=1
Tue Nov 7 12:53:01 2006: INFO: retention-policy=1W
Tue Nov 7 12:53:01 2006: INFO: backup-level=0
Tue Nov 7 12:53:01 2006: INFO: databases=NetFlix
Tue Nov 7 12:53:01 2006: INFO: source-directory=/var/lib/mysql-zrm/dailyrun/20061107115700
Tue Nov 7 12:53:01 2006: INFO: html-reports=backup-status-info
Tue Nov 7 12:53:01 2006: INFO: backup-mode=logical
Tue Nov 7 12:53:01 2006: INFO: password=******
Tue Nov 7 12:53:01 2006: INFO: compress=
Tue Nov 7 12:53:01 2006: INFO: user=root
Tue Nov 7 12:53:01 2006: INFO: }
Tue Nov 7 12:53:01 2006: INFO: Getting mysql variables
Tue Nov 7 12:53:01 2006: INFO: mysqladmin --user="root" variables
Tue Nov 7 12:53:01 2006: INFO: datadir is /var/lib/mysql/
Tue Nov 7 12:53:01 2006: INFO: mysql_version is 5.0.22-log
Tue Nov 7 12:53:01 2006: INFO: log_bin=ON
Tue Nov 7 12:53:01 2006: INFO: Uncompressing backup
Tue Nov 7 12:53:01 2006: INFO: Command used is 'cat "/var/lib/mysql-zrm/dailyrun/20061107115700/backup-data" | gzip -d | tar --same-owner -xpsC "/var/lib/mysql-zrm/dailyrun/20061107115700" 2>/tmp/T9TsTJkVC0'Tue Nov 7 12:53:33 2006: INFO: restoring using command mysql --user="root" -e "set character_set_client=utf8;set character_set_connection=utf8;set character_set_database=utf8;set character_set_results=utf8;set character_set_server=utf8;source /var/lib/mysql-zrm/dailyrun/20061107115700/backup.sql;"
Tue Nov 7 13:15:54 2006: INFO: Restored database(s) from logical backup: NetFlix
Tue Nov 7 13:15:54 2006: INFO: Shutting down MySQL
Tue Nov 7 13:16:01 2006: INFO: Removing all of the uncompressed/unencrypted data
Tue Nov 7 13:16:01 2006: INFO: Restore done in 1380 seconds.
MySQL server has been shutdown. Please restart after verification.

Success! Once you've verified your database you can restart the MySQL server and your database will be ready for use!


How helpful was this article to you?

Related Articles

article Failed to restore database from raw backup

(No rating)  7-25-2007    Views: 16   
article How do I move Amanda server to another machine?
It is easy to move Amanda server from one...

(No rating)  10-23-2006    Views: 43   
article How to setup an LVM Snapshot Backup using ZRM for your MySQL Database
lvm.htmlInfo: As a best practice it isalways a...

(No rating)  11-15-2006    Views: 71   

User Comments

Add Comment
No comments have been posted.