Shell Script To Backup MySQL Database

In search of a Shell script to take a database backup every day through cron? then your search ends here. Read this tutorial, to save your time by taking a database backup every day manually.

Taking regular backup is as important as writing good code, and it is always recommended to take database backup every day or week depending on the size of the database and the site users count. As a developer sometimes we forget to take database backup, so the best thing is to automate this and let your server deal with this whole you can concentrate on other important things.
So first of all we have to create a Shell (.sh)script which will hold the logic to grab the data and store it in a compressed filed.

Shell Script:
# Step 1: set up all the variables
###Set the file path where you want to store the backup file and set the name of the file.
FILE=/path/to/your/backup_dir/my_db_file.sql.$(date +'%Y%m%d')
###Database Details:

# Step 2: If you are running this script more then one time then delete the previous copy of db file.
rm -f "$FILE" "$FILE.gz"
# Step 3: Take a MySQL backup.
mysqldump -opt -user=${USER} -password=${PASS} ${DATABASE} > ${FILE}
# Step 4: gzip/compress the MySQL database dump file.
gzip $FILE

Now that we have created the shell script now upload it in to your file server where all your website files reside. If you have the database host name other then localhost then you have to update the above script line like below.

mysqldump -opt -user=${USER} -password=${PASS} ${DATABASE} > ${FILE}

# replace above line with this

mysqldump -opt -user=${USER} -password=${PASS} -host=${DBSERVER} ${DATABASE} > ${FILE}

Now you have to add a cron to your crontab, which will run the above script on desired time.

So let say we want to take a database backup after every 6 hour, so you need to use below command to edit cron.

$ crontab -e

and over there write below code.

0 */6 * * * sh /path/to/your/backup_dir/

Every day in the backup_dir directory a new file will be generated namely my_db_file.sql.(date).

Raunak Gupta

Raunak Gupta

I'm Raunak Gupta, a seasoned software developer with over 9 years of experience in a wide range of programming languages, frameworks, and tools. I started my journey as a WordPress & CakePHP developer in 2014, diving deep into the world of OOPs, Request handling, and SEO. Along the way, I crafted numerous dazzling WooCommerce stores, tamed payment gateways, optimized for full filament functionality, and achieved ultra-low latency for lightning-fast load times. My expertise extends to BI tools, website builders, DevOps, and team leadership. I like to help upcoming developers, so I share my experience through this blog and by assisting fellow developers on Stack Overflow, where I've earned a stellar reputation with over 10k+ points of recognition.

Articles: 29

Leave a Reply

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