How to import and export a MySQL database

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

• Transferring a database from one web hosting account or provider to another.
• Importing a third-party database.
• Backing up a database.

Exporting a MySQL database

You can export a MySQL database to a file by using the mysqldump command line 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:

   mysqldump -u username -p dbname > dbexport.sql
   

   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 your Webhost.Berlin cPanel account.
2. In the Databases section of the cPanel home screen, click MySQL® Databases.
3. Under Create New Database, type the name of the database in the New Database text box.
4. Click Create Database.
5. When the database is created, click Go Back.
6. Under Add User to Database, select the user that you want to add in the User list box.
7. In the Database list box, select the new database.
8. Click Add.
9. Click the check boxes to grant the user specific privileges, or click the ALL PRIVILEGES check box to grant the user all permissions to the database.
10. Click Make Changes.

Importing 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.

Method #1: Use phpMyAdmin

You can import a MySQL database using the phpMyAdmin web interface.

To import a MySQL database using phpMyAdmin, follow these steps:

1. Log in to your Webhost.Berlin cPanel account.
2. In the Databases section of the cPanel home screen, click phpMyAdmin.
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 Go. 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 also import a MySQL database from the command line using the mysql program.

To import a MySQL database using the mysql program, follow these steps:

1. Transfer the dbexport.sql file to your Webhost.Berlin account using SCP, SFTP, or FTP.
2. Log in to your Webhost.Berlin account using SSH.
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:

   mysql -u username -p dbname < dbexport.sql
   

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

Troubleshooting the database import

You may receive one of the following error messages when you try to import a MySQL database using either cPanel 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.

More Information

• For more information about the mysqldump command line program, please visit 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.
• For more information about phpMyAdmin, please visit http://www.phpmyadmin.net.

Related Articles

• Connecting to MySQL from the command line

  You use the mysql program to connect directly to MySQL from the command line. Learn how here.