How to import an SQL file using the command line in MySQL

To import an SQL file using the command line in MySQL, we can use the mysql command-line client. This utility lets us interact with the MySQL server directly from the terminal or command prompt.

There are two methods to import an SQL file using the command line in MySQL on Linux:

1. Redirection method

2. The source command method

Both methods achieve the same result of importing the SQL file into the MySQL database.

Redirection method to import the SQL file

The following are the steps to import the SQL file using the redirection method:

Step 1: Create the database

To create a database in MySQL right from the command line interface (CLI), we can use the following mysqladmin command:

Note: Replace the your_username with your MySQL username and the your_database_name with the name of the target database.

Step 2: Import the SQL file

We can import the SQL file using the redirection (<) method, right from the CLI. The following command takes the MySQL username, the target database, and the SQL file that needs to be imported as inputs:

Note: Replace the your_username with your MySQL username, the your_database_name with the name of the target database, and the your_sql_file.sql with the path to the SQL file we want to import.

This method assumes that you have the target database present. If that's not the case, you can create a database by following steps 1-3 of the below method, and then run this command from the shell.

Step 3: Verify the import

Once the import process is complete, we can check if the data has been successfully imported into the database. We can access the MySQL prompt by running the following command:

Note: Replace the your_database_name with the desired name for your database.

Then, we can query the database to ensure that the data is present by following step 6 of the next method.

Source command method to import the SQL file

The following are the steps to import the SQL file using Source command method:

Step 1: Open the terminal

Step 2: Access MySQL command line client

We need to type the following command and press Enter to access the MySQL command-line client. It will prompt us to enter our MySQL user password.

Note: Replace your_username with your MySQL username. After entering the command, you will be asked to enter your MySQL password. If you haven't set the password, the default password will be empty. So, just hit Enter and you'll be logged into your MySQL account.

Now, we're in the MySQL client and all the following commands will be directly executed in this client.

Step 3: Create the database

If the database we want to import the SQL file into does not exist, we can create it using the following command inside the MySQL command line client:

Note: Replace the your_database_name with the desired name for your database. This step is optional and there is need to create the desired database only if it doesn't exist. In case you already have the target database, you can skip this step and jump directly to the next step.

Step 4: Use the database

Now that we have created the database, we can use the following MySQL command line client command to tell the MySQL client that we'll be using this database:

Note: Replace the your_database_name with the desired name for your database.

Step 5: Import the SQL file

We can use the following source command within the MySQL command line client to directly import the SQL file:

Note: Replace the /path/to/sql/your_sql_file.sql with the actual path to the SQL file on your system.

Step 6: Verify the import

Once the import process is complete, we can check if the data has been successfully imported into the database by making queries to our database. We can access the MySQL prompt by running the following command:

Note: Replace the your_database_name with the desired name for your database.

The successful execution of the above query ensures that the data is present.

Example

Let's assume we have the following SQL file named the employees.sql located in the /Project/ directory, and we want to import it into the company database:

The employees.sql file creates a table named employees with columns id, first_name, last_name, age, and email, and inserts sample data for three employees into the table.

Here's how we would import it into MySQL using both methods:

Redirection method

We need to run the following commands in a new terminal window:

Note: We've omitted the -p option in the above mysql commands since the password is empty. By keeping that tag in the commands, it'll halt the automation execution of all the commands at once.

The last command will take us to the mysql client command line, we can enter the following SQL query to confirm that the data got successfully imported into the company database:

Source command method

To opt for this method, we first need to log in to the mysql client by running the following commands in a new terminal window:

Once we're in the mysql client command line, we can directly execute the following commands to create a database, and import the SQL file into it:

Expected output

We can expect the following output for the above examples. Both the above methods will lead to the same output.