Pre-req: The backup dir (taken using the backup script earlier) should be accessible from the target server and untar the bzip2 backup file before starting with restore.

This script will restore all the objects of all the schema(including mysql schema) use filters for selective restore.

#!/bin/sh
#============================================================================
# File:         DV_MySQL_restore.sh
# Description:  restore individual table backup to each database.
# Usage:        sh DV_MySQL_restore.sh >DV_MySQL_restore.log 2>DV_MySQL_restore.err
#============================================================================

MYSQL=”/usr/local/mysql/bin/mysql”
BKP_DATE=$(date ‘+%u’)  ## current format of backup day/time
HOST=`hostname`
DNAME=${HOST}-${BKP_DATE}* ## for latest backupdir based on day.
MUSER=”xxxxxx”
MPASS=”xxxxxx”
SRC_BACKUP_DIR=”/mnt/backup/”$DNAME

##check if the backup dir exist or not..
if [ ! -d $SRC_BACKUP_DIR ]; then
echo Directory NOT found..
echo manually unzip the .tar.bz2 file to make sure we use the correct backupfile.
echo Plz check the target dir for backups.
exit
else
echo Directory found continue with backup
cd $SRC_BACKUP_DIR
fi
### directory found.Proceed with backup

cd $SRC_BACKUP_DIR
### Get all databases name from source host,Assuming we have access to mysql on source from target###
DATABASES=`dir |egrep -vw “information_schema|mysql”`
for db in $DATABASES
do
cd ${SRC_BACKUP_DIR}/${db} ### ON the assumtion that /mnt/backup is a SHARED MOUNT POINT i.e accessible from the TEST SERVER too
echo “starting restore of database — $db”
$MYSQL -u$MUSER -p$MPASS -e “CREATE DATABASE IF NOT EXISTS ${db}”
## database created proceed with each table restore.Get list of tables from source.
TABLES=`ls |grep -v $db”_routines_triggers.sql.gz”`
for table in $TABLES
do
cd $SRC_BACKUP_DIR/$db/
FILE=$table  ##use .gz for compressed files.
echo $table
gunzip < $FILE | $MYSQL -q -u$MUSER -p$MPASS -D ${db} #for .gz files. if compression is done.
###$MYSQL -q -u$MUSER -p$MPASS -D ${db} < $FILE  ## for each table with .sql file
done
## once table restore is done.proceed with routines_triggers restore in each database.
echo “starting restore of routines/triggers to database — $db”
cd ${SRC_BACKUP_DIR}/${db}/
ROUTINE=${db}_routines_triggers.sql.gz #for .gz files. if compression is done.
###$MYSQL -q -u$MUSER -p$MPASS -D ${db} < $ROUTINE  ## for routines/triggers in each db.
gunzip < $ROUTINE | $MYSQL -u$MUSER -p$MPASS -D ${db}
echo “completed restore of database — $db”
done
## restore of triggers/routines is done for each db.
## all database restore is completed.