Linux Shell Script Backup MySQL Database

#!/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…