(Transferred from the wiki by Peter)
I modified crouse's mysql backup script with some modifications and have been using it for a few weeks. It saved my tush yesterday, so I thought I'd share it.
- It stores backup files in the directory /var/backups/mysql that it automatically creates. It also only backs up if MySQL is running by checking for the lock file.
- The nice thing about it is that you don't have to specify the names of the databases. It figures it out by itself.
- Stick it in cron, and make sure you have enough space in /var !
- The script is really simple, and won't backup databases with the string "Database" (with the capitalization) in the name
- You are free to modify it if you think you can get better functionality without making it too complicated. Here is the forum thread where I mentioned it first. You can post comments on it there.
You run it like this:
Here is the script
# mysql-backup <root-username> <root-password>The code looks like this:
# Create the backup directory if it doesn't already exist
if [ ! -d /var/backups/mysql ]; then
mkdir -p /var/backups/mysql
# Do backups
if [ -f "$LOCK_FILE" ]; then
# Do MySQL backups
for i in `/usr/bin/mysql -B -u $1 -e 'show databases' -p$2 | /bin/grep -v Database`; do
/usr/bin/mysqldump --opt -u $1 -p$2 $i | \
gzip > /var/backups/mysql/$i.`date --iso-8601`.sql.gz