BuycPanel Blog

BuycPanel Blog

Latest news and updates

12/24/2014 – How to Import Your MySQL Database in cPanel

Posted by Jamison on 24 12 2014.

Moving databases is one the things you must know how to do when hosting sites. Luckily you have cPanel to help you make this seemingly complicated task simple.

 

To import a MySQL database using cPanel simply:

 

  1. Login to cPanel.
  2. Go to phpMyAdmin.
  3. Click to create new database name in the top left, or choose an existing database.
  4. Click Import in the main area of phpMyAdmin.
  5. Browse for the .sql file on your computer and click Open.

 

Here you will be presented several options like defining the format of the file and the compatibility type to use. Everything is usually set to the default settings.

 

  1. Click Go to proceed.
  2. You should get a message saying the import has been successful.

 

Common issues with importing databases

 

Import file is too large

 

Importing files should never take too long. If after 30 seconds of attempting to import phpMyAdmin will time out, the problem is that your file is just too big. When this happens you usually have to get the help of your webhost service provider. Some providers will ask you to send them your database using your account.

 

Access denied for user ‘username1’@’localhost’ to database ‘user2_database’

 

This problem arises when your import file contains an SQL query that tries to create a database for the wrong username. Notice the usernames don’t match.

 

The import file must be edited to match your new username. You can do this yourself or again ask the help of your webhost service provider.

 

Access denied for user ‘username1’@’localhost’ to database ‘database name’

 

Just like the previous issue, the correct username is missing. On a shared sever, your database names must always look like something_something.

 

Unknown database ‘username1_database name’

 

The problem here is that your import file does not have a query to create the database before importing the data. To solve this:

 

  1. Log into cPanel.
  2. Go to MySQL Databases.
  3. Create a database with that name (database name).
  4. Try the import again.

 

Can’t create database ‘username1_wrdp9’; database exists

 

The problem happens when your import file contains an SQL query that attempts to create a database that already exists. If this happens:

 

  1. Login to cPanel.
  2. Go to MySQL Databases.
  3. Remove that empty database.
  4. Try the import again.

 

If the database is not empty, you must edit the import file and remove the CREATE DATABASE query. It should look like this:

 

â–¡CREATE DATABASE `username1_database name` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near…

 

This happens when your file does not contain backup data for a database, or if the file has been somehow changed or corrupted. Ask the help of your webhost service provider.

 

Access denied for user ‘username1’@’localhost’ to database ‘username1_database name’

 

If you get this error, this means you do not have privileges to use Create. What’s wrong here is that your import file contains at least one SQL query that is trying to create a database, and you do not have the privilege to do so.

 

Again you can always ask the help of your webhost service provider. Or edit the file yourself. The import file must be edited to remove the CREATE DATABASE query. It should something like this.

 

â–¡CREATE DATABASE `username1_dtabase name` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;

 

After you have removed the code:

 

  1. Login to cPanel.
  2. Go to MySQL Databases icon.
  3. Create your database.

 

The name of this database must be the same with the one that was removed from the import file (in this example, database name). You can now import the modified import file.