# Importing and exporting a MySQL database

> Export a MySQL database to a file and import it again using phpMyAdmin in cPanel or the mysql and mysqldump commands.

Source: https://helpcenter.ruachost.com/developers/databases/importing-and-exporting-a-mysql-database/

**Importing and Exporting a MySQL Database**\
Learn how to import MySQL databases and export MySQL databases with this guide featuring step-by-step instructions, code snippets, and links to related articles.

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**\
Log in to cPanel.

In the DATABASES section of the cPanel home screen, click phpMyAdmin.

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

Click the Export tab.

Under Export method, confirm that Quick is selected.

Under Format, confirm that SQL is selected.

Click Export.

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.

Click Save.

**Method #2: Use the mysqldump Program**\
Access the command line on the computer where the database is stored.

Type the following command, replacing `username` with your username, and `dbname` with the name of the database:

```
mysqldump --routines -u username -p dbname > dbexport.sql
```

Type your password when prompted. The `dbexport.sql` file now contains all of the data for the `dbname` database.

**Creating a New MySQL Database and Assigning a User**\
Before importing, create a new database in cPanel and assign a user to it:

1. Log in to cPanel.
2. In the Databases section, click Manage My Databases (or MySQL Databases in older versions).
3. Under Create New Database, type the name and click Create Database.
4. Click Go Back.
5. Under Add User to Database, select the user and the database, then click Add.
6. Grant privileges by checking the boxes or selecting ALL PRIVILEGES.
7. Click Make Changes.

**How to Import a MySQL Database**\
After creating a new database, you can import the database using phpMyAdmin or the mysql command line program.

**Method #1: Use phpMyAdmin**\
Log in to cPanel and click phpMyAdmin.

Select the database to import into, then click the Import tab.

Click Browse, select the `dbexport.sql` file on your local computer, and click Import.

**Method #2: Use the mysql Program**\
Transfer the `dbexport.sql` file to your hosting account using SCP, SFTP, or FTP.

Log in using SSH and navigate to the directory containing the file.

Run the following command, replacing `username` and `dbname`:

```
mysql -u username -p dbname < dbexport.sql
```

The database will now contain the imported data.

**Troubleshooting a MySQL Database Import**\
Common errors:

- **ERROR 1044:** Access denied for user — occurs when the database in the SQL file does not match the user. Edit the file to match the correct username.
- **ERROR 1049:** Unknown database — occurs when the target database does not exist. Create it first.
- **ERROR 1064:** SQL syntax error — occurs when the import file is corrupted, altered, or unsupported. Ensure it contains valid SQL statements.
- **ERROR 1227:** Access denied; SUPER privilege needed — occurs when SQL statements require superuser privileges. Remove these statements if possible.

For more information about MySQL user privileges, see the related article.

## More Information

- [mysqldump command-line program](http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html)
- [mysql command line program](http://dev.mysql.com/doc/refman/5.1/en/mysql.html)
- [phpMyAdmin](http://www.phpmyadmin.net/)