+ Download this script
+ Modify settings according to your setup
+ Install cron job as follows to run script every hour
# Backup database every 1 hr to /nas @hourly /root/scripts/db1hr.backup.sh >/dev/null 2>&1
Sample Shell Script To Dump All MySQL Databases
#!/bin/bash # A simple shell script to backup all MySQL Server Database # Dump all MySQL database every hour from raid10 db disk to /nas/mysql # Each dump will be line as follows: # Directory: /nas/mysql/mm-dd-yyyy # File: mysql-DBNAME.04-25-2008-14:23:40.gz # Full path: /nas/mysql/mm-dd-yyyy/mysql-DBNAME.04-25-2008-14:23:40.gz # ------------------------------------------------------------------------- # Copyright (c) 2005 nixCraft project <http://cyberciti.biz/fb/> # This script is licensed under GNU GPL version 2.0 or above # ------------------------------------------------------------------------- # This script is part of nixCraft shell script collection (NSSC) # Visit http://bash.cyberciti.biz/ for more information. # ------------------------------------------------------------------------- NOW=$(date +"%m-%d-%Y") # mm-dd-yyyy format FILE="fs-full-$NOW.tar.gz" BAK="/nas/mysql/${NOW}" ### Server Setup ### #* MySQL login user name *# MUSER="root" #* MySQL login PASSWORD name *# MPASS="YOUR-PASSWORD" #* MySQL login HOST name *# MHOST="127.0.0.1" #* MySQL binaries *# MYSQL="$(which mysql)" MYSQLDUMP="$(which mysqldump)" GZIP="$(which gzip)" # assuming that /nas is mounted via /etc/fstab if [ ! -d $BAK ]; then mkdir -p $BAK else : fi # get all database listing DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')" # start to dump database one by one for db in $DBS do FILE=$BAK/mysql-$db.$NOW-$(date +"%T").gz # gzip compression for each backup file $MYSQLDUMP -u $MUSER -h $MHOST -p$MPASS $db | $GZIP -9 > $FILE done
Want to read Linux tips and tricks, but don't have time to check our blog everyday? Subscribe to our email newsletter to make sure you don't miss a single tip/tricks.
- Download Script
- Email this to a friend
- Rss Feed
- Last Updated: 09/16/08
{ 4 comments… read them below or add one }
Elegant and very useful.
I love it, thanks.
This script is very much useful.
After taking backup I want to dump the same in another Database [In some aother machine] How to do that?
The database name and everything should remain same in the another DB also.
My intention is to create Mirror Database.
Thank you Vivek it’s a great work!
Better remove this line FILE=”fs-full-$NOW.tar.gz” because you already used FILE=$BAK/mysql-$db.$NOW-$(date +”%T”).gz