How to Backup MySQL Database using Bash Script

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.


### 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

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

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


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

Deep 4:46 am


Your email address will not be published. Required fields are marked *

All comments are subject to moderation.

1 Comment