At the end of this message is a script that I run on MySQL servers. At some
point in my life I got tired of being asked to restore a database only then
to realize that I had to restore that single database from a 1 GB backup.sql
file that contained about thirteen or so database backups. No fun. The
script below will dump each database to its own file and take care of some
other issues, e.g., locking tables while the backup is performed.
(Be sure to test the restore before you kill the old server of course.)
As far as the mysql table, here is what you do:
Step 1: Load all your backups into mysql, including the 'mysql' database:
# mysql < db1.sql
# mysql < db2.sql
# mysql
> drop database mysql;
> exit
# mysql < mysql.sql
Step 2: Flush your privs:
# mysql
> flush privileges;
> exit
The nice thing about mysql is that you can dump and reload the 'mysql'
database without the server going bonkers.
...
#!/bin/sh
PATH=/bin:/usr/bin:/usr/local/bin
umask 077
databases=`mysql -B -e 'show databases' | awk 'NR > 1 {print}'`
preprend="`hostname -s`.mysql"
backupdir=/home/mysql_backup
[ ! -d $backupdir ] && mkdir -p $backupdir
cd /tmp
for db in $databases; do
dumpfile="$preprend.$db.sql"
echo "Dumping $db.."
mysqldump --lock-tables --add-drop-table -B $db > $dumpfile
tar cfz $backupdir/$dumpfile.tgz $dumpfile
rm -f $dumpfile
done
...
---
Puryear Information Technology, LLC
Baton Rouge, LA
225-343-3056
http://www.puryear-it.com
Author of "Best Practices for Managing Linux and UNIX Servers"
Download your free copy:
http://www.puryear-it.com/manage-linux.htm
