Daily migration script

I’m attempting to setup a sandbox version of a web site I’m developing so my users can experiment without their changes showing up on the live site.

I already have a nightly backup script (below) that I’m using for nightly backups and I’d to add code to it that will also duplicate the database into my sandbox database.

I’ve searched around but I can’t seem to find a solution that will work. I don’t believe my SQL users can fully drop databases, so I’m left with the option of completely emptying my sandbox database, and then using one of the migrate, or restore scripts found on the dreamhost wiki. I’m just not sure of the best way to empty my sandbox database.

Any help.

Here is my backup script if this helps




mkdir -p ~/archives
cd ~/archives
suffix=$(date +%y%m%d)
echo “SQL Backup of “$dbname” attached” >> tmp.txt
mysqldump --opt -u $dbuser -p$dbpass -h $dbhost $dbname > SQL_"$dbname"_$suffix.sql

tar zcf SQL_Backup_"$dbname"$suffix.tar.gz *.sql
mutt -s “SQL Backup of “$dbname”” -a SQL_Backup
"$dbname"_$suffix.tar.gz $email < tmp.txt
rm -rf {*.sql,tmp.txt}


I added these 2 lines of code

mysql -u $dbuser -p$dbpass -h $dbhost sandboxandy < …/backup/drop/drop.sql
mysql -u $dbuser -p$dbpass -h $dbhost sandboxandy < SQL_"$dbname"_$suffix.sql

The drop.sql file is simplly a file with the SQL drop commands for all of the tables in my database. Hope this helps someone else