Bash script to backup all your MySQL databases

/ / Leave a comment / Bash, MySQL, MySQLDump

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 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.
backup.sh
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
#!/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 --routines --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.

http://danieldvork.in/script-for-mysql-backup-to-dropbox/

Etiquetas: , , ,