Importing a database with phpMyAdmin, and the size limit that stops you

Create an empty database, open it in phpMyAdmin, go to the Import tab, choose the file (.sql, .zip or .gz) and click Import (or Go). If the file is bigger than the maximum size shown above the file field, the import will not start. That limit stops almost everybody at least once.

Step by step

1 If you do not have the database yet, create it with its user. See creating a user and giving it the right privileges.
2 In cPanel open phpMyAdmin and click the name of the empty database in the left column.
3 Click Import. Note the maximum size written near the file field.
4 Choose the file. phpMyAdmin reads compressed files (.gz and .zip) without you unpacking them first. Leave the character set on utf-8.
5 Click Import and wait for the green success message. Then check that the tables appear in the left column.

When it fails, and what to do

Symptom Cause Fix
The file is refused because of its size It is bigger than the phpMyAdmin limit. Compress it to .gz or .zip (SQL compresses very well) or split it. As a last resort, import over SSH.
The page stops or times out halfway The import took longer than the server lets a page run. Split the file into parts or use SSH. Do not just reload blindly: first look at how many tables made it in.
#1273 Unknown collation The copy comes from MySQL 8. Replace utf8mb4_0900_ai_ci with utf8mb4_unicode_ci in the file.
#1227 Access denied, mentioning DEFINER The file creates views or routines as the old user. Delete the DEFINER=... clauses from the file.
#1062 Duplicate entry The database was not empty. Empty it first. See emptying without deleting.
The limit belongs to the panel’s phpMyAdmin, and as a rule it does not move when you change the PHP version or the limits of your site. Do not waste time editing .user.ini to raise it. If the file is large, change the method instead of trying to change the limit.

Before you press Import

Three checks save a lot of trouble. First, confirm you are inside the right database: the import goes into the database that is open, and from the start page phpMyAdmin tries to create whatever databases the file mentions. Second, open the file and look for CREATE DATABASE and USE lines: the database name in your cPanel carries the account prefix, so delete those lines. Third, if you are importing into a database that already holds data, export what is there first, so you can go back.

The safe route for big files is SSH: mysql -u user -p database_name < copy.sql. See importing a large database over SSH.

Import never finishes, or the error is not one of these? Tell us the exact error and the file size and we will look.

Open a support ticket

SEE ALSO

Importing a large database over SSH

Exporting a database with phpMyAdmin

Character sets: utf8mb4, collation, and the question marks in your text

RECOMMENDED PRODUCT

Web hosting with cPanel

Domain and SSL included, daily backups and the panel you already know. from $6.60/mo (3-year plan, with coupon)

See plans
  • 0 Users Found This Useful
Was this answer helpful?

Related Articles

Creating databases and using phpMyAdmin Almost every site that isn't just static pages needs a database. In cPanel you create one in about a minute - and there...
What time the server is on, and why PHP and MySQL can disagree A form that records the wrong time, an order dated yesterday, a scheduled job firing two hours early - it is nearly alwa...
Backing up the database of a Drupal site Drupal files are the skeleton; the database is everything else. Pages, users, menus, permissions, site configuration and...
Connecting to your MySQL database with MySQL Workbench MySQL Workbench is a program you install on your own computer to work on a database the way you would work in a spreadsh...
When the site says it cannot connect to the database It is one of the more frightening messages a site can show, because there is no page at all behind it: just a line of wh...
Moving a database to another account or server Export the database from the source, create an empty database and user at the destination, import the file and point the...