Data that exist in one place barely exists at all. It’s a hardware failure or mistyped command away from disappearing completely. That’s a scary thought, considering business’ most valuable assets include data. Imagine recreating everything in your database from scratch!
Regular database backups are essential because that’s where your most important data are stored, whether it’s customer details, email addresses, product catalogs, content, or the hundreds of other types of information businesses need to function. MySQL® is by far the most widely used open-source database in the world, and that’s why cPanel & WHM makes it so easy to back up and restore MySQL.
In this article, we take a brief look at what a MySQL backup is and show you how to use cPanel’s MySQL backup and restore tools to keep your data safe.
What is MySQL?
MySQL is an open-source relational database management system (RDBMS) used to store, organize, and retrieve information efficiently. cPanel & WHM uses MySQL, and so do many of the most popular content management systems and ecommerce applications, including WordPress®, Joomla, Drupal, and Magento®.
MySQL stores data in tables, which are divided into columns of a predefined data type. Individual records make up the table’s rows, and they have to conform to the types defined by the columns. For example, here is a section of the posts table from a new WordPress site’s database.
How do users get data into and out of MySQL? Unlike a file system, we can’t just edit a file and hit save. We have to talk to the RDBMS in its own language, which is called SQL. To generate the table in the image, we sent the following SQL statement:
SELECT ID,post_date,post_title,post_type,comment_count FROM wp_posts;
As a cPanel user, you won’t have to write SQL because cPanel takes care of that behind the scenes. However, it’s useful to understand what it is because MySQL backups are just a list of SQL statements.
When you run a backup, the software generates the statements needed to recreate the database and saves them to a file. This is often called “exporting” or “dumping” the database. When you restore, MySQL runs those statements to return the database to the state it was in when the backup was made.
How to Back Up a MySQL Database in cPanel
Our goal is to dump a database and download the resulting SQL file to our computer, where we can keep it or move it to a more secure location. It is possible to do this from the command line with the “mysql” client, but the cPanel MySQL backup and restore offers an easy-to-use interface.
Either of these methods can be used to back up databases on your server. For this example we are going to use a WordPress installation and a WordPress Database.. A quick warning before we proceed: a WordPress site is composed of a database and files and a complete WordPress backup must include both.
- First, select Backup in the Files section of the main page. This page is a useful unified interface for backing up the files and databases associated with your cPanel account.
- Next, find the Download a MySQL Database Backup section, where you will see a list of the databases that you can download.
Finally, click on your chosen database to initiate a backup, and cPanel will generate an SQL file and save it to the default download location on your computer.
This is the most straightforward way to back up MySQL in cPanel, but you can also use the integrated phpMyAdmin administration tool to adjust export settings or back up several databases at once. Here’s how to do that:
Back Up a Database in cPanel with phpMyAdmin
Select phpMyAdmin in the Databases section of the cPanel main page.
On the left is a list of databases associated with your cPanel account. Click on the one you would like to back up and then on the Export tab at the top of the page.
In most circumstances, the Quick setting provides the optimal export configuration for MySQL backups, but the Custom setting is useful to exclude tables, rename the exported file, or carry out more advanced configuration. If you would like to export several databases simultaneously, open phpMyAdmin and, without selecting a database in the sidebar, click the Export tab. The default Quick export method will export all available databases. To customize which databases are exported, choose the Custom export method, and select the ones you would like to back up.
Schedule MySQL Backups with Cron in cPanel
We’ve seen how easy it is to back up MySQL databases in cPanel, but what if you want to automatically back up a database on a schedule? It’s all too easy to forget, and scheduling ensures that the safety of your data don’t depend on your memory.
To automate a MySQL backup, you can use the cron task scheduler and the command-line “mysqldump” tool. Navigate to the cPanel Cron Jobs page, which you’ll find under Advanced on the main page.
Use the drop-down boxes to select a time interval, such as once per week.
In the command field, enter the following, replacing the uppercase sections with the appropriate details for your database:
mysqldump -u USERNAME -p PASSWORD database > database_backup.sql
Click the Add New Cron Job button, and you’re done. Cron will run the backup at the indicated time interval, saving the SQL file to the location specified at the end of the command (the part after the > symbol).
To learn more about automating tasks with cron jobs, read How To Configure a Cron Job.
How to Restore a MySQL Database in cPanel
To restore a database, open the Backup interface, which you will find in the Files section of the cPanel main page.
Restoring an SQL file overwrites tables and deletes data. If, for example, you published a blog post to your CMS after you last backed up, it will be irreversibly deleted when you restore. Before restoring a backup, make sure it won’t remove data that you want to keep.
In the section titled Restore a MySQL Database Backup, click Choose File and select an SQL file from your local machine. Click Upload, and cPanel will import the file.
For more complex SQL imports or to replicate rather than replace a database, you can use the phpMyAdmin tool described in the previous section. However, the Backup page is preferable for standard restores because cPanel handles database prefixes and configuring SQL statements.
It’s hard to overstate the importance of regular MySQL backups. Without a recent backup, your business and its sites are a hardware glitch away from disaster. With cPanel & WHM, you and your users benefit from a simple two-click backup and restore process.
As always, if you have any feedback or comments, please let us know. We are here to help in the best ways we can. You’ll find us on Discord, the cPanel forums, and Reddit.
