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:
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.
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:
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:
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:
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.