> ## Documentation Index
> Fetch the complete documentation index at: https://kb.hosting.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Importing and exporting a MySQL database

> Master importing and exporting MySQL databases with step-by-step instructions, code snippets, and related resources.

This article describes how to import MySQL databases and export MySQL databases. You can import and export databases for a variety of scenarios, including:

* Transferring a MySQL database from one web hosting account or provider to another.

* Importing a third-party MySQL database.

* Backing up a MySQL database.

## How to export a MySQL database

You can export a MySQL database to a file by using phpMyAdmin or the **mysqldump** command-line program.

### Method #1: Use phpMyAdmin

You can export a MySQL database using the phpMyAdmin web interface. To do this, follow these steps:

1. Log in to cPanel.
   > 📘 Note
   >
   > If you do not know how to log in to your cPanel account, please see [this article](/docs/accessing-cpanel).

2. In the **DATABASES** section of the cPanel home screen, click **phpMyAdmin**:\
   ![cPanel - Databases - phyMyAdmin icon](https://static.hosting.com/kb/kb-cpanel-78-databases-phpmyadmin-icon.png)

   The phpMyAdmin administration page appears in a new window.

3. In the left pane of the phpMyAdmin page, click the database that you want to export.

4. Click the **Export** tab.

5. Under **Export method**, confirm that **Quick** is selected.

   > 📘 Note
   >
   > If you are using an older version of phpMyAdmin that does not have the **Quick** option, follow these steps instead:
   >
   > * In the **Export** section, click **Select All**.
   >
   > * Select the **Save as file** checkbox, and then click **Go**. The export process runs.

6. Under **Format**, confirm that **SQL** is selected.

7. Click **Export**.

8. In the **Save File** dialog box, type the filename and select the directory where you want to save the exported database on your local computer.

9. Click **Save**. The export process runs.

### Method #2: Use the mysqldump program

You can export a MySQL database from the command line using the **mysqldump** program. To do this, follow these steps:

1. Access the command line on the computer where the database is stored. For example, if the database is on another web hosting account or with another web hosting provider, log in to the account using SSH. If you have physical access to the computer, you can open a DOS or terminal window to access the command line.

2. Type the following command, and then press Enter. Replace ***username*** with your username, and ***dbname*** with the name of the database that you want to export:

   ```bash theme={null}
   mysqldump --routines -u username -p dbname > dbexport.sql
   ```

   > 📘 Note
   >
   > This example uses the *dbexport.sql* filename for the exported database, but you can name the file whatever you want.

3. Type your password at the **Enter password** prompt.

4. The *dbexport.sql* file now contains all of the data for the ***dbname*** database. If the *dbexport.sql* file is on a remote computer, download the file to your local computer.

## Creating a new MySQL database and assigning a user

Before you can import the database, you must create a new database in cPanel and assign a user to it. To do this, follow these steps:

1. Log in to cPanel.
   > 📘 Note
   >
   > If you do not know how to log in to your cPanel account, please see [this article](/docs/accessing-cpanel).

2. On the **Tools** page, in the **Databases** section, click **Manage My Databases**:\
   ![cPanel - Databases - Manage My Databases icon](https://static.hosting.com/kb/kb-cpanel-120-databases-manage-my-databases-icon.png)

   > 📘 Note
   >
   > If you are using cPanel version 118 or earlier, click **MySQL Databases** instead:\
   > ![cPanel - Databases - MySQL Databases icon](https://static.hosting.com/kb/kb-cpanel-jupiter-databases-mysql-databases-icon.png)

3. Under **Create New Database**, in the **New Database** text box, type the name of the database.

4. Click **Create Database**. cPanel creates the database.

5. When the database is created, click **Go Back**.

6. Under **Add User to Database**, in the **User** list box, select the user that you want to add.

7. In the **Database** list box, select the new database.

8. Click **Add**.

9. Select the check boxes to grant the user specific privileges, or select the **ALL PRIVILEGES** check box to grant the user all permissions to the database.

10. Click **Make Changes**. cPanel adds the user to the database.

## How to import a MySQL database

After you have created a new database in cPanel, you can import the database's contents by using phpMyAdmin or the *mysql* command line program.

<Warning>
  **Important**

  If the exported database file contains any **CREATE DATABASE** statements, you must remove them or comment them out. Otherwise, the import process will fail.
</Warning>

### Method #1: Use phpMyAdmin

You can import a MySQL database using the phpMyAdmin web interface. To do this, follow these steps:

1. Log in to cPanel.
   > 📘 Note
   >
   > If you do not know how to log in to your cPanel account, please see [this article](/docs/accessing-cpanel).

2. In the **DATABASES** section of the cPanel home screen, click **phpMyAdmin**:\
   ![cPanel - Databases - phyMyAdmin icon](https://static.hosting.com/kb/kb-cpanel-78-databases-phpmyadmin-icon.png)

   The phpMyAdmin administration page appears in a new window.

3. In the left pane of the phpMyAdmin page, click the database that you want to import the data into.

4. Click the **Import** tab.

5. Under **File to Import**, click **Browse**, and then select the *dbexport.sql* file on your local computer.

6. Click **Import**. The import process runs.

7. The database should now contain the data that is in the *dbexport.sql* file.

### Method #2: Use the mysql program

You can import a MySQL database from the command line using the *mysql* program. To do this, follow these steps:

1. Transfer the *dbexport.sql* file to your hosting.com account using SCP, SFTP, or FTP.

2. Log in to your hosting.com account [using SSH](/docs/using-ssh-secure-shell).

3. Change to the directory where you uploaded the *dbexport.sql* file. For example, if you uploaded the *dbexport.sql* file to your home directory, type `cd ~`.

4. Type the following command, and then press Enter. Replace ***username*** with your username and ***dbname*** with the name of the database that you want to import the data into:

   ```bash theme={null}
   mysql -u username -p dbname < dbexport.sql
   ```

5. The ***dbname*** database should now contain the data that is in the *dbexport.sql* file.

### Troubleshooting a MySQL database import

You may receive one of the following error messages when you try to import a MySQL database using either phpMyAdmin or the *mysql* program:

* **ERROR 1044: Access denied for user 'username1'@'localhost' to database 'username2\_database'**\
  This error message occurs when the import file contains an SQL statement that attempts to access a database for the wrong username. Note in this example that *username2* in *username2\_database* does not match *username1* in '*username1*'@'*localhost*'. You must edit the import file and change *username2* to your new *username1*.

* **ERROR 1049: Unknown database 'username\_database'**\
  This error message occurs when the target database does not exist. Make sure you create the database first as described above, and then try to it import again.

* **ERROR 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 'username\_database' at line x.**\
  This error message occurs when the import file does not contain backup data for a database or there is a MySQL syntax error in the file. Alternatively, the import file may be altered, corrupt, or in an unsupported format. (Import files must contain SQL statements; other file formats such as CSV do not work with the *mysql* program.) Try exporting the database again, and then try to import it.

* **ERROR 1227: Access denied; you need (at least one of) the SUPER privilege(s) for this operation**\
  This error message occurs when the import file contains one or more SQL statements that require superuser privileges (such as **SET GLOBAL** or **CREATE DEFINER** statements). In some cases, you can just delete these statements from the *.sql* file and rerun the import process. For example, **CREATE DATABASE** statements can be safely removed, because you should have already created the database in cPanel. If you require MySQL superuser access, however, you should consider migrating your account to a VPS or Dedicated server, where you have complete control over the environment.
  > 📘 Note
  >
  > For more information about MySQL user privileges on shared servers, please see [this article](/docs/mysql-user-privileges-on-shared-servers).

## More information

* For more information about the *mysqldump* table/database command line program, please visit [http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html](http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html).

* For more information about the *mysql* command line program, please visit [http://dev.mysql.com/doc/refman/5.1/en/mysql.html](http://dev.mysql.com/doc/refman/5.1/en/mysql.html).

* For more information about phpMyAdmin, please visit [http://www.phpmyadmin.net](https://www.phpmyadmin.net).

## Related articles

* [Connecting to MySQL from the command line](/docs/connect-to-mysql-from-the-command-line)

* [MySQL user privileges on shared servers](/docs/mysql-user-privileges-on-shared-servers)

* [Managing MySQL databases, users, and tables from the command line](/docs/managing-mysql-databases-and-users-from-the-command-line)