Export, import and move a MySQL database with phpMyAdmin

In short: phpMyAdmin in cPanel lets you save a MySQL database to a file (export) and load a file into a database (import), right in your browser, with nothing to install. Moving a database to another server is exactly these two jobs plus one more: pointing the site's configuration at the new database.

Finding phpMyAdmin

1. Log in to cPanel. You can also reach it from the client area: open My Services and pick your hosting plan.
2. Under Databases, click phpMyAdmin. It opens in a new tab.
3. The left column lists the account's databases. Each name starts with your cPanel username and an underscore, for example user_shop.

Before you move a database

• Freeze changes. Orders, comments or sign-ups made after the export will not reach the new server. For a shop or a busy site, turn on maintenance mode or choose a quiet hour.
• Keep the original. Do not delete the database on the old server until the site has run without problems on the new one for a few days.
• Moving the whole site? Files, e-mail and DNS have their own steps, covered in moving your website to TopHost. This article covers only the database.

Step 1. Export the database

With phpMyAdmin

1. Click the database name in the left column to see its tables.
2. Open the Export tab at the top.
3. Pick a method:
   • Quick dumps the whole database with standard options. This is fine most of the time.
   • Custom lets you tick individual tables, choose a character set or compress the output.
4. Keep SQL as the Format if the file will go back into MySQL later.
5. Click Export (called Go in some versions). Your browser downloads a .sql file.

Tip: for a large database, choose Custom and set compression to gzipped or zipped. The file shrinks several times over, which makes both the download and a later upload faster.

From the command line

If you have SSH access on the old server, this command asks for the password and writes a compressed copy straight away:

mysqldump -u old_user -p old_database | gzip > database.sql.gz

Whichever way you export, open the start of the file in a text editor to make sure it contains CREATE TABLE and INSERT lines and is not an error page.

Step 2. Prepare the target database

You import into a database that already exists in the account. If you do not have one yet, open MySQL Databases in cPanel, create the database and a user, then give that user ALL PRIVILEGES on it. The detailed steps are in creating a MySQL database in cPanel. Write down the database name, user name and password exactly as cPanel shows them, account prefix included.

The database should be empty. If you are importing over one that already has tables, export it first as a safety copy.

Step 3. Import the file

With phpMyAdmin

1. Select the target database on the left.
2. Open the Import tab.
3. Click Choose File (or Browse) and pick the file from your computer. Plain .sql files work, and so do .sql.gz and .sql.zip archives.
4. Leave the format on SQL and the other options as they are unless you have a reason to change them.
5. Scroll to the bottom and click Import. Wait for the green success message before closing the tab.

When the file is too big for the browser

phpMyAdmin displays the maximum upload size next to the file field. If your dump is larger, try these in order:

1. Compress the file and try again: a .sql.gz archive is several times smaller.
2. Import over SSH or from Terminal in cPanel (see connecting to the server via SSH). Upload the file to your account first, then run:

   gunzip < database.sql.gz | mysql -u cpuser_dbuser -p cpuser_dbname

3. Ask support. Upload the file with File Manager or FTP and open a ticket stating the file path and the database it belongs in.

Step 4. Update the site configuration

After a move, the site still holds the old access details in its configuration file. Replace the database name, user and password with the new ones and set the host to localhost. For WordPress this is wp-config.php:

define( 'DB_NAME', 'cpuser_dbname' );
define( 'DB_USER', 'cpuser_dbuser' );
define( 'DB_PASSWORD', 'the password you set' );
define( 'DB_HOST', 'localhost' );

Where Joomla, OpenCart, PrestaShop and other platforms keep these values is listed in MySQL connection details.

Step 5. Test before switching DNS

You can open the site on the new server before the domain points there by editing the hosts file on your computer, as described in accessing the site before the domain is delegated. Check the home page, a few inner pages, the admin login and any form. If the domain name itself changes with the move, WordPress also stores the old address in the database, and it has to be replaced there too.

Common errors and what they mean

Related articles

• Creating a MySQL database in cPanel
• Moving your website to TopHost
• Restoring a MySQL database with JetBackup 5
• Backups in cPanel: what runs automatically and how to keep your own copy