There are different ways to import MySQL database’s contents. In this article we will discuss the 2 most common methods:
1) Importing Databases by using phpMyAdmin
2) Importing Databases by using Command Line (SSH)
We will cover frequent issues and recommended solutions, too.
1) Importing Databases by using phpMyAdmin
It’s necessary to do the following steps:
The steps below will walk you through the steps in order to assist with following along:
1. Go to phpMyAdmin;
2. Select the new database name in the top left;
3. Then click Import in the basic area of phpMyAdmin;
4. Finally, browse for the .sql file on your computer;
5. And click Go. That’s it. If you receive an error message, please look at the list below in this article.
2) Importing Databases by using Command Line (SSH)
If you are familiar with SSH and feel as an advanced user, you will prefer to make changes of your account by command line because you will have more options with few limitations.
So, log into your account by SSH and follow the steps:
Note: Don’t forget that the database file must be in .sql format, it mustn’t be compressed in a .zip or .tar.gz.
1. Via cPanel File Manager, an FTP client or SSH upload the database to your home directory.
2. If you haven’t any database, please create a database and give full privileges and access of the user. Remember your username and password.
3. Then log into the server by SSH.
4. Check via the ls -l command if the file is in your current directory.
5. In case the file is not present, upload or move the file to the desired directory.
6. Write the following command and replace 'user_name', 'database_name, and 'file.sql' with the correct values for your database and file:
mysql -p -u user_name database_name < file.sql
7. Finally, after typing of your password, the database will be imported.
Note: It’s recommended your database name and username to have username prefix (for example: 'username_databasename'; 'username_databaseusername').I. Frequent issues and recommended solutions
1) Upload Limit
The phpMyAdmin upload limit is 50 MB. It also refers to VPS and Dedicated servers.
The only way to import larger file than 50 MB is to connect with us via a phone or a chat form.
Note: Users on a VPS or Dedicated server can import larger file by increasing the cPanel PHP max upload size and the cPanel PHP max POST size from WHM, under Tweak Settings.
2) Script Timeout Passed
If import of the files takes too long time, this error will be displayed and the phpMyAdmin import process will be terminated. In this case, it’s recommended to contact us.
If you upload your database to the /home/USERNAME section of your account, they will be better preserved and secured. Please, replace USERNAME with your cPanel username.
3) #1044 - Access denied for user 'username1'@'localhost' to database 'user2_wrdp9'
It notes that your import file contains an SQL query which attempts to create a database for the wrong username. Take in mind that the user2 in 'user2_wrdp9' is not the same as the username1 in 'username1'@'localhost'. In this case somebody should edit the import file and change the old user2 to your new username1. If it’s difficult for you and need help, don’t hesitate to contact us. In the following example you will see how it looks like (notice 3 places where the username is outdated):
□□--□-- Database: `user2_wrdp9`□--□CREATE DATABASE `user2_wrdp9` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;□USE `user2_wrdp9`;□□--
4) #1044 - Access denied for user 'username1'@'localhost' to database 'wrdp9'
Like the previous issue, its correct username is missing. On a shared sever it’s necessary database names to be as “something_something”.
5) #1049 - Unknown database 'username1_wrdp9'
This error is displayed when your import file does not have a query to create the database before importing the data. So you should go to cPanel -> MySQL Databases and create a database with that name (in the example of this article, "wrdp9"). Finally, re-attempt your import and everything will be OK.
6) #1007 - Can't create database 'username1_wrdp9'; database exists
This error means that your import file contains an SQL query which attempts to create a database that already exists. If the database is empty, just go to cPanel > MySQL Databases and remove it and then re-attempt the import. If the database is not empty, it’s necessary to edit the import file and remove the CREATE DATABASE query. In this case, if you need help, please contact us. In the following example you will see how it looks like:
□CREATE DATABASE `username1_wrdp9` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
7) #1064 - 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 issue will be displayed if the file does not contain backup data for a database. Another reason could be that the file has been changed or corrupted.
II. Less Frequent Issue:
ERROR 1044 (42000): Access denied for user 'username1'@'localhost' to database 'username1_wrdp9'
It shows that you do not have privileges on user_* to use Create. It’s recommended to contact us.
If you want to try to resolve it alone, here is the way:
Your import file contains at least one SQL query which attempts to create a database, but you can’t do it. So, first you should edit the import file and remove the CREATE DATABASE query (as we’ve already mentioned is some of the above examples). In the following example you will see how it looks like:
□CREATE DATABASE `username1_wrdp9` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
Then go to cPanel and click the MySQL Databases icon. The next step is to create your database with the name which was removed from the import file (in our example, "wrdp9"). Finally, import the modified import file and as a result it will work now.