How to import and export a MySQL database

The content of this article explains the procedure for importing and exporting MySQL databases. It is possible to 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 table/database command line program.

1 Method: Use phpMyAdmin

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

1. Log in to cPanel.
2. Click phpMyAdmin in the DATABASES section of the cPanel home screen:                                                                                                                                                                     The phpMyAdmin administration page appears in a new window.
3. Click the database that you want to export in the left pane of the phpMyAdmin page.
4. Click the Export tab.
5. Confirm that Quick is selected under the Export method.

   If you using an older version of phpMyAdmin which doesn’t have a Quick option, please follow these steps:

   • Click Select All in the Export section.
   • Select the Save as file checkbox, and then click Go, and the export process runs.
6. Confirm that SQL is selected under Format.
7. Click Go.
8. Type the filename and select the directory where you want to save the exported database on your local computer in the Save File dialog box.
9. Click Save, and the export process runs.

2 Method: Use the mysqldump table program

Please follow these steps to export a MySQL database from the command line using the mysqldump table/database program:

1. To access the command line of the computer where the database is stored, you can log in to the relevant account using SSH if the database is hosted on another web hosting account or with a different web hosting provider. Alternatively, if you have physical access to the computer, you can open a terminal or DOS 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 --routines -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 in the Enter password field.
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

To import the database, you need to first create a new database in cPanel and then assign a user to it. Please follow these steps to do this:

1. Log in to cPanel.
2. Click MySQL® Databases in the DATABASES section of the cPanel home screen:     
3. Type the name of the database in the New Database text box, under Create New Database.
4. Click Create Database, and cPanel creates the database.
5. When the database is created, click Go Back.
6. Select the user that you want to add in the User list box, under Add User to Database.
7. Select the new database in the Database list box.
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, and cPanel adds the user to the database.

How to import a MySQL database

Once you have created a new database in cPanel, you can proceed to import the database’s contents using either phpMyAdmin or the mysql command line program.

1 Method: Use phpMyAdmin

Please follow these steps to import a MySQL database using the phpMyAdmin web interface:

1. Log in to cPanel.
2. Click phpMyAdmin in the DATABASES section of the cPanel home screen:                                                                                                                                                                     The phpMyAdmin administration page appears in a new window.
3. Click the database that you want to import the data into in the left pane of the phpMyAdmin page.
4. Click the Import tab.
5. Click Browse under File to Import, and then select the dbexport.sql file on your local computer.
6. Click Go and the import process runs.
7. The database should now contain the data that is in the dbexport.sql file.

2 Method: Use the mysql program

Please follow these steps to import a MySQL database from the command line using the mysql program:

1. Transfer the dbexport.sql file to your Beehosting account using SCP, SFTP, or FTP.
2. Log in to your Beehosting account.
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 after that 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 a MySQL database import

When attempting to import a MySQL database using either phpMyAdmin or the mysql program, you might encounter one of the following error messages:

• ERROR 1044: Access denied for user ‘username1‘@’localhost‘ to database ‘username2_database‘
  The occurrence of this error message indicates that the import file contains an SQL statement that attempts to access a database for the wrong username. Please take note that in this particular example, username2 in username2_database does not match username1 in ‘username1‘@’localhost‘. You need to edit the import file and change username2 to your new username1.
• ERROR 1049: Unknown database ‘username_database‘
  If the target database does not exist, this error message will appear. Therefore, it is important to create the database first as described earlier, and then attempt the import once 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.
  If the import file is missing backup data for a database or if there is a MySQL syntax error in the file, this error message will appear. 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
  If the import file contains one or more SQL statements that demand superuser privileges (such as SET GLOBAL or CREATE DEFINER statements), this error message will appear. 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. In case you require MySQL superuser access, it would be advisable to move your account to a VPS or Dedicated server. This would provide you with complete control over the environment.

More Information

• If you need more information about the mysqldump table/database command line program, please visit http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html.
• If you need more information about the mysql command line program, please visit http://dev.mysql.com/doc/refman/5.1/en/mysql.html.
• If you need more information about phpMyAdmin, please visit http://www.phpmyadmin.net.

Looking for an outstanding cPanel-based web hosting provider? Try our web hosting packages with cPanel