How to Import and Export a MySQL Databases

Different ways to import and export the Mysql database.

1) Using command Line:

1. Open the XAMPP/WAMP control panel. Make sure Apache and MySql service modules are started.
2. Open Command prompt (Start -> All Programs -> Accessories -> Command prompt).
3. Type below command line in the command prompt to recognizes MySql command (Figure 1).

   c:\xampp\mysql\bin

4. Then to export your database, type below command:

mysqldump -u [username] -p [databasename] > [FileName].sql

databasename – name of the database to be exported.
FileName – Name of the SQL file you want to save. Also, you can add path in FileName as D:coverpoint.sql

5. Press enter and then you will be prompted to enter your user password.
6. If you had set the password before, enter that password or else press enter.
7. Next, you will see the cursor blinking in the command prompt. Wait for some time to export your database (depends on the size of the database).
8. This exports the database to the path which you are currently in i.e., c:\xampp\mysql\bin or in the path you have exported i.e., for ex: D: coverpoint.sql (Figure 2).
9. To import your database, type below command (Figure 3):

mysql -u [username] -p [databasename] < [FileName].sql

databasename – name of the database to which you want to import.
FileName – Name of the SQL file you want to import. Also, you can add path in FileName as D: coverpoint.sql

10. Press enter and then you will be prompted to enter your user password.
11. If you had set the password before, enter that password or else press enter.
12. Next, you will see the cursor blinking in the command prompt. Wait for some time to export your database (depends on the size of the database)(Figure 4).

Figure 1

Figure 2

Figure 3

Figure 4

(Click on the images to enlarge it.)

2) phpMyAdmin:

phpMyAdmin is a free and open source and cross platform tool for the administration of MySQL or MariaDB via a web browser. It is built with PHP, XHTML, CSS, and JavaScript. It is a browser-based administration tool hence, users can access it very easily on any browser. It can perform various tasks such as creating, modifying or deleting databases, tables, fields, and rows as well as executing SQL statements or managing users and permissions. Here, we will be learning about Importing and Exporting database of phpMyAdmin.

For Import:

1. Open PHPMyAdmin by typing http://localhost/phpmyadmin or http://127.0.0.1/phpmyadmin in your browser (if its password protected then log into PHPMyAdmin).
2. Create an empty database from the Databases tab and by clicking the create button (Figure 5).
3. Click the import tab in the top menu.
4. Click the browse button and select the file to be imported in your file system.
5. Click open (if you’re using chrome browser).
6. Select the format of the import file (Figure 6)
7. Click Go.

When the database has been imported successfully, you should see the message “Import has been successfully finished, 16 queries executed. (coverpoint.sql)” (Figure 7).

For Export:

1. Open PHPMyAdmin by typing http://localhost/phpmyadmin or http://127.0.0.1/phpmyadmin in your browser (if its password protected then log into PHPMyAdmin).
2. From the left-side navigation, select the database you want to export.
3. Click the Export tab in the top menu (Figure 8).
4. Under the Export, choose between a Quick or a custom export. If you are a beginner just choose Quick method so it’ll work most of the time. If you choose custom, you can configure the following:
   1. Tables(s): You can choose the tables you want to export.
   2. Output:
      1. Save output to a file: Choose template name, character set and compression for the output export file.
      2. View output as text.
      3. You can skip tables larger than ##(some size)MB.
   3. Format: you can choose the format as SQL, PDF etc. And also you can format with specific options.
   4. Object creation options: Choose the statements you want to add.
   5. Data creation options: choose the function to use when dumping data and syntax to use when inserting data.
5. Click on the Go button.
6. Your exported file will be saved as coverpoint.sql (Figure 9).

Figure 5

Figure 6

Figure 7

Figure 8

Figure 9

(Click on the images to enlarge it.)