Rather than use --all-databases, which will prevent you from being able to selectively restore any single database, consider the following:
Ideally, you should have a daily backup, with some history. It should be bulletproof (--force), it should be logged (>> ...log), it should be compressed (| gzip), it should keep separate copies of each database, and it should automatically pick up any databases that are added.
Consider, rather, a shell script like this:
#!/bin/bash Host=server.domain.com BDir=/home/backup/backup/mysql Dump="/usr/bin/mysqldump --skip-extended-insert --force" MySQL=/usr/bin/mysql Today=$(date "+%a") # Get a list of all databases Databases=$(echo "SHOW DATABASES" | $MySQL -h $Host) for db in $Databases; do date=`date` file="$BDir/$Host-$db-$Today.sql.gz" echo "Backing up '$db' from '$Host' on '$date' to: " echo " $file" $Dump -h $Host $db | gzip > $file doneWhich is assuming that you have a file
~/.my.cnf(chmod 600), that has:[client] user = "BACKUP" password = "SOMEPASS8342783492"Make sure that whatever user you are using for
BACKUPhas this grant statement:GRANT SELECT, SHOW VIEW ON *.* TO BACKUP@localhost IDENTIFIED BY 'SOMEPASS8342783492';So simply add this to a nightly cronjob, and you have a daily backup that rotates each 7 days week.
0 3 * * * backup-mysql >> backup-mysql.log 2>> backup-mysql.log
The backup directory then contains:
-rw-r--r-- 1 backup backup 2217482184 Sep 3 13:35 base.appcove.net-VOS4_0-20090903.sql.gz -rw-rw-r-- 1 backup backup 2505876287 Dec 25 00:48 base.appcove.net-VOS4_0-Fri.sql.gz -rw-r--r-- 1 backup backup 2500384029 Dec 21 00:48 base.appcove.net-VOS4_0-Mon.sql.gz -rw-r--r-- 1 backup backup 2506849331 Dec 26 00:48 base.appcove.net-VOS4_0-Sat.sql.gz -rw-r--r-- 1 backup backup 2499859469 Dec 20 00:48 base.appcove.net-VOS4_0-Sun.sql.gz -rw-rw-r-- 1 backup backup 2505046147 Dec 24 00:48 base.appcove.net-VOS4_0-Thu.sql.gz -rw-rw-r-- 1 backup backup 2502277743 Dec 22 00:48 base.appcove.net-VOS4_0-Tue.sql.gz -rw-r--r-- 1 backup backup 2504169910 Dec 23 00:48 base.appcove.net-VOS4_0-Wed.sql.gz -rw-r--r-- 1 backup backup 76983829 Dec 25 00:49 base.appcove.net-VOS4_Mail_0-Fri.sql.gz -rw-r--r-- 1 backup backup 76983829 Dec 21 00:49 base.appcove.net-VOS4_Mail_0-Mon.sql.gz -rw-r--r-- 1 backup backup 76983829 Dec 26 00:49 base.appcove.net-VOS4_Mail_0-Sat.sql.gz -rw-r--r-- 1 backup backup 76983829 Dec 20 00:48 base.appcove.net-VOS4_Mail_0-Sun.sql.gz -rw-rw-r-- 1 backup backup 76983829 Dec 24 00:49 base.appcove.net-VOS4_Mail_0-Thu.sql.gz -rw-rw-r-- 1 backup backup 76983829 Dec 22 00:49 base.appcove.net-VOS4_Mail_0-Tue.sql.gz -rw-r--r-- 1 backup backup 76983829 Dec 23 00:49 base.appcove.net-VOS4_Mail_0-Wed.sql.gz -rw-r--r-- 1 backup backup 304803726 Dec 25 00:49 base.appcove.net-WeSell_0-Fri.sql.gz -rw-r--r-- 1 backup backup 303480087 Dec 21 00:49 base.appcove.net-WeSell_0-Mon.sql.gz -rw-r--r-- 1 backup backup 304710121 Dec 26 00:49 base.appcove.net-WeSell_0-Sat.sql.gz -rw-r--r-- 1 backup backup 303791294 Dec 20 00:49 base.appcove.net-WeSell_0-Sun.sql.gz -rw-rw-r-- 1 backup backup 305315415 Dec 24 00:49 base.appcove.net-WeSell_0-Thu.sql.gz -rw-rw-r-- 1 backup backup 302516217 Dec 22 00:49 base.appcove.net-WeSell_0-Tue.sql.gz -rw-r--r-- 1 backup backup 303314217 Dec 23 00:49 base.appcove.net-WeSell_0-Wed.sql.gz -rw-r--r-- 1 backup backup 135301 Dec 25 00:30 dc40.appcove.net-mysql-Fri.sql.gz -rw-r--r-- 1 backup backup 135301 Dec 21 00:30 dc40.appcove.net-mysql-Mon.sql.gz -rw-r--r-- 1 backup backup 135301 Dec 26 00:30 dc40.appcove.net-mysql-Sat.sql.gz -rw-r--r-- 1 backup backup 135301 Dec 20 00:30 dc40.appcove.net-mysql-Sun.sql.gz -rw-rw-r-- 1 backup backup 135301 Dec 24 00:30 dc40.appcove.net-mysql-Thu.sql.gz -rw-rw-r-- 1 backup backup 135301 Dec 22 00:30 dc40.appcove.net-mysql-Tue.sql.gz -rw-r--r-- 1 backup backup 135301 Dec 23 00:30 dc40.appcove.net-mysql-Wed.sql.gz
The Gahooa Perspective 