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.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
#!/bin/bashUSER="your_user"PASSWORD="your_password"OUTPUT="/Users/rabino/DBs"rm "$OUTPUT/*gz" > /dev/null 2>&1databases=`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 fidone |
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 -e00 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/