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
Here are basic Definitions of Shell Script and Cron Job.
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 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.
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
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.
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.
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.
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
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
$ crontab -e
after this command it will ask you which text editor you want to use we are chosing nano
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
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/