(2013-01-25) Mysql Restore Scripts For Cpanel Backups

Okay, so I had to do some serious hacking of code a few weeks ago, when innoDB messed up big time.

We have a remote backup server which takes the CPBackup tar files just for databases (though the whole directory structure is still there), but this presents a fairly irritating problem of requiring to untar each archive, find the database and move on. This also causes a lot of disk space to be consumed for no 
reason (especially when dealing with lots of databases in one account - not all the databases will have innoDB tables).

So anyway, I had to rush to code something that worked and ended up with a pair of scripts that take a list of databases to restore and does all the heavy lifting for you.

You'll need to generate a list of databases you want to restore and stick it in the file listed as the variable “backupfile” in the scripts.

On the backup server you need to use “mysqlBackup” (I couldn't think of a better name).
#!/bin/bash basedir="/backups/thedate/" backupdir="mysqlbackup" backupfile="mysqlbackup.txt" remoteserver="someserver.domain.tld" remotedir="/root/" if [ -d $basedir/$backupdir ]; then echo "Backup directory already created, skipping." else mkdir $basedir/$backupdir fi while read line; do name=${line%%_*} db=${line#*_} dbfile=$name"_"$db echo $dbfile if [ -f "$basedir/$backupdir/$dbfile.sql" ]; then echo "Database copy already done, skipping." else cd "$basedir/$name" tar xvf cpmove-$name.tar.gz "cpmove-$name/mysql/$dbfile.sql" cd "cpmove-$name/mysql" cp "$dbfile.sql" "$basedir/$backupdir/" cd "$basedir/$name" rm -rf cpmove-$name/ cd "$basedir/" fi done < $backupfile cd "$basedir/$backupdir" tar -acf "$backupdir.tar.gz" * rsync -P "$backupdir.tar.gz" root@$remoteserver:$remotedir rsync -P "$backupfile" root@$remoteserver:$remotedir exit 0
That will extract just the needed databases from the archive (so it can loop round the same archive several times without causing any unneeded overhead), move them to a temporary location, tar all the databases up and send both the new tar file and the database list off to the server you are restoring on (a MySQL server or just a normal cPanel server). Then, on the actual server holding the live databases, you'll want to use “mysqlRestore”.
#!/bin/bash basedir="/root/" backupdir="mysqlbackup" backupfile="mysqlbackup.txt" errLog="mysqlRestore.err" if [ -d "$basedir/$backupdir" ]; then echo "Backup directory already created, skipping." else mkdir "$basedir/$backupdir" fi if [ -f "$basedir/$backupdir.tar" ]; then mv $backupdir.tar "$basedir/$backupdir/" cd "$basedir/$backupdir/" tar xvf $backupdir.tar rm $backupdir.tar cd "$basedir/" else echo "Backup tar does not exist, hoping for the best and skipping." fi while read line; do name=${line%%_*} db=${line#*_} dbfile=$name"_"$db echo $dbfile if [ -f "$basedir/$backupdir/$dbfile.sql" ]; then echo $dbfile mysql -e "create database $dbfile;" mysql $dbfile < "$basedir/$backupdir/$dbfile.sql" else echo "SQL Backup file does not exist, skipping." echo "$dbfile.sql" >> $errLog fi done < $backupfile exit 0
You need to be running as a user with a my.cnf defined so you can log in to your MySQL root without a password, otherwise it will be *very* tedious. All it does is extracts the archive into a new temporary folder then imports each database one by one. It also runs pretty quick when doing this and I've not seen any issues when restoring several hundred in one go (yet, anyway). These scripts are totally work in progress though, but thought it might help out with people who have serious database problems and just want to do a restore to the last backup - you could easily modify the scripts to run from just a separate partition as well if that's how your server(s) are configured. Also, I'm not responsible if you break something using these!