Logo

How to Import and Export a MySQL Database

Try this guide with our instant dedicated server for as low as 40 Euros

import and export mysql database

Imagine you have built a website for your organization. However, for various reasons, you have to migrate it to a different server or you need to create a backup for security purposes..

In almost all cases, you always find a database attached to the website. That means you need to migrate or move the database with the website files. Here, knowing how to import and export a MySQL database comes in handy. 

Whether you’re sharing a MySQL database, creating a backup, or importing data into a blank database, understanding the major steps in the import and export process is crucial. 

In this tutorial, we will discuss how to export and import a MySQL database. We will start with an introduction to the MySQL database and then discuss how to import and export data. 

Table Of Contents

  1. What is a MySQL Database?
    1. The Key Features of MySQL Database
  2. How to Import and Export MySQL Database
    1. The Prerequisites to Importing and Exporting MySQL Database
    2. How to Export MySQL Database
    3. How to Import a MySQL Database
  3. Conclusion
  4. FAQs

What is a MySQL Database?

MySQL is a popular open-source relational database management system (RDBMS) that uses Structured Query Language (SQL) for its operations. It is widely used for web applications and other data-driven software due to its speed, reliability, and ease of use.

The Key Features of MySQL Database

Some of the key features of MySQL databases are:

Relational Model

MySQL databases organize data into tables with rows and columns, making it easy to manage and query data.

SQL Queries

Users can perform a variety of operations such as inserting, updating, deleting, and retrieving data using SQL commands.

Scalability

A MySQL database can handle large volumes of data and numerous transactions, making it suitable for both small and large applications.

Security

MySQL offers robust security features to protect data, including user authentication and granular access control.

Cross-Platform Support

MySQL runs on various operating systems, including Windows, Linux, and macOS, providing flexibility in deployment.

How to Import and Export MySQL Database

Now that you have a basic understanding of MySQL, let us discuss how to import and export a MySQL database. However, before that, let us take a quick look at the prerequisites. 

The Prerequisites to Importing and Exporting MySQL Database

Before moving into the details of the steps, ensure you have the following.

  • You have MySQL installed and configured on your server
  • You have access to a MySQL root account.
  • You can access the terminal or a command line
  • You have cPanel access to your server
  • You have MySQL Workbench installed

How to Export MySQL Database 

Exporting a MySQL database is crucial for several reasons such as maintenance, migration, and data security. 

MySQL offers several methods to export database content. We will discuss exporting the database through the following methods:

  • mysqldump
  • phpMyAdmin
  • MySQL Workbench

Let us discuss each in detail.

Method #1: Utilize mysqldump

The mysqldump command from MySQL allows users to create a dump file with all of the data from a given database. 

A dump file or a memory dump is automatically or manually built when an application or system crashes and records the contents of the system’s memory at that particular moment. This dump file is used as a data backup or restore the settings and environment to the previous position.

We recommend you to follow the steps below to export the MySQL database to a dump file.

Open a terminal window and execute the following command.

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

Replace [database] with your database name and [dump-file] with a custom name for the SQL file.

Here,

-u: Indicates MySQL username.

-p: Prompts to enter the MySQL password.

[database]: The name of the database to be exported.

[dump-file].sql: The desired filename for the exported SQL file.

Note that you will not see any success or failure messages in the terminal. So, if the command is successful, the dump file is saved to the current working directory. 

To verify the export was successful, execute the following commands (We recommend you to execute the appropriate command for your operating system)

For Linux or Mac systems:

# head –n 5 [dump-file].sql

rs_db

For Windows, use the Get-Content command:

# Get-Content -TotalCount 5 dump-file.sql

The system prints the initial five lines of the specified dump file. Verify the Database field to ensure the correct database has been exported.

Method #2: Use phpMyAdmin

IMPORTANT: As mentioned in the prerequisite, you need cPanel access for this method. If you don’t have it, you should choose method #1 or method #3 to export the MySQL database. 

phpMyAdmin is a user-friendly web interface commonly available in web hosting control panels like cPanel. It provides a convenient way to manage MySQL databases, including exporting them for backup, migration, or other purposes.

Here are the major steps you need to follow for exporting the MySQL database using phpMyAdmin.

Open the cPanel URL in a web browser and log in to your web hosting account with administrator credentials. The specific URL will vary depending on your hosting provider. It’s typically https://yourdomain.com/cpanel. Replace yourdomain.com with your actual domain name.

Next, locate the Databases section within the cPanel. 

Select the phpMyAdmin option to launch the phpMyAdmin interface.

cpanel interface

In the left-hand pane of the phpMyAdmin window, you can see a list of available databases. 

Click on the database you want to export.

Note: It might take a moment for the database details to load.

Once the database is selected, select the Export tab at the top of the phpMyAdmin window.

In the Export tab, you will be prompted to choose the export method: Quick or Custom.

We recommend the Quick method. It is the simplest and thus the most suitable choice for most users. It selects all tables in the database and exports them with basic options.

phpmyadmin interface

Next, ensure SQL is selected as the output format. If not, choose it from the dropdown menu.

Click Go to trigger the export process.

Click Export to download the database dump file to your local system.

Depending on the database size, the process may take a few seconds or minutes to complete.

Method #3: Use MySQL Workbench

Compatible with Linux, Windows, and macOS, MySQL Workbench is a graphical user interface (GUI) tool for managing MySQL databases. It offers a user-friendly GUI for managing MySQL databases, including exporting them to local and other locations.

You can follow these steps to export a dump file using MySQL Workbench.

Open MySQL Workbench on your system and connect to your MySQL server.

In the Navigation pane (on the left side), locate the Server Connections section.

Choose Data Export from the dropdown menu.

mysql workbench interface

In the Data Export window that appears next, select the database you wish to export in the Expo Schema section.

Next, in the Export Options section, choose the Export to Self-Contained File option.

Click Start Export to initiate the export. You might see a progress bar or status message.

click rs test from data export

Workbench generates and saves the file in the directory specified. Once the export is completed, a message will be displayed in the Workbench interface.

How to Import a MySQL Database

MySQL databases can be imported through various methods. You can see that these methods mirror the export methods we discussed in the previous section. 

  • mysqldump
  • phpMyAdmin
  • MySQL Workbench

Before importing a database, you should make sure that you have an existing database created on your MySQL server to receive the data. If you do not have the database, follow these steps to create one before importing the data:

Open a terminal or command prompt. 

Connect to the MySQL shell using the following command:

# mysql -u root -p

Replace root with your administrator username. 

Enter your password when prompted.

Next, create a new database by executing the following SQL statement:

mysql> CREATE DATABASE [database];

Replace, database with the name of your desired database.

create database

The system should return a Query OK message.

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

With the database set up, proceed with one of the following methods to import the data from the dump file:

Method #1: Use mysqldump

Open a terminal or command prompt.

Navigate to the directory containing the exported database dump file (.sql file).

Execute the mysql command to import the data.

# mysql -u root -p [database] < [dump-file]

Replace database_name with the existing/new database name on the server, and dump_file.sql with the database dump file.

Enter your password when prompted. Note that the command will not produce any output if the data was successfully imported.

So, to verify if the database is successfully imported, log back into the MySQL shell and load the database.

mysql> USE [database];

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

change database

Next, execute this MySQL statement to display the contents of the database.

mysql> SHOW TABLES;

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

show tables

Method #2: Utilize phpMyAdmin

Follow these steps to import a dump file into a new database using the phpMyAdmin web interface.

Access the phpMyAdmin interface through the web browser (usually the URL is http://localhost/phpmyadmin).

Log in to cPanel using the admin credentials. 

Navigate to the Databases section and click on phpMyAdmin.

In the left-hand pane, select the database where you want to import the data.

Click on the Import tab at the top menu.

click import on phpmyadmin

Under the File to Import section, click Browse and select the dump file on your system.

Ensure SQL is selected under Format.

select sql format

Click the Import button.

phpMyAdmin will display a message upon successful import.

Method #3: Use MySQL Workbench

MySQL Workbench offers versatile import options, enabling selective imports and support for various formats and encodings. 

Follow the steps below to import an SQL file into a MySQL database.

Open MySQL Workbench and connect to your MySQL server using the current connection settings or create a new connection profile.

From the menu bar, select Server.

Choose Data Import from the dropdown menu to open the Data Import window.

click data import

Under the Import Options choose Import from Self-Contained File.

Enter or select the dump file location.

For the Default Target Schema, choose your desired database. Alternatively, click New to create one.

Click Start Import to begin the process.

click start import

MySQL Workbench will then import the contents of the dump file into the selected target schema.

Conclusion

Mastering the import and export of MySQL databases is crucial for effective database management. 

Whether you’re a MySQL user managing databases on a local or remote database server, understanding the configuration options, file formats, and steps involved in the export and import processes is essential. 

By following the steps outlined in this guide, you can confidently import the database, export the database, and handle various export operations to ensure the integrity and availability of your database tables.

FAQs

Q. What is MySQL Database?

For online applications, MySQL is a popular open-source relational database management system. It is often paired with PHP and Apache to create a LAMP stack.

Q. In what way do I export a MySQL database’s contents?

The mysqldump command-line utility can export data from a MySQL database. You can produce a SQL-formatted database backup with this program.

Q. What is the process for importing data into a MySQL database?

Importing data into a MySQL database is possible using the mysql command-line tool. This program can restore a database from an SQL file containing the information.

Q. Can I export and import data from dbForge Studio for MySQL?

Yes, dbForge Studio for MySQL is a comprehensive tool that allows users to export and import data from MySQL databases efficiently. It offers an easy-to-use interface for database task management.

Q. How can I create a database in MySQL?

The CREATE DATABASE statement in the MySQL command-line interface can create a database in MySQL. With this command, you can specify the database name you want to create.

Q. What is the importance of database backups in MySQL?

Backing up your MySQL database is crucial to protect your data against system failure, accidental deletion, or damage. Regular backups ensure that you can restore your database to a previous state in an emergency.

Q. How can I access the command line for MySQL database management?

You can access the command line for MySQL database management by opening a terminal or command prompt and logging in with the appropriate credentials using the mysql command. This offers a straightforward method of communicating with the database system.

Try this guide with our instant dedicated server for as low as 40 Euros