+ 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. # ------------------------------------------------------------------------- # 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
Get the latest tutorials on SysAdmin, Linux/Unix, Open Source, and DevOps topics:
- RSS feed or Weekly email newsletter
- Share on Twitter • Facebook • 6 comments... add one ↓
Related Shell Scripts:
| Category | List of Unix and Linux commands |
|---|---|
| File Management | cat |
| Firewall | Alpine Awall • CentOS 8 • OpenSUSE • RHEL 8 • Ubuntu 16.04 • Ubuntu 18.04 • Ubuntu 20.04 |
| Network Utilities | dig • host • ip • nmap |
| OpenVPN | CentOS 7 • CentOS 8 • Debian 10 • Debian 8/9 • Ubuntu 18.04 • Ubuntu 20.04 |
| Package Manager | apk • apt |
| Processes Management | bg • chroot • cron • disown • fg • jobs • killall • kill • pidof • pstree • pwdx • time |
| Searching | grep • whereis • which |
| User Information | groups • id • lastcomm • last • lid/libuser-lid • logname • members • users • whoami • who • w |
| WireGuard VPN | Alpine • CentOS 8 • Debian 10 • Firewall • Ubuntu 20.04 |
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.