Take daily backup of Mysql database using shellscript

Introduction :

To take backup of a Mysql Database we have to first write a Shell Script and then Set up

a Cron Job for that script so the script will automatically run at the specified time and will

take backup.

Here are basic Definitions of Shell Script and Cron Job.

Shell Scripting:

So basically Shell script is a file where we write commands a set of commands and

these commands will be executed in sequence means commands will be executed one after

another. Shell Script helps us to automate tasks if it is so repitative. Shell script works in

Unix and Linux operating systems. In shell script you basically wirte instructions in a text

file and run them as they are a combined program. Shell Script file will be of .sh extension.

Cron Job:

Cron job is a feature in Linux or a Unix like operating system where we can automate

the execution of tasks by scheduling them at a specific time or date. Cron is a time based task Scheduler which used to run commands or Scripts automatically. To automate a script or a

task we have to set up a Cron Job and specify in this job that when this perticular job will run

like on which day or week or month’s day, and also at what specific time of the day. Like its features we need Cron job to automate our daily database backup.

Most often database of a website would be in sql format and may use MySQL so here are the steps for taking daily backup of a website, according to MySQL database.

Step 1:

In this first step we have to connect to the database using command line and for

establishing connection with database we will need Username, Password and name of the

MySQL database.

So command will be something like this :

$ sudo mysql -u [User_name] -p  [Name_of_Database]

after running the above command you will be prompted to put password here.

remember you have to put username and password and name of the database which

 you want to connect and take backup of.

Step 2:

Next we have to create a backup file where all the backed up data will be stored.

In this command we again need to put the Username password and database name of the

database which we are taking backup of

$ sudo mysqldump -u [User_name] -p [Name_of_database] > backup.sql

this will again prompt to put password so put password here.

So basically above command will create a backup file with ‘backup.sql’ name.

Step 3:

Now our backup file is created so now you can save it to the location which you want.

We are creating a new folder to store this file here.

$ mkdir /home/ubuntu/backups

the folder will be created as named “backups”

and then move the backup.sql file to this folder.

$ mv backup.sql /home/ubuntu/backups /backup-$(date +%Y-%m-%d).sql

in above command we made backup file using a datestamp which will name our file as

current time and date in year-month-day format.

Step 4:

Now we have to create a Backup script so after this we can set a cron job

use a text editor you know and create a .sh file

$ nano /home/ubuntu/backup_script.sh

and add these bleow lines in the script file

in above file we mentioned that use specified database and create a backup file in the selected

folder  with the name backup-(current date).sql format so every day a new .sql file will be created

after cron job and to recognize which file is created at which day so we added a

($date stamp) so each file will have its created date in its name.

Now script is created but we have to add execution permission to the script

$ chmod  +x  /home/ubuntu/backup_script.sql

Step 5:

Now we are all set to set a Cron Job so let’s see how we can do it.

First we have to open the crontab file using a text editor so use any text editor you are

comfertable with

$  crontab  -e

after this command it will ask you which text editor you want to use we are chosing nano

for now.

This is how crontab file will look when you first time open it.

As we setting cron job for to happen backup daily so add below line in the file at the very end without # because hashtag is for comment.

0  0  *  *  *   /home/ubuntu/backup_script.sh

ctrl+x and y for yes

In the last commented line there is reference of the Job to understand the format.

in above line which we wrote in the file means the backup_script.sh file will be run daily

midnight everyday.

Step 6:

Now we should check our script manually if it works fine or not so run the following command to see the script work.

$ ./home/ubuntu/backup_script.sh
	      or
$ bash  /home/ubuntu/backup_script.sh

if the above command creates the backup file in the backups folder which created and there

You Go! Your daily backup using shell script is done.

Checkout our other blogs :

Creating a VPC in AWS: Simplified Setup for Secure Networking : https://plainsurf.com/creating-a-vpc-in-aws-simplified-setup-for-secure-networking/

Blocking Specific Countries with Nginx using GeoIP : https://plainsurf.com/blocking-specific-countries-with-nginx-using-geoip/