+ Download this script
+ Modify settings according to your setup
+ Install cron job as follows to run script every hour
1 2 | # 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 | #!/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. # ------------------------------------------------------------------------- # Last updated: Jul-16-2009 - Fixed a small bug # - Make sure NAS really mounted on $NAS # ------------------------------------------------------------------------- NOW=$(date +"%m-%d-%Y") # mm-dd-yyyy format FILE="" # used in a loop NASBASE="/nas" # NAS Mount Point BAK="${NAS}/mysql/${NOW}" # Path to backup dir on $NAS ### 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)" # Make sure nas is really mounted mount | awk '{ print $3}' |grep -w $NASBASE >/dev/null if [ $? -ne 0 ] then echo "Error: NAS not mounted at $NASBASE, please mount NAS server to local directory and try again." exit 99 fi ### NAS MUST BE MOUNTED in Advance ### # 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 |
Took the idea from the same script and made this one:
save all mysql databases into a compressed file like /root/mysqlbackup.23.12.11.tar.gz
#!/bin/bash ############# #Setting File Name ############# d=$(date | awk '{ print $3 }') m=$(date +"%m") y=$(date +%Y | cut -c3-4) #echo "myfile.$d.$m.$y" ############# #credentials ############# MUSER="root" MPASS="youpassword" TPATH="/tmp/mysqlbackup" BPATH="/root" ############ #Tool ############ MYSQL="$(which mysql)" MYSQLDUMP="$(which mysqldump)" TAR="$(which tar)" ############ #Create tmp dir ############ if [ ! -d $TPATH ]; then mkdir -p $TPATH echo "Directory Created" else echo "Directory Already Exists" fi ############ #backup ############ DBS="$(mysql -u root -p$MPASS -Bse 'show databases')" for db in $DBS do $MYSQLDUMP -u $MUSER -p$MPASS $db > $TPATH/$db.sql done ############ #Compress and remove tmp dir ############ echo "Compressing Databases" $TAR zcf $BPATH/mysqlbackups.$d.$m.$y.tar.gz $TPATH echo "Removing Temparory Directory" /bin/rm -rf $TPATHBetter remove this line FILE=”fs-full-$NOW.tar.gz” because you already used FILE=$BAK/mysql-$db.$NOW-$(date +”%T”).gz
Thanks for the heads-up.
Thank you Vivek it’s a great work!
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.
Elegant and very useful.
I love it, thanks.