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

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.

mysql> CREATE DATABASE DB_NAME;

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.