There are various ways to import a MySQL database. This article will explain the steps that can be directly executed using the tools available on our servers. It will also cover common issues, solutions, and frequently asked questions.
Note: cPanel limits database uploads to 50 MB. If you try to import a database larger than 50MB, you may experience a timeout. If this happens, please reach out to us for support.
How to Import Databases Using phpMyAdmin:
STEP 1: Log in to cPanel.
There are two methods to log into your cPanel.
- Method 1: Log in to your cPanel directly.
- Method 2: Log in to your cPanel through your Customer Portal.
Through your Customer Portal;
- Log in to your Customer Portal.
- Click on the "Log in to Cpanel" icon.
STEP 2: In the Databases section, click on phpMyAdmin.
STEP 3: If the database is not available, start by creating the database, then create a user and grant that user full privileges through MySQL® Databases. Be sure to note down the username and password.
For detailed instructions, you can refer to the article: “How to Create or Delete a MySQL Database or User.”
STEP 4: In phpMyAdmin, choose the newly created database name from the left-side menu.
STEP 5: In phpMyAdmin, click on the Import tab in the main section.
STEP 6: Use the Choose File tab to locate and select the .sql file on your computer.
STEP 7: After attaching the file, scroll down and click on Import.
STEP 8: A confirmation message will appear indicating the import's success.
Common Issues
Import File Too Large
The phpMyAdmin upload limit for LyteHosting shared and reseller hosting plans is set to 50MB by default. This is also the default on VPS and Dedicated servers.
If you’re on a VPS or Dedicated server, you can increase this limit by adjusting the cPanel PHP max upload size and cPanel PHP max POST size through WHM under Tweak Settings.
To import a file larger than 50MB, please go ahead and upload it to your server and contact us, providing the file name and database name for the import.
Script Timeout Error
This error occurs when the import process takes too long for phpMyAdmin, resulting in the process being terminated.
If you encounter this timeout issue, upload the database backup file to your LyteHosting account and reach out to us with the details. Our technical supports will assist with the import.
For security reasons, it’s recommended to upload your database to the /home/USERNAME directory (replace USERNAME with your cPanel username).
#1044 - Access Denied for User 'username1'@'localhost' to Database 'user2_wrdp9'
This error occurs when your import file contains an SQL query attempting to create a database with the wrong username. Notice that 'user2' in 'user2_wrdp9' does not match 'username1' in 'username1'@'localhost'. The import file needs to be edited to replace 'user2' with your correct username, 'username1'. (If you're not comfortable editing the file, we can assist you.) Here's an example of the outdated username references:
□□--□-- Database: user2_wrdp9□--□CREATE DATABASE user2_wrdp9 DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;□USE user2_wrdp9;□□--
#1044 - Access Denied for User 'username1'@'localhost' to Database 'wrdp9'
This issue is similar to the previous one, where the correct username is missing. On shared servers, your database names must include the cPanel username prefix (e.g., something_something).
#1049 - Unknown Database 'username1_wrdp9'
This error indicates that the import file doesn't include a query to create the database before importing the data. To resolve this, go to cPanel > MySQL Databases and create the database with the appropriate name (for example, "wrdp9"). Then, try importing the file again.
#1007 - Can't Create Database 'username1_wrdp9'; Database Exists
This error occurs when the import file contains a query attempting to create a database that already exists. If the database is empty, go to cPanel > MySQL Databases and delete the empty database, then try the import again. If the database is not empty, the import file needs to be edited to remove the CREATE DATABASE query. (If you're not comfortable editing the file, we're happy to assist you.) Here's an example of the outdated query:
□CREATE DATABASE username1_wrdp9 DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
#1064 - SQL Syntax Error
This error indicates that the file is either missing database backup data or the file has been altered or corrupted.
ERROR 1044 (42000): Access Denied for User 'username1'@'localhost' to Database 'username1_wrdp9'
This error occurs when you don't have the necessary privileges to create the database. HostGator needs to resolve this issue for you.
If you need to proceed with the import immediately and can't wait, here's a workaround.
The issue is that the import file contains a query attempting to create a database, and you lack the permissions to do so. The import file should be edited to remove the CREATE DATABASE query. (If you need help with this, feel free to reach out.)
Here's an example of the problematic query:
□CREATE DATABASE username1_wrdp9 DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
Once you remove this code, go to cPanel and click the MySQL Databases icon. Create the database with the name that was removed from the import file (e.g., "wrdp9"). Afterward, you can import the modified file, and it will work.
Frequently Asked Questions
How do you create a MySQL backup using phpMyAdmin?
It's crucial to back up your database to restore it if something goes wrong. You can do this using the backup feature in cPanel or the export tab in phpMyAdmin.
Do you need to create a database before importing another one?
Yes, you need an existing database and a database user to import the .sql file. Each .sql file corresponds to a single database. If you have multiple .sql files to import, you’ll need to create a separate database for each one in cPanel. If there is no existing database, you can create one through cPanel's MySQL Databases section.
How do you add a MySQL database and user to your account?
For guidance on creating a database and user, please refer to the article "How to Create or Delete a MySQL Database or User."
Do you have to delete the old database before importing the backup database file, or is that done automatically?
If you plan to import a different .sql file or your backup into an existing database, you must first DROP/DELETE all database tables. If you don’t drop them, tables that don’t exist in the new .sql file will remain and may be overwritten. However, you can choose to retain some tables in the database. Tables in the new .sql file will overwrite those with the same name.
