Knowledge Base

How Can We Help?

How to use the Command Line to Import and Export Databases in MySQL

You are here:

MySQL is a relational database management system that is open-source. It is ranked as the second most popular database worldwide and offers a wide range of features, making it easy to work with small databases and scale them based on your needs. Additionally, MySQL allows for server replication, allowing you to distribute the load across multiple servers. These powerful capabilities include the ability to import and export databases using the command line interface.

This tutorial will cover the process of importing and exporting databases from the command line interface. The process is straightforward and user-friendly. Let’s start by learning how to export MySQL databases using the command line interface.

Exporting MySQL Databases using the Command Line

MySQL provides the “mysqldump” utility, which makes exporting databases a breeze. This utility allows you to export a single database or multiple databases with ease. Let’s explore how to export a single database using mysqldump.

To export a single database, input the following command on your server. Prior to executing this command, make sure to connect to your server using SSH.

$ mysqldump -uUSERNAME -p DB_NAME > exported.sql

Remember to replace the “USERNAME” and “DB_NAME” placeholders in the command with the appropriate values. Once executed, you will be prompted to enter the password for the specified user. After entering the correct password, the database will begin exporting to the file mentioned in the command (e.g., ‘exported.sql’ in our case).

This indicates that the entire database will be exported to a file named ‘exported.sql’. Feel free to export to any file by modifying the file name in the command.

Now let’s cover exporting MySQL databases, excluding specific tables. Although this feature is not commonly used (since most exports involve all tables within a database), you might find it useful. Execute the following command to export a database while excluding certain tables.

$ mysqldump -uUSERNAME -p DB_NAME --ignore-table=DB_NAME.TABLE_NAME > exported.sql

Again, make sure to replace the variables in the command with the appropriate username, database name, and table name. To exclude multiple tables from the export process, include an additional “–ignore-table” option in your command for each table you wish to ignore.

This is the process of exporting MySQL databases using the command line interface. Now let’s move on to importing MySQL databases.

Importing MySQL Databases via the Command Line

Importing MySQL databases is even simpler. We can leverage the main “mysql” command to import databases that have been exported and saved as SQL files. However, you must first create the MySQL database into which you want to import your SQL file.

Let’s begin by creating a MySQL database. Log in to your MySQL server using the following command:

$ mysql -uUSERNAME -p;

After logging in, execute the following command to create a database on your MySQL server.


Now let’s import the SQL file into our newly created database. Execute the following command to import a MySQL database from an SQL file:

$ mysql -uUSERNAME -p DB_NAME < import_file.sql

You will be prompted to enter the password. After entering the password, the command will initiate the data import from the specified SQL file. That covers the process of importing databases in MySQL.

Conclusion: Importing and exporting data in MySQL is a straightforward process. The “mysqldump” utility offers several additional options that allow you to customize the exported data. If you require assistance with importing and exporting in MySQL, feel free to reach out to us. We’ll provide a solution or assistance as soon as possible.

Command Line Methods for Transferring and Receiving MySQL Databases

Leave a Comment