How to Import Your MySQL Database
How Can I Import a Database?
There are multiple ways by which you can import the contents of a MySQL database. This article will cover the two methods that can be done directly with the tools on our servers.
This article also features a list of common issues and recommended solutions, and some commonly asked questions.
Note: The cPanel has imposed an upload size limit of 50mb for databases. You may encounter a time out prompt when importing more than 50mb. If you experience this time out, please contact us via phone or Live Chat so we can assist you.
Importing Databases via phpMyAdmin
This video will visually walk you through importing a database using phpMyAdmin from cPanel:
The steps below will walk you through the steps in the video.
- Log into the cPanel.
- Locate the Databases section.
- Click phpMyAdmin.
- If the database does not exist, please create a database first, a user, and give the user full privileges to the database through the MySQL® Databases. Note the username and password.
- Inside the phpMyAdmin, click the new database name from the left-hand menu.
- Click the Import tab in the main area of phpMyAdmin.
- Browse for the .sql file on your computer using the Choose File button.
- Scroll down and click Go.
- A confirmation prompt will appear on the top page notifying you about the successful import.
Importing Databases via Command Line (SSH)
Advanced users may wish to use SSH to access and make changes to their accounts, offering a more robust set of options with fewer limitations. These instructions will require a basic familiarity with SSH. If you are interested in learning to use SSH, please check the article below for information on how to get started:
To import your database after you have logged in to your account with SSH:
- Upload your database to your home directory (This may be done with cPanel File Manager, an FTP client, or SSH).
- If the database does not exist, please create a database, a user, and give the user full privileges to the database. Note the username and password.
For assistance with creating a Database and Database user, please see the article below:
- Log into your server via SSH.
- Use the ls -l command to verify that the file is in your current directory.
- If the file is not present, upload/move the file to the correct directory, or navigate to the directory where the file is located.
- Enter the following command, replacing '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
- You will be prompted for your database user password, and then your database will be imported.
- HostGator recommends using the database user with the database user's password.
- Make sure your database name and username has your HostGator username prefix (e.g. 'username_databasename'; 'username_databaseusername').
Your import file is too large!
The phpMyAdmin upload limit on HostGator shared and reseller hosting packages is 50mb. This is also the default on VPS and Dedicated servers.
Script Timeout Passed
This error is received when the database is taking too long for phpMyAdmin to import, causing the phpMyAdmin import process to be terminated.
For security reasons, it's best to upload your database to the
/home/USERNAME section of your account. (Replace USERNAME with your cPanel username.)
#1044 - Access denied for user 'username1'@'localhost' to database 'user2_wrdp9'
The problem here is your import file contains an SQL query that attempts to create a database for the wrong username. Notice the user2 in 'user2_wrdp9' does not match the username1 in 'username1'@'localhost'. Someone must edit the import file and change the old user2 to your new username1. (If you do not feel comfortable editing your import file, we will be glad to help you do that.) Here is an example of what 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`;□□--
#1044 - Access denied for user 'username1'@'localhost' to database 'wrdp9'
Same as the previous issue; the correct username is missing. On a shared sever, your database names must always look like something_something.
#1049 - Unknown database 'username1_wrdp9'
The problem is your import file does not have a query to create the database before importing the
data. Simply go to cPanel > MySQL Databases and create a database with that name (in my example, "wrdp9"). Then re-attempt your import.
#1007 - Can't create database 'username1_wrdp9'; database exists
The problem here is your import file contains an SQL query that attempts to create a database that already exists. If the database is empty, simply go to cPanel > MySQL Databases and remove that empty database; then re-attempt your import. If the database is not empty, someone must edit the import file and remove the CREATE DATABASE query. (If you do not feel comfortable editing your import file, we will be glad to help you do that.) Here is an example of what it looks like:
□CREATE DATABASE `username1_wrdp9` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
#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...
The file does not contain backup data for a database, or the file has been changed or corrupted.
Less Common Issues
ERROR 1044 (42000): Access denied for user 'username1'@'localhost' to database 'username1_wrdp9'
If you get this error, you do not have privileges on user_* to use Create. HostGator needs to correct this issue for you.
However, if you need to import now, and can't wait, here is a work-around.
The problem here is your import file contains at least one SQL query that attempts to create a database, and you do not have the privilege to do so. Someone must edit the import file and remove the CREATE DATABASE query. (If you do not feel comfortable editing your import file, we will be glad to help you do that.) Here is an example of what it looks like:
□CREATE DATABASE `username1_wrdp9` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
After you remove this code, you must go to cPanel and click the MySQL Databases icon. Here you must create your database with the name which was removed from the import file (in my example, "wrdp9"). Then you can Import the modified import file and it will work.
How Do You Create a MySQL Backup via your phpMyAdmin?
It is always important to create a backup of your database so it can be restored in case of unforeseen events.
Here is a video to guide you with our different methods in making a MySQL backup either through the Backup feature of your cPanel or through your phpMyAdmin.
The following are steps on how to navigate in your phpMyAdmin to export a backup.
- In your cPanel, click phpMyAdmin.
- Select the database you wish to backup.
- Click the Export tab.
- Select the Quick radio button.
- You can also choose the format type from the Format drop-down menu. By default, the database is set to be using the .sql format since that is the platform it's using.
- Click Go.
- This database will then be downloaded in an .sql file. Depending on the database’s size, the downloading may take some time.
If it is taking too long for your database to finish downloading, then it is best to compress your database before exporting.
- In the Export tab, select the Custom radio button.
- You will notice a list of tables below. You can also deselect the table you do not wish to backup. However, if you are not sure what to leave out, then you can leave it as it is. By default, all tables are selected.
- Under Output, look for Compression.
- Select gzipped from the drop-down menu.
The zipped and gzipped options are both compression methods. Gzip is the standard file compression for the Linux system and is faster than ZIP. It also saves more disk space than the ZIP compression application.
- Scroll down then click the Go button.
Do You Need to Create a Database Before You Import Another One?
There needs to be an existing database and database user in the server to attach the imported .sql file. One database is for one .sql file. If you have multiple .sql files to be imported, you will need to create databases for each one of them through your cPanel.
How Do You Add a MySQL Database and Database User to Your Account?
To create a database and database user, please refer to this article, How to Create or Delete a MySQL Database or User.
Do You Have to Remove the Old Database First Before You Import the Backup Database File or Is That Done Automatically?
Suppose you wish to use a different .sql file or import your backup file for your existing database, you first need to DROP all the database tables. Not dropping all tables will retain the tables that do not exist in the new .sql file and overwrite the existing ones. However, you can still retain some tables in that database if you wish. The tables in your new .sql file will overwrite the tables with the same table name existing in the database.