The following bash script is written to automate the process of backing up all your various MySQL databases running on either a local or remote MySQL server, using the useful mysqldump utility to do the actual backups.
What the script does is pretty simple to understand really.
First, you define all your server connections. Then it queries the server to find out which databases are currently running in the MySQL Server instance. Armed with this list, it runs through them all (ignoring the ones you specified on the ignore list) and pulls down a mysqldump of each database, gzipping it to its final backup file name.
Simple eh? So let’s see it then:
#!/bin/bash MyUSER="mysql_user_account" MyPASS="mysql_user_account_password" MyHOST="localhost" # Linux bin paths, change this if it can't be autodetected via which command MYSQL="$(which mysql)" MYSQLDUMP="$(which mysqldump)" CHOWN="$(which chown)" CHMOD="$(which chmod)" GZIP="$(which gzip)" # Backup Dest directory, change this if you have someother location DEST="/var/mysql_backups" # Main directory where backup will be stored MBD="$DEST/sql_dumps" # Get hostname HOST="$(hostname)" # Get data in dd-mm-yyyy format NOW="$(date +"%Y%m%d-%H%M%S")" # File to store current backup file FILE="" # Store list of databases DBS="" # DO NOT BACKUP these databases IGGY="test" [ ! -d $MBD ] && mkdir -p $MBD || : # Get all database list first DBS="$($MYSQL -u $MyUSER -h $MyHOST -p$MyPASS -Bse 'show databases')" echo "Launching backup script at $(date)" for db in $DBS do skipdb=-1 if [ "$IGGY" != "" ]; then for i in $IGGY do [ "$db" == "$i" ] && skipdb=1 || : done fi if [ "$skipdb" == "-1" ] ; then FILE="$MBD/$db.$MyHOST.$NOW.sql.gz" # do all inone job in pipe, # connect to mysql using mysqldump for select mysql database # and pipe it out to gz file in backup dir echo "Starting backup process for $db (espreports.com) [$(date)]" $MYSQLDUMP --opt --compress --single-transaction -u $MyUSER -h $MyHOST -p$MyPASS $db | $GZIP -9 > $FILE echo "-- Complete ($FILE) [$(date)] --" fi done echo "Backup script completed execution at $(date)"
And we’re done. Nifty. (And damn useful to boot!)
