How to import and export a database with phpMyAdmin

This guide shows how to save a MySQL database to a file and load a file back in, using phpMyAdmin in cPanel. There’s also a section on databases too big for the browser.

Open phpMyAdmin

1. Log in to cPanel.
2. In the Databases section, click phpMyAdmin.
3. In the left column, click the database you want to work with. If you aren’t sure which one a WordPress site uses, look for DB_NAME in its wp-config.php file.

Export a database

Quick export

This is fine for most sites.

1. With the database selected, open the Export tab.
2. Leave Export method on Quick.
3. Leave Format on SQL.
4. Click Export (or Go) and save the .sql file that arrives.

Custom export

Choose Custom when you need more control.

1. On the Export tab, pick Custom.
2. Under Tables, untick any you don’t need. Large log or statistics tables can often be left out.
3. Under Output, set Compression to gzipped to make the file much smaller.
4. Under Object creation options, tick Add DROP TABLE if you’ll import this over an existing copy, so old tables are replaced cleanly.
5. Click Export.

Import a database

Importing writes into whichever database is selected, so create an empty one first if you’re moving a site. See how to create a MySQL database.

1. In phpMyAdmin, click the target database in the left column.
2. Click the Import tab.
3. Click Choose File (or Browse) and pick your .sql, .sql.gz or .zip file.
4. Leave the other settings as they are.
5. Click Import (or Go) at the bottom.

When it finishes you’ll see a green message saying how many queries ran. The tables now appear in the left column.

If the database already has tables with the same names and your file doesn’t drop them first, the import will fail. Export a copy of the current data, then select all tables on the Structure tab and choose Drop before importing.

File size limits

The Import tab shows a maximum upload size next to the file box. That limit comes from the PHP settings on the account. If your file is bigger:

• Compress it as .sql.gz first. Text compresses well, often to a fifth of the size.
• Export only the tables you need, and import them in batches.
• Use SSH, described below, which has no browser upload limit.

Large databases over SSH

For databases of a few hundred megabytes or more, the command line is faster and more reliable. You need SSH access; see how to connect to your hosting with SSH.

Upload the .sql file to your home directory with File Manager or FTP, then connect over SSH and run:

mysql -u cpuser_dbuser -p cpuser_dbname < backup.sql

Replace cpuser_dbuser with the database user and cpuser_dbname with the database name, both including your cPanel username prefix. You’ll be asked for the database user’s password. Nothing is shown while it runs; the prompt returns when it’s done.

To export over SSH instead:

mysqldump -u cpuser_dbuser -p cpuser_dbname > backup.sql

Delete the .sql file from the server once you’ve finished. Leaving it in public_html would let anyone download it.

Common errors

• #1046 No database selected. You clicked Import without choosing a database in the left column first.
• #1050 Table already exists. The tables are already there. Drop them, or re-export with Add DROP TABLE ticked.
• #1044 Access denied for user. The file contains a CREATE DATABASE or USE line naming a database that isn’t yours. Open the file in a text editor and remove those lines near the top.
• #1273 Unknown collation. The file came from a newer MySQL or MariaDB version. Open it in a text editor and replace the unknown collation, for example utf8mb4_0900_ai_ci, with utf8mb4_unicode_ci.
• The page times out or goes blank. The file is too large for the browser. Use the SSH method.

Related guides

• How to download a full backup of your account
• How to move a WordPress site to webhostgb
• How to fix “error establishing a database connection”

Still stuck? Open a support ticket and we’ll help.