Take MySql database backup daily automatically in Ubuntu Server using cron

When you website or product have lots of important data then you might be always worry about database backup. But taking the database backup daily is a time-taking job. Here we are providing solution for automatic MySql database backup on Monthly / Hourly / Weekly / Daily or Hourly basis.

To take backup of mysql database automatically you need to do following steps:

Step 1:

Connect and login to your SSH Terminal of your Ubuntu server.

Step 2:

Install mysqldump and zip to take backup.

sudo apt install mysql-client-5.7

sudo apt-get install zip

Step 3:

Create a backup directory in your server root.

sudo mkdir /backup

Change permission of backup directory.

sudo chmod -Rf /backup

Step 4:

Write backup code in a shell script file.

touch /backup/script.sh

Open file for edit:

nano /backup/script.sh

Add following lines and save.

#!/bin/bash
now=$(date +%d%m%Y-%H:%M:%S)
filename=$1
backupfilename=$1-$now
mysqldump -u [Database Username] -p[Database Password]  -h [Database Host] [Database Name] > /backup/backup$backupfilename.sql
zip -r /backup/backup$backupfilename.zip /backup/backup$backupfilename.sql
rm /backup/backup$backupfilename.sql

Now your shell script is ready to take backup of your database. To test this script run following.

./backup/script.sh

Step 5:

Now add a scheduler to automatically execute this script. To do this use cron and use the following to add the script in cron:

crontab -e

Add the line below to execute the script at daily midnight.

0 0 * * * /backup/script.sh

Now save and exit.

Now you will get back up on daily basis.

Send MySql Database Backup on your mail

If you want your backup receive on your mail then follow the following steps to configure email.

Step 1:

Install mail client mutt.

sudo apt install mutt

Step 2:

Allow sending email without size limit.

sudo postconf -e mailbox_size_limit=0
sudo postconf -e message_size_limit=0

Step 3:

Send the email with backup attachment to an email id.

Add following in script.sh file:

echo "Hi, Your database backup for date $backupfilename is ready" | mutt -a /backup/backup$backupfilename.zip -s "Database Backup - $backupfilename" -- sheetal@devstudioonline.com

#!/bin/bash
now=$(date +%d%m%Y-%H:%M:%S)
filename=$1
backupfilename=$1-$now
mysqldump -u [Database Username] -p[Database Password]  -h [Database Host] [Database Name] > /backup/backup$backupfilename.sql
zip -r /backup/backup$backupfilename.zip /backup/backup$backupfilename.sql
rm /backup/backup$backupfilename.sql
echo "Hi, Your database backup for date $backupfilename is ready" | mutt -a /backup/backup$backupfilename.zip -s "Database Backup - $backupfilename" -- sheetal@devstudioonline.com

Now you will get a daily email. Enjoy backup.