How to Backup MySQL Database using Bash Script

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

Please replace the 'mysqlpassword'  mentioned in the script with your mysql root password. This shell script will create a file named '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. The path of the 'dblist' and backup destination can be modified by users.

MySQL Backup Script

The following shows a simple MySQL backup script:


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


In this tutorial we learned how to write a simple shell script to take a backup of MySQL database. I hope you enjoyed reading this and please leave your suggestions in the comment section.

1 thought on “How to Backup MySQL Database using Bash Script”... add one

Leave a Comment