MySQL backup database automatically

There are several ways to create a backup. But if you want to have full control then I will recommend using the following procedure to create the backup and configure the cronjob.

If you have not already installed MySQL you must first install it otherwise skip to section 2

MySQL Installation on Linux Server (Ubuntu)

You can locate the complete LAMP Server installation here.

However to only install MySQL, just follow the below procedure.

You need to first install the database Server and Client both will be installed using the below command.

sudo apt install mariadb-server mariadb-client -y

Now let us follow the same step as mentioned in apache2. Enable and Start the service and then check the version and status of the service

sudo systemctl start mariadb && sudo systemctl enable mariadb

Now let us check the status of MariaDB

sudo systemctl status mariadb

Before we proceed further let us now secure the installation, it is optional but recommended for the production environment. Run the below commands for securing the installation.

sudo mysql_secure_installation

You will need to enter when the password is promoted as there is no password. THen you have to set the root password. disallow remote and anonymous access and remove the test database.

MySQL Create Database

Open MySQL using the root user

mysql -u root -p

Let us now create a user syncbricksuser, password syncbricks and dataabse syncbricksdb

CREATE DATABASE syncbricksdb;
CREATE USER syncbricksuser@localhost IDENTIFIED BY 'syncbricks';
GRANT ALL PRIVILEGES ON syncbricksdb.* TO 'syncbricksuser'@localhost;
FLUSH PRIVILEGES;
EXIT;

Backup and restore MySQL DB

it is important to back up the database and in case of a database disaster, you will need to restore.

the command includes greater than > and Less than sign < which can be used for backup and restore.

> used for Backup

< used for restore.

Access to the database is behind the sign and the file name is after the sign.

Don’t be confused, just follow the below procedures to backup or restore the database.

MySQL Database Backup

To back up the database using the command.

mysqldump -u [user name] –p [password] [options] [database_name] [tablename] > [dumpfilename.sql]

In case you want to the backup the entire database in my case, I will backup the database syncbricksdb that I created I will use the following command

mysqldump -uroot -psyncbricks syncbricksdb > /var/backups/manualbackup.sql

In above command user U is root password P is syncbricks database name is syncbricksdb so command must be clear to you now. If this doesn’t display any error it means the backup is completed. You can verify the backup by going to the specified directory. Now as you learn how to create the backup. Let us now schedule the backup to run automatically.

MySQL Database Backup – Auto-Schedule

We will schedule the mySQL Database backup automatically and will store in a folder with (Date+Hour) and inside the folder the file will be stored with file name that you want to have.

Let us follow the below steps to create a backup.

Create a batch file called backup.sh, it can be anywhere but I will create that in the root folder.

We will first create a file by typing the following command in CLI

nano backup.sh

This will open a new file we will create the new file as follows. You can either copy the entire script or you can type each line one by one to know the procedure;

There must be already a folder in /var/backup so we will use that folder to create our backups. But it is always a good idea to store the backup on a network drive or external storage. We can do that later.

Now edit the backup.sh file as below

#!/bin/bash
#Create Direcotory with current date and hour using below command
#create a backup directory that will remain always there and inside the directory you will create the date folders
cd /var/backups
mkdir "$(date +"%d-%m-%Y-%H")"
#now below command will copy the databackup to folder
mysqldump -uroot -psyncbricks syncbricksdb > /var/backups"$(date +"%d-%m-%Y-%H")"/backup.sql

For all dates, related scripts, and commands click here

Now the script is ready let us configure the schedule using crontab

Scheduling Backup using Cronjob

let us now configure the schedule. Now edit the Cronjob by running below command

crontab -e

read the script and locate the line that says like this

m h dom mon dow command

The above line means the following

• m – Minute – 0 through 59
• h – Hour – 0 through 23
• dom – Day of Month – 0 through 31
• mon – Month – 0 through 12
• dow – Day of Week – 0 through 7 (0 and 7 are both Sunday)

Depending upon when you want to schedule the backup you will need to run this command. So I will be using the 10th minute of every hour to start the backup I will be using the below command, depending upon the backup strategy you can use the command accordingly.

10 * * * * exec '/bin/bash /home/backup.sh'

Save the crontab file.

Done the script will automatically now run every 10 minutes of the hour.

Why create MySQL Database backup?

Though this is a silly question many people ask, and it is also a valid question if you are already taking VM backup where the database is stored so why you need to create the back. The answer is that VM can have various other services and databases. You might need to restore the specific database due to any reason, it could be because of any corrupt table in a database or entire database. Or worst case scenario failure of the database, server or VM, or location itself. So doing backup and storing it at the appropriate place is important.

How to Restore MySQL Database

In case of any disaster, when there is no possibility than restoring the data, you must follow the policy within your organization to when and how the database has to be restored. ensure that you have the latest backup file available with you and ensure which database you want to restore.

Suppose in this example where we have been using syncbricksdb, we will now restore the same database.

Our files are located in /var/backup/*folders*

we will choose one of the folders to locate our files and then restore the database. If you see the section manual command in this blog simply replace the > sign with < sing it will restore the datbase;

For example

Backup

mysqldump -uroot -psyncbricks syncbricksdb > /var/backups/manualbackup.sql

Restore

mysqldump -uroot -psyncbricks syncbricksdb < /var/backups/manualbackup.sql

Be careful while using the command, you might be thinking you are backing up but you might be restoring 😉 be careful.

If you want to install a complete LAMP Server you can click here.

If you have any comments regarding this blog please let me know in the comments below.