How to import and export a MySQL database

This tutorial describes how to do mysql import and export by using the system console. Also helped you to restore the database from the dump file.

You can export and import databases for various scenarios, including:

?     Migrating a MySQL database from one hosting account to another or from one provider to another.

?     Importing a third-party MySQL database.

?     While taking a MySQL database backup.

How to do mysql import and export?

Before jumping to the mysql import or export process, let�s understand What MySQL is.

What is MySQL?

MySQL is an open source database server. It is a relational database management system. This database is used to store data in the table format. This open source database server is available to install on various operating systems. 

In case of mysql database transfer, you can easily create a database dump and restore it on the target database server. To MySQL import and export databases, MySQl server provides console utilities.

How to export a MySQL database

Exporting a MySQL database to a file is an easy task. You just need to use phpMyAdmin or by using mysqldump table/database command line program.

Method #1: Use phpMyAdmin

To export a MySQL database using the phpMyAdmin web interface, you need to follow these steps: 

1. Log in to cPanel. 

2. Now naviate the DATABASES section in the cPanel home screen. 

3. After that, click phpMyAdmin mentioned in the DATABASES section. 

A new window appears with the phpMyAdmin administration page. 

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

5. Click the Export tab. 

6. Under the Export method, select the radio button mentioning �Quick - display only the minimal options�. 

7. Under Format, select the SQL from the drop down. 

8. Click Go. 

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

10. Click Save. The process for export runs.

Method #2: Use the mysqldump table program

 By using the mysqldump table/database program, you can also export a MySQL database from the command line with the help of these steps:

 1. Go to the command line on the computer where the database is stored. For instance, if  your database is with another web hosting account or with another web hosting provider, then by using the SSH, log in to the account.

 In case of physical access to the computer, you can open a terminal window or DOS to access the command line.

 2. Type the below mention command, and then press Enter.

Further, enter your username at the place of username, and dbname with your database name that you want to export:

Copymysqldump --routines -u username -p dbname > dbexport.sql

3. At the Enter password prompt, enter your password. 

4. For the dbname database, the dbexport.sql file contains all the data. Incase, If the dbexport.sql file is on a remote computer, download it to your local computer. 

Creating a new database in MySQL and assigning a user 

Before importing the database, you must create a new database in cPanel and assign a user to it by following these steps: 

1. Go to the cPanel and Login.
2. On the home screen of cPanel section go to the DATABASES section, and click MySQL� Databases:
3. Under Create New Database, give the name to the database in the New Database text box.
4. Click Create Database. After that, cPanel creates the database.
5. Once the database is created, click Go Back.
6. Go to the option �Add User to Database�, given in the User list box, select the user that you want to add.
7. In the list box of Database, select the new database.
8. Click Add.
9. If you want to grant the user specific privileges, then Select the specific check boxes. Otherwise, select the ALL PRIVILEGES check box to grant the permissions to the database to all the users.
10. Click the Make Changes button. With the click, cPanel adds the user to the database. 

How to import a MySQL database

Once you have created a new database, only then you are able to import the database's contents. By using mysql command line program or phpMyAdmin.

Method #1: Use phpMyAdmin

By using the phpMyAdmin web interface, you can import a MySQL database by following these steps: 

1. Go to the cPanel and Login yourselfl.
2.  Click the phpMyAdmin available In the DATABASES section of the cPanel home screen,: 

A new window appears that shows the phpMyAdmin administration page.

3. In the left pane of the phpMyAdmin page, you can click the database that you want to import the data into.
4. Click the Import tab.
5. In �File to Import� section, click Browse, and then select the dbexport.sql file on your local computer.
6. Click Go. The process of import runs.
7. Now, your database has all the data which is in the file name: dbexport.sql.

Method #2: Use the mysql program

bY using the mysql program, you can smoothly import a MySQL database from the command line by following these steps:

1. Transfer the dbexport.sql file to your �go4hosting� hosting account using SFTP, SCP, or FTP.
2. Log in to your hosting account using SSH.
3. Now, you need to change the directory where you uploaded the file: dbexport.sql. Type the following command, and then press Enter. Enter username in place of username and dbname in place of name of the database that you want to import the data into: 

Copymysql -u username -p dbname < dbexport.sql 

4. Now, the database name which you entered in previous step i.e. dbname database contains the data that is in the dbexport.sql file. 

As you see,  It is very easy to do mysql import and export of the data. You can also use several other options available in mysqldump utility. You can customize your exported data with the help of these utility options. 

If you need any help with MySQL import and export get in touch with our support team. We will get back to you with the appropriate solution.

Was this answer helpful? 7 9