MySQL Backup Bash Shell Script
Which MySQL backup scripts do I use ? The question has come up a few times already so decided to post what MySQL backup script I am using right now. I’ve used quite a few different ones all using some form of mysqldump. Update: script has evolved into mysqlmybackup.sh details here.
The current MySQL backup shell script I use was written by Vivek Gite. I altered the script to also add hour, minute and seconds in the time stamp for the mysqldump sql backup file. Settings to customise the backup script to your needs are pretty self explanatory.
The mysqldump sql backup files will be located at /backup/mysql by default. Change the location to suite your own needs. Also change CHOWN and CHMOD values if you want the backup sql files to be accessible by a user other than root. You’ll also be able to set which MySQL database names you can ignore in the backup process.
Save below script as mysqlbackup.sh and CHMOD +x the file and use a cronjob to schedule when to run mysqlbackup.sh. I have a scheduled cronjob to automatically backup my MySQL databases (lightly used ones) every 12 hours.
#!/bin/bash # Shell script to backup MySql database # To backup Nysql databases file to /backup dir and later pick up by your # script. You can skip few databases from backup too. # For more info please see (Installation info): # http://www.cyberciti.biz/nixcraft/vivek/blogger/2005/01/mysql-backup-script.html # Last updated: Aug - 2005 # -------------------------------------------------------------------- # This is a free shell script under GNU GPL version 2.0 or above # Copyright (C) 2004, 2005 nixCraft project # Feedback/comment/suggestions : http://cyberciti.biz/fb/ # ------------------------------------------------------------------------- # This script is part of nixCraft shell script collection (NSSC) # Visit http://bash.cyberciti.biz/ for more information. # ------------------------------------------------------------------------- MyUSER="SET-MYSQL-USER-NAME" # USERNAME MyPASS="SET-PASSWORD" # PASSWORD MyHOST="localhost" # Hostname # Linux bin paths, change this if it can not 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="/backup" # Main directory where backup will be stored MBD="$DEST/mysql" # Get hostname HOST="$(hostname)" # Get data in dd-mm-yyyy format NOW="$(date +"%d-%m-%Y-%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 || : # Only root can access it! $CHOWN 0.0 -R $DEST $CHMOD 0600 $DEST # Get all database list first DBS="$($MYSQL -u $MyUSER -h $MyHOST -p$MyPASS -Bse 'show databases')" 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.$HOST.$NOW.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 :) $MYSQLDUMP -u $MyUSER -h $MyHOST -p$MyPASS $db | $GZIP -9 > $FILE fi done |
