Table of contents
1.
Introduction
2.
MySQL Export Database
2.1.
By Command Line Tool
2.2.
By MySQL Workbench
3.
MySQL Import Database
3.1.
By Command Line Tool
3.2.
By MySQL Workbench
4.
FAQs
5.
Key Takeaways
Last Updated: Mar 27, 2024

MySQL Export and Import Database

Author Apoorv Dixit
0 upvote
Career growth poll
Do you think IIT Guwahati certified course can help you in your career?

Introduction

Database export-import refers to the moving of data from one place to another. Export and import of data are useful in backing up data and restoring it if the original data is lost. A good example of this is storing data on cloud drives. We store data on drives(export) from local storage, and whenever we want, we can restore(import) it to our system from the drive.

We can export and import databases in MySQL in two ways:

  1. Command Line Tool
  2. MySQL Workbench
     

Let's discuss importing and exporting databases in these two ways in detail.

MySQL Export Database

By Command Line Tool

To export databases using the command-line tool, follow the following steps.

Step 1: Open the command line tool using username and password.

Step 2: Execute the following command to show databases on the server.

mysql> SHOW DATABASES; 

OUTPUT

Step 3: From the above databases, we will select the 'student' database. The database and its table are shown below.

Step 4: Now, you need to access the command line on your system where the database is stored. For this, you can open the DOS or terminal window. If you have installed MySQL in C drive, copy the path address in your DOS or terminal and press enter. Your path may look similar to the given below.

C:\Users\Ninja> CD C:\Program Files\MySQL\MySQL Server 8.0\bin 

 

Step 5: Use mysql dump tool to export the database.The tool uses login credentials of the MySQL user, and you can use the following command after providing login information.

$ mysqldump -u username -p database_name > desiredplace\dbname.sql 

Here:

-u ensures that MySQL username will be followed,

username is the name of the user to which we can log in to the database,

-p ensure the password associated with the username,

database_name is the name of the database we want to export,

desiredplace\dbname.sql is the folder where we want to export,

dbname.sql is the file with which the output will be saved.

For example, let's consider the user root and database name as 'Student,' folder name: Backup, and output database as studb.sql.

mysqldump -u root -p mytestdb > D:\Backup\studb.sql 

 

Step 6: After pressing enter, it will ask for the password for the mentioned username. After entering the password, it will create the backup file in the specified location with .sql suffix.

By MySQL Workbench

Now let's see the exporting of the database using MySQL workbench. Follow the steps:

Step 1: Open the workbench and type the password for the selected username.

Step 2: Go to the menu bar and click on the server. Select the 'Data Export' option from the popup, and a new window will open with data export settings and options.

 

Step 3: Select the desired database for export. You can also select multiple databases which you want to include in the export file. Here we will select the 'Student' database.

Step 4: After selecting the database, click the drop-down option and select from any of the options available accordingly.

  • Dump Data and Structure will save both table structure and data rows.
  • Dump Data Only will save only the inserted rows in the tables.
  • Dump Structure Only will save only the table structure, which are database columns and data types defined by us.


Step 5: Select 'Export to Dump Project Folder' or 'Export to self-contained File' in the export options.

Then select the export file location; you can also keep it to default.

Step 6: Click the Start export button and see the progress bar and log. You can verify the export files in the document folder of your system.

MySQL Import Database

By Command Line Tool

To import databases using the command-line tool, follow the following steps.

Step 1: Open the command line tool using username and password.

Step 2: Create a' test' database using the following statement.

mysql> CREATE DATABASE test; 

Step 3: Execute the below command to verify it does not have any table.

mysql> USE test; 
mysql> SHOW TABLES; 

OUTPUT:

Step 4: Now, you need to access the command line on your system where the database is stored. For this, you can open DOS or terminal window. If you have installed MySQL in C drive, copy the path address in your DOS or terminal and then press enter. Your address may look similar to the given below.

C:\Users\Ninja > CD C:\Program Files\MySQL\MySQL Server 8.0\bin 

 

Step 5: Use MySQL dump tool to import the database. The tool uses the login credentials of the MySQL user, you can use the following command after providing login information.

$ mysqldump -u username -p database_name < desiredplace\dbname.sql 

For example

mysqldump -u root -p test < D:\Backup\testdb.sql 

 

Step 6: After pressing enter, it will ask for the password for the mentioned username, and the database will be imported successfully.

You can now open the command-line tool again and verify whether the selected database contains a new table or not.

By MySQL Workbench

Now let's see the importing of databases using MySQL workbench. Follow the steps:

Step 1: Open the workbench and type the password for the username.

Step 2: Go to the menu bar and click on the server. Select the 'Data Import' option from the popup, and a new window will open with data import settings and options.

Step 3: In the import options, select 'Import to Dump Project Folder' or 'Import to self-contained File.'

Step 4: Here, we will select 'import from self-contained file' and choose the desired database for importing.

Step 5: Now, choose the 'Dump Structure and Data' option and click the Start Import button to import the databases.

You can now navigate to the Schema on the workbench window and refresh it to see newly imported databases.

FAQs

  1. What is MySQL workbench?
    MySQL Workbench is a unified GUI tool for database architects, and it provides data modeling, SQL development, and comprehensive administration tools for user administration, server configuration, backup, and much more. 
     
  2. What is a command line?
    Command-line is a text-based application for viewing, handling, and manipulating files on your computer. It's much like Windows Explorer but without GUI.
     
  3. What is the MySQL database name?
    There is no default database name. A fresh MySQL server install will have zero databases. The install script will run after the server runs to create a MySQL database.
     
  4. What is exporting a database?
    Exporting a database means saving a source database in a file that can be used for storage or distribution.
     
  5. What is importing a database?
    Importing a database means restoring data from the file to a destination database. You can import a database to the same or another database server.

Key Takeaways

In this article, we discussed importing and exporting Databases in MySQL. It is done by a command-line tool or by MySQL workbench in which export and import of databases is more GUI friendly. We discussed step-by-step procedures to export-import databases by these two methods.

You can also check out the top 100 SQL Problems to get hands-on experience with frequently asked interview questions.

Refer to the Guided Path to learn more about DBMS. You can visit Coding Ninjas Studio to practice programming problems for your complete interview preparation and land your dream job.

Live masterclass