Skip to content
Ruachost Help Center

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

Section titled “More Information”