Backup MySQL Database Using Shell Script

September 27, 2013 | By
| Reply More

Regular backup of mysql databases are also as important as backing up your code. So I wrote this script to backup all of my databases on local disk. I then added gzip to compress the sql file to save disk space.  You can always copy them over network so as to retain multiple copies of your database.

Please replace the "mysqlpassword"  mentioned in the script with your mysql root password. This script will create a file as dblist  in /usr/local that will list all the databases in MySQL Server.

The db's are backed up in /Backup/mysqlbackup directory with the directory name as the date of the day. Path of the dblist and backup destination can be modified by users.

#!/bin/bash

### Create Directory with Date where Database backup will be stored. ####

month=$(date | awk '{print $2}')
day=$(date | awk '{print $3}' )
year=$(date | awk '{print $6}')
foldername=$(echo $day$month$year"_backups")

### List all the databases in /usr/local/dblist file. ####

mysql -u root -p'mysqlpassword' -e 'show databases' >/usr/local/dblist
list=$(cat /usr/local/dblist)
echo $foldername

### Create Backup Directory in /Backup/mysqlbackup …  ####
mkdir -p /Backup/mysqlbackup/$foldername

for i in $list
do

echo $i
mysqldump -u root -p'mysqlpassword' $i | gzip > /Backup/mysqlbackup/$foldername/$i.sql.gz
echo " "$i".sql.gz file saved.."
done

You can put this shell script in crontab and run everyday. In this way you will have daily backups of all your databases.

Sample Output

./mysql.sh

17Sep2013_backups
database1
database1.sql.gz file saved...
hello_db
hello_db.sql.gz file saved...
site2
site2.sql.gz file saved...
test
test.sql.gz file saved...

Filed Under : SHELL SCRIPTS

Tagged With : ,

Free Linux Ebook to Download

Leave a Reply

Commenting Policy:
Promotion of your products ? Comment gets deleted.
All comments are subject to moderation.