For one of my servers, I needed a script that made daily dumps of all the databases that were running on it. In other words, I needed it to do a mysqldump of all the databases. To achieve this, I end up writing the bash script below. It takes an optional –cron flag to minimize the output and just echo any failed dumps (this can be useful if you have the output of your cronjobs mailed to you, but only want to receive an e-mail if something went wrong). Without this flag, the progress of the backups is displayed on the screen.
Here’s the source:
#!/bin/bash if [[ $1 == "--cron" ]]; then # Cron mode, minimize output to only show failures mysql -u user -p pass -B -e "SHOW DATABASES"|sed 1d|while read db do mysqldump -u user -p pass $db|gzip>"/home/db_backup/$db.sql.gz" if [[ $? != 0 ]];then echo "Backup of database $db FAILED!";fi done else # Default mode, be verbose mysql -u user -p pass -B -e "SHOW DATABASES"|sed 1d|while read db do echo -n "Backing up database $db: " mysqldump -u user -p pass $db|gzip>"/home/db_backup/$db.sql.gz" if [[ $? == 0 ]];then echo "OK.";else echo "FAILED!";fi done fi
It’s a pretty straight-forward script. The mysql client is called with the user/pass combination and then the query`SHOW DATABASES` is used to get a list of all the current databases on the server (to which our user has access). Next, a `sed 1d` is used to strip the first line of the output from that query, which is “Databases”. I’m not interested in this header line, as it’s not a database.
Next, I use a while loop to pass the databases to the mysqldump utility. The output is also redirected to gzip, because SQL dumps can get a very high compession ratio and thus save a massive amount of disk space. All the dumps are stored in the /home/db_backup folder.
Finally, the exit code of the mysqldump command is checked (which is stored in $?). If it’s 0, we’re all good and the dump was succesful. Anything other than 0 is bad, meaning something went wrong, so a “FAILED” message is printed whenever that happens.