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.
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.
(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