MySQL Backup | Easy Guide To Backup MySQL Database With Automated Script

What is MySQL backup?

Every website comprises of two parts. One is the external “skin” of the site or the templates that show the contents of the site to the visitor.

The second is the “database” which is the collection of all the posts or contents of the site.

The templates pull out the content from the database and present it to the visitor to the website.

MySQL is the engine that holds the database in tables.

MariaDB is a modified version of MySQL with several improvements and modifications.

Why is MySQL backup necessary

As is evident, the “skin” or the template of the website can be easily changed and replaced.

However, the database which is a collection of the content and posts cannot be replaced.

If there is corruption or a fault in the server of the website, and the database is corrupted, all the content would be lost.

Years’ worth of content can disappear without a trace in a second if there is a slight defect in the hard disk of the server.

That is why it is very important that a periodical backup of the MySQL database is retained.

The backup of the MySQL should be stored in a remote place such as Amazon S3 storage, Google Drive, Google Cloud, Dropbox etc.

The amount charged by Amazon S3 and Google Cloud for storing large volumes of data is so cheap that one can have a backup of multiple copies of the MySQL Database in multiple locations without feeling the pinch.

Backup with PhpMyAdmin

PhpMyAdmin is an excellent tool for taking and restoring MySQL backups.

The best aspect of PhpMyAdmin is that it provides a graphical interface so that the webmaster can see what he is doing.

PhpMyAdmin MySQL Backup

As one can see from the image, PhpMyAdmin has an export button.

One has to select the MySQL database and click the export button.

There are self-explanatory choices offered of whether the database should be saved as a sql file or in other formats.

The MySQL database can then be stored on the computer and thereafter stored in a remote location such as Amazon S3 or Google Cloud.

To restore the MySQL database, one has to choose the database and click the import button.

Disadvantages/ limitations of using PhpMyAdmin

PhpMyAdmin is only suitable for small databases. If the MySQL database is large, say 100 MB or so, using this tool is not practical.

In that event, one has to use the command line or SSH as a means to backup the database.

MySQL Backup through command line or SSH

Doing a backup of the MySQL database through the command line or SSH (secure shell) (using putty) is also very simple.

This method is the only appropriate method if the size of the database is large and using FTP to backup or restore is not practical.

MySQL Backup through command line or SSH

(i) show databases from MySQL prompt

Log in as root to your MySQL server, using the following command:

 mysql -u root -ppassword

After successful login you will be in MySQL shell. Run the following command:

SHOW DATABASES

You will see all available databases

(ii) Backup MySQL Data with mysqldump

Mysqldump is a utility within MySQL which creates a “dump” of the database.

Export all databases

To backup all databases, run this command in SSH

mysqldump --all-databases -uuser -ppassword >all-database-backup-date.sql

If you want to backup only one database, use this command

mysqldump -uuser -ppassword database_name > dbname.sql

If you want to backup more than one database but not all, use this command

mysqldump -uuser -ppassword --databases database_one database_two > two_databases.sql

Restore / import into MySQL from sql backup file

To import all databases into MySQL from a backup file, use this command:

mysql -uuser -ppassword < all-database-backup-date.sql

To import only one database into MySQL use this command:

mysql -uuser -ppassword database_name < dbname.sql

mysql backup script

The entire process of backing up the MySQL database can be written into a shell script.

Create a file called vps-database-backup-script.sh

 nano vps-database-backup-script.sh 

Paste the following contents in the file after making appropriate changes


#!/bin/sh

THEDATE=`date +%d-%m-%Y-%H%M`

# export database

mysqldump --all-databases -uusername -ppassword | gzip > /path-to-backup-folder-in-VPS/DB-BACKUP-${THEDATE}.sql.gz

# remove backups older than 1 days

find /path-to-backup-folder-in-VPS/DB* -mtime +1 -exec rm {} \;

Make the script writeable

chmod 0700 vps-database-backup-script.sh

Run the script to create a backup:

 bash vps-database-backup-script.sh 

mysql automatic backup

One you are sure that the script is working as it should, you can create a cron job so that automatic backups of the mysql data is taken at periodic intervals.

See the existing crontab entries with the command

crontab -l

Add new entries to the crontab with the command

 crontab -e  

Decide when you want the cron job to run.

Crontab.guru provides an excellent representation of what the crontab entries mean.

A cron entry like

 0 2 * * *

means that that the specified event will happen at 2.00 am every day.

The cron entry

 0 2 * * 7

means that the event will happen at 2.00 am on Sunday.

So an entry to run the database backup script everyday at 2 am will read as follows:

 0 2 * * * /path-to-the-vps-database-backup-script.sh > /dev/null 2>&1

Edit the crontab with the entry

crontab -e  

Enter the new crontab command as set out above and save and close the file by pressing ‘Ctrl + x’ and then ‘Y’.

Export the MySQL database file to Amazon S3 and/or Google Cloud

The script will also export the database backup file to Amazon S3 or Google Cloud using a utility called S3CMD.

I have already provided detailed instructions on how to achieve this task in the tutorial “Backup VPS To Amazon S3 | Automatic Script | Files & Mysql | S3cmd | Cron Job

Backup VPS To Amazon S3 | Automatic Script | Files & Mysql | S3cmd | Cron Job

Leave a Reply

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