This article is about MySQL database export/backup and import/restore methods and procedures. Exporting and backing up a database are essentially the same but have different procedures. For example a backup might be an older version of a database. What is exported or backed up is a .sql file that has the data tables. A database is created as an empty container to hold the data tables. SQL stands for Structured Query Language.
Export / Backup Methods
There are differences in MySQL export files depending on the method used. First, there is the MySQL dump and, second, the phpMyAdmin SQL Dump. MySQL dump may drop the existing tables (if they exist) while phpMyAdmin SQL Dump does not when exported using the "Quick" method.
MySQL dump file content example
If you are not sure what kind of dump file you may have you can open a .sql file in any Text Editor and look at the top line. Below you see MySQL dump. A database contains tables of data. Notice the line "DROP TABLE IF EXISTS". This will delete the current table in the database first, followed by "CREATE TABLE" which will use the data table in the dump file. This is important to know when the database is not empty and may already contain a previous set of tables for a given application like WordPress, in this example. Also notice the name of the database pjovarec_wp2 that this file was created from. The name of the database in the .sql file might not be the same as the database you are importing the data into. But you can tell it came from the pjovarec cPanel account.
-- MySQL dump 10.13 Distrib 5.7.32, for Linux (x86_64)
--
-- Host: localhost Database: pjovarec_wp2
-- ------------------------------------------------------
-- Server version 5.7.32
...
--
-- Table structure for table `wp_commentmeta`
--
DROP TABLE IF EXISTS `wp_commentmeta`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `wp_commentmeta` (
...
Using cPanel > Files > Backup > Download MySQL Database Backup
This is the easiest way to backup a MySQL database. Go to cPanel > Files > Backup.
Then click on the the desired database to download it to your local computer.
The file that is exported this way may be in a compressed format like .tar.gz or .zip and uses the MySQL dump method described earlier in this article. The enclosed file is a .sql file. Although it may be possible to import/restore using the compressed version, it is better to unzip (or un-archive) the file before using it this way. Errors can occur when trying to restore/import the .tar.gz file instead of the enclosed .sql file.
Command Line mysqldump
Export a database using the command line in cPanel > Terminal. Replace the username, database_name and data-dump file name as needed. You may be able to use your cPanel username and password for any database. Or use the database username and password of a particular database. The file will be found in the directory where this command is run. So if you want to save it in the public_html folder for example, you could first cd (change directory) to that folder (also called www in cPanel as an alias link to public_html).
$ mysqldump -u username -p database_name > data-dump.sql
See this Digital Ocean article on how to use MySQL on the command line to export a database.
Export from phpMyAdmin
Open phpMyAdmin in cPanel > Databases. When exporting from phpMyAdmin you can select "Quick" or "Custom". When using "Quick" there is no dropping of the current tables added. However, advanced users can add Drop, for example, when selecting the "Custom" export.
Export method: Quick. Select Go to export.
When using the Custom method, there are more options that can be selected such as Dropping of existing tables. This is for advanced users and has many options to tailor the export file as needed.
Notice in the example phpMyAdmin SQL Dump file below there is no dropping of the existing table before creating a new one when using the "Quick" export. So when restoring from a phpMyAdmin SQL Dump file created with "Quick" export, the database needs to be empty or you will get import errors that a table already exists. Read more about how to handle that situation in the Import section below.
-- phpMyAdmin SQL Dump
-- version 4.9.7
-- https://www.phpmyadmin.net/
--
-- Host: localhost:3306
-- Generation Time: Jun 20, 2021 at 02:03 AM
-- Server version: 5.7.32
-- PHP Version: 7.3.28
...
--
-- Table structure for table `wp_commentmeta`
--
CREATE TABLE `wp_commentmeta` (
...
Download a database backup from JetBackup
If you are looking for an earlier version of a database, use JetBackup.
Select cPanel > Files > JetBackup > Database Backups.
Select GENERATE DOWNLOAD database from desired date. Once the download is generated, there will be a Download button to select.
The files generated here will be compressed like .tar.gz and use the MySQL dump method.
Import / Restore Methods
Does the database already exist? To import a database .sql file there needs to be a database existing or one created. The name of the database in the cPanel at Reclaim starts with the cPanel <username>_wp1 for example. This would be a Wordpress site number 1 and any additional installs may end in _wp2, _wp3, _wp4 which is how Installatron My Apps names them.
To see if the database exists, like when you are migrating a site from another server or troubleshooting, look in cPanel > phpMyAdmin or MySQL Databases. The wp-config.php file for Wordpress, or the db.ini file for Omeka are examples of where to view or edit the database name, database user and password for an application. You can also look in cPanel > My Apps > view/edit settings (the icon looks like a wrench) in the Advanced Tab.
Restoring a database replaces the current database with a backup version you select. And it's important to note that in some cPanel accounts you can't delete a database. You may be able to rename a database or empty it of data however. You may create a new database in cases where there are problems with an existing one.
The database needs to have a user associated with Privileges (usually all) and a password. The usual host name is "localhost" if on the same server. So when manually moving a Wordpress site to a different cPanel account, the wp-config.php file DB section will need to be updated with the new database name, user and password. It is common to use the same database username to match the name of the database. Or you can choose to use one database user and associate it with any of your databases for your various apps like WordPress and Omeka. Installatron My Apps will create a database user with the same name as the database itself.
It's important to keep the cPanel > Installatron My Apps settings up to date when making any changes to the name of the database or other settings. If you made changes and didn't update My Apps settings you may get unexpected results when the app is backed up or cloned.
See these articles for more details on creating databases, users and on My Apps settings and config files like wp-config.php (WordPress) or db.ini (Omeka).
- Create Databases and add Users
- My Apps settings and config files
- Wordpress wp-cli search/replace database for updating site url or other data
Import using phpMyAdmin
Find the desired database in phpMyAdmin. Open phpMyAdmin in cPanel > Databases. Click on the plus sign (+) by the database name to expand any data tables. If the database is empty, no tables will show. If you just created the database it should be empty. If it is an existing database it may already have data tables. If it is empty, you can go ahead and import an .sql file (created by MySQL dump or phpMyAdmin SQL Dump) using the Import command shown at the top of the phpMyAdmin interface.
What if there is data already and not a blank database?
In the example shown below, the database is not empty and has tables of data from an application like WordPress in this example.
At this point you could import a MySQL dump file since it will delete any existing tables. But you can't import a phpMyAdmin SQL dump file created using the "Quick" method since the tables already exist causing an error. So it's best in any case to go ahead and "Drop" all the tables in the database to avoid any errors.
To empty the database of data, click on the name of the database, then select all the tables in phpMyAdmin (you can select all the tables by choosing "Check All"), then select Drop from the menu as shown below. This won't delete the database itself but will remove any data tables. Then import the .sql file using Import shown at the top:
Import a database using mysql on the command line
This is an example of how to import a database using the command line in a terminal. Replace the username, database-name and .sql file as needed. You may be able to use your cPanel username and password for any database. Or use the database username and password of a particular database.
$ mysql -u username -p database-name < data-dump.sql
See this Digital Ocean article on how to use MySQL on the command line to import a database.
See this Wordpress support article for an example of using the command line to import a database.
Restore a MySQL Database in cPanel > Backup
Use this method to restore a database using a MySQL dump file. Although the backup file may be in a compressed format like .zip or tar.gz, the actual file contained is a .sql file.
Restore using JetBackup > Databases
To restore an older version of a database go to cPanel > JetBackup > Database Backups as shown below. Find the needed database by name and the select a date to restore from. Click the Restore button to restore the database.
References for:
Backing up app databases
Restoring a Wordpress database
Exporting/Importing MySQL
Official Developer Guides