In this article, we’ll discuss how you can connect to phpMyAdmin for your database and perform an export (backup) of the database as well as how to important (restore) from a database backup file.
First, we need to connect to phpMyAdmin for the database in question. In order to do this, you can view our separate guide on logging into phpMyAdmin via WCP if your domain is in our windows hosting environment. If your domain is in our Linux/cPanel hosting environment then you can access the phpMyAdmin interface via your domains cPanel (cPanel >> Home >> Databases >> phpMyAdmin).
Alternative access to phpMyAdmin (instead of logging in automatically via the control panel) can be done if you know the host, username, & password in order to connect to your database. If this is the case then you can also navigate directly to the URL below and log in manually:
Once logged into phpMyAdmin follow the steps below for each needed section:
Backup Database via phpMyAdmin
When logged into phpMyAdmin you should see on the left-hand side a list of databases that the MySQL user you’re logged into has access to. Select the desired database from the list of options for the database you’re wanting to perform an export on.
2. You should then see a list of tables under that database ( if any ). At the top of the page a menu full of options available for the database can be found. In this menu find and select the option for ‘Export’.
3. You should now see confirmation of the database that you are preparing to backup. Here there should be an option for ‘Export Method’ that contains the options ‘Quick’ and ‘Custom’. Select the appropriate option here.
Quick - The default value for the export method. This attempts to backup all data and tables that are currently in the selected database.
Custom - Overrides the default value for the export method. You can select individual tables that you want to export if you don’t want to export the entire database, as well as you can do functions such as changing the database name, table names, and column names for the selected database so that when you restore from this backup file it creates the database, tables, and/or columns with the new names instead of the original name.
4. The next section should show a list of available export formats you can create this backup file with. Normally you will keep this as the default value of ‘SQL’, especially if you need to restore this backup to another MySQL Database.
5. Review the backup options you selected to ensure they are set to your liking, then click ‘Go’ to start the export.
The backup should now process and you should see the backup is saved to your local computer. The download option may vary based on the browser you are using ( I.E. Chrome, Firefox, ME, etc. ).
Restore Database via phpMyAdmin
Prior to doing a restore of the database/tables we recommend confirming that the tables you are going to be restoring do not already exist… as the restore process might either fail or overwrite existing tables. In most cases this process will fail until you completely remove the tables to prevent an error about duplicate tables existing.
When logged into phpMyAdmin you should see on the left-hand side a list of databases that the MySQL user you’re logged into has access to. Select the desired database from the list of options for the database you’re wanting to perform an import on.
2. You should then see a list of tables under that database ( if any ). At the top of the page a menu full of options available for the database can be found. In this menu find and select the option for ‘Import’.
3. The page should now show that you’re attempting to import into your selected database. Click the button for ‘Choose File’ to select a file from your local computer.
Note: Normal Backup files for MySQL Databases are in the .sql format.
4. In most common cases you will leave the rest of the options as their default values. To start the restore process click ‘Go’.
5. The restore will kick off and phpMyAdmin will attempt to import the MySQL database from the backup file you provided. Depending on the size of the file this may take some time to complete.
Note: If you have a large database file you’re attempting to restore it may be best to attempt the restore with actual MySQL Management software ( Such as MySQL WorkBench ) rather than via phpMyAdmin… since phpMyAdmin is built on PHP and will use PHP limits such as ‘upload_max_filesize’ and ‘max_execution_time’.
These PHP settings can be configured and modified on the server that phpMyAdmin is hosted on, however. So you can theoretically increase these limits and continue to use phpMyAdmin instead.
If the restore is successful you should see a green bar showing that the import was successful, along with the number of queries it performed during the import.
What If the Restore Failed?
If the restore failed you should see a red prompt appear. Inside the prompt will be two sections:
SQL Query - The section in the error labeled ‘SQL Query’ is the code portion of the import script where the import failed at. A MySQL backup file is just a large query that re-creates each database table and the data within each table. So it acts the same as if you were using an SQL query to import a data value into a table.
MySQL Said - The section in the error labeled ‘MySQL Said’ will show the actual error that MySQL spits back to the end user. There can be many different reasons an import script could throw an error.
Let’s provide an example of an SQL Query error and the associated ‘MySQL Said’ below:
SQL Query:
CREATE TABLE 'wp_commentmeta' (
'meta_id' bigint(20) UNSIGNED NOT NULL,
'comment_id' bigint(20) UNSIGNED NOT NULL DEFAULT '0',
'meta_key' varchar(255) DEFAULT NULL,
'meta_value' longtext
) ENGINE=InnoDB DEFAULT CHARSET=utf8
MySQL Said: #1050 - Table 'wp_commentmeta' already exists
In this example: The ‘MySQL Said’ section tells us the exact information we needed. We now know that a table already exists called ‘wp_commentmeta’ on the database we’re trying to restore to. It cannot create what already exist. It’s recommended to remove all existing tables and data from the database prior to performing the restore.
This is just one example though of an error you may run into during a restore. Another common error would be a timeout during the restore. This timeout in most cases is controlled in the PHP settings on the server for phpMyAdmin, as of course, phpMyAdmin is just a PHP application. In these cases, we recommended that you either update the php.ini for the phpMyAdmin site to have a larger timeout set, or that you use an application such as MySQL WorkBench for restoring MySQL Databases. An application such as this should not run into these same timeouts that you would in phpMyAdmin.