Backing up all your MySQL databases one by one is a pain. Here is a small bash script I made to dump and compress all my databases to my Dropbox folder.

  • The script will skip any database whose name starts with an underscore, so I can have test or junk databases that I don’t want to back up.
  • Every time it runs, it’ll delete all the previous backups (I don’t care, but if you do then just comment out line 7)
  • You need to change lines 3, 4 and 5 to reflect your MySQL user, password and folder where you want to put the dumps.
#!/bin/bash

USER="your_user"
PASSWORD="your_password"
OUTPUT="/Users/rabino/DBs"

rm "$OUTPUT/*gz" > /dev/null 2>&1

databases=`mysql --user=$USER --password=$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`

for db in $databases; do
    if [[ "$db" != "information_schema" ]] && [[ "$db" != _* ]] ; then
        echo "Dumping database: $db"
        mysqldump --force --opt --user=$USER --password=$PASSWORD --databases $db > $OUTPUT/`date +%Y%m%d`.$db.sql
        gzip $OUTPUT/`date +%Y%m%d`.$db.sql
    fi
done

Now you just need to make it executable:

chmod 700 backup.sh

And then add it to the crontab so it’ll run automagically:

crontab -e
00 20 * * * /path/to/backup.sh

In this case it’ll run every day at 8 PM. You can learn more about crontab here.