How to Import and Export MySQL Database

March 11, 2024

Introduction

MySQL allows administrators to export and import databases using built-in and third-party tools. Exporting a database creates a dump file that can later be imported to any MySQL deployment, making it easy to back up and migrate between different systems.

This tutorial will walk you through how to export a MySQL database and import it from a dump file.

How to import and export a MySQL database.

Prerequisites

How to Export MySQL Database

MySQL provides multiple ways to export database content, including the CLI, web UI, and GUI methods. The following sections provide tutorials for exporting MySQL databases using mysqldump, phpMyAdmin, and MySQL Workbench.

Exporting MySQL Database With mysqldump

Note: The commands below can all be run with a root database user account. If you already have another user account with sufficient privileges, you can use it instead. You may also add a new MySQL user account.

MySQL includes the mysqldump command that creates a dump file with all the data related to the specified database. Once generated, the file can be shared or used as a backup.

Follow the steps below to export a MySQL database to a dump file:

1. Enter the following command in a terminal window, replacing [database] with the database name and [dump-file] with a custom name for the SQL file:

mysqldump –u root –p [database] > [dump-file].sql

If successful, the command provides no output. The dump file is saved to the current working directory.

To run the command in Windows, provide the full path to the mysqldump.exe. For example:

C:\tools\mysql\current\bin\mysqldump -u root -p [database] > [dump-file].sql

Note: To avoid typing the full path, learn to set environment variables in Windows and add mysqldump to the Path variable.

2. Confirm the export in Linux by entering:

head –n 5 [dump-file].sql
Checking the mysqldump file with the head command.

In Windows, use the Get-Content command:

Get-Content -TotalCount 5 dump-file.sql

The system prints the first five lines of the specified dump file. Check the Database field to confirm the correct database has been exported.

Checking the mysqldump file with the Get-Content command.

Note: The dump file is saved to the current working directory from which you run it.

Exporting MySQL Database Using phpMyAdmin

Server and site management platforms, such as cPanel, often include phpMyAdmin, a webUI program for managing web servers. If you have access to cPanel and want to export a database from a web server, follow the steps below to perform this action with phpMyAdmin:

1. Log into cPanel.

2. In the Databases section, select phpMyAdmin.

Locating phpMyAdmin in cPanel.

3. Select the database to export in the menu on the left side of the main phpMyAdmin window. Wait for the database to load.

4. Select the Export tab from the tab bar at the top of the window.

5. Set the export method to Quick.

6. Ensure the export format is SQL.

7. Select Export to download the dump file to the local system.

Exporting procedure in phpMyAdmin.

Exporting a MySQL Database via MySQL Workbench

MySQL Workbench is a GUI tool for MySQL database management, available for Linux, Windows, and macOS. Proceed with the following steps to export a dump file using MySQL Workbench:

1. Launch MySQL Workbench and connect to the MySQL database.

2. Select the Server item in the menu bar.

3. Choose Data Export from the menu.

Finding the Data Export section in MySQL Workbench.

4. Select the databases to export in the Expo Schema section.

5. Choose the Export to Self-Contained File option in the Export Options section.

6. Click the Start Export button to start the process.

Exporting data in MySQL Workbench.

Workbench generates the file and places it in the directory listed next to the selected export option. By default, the file is in the /home/[user]/dumps directory.

How to Import MySQL Database

All the environments listed in the section on exporting MySQL databases can also be used to import databases to the local MySQL deployment. Refer to the relevant section below to learn how to import dump files using MySQL CLI, phpMyAdmin, and MySQL Workbench.

Importing MySQL Database Using mysqldump

Importing a database requires the local MySQL installation to have at least one database set up. If you are starting from scratch, set up a blank database by following the steps below:

1. Launch the MySQL shell:

mysql –u root –p
Launching the MySQL shell.

On Windows, use the following command to launch MySQL:

[path-to-mysql-binary]\mysql –u root –p

2. Enter the root password when prompted. The mysql command prompt appears.

3. Create a new database by entering the command below. Replace [database] with a database name.

CREATE DATABASE [database];
Creating a new database in MySQL.

The system returns the Query OK message.

4. Exit the MySQL shell by pressing Ctrl-D or typing exit.

With a database set up, proceed with the following steps to import the dump file:

1. Type the command below, replacing [database] with the name of the database you want to use and [dump-file] with the file containing a database backup:

mysql –u root –p [database] < [dump-file].sql

On Windows, type the full path to the mysql.exe program. For example:

C:\tools\mysql\current\bin\mysql –u root –p [database] < [dump-file].sql

Note: When executing the command above, ensure that the dump file is in the current working directory. Alternatively, provide the full path to the file.

A successful import does not produce an output.

2. To check the database, log back into the MySQL shell and load the database by entering:

USE [database];

Replace [database] with the database that received the imported data.

Output from the USE command in MySQL.

3. Display the contents of the database by typing:

SHOW TABLES;

Check if the tables from the imported database are present in the system.

Listing the tables of a database.

Importing MySQL Database With phpMyAdmin

Follow the steps below to import a dump file into the new database using the phpMyAdmin web interface:

1. Log into cPanel.

2. In the Databases section, select phpMyAdmin.

3. In the left column, select the database to import to.

4. Click the Import tab in the tab bar.

5. In the File to Import section, select Browse and provide the location of the dump file.

Importing procedure in phpMyAdmin.

6. Set the import format to SQL.

7. Select Import.

Importing procedure in phpMyAdmin - continuation.

phpMyAdmin reads the file and imports its contents.

Importing MySQL Database via MySQL Workbench

MySQL Workbench features flexible import options, allowing for selective imports, various formats, encodings, etc. The following procedure shows how to import an SQL file into a MySQL database using MySQL Workbench.

1. Select the Server item from the menu bar.

2. Choose Data Import from the menu that appears.

Locating the Data Import section in MySQL Workbench.

The Data Import window opens.

3. Select Import from Self-Contained File in the Import Options section. Provide the path to the dump file.

4. Choose an existing database for the Default Target Schema. To create a new database, select the New button.

5. Select Start Import.

Importing procedure in MySQL Workbench.

MySQL imports the contents of the dump file to the default target schema.

Conclusion

After reading this guide, you should know how to export and import a MySQL database. The methods included using the command line, server management web UI, and a graphical utility.

Next, check out our article on how to easily export from MySQL database table to CSV and how to import CSV into MySQL table.

Was this article helpful?
YesNo
Marko Aleksic
Marko Aleksić is a Technical Writer at phoenixNAP. His innate curiosity regarding all things IT, combined with over a decade long background in writing, teaching and working in IT-related fields, led him to technical writing, where he has an opportunity to employ his skills and make technology less daunting to everyone.
Next you should read
How To Set Up MySQL Master Slave Replication
January 16, 2024

In cloud computing, master-slave data replication refers to storing the same information on multiple servers...
Read more
How to Rename a MySQL Database
March 20, 2024

As an administrator, you may need to rename a database. However, for security, the command to rename a...
Read more
How to Install MySQL 8.0 in Ubuntu 18.04
December 12, 2018

MySQL is an open-source relational database server tool for Linux operating systems. It is widely used in...
Read more
How to Reset or Change MySQL Root Password on Linux or Windows
December 12, 2023

MySQL is a database management system. It's operated under the Open Source software model and has become a...
Read more