How to import SQL file using CLI in MySQL?

What is an SQL file?

A SQL file is one that contains SQL (Structured Query Language) statements and has the extension .sql. SQL is a Structured Query Language used to retrieve and modify data of database tables. SQL file is a collection of SQL DML statements (Insert, Update, Delete or Select statement).

Following are the steps need to complete before importing sql file

Advertisement
  1. Open MySQL Command Line
  2. Change or Switch database in which file will be imported
  3. Import .SQL file

 

How to open MySQL from command line

There are two ways to open MySQL server 8.0 from command line, once it installed successfully

  1. Windows search option
  2. mysql -uroot–p command

 

Method-1: Windows Search option

We can directly search for ‘MySQL 8.0 Command Line Client –Unicode’ on windows search and open MySQL Command line client as shown in below figure.

How to import SQL file using CLI in MySQL?

 

Click on Open option and start command line with MySQL prompt

How to import SQL file using CLI in MySQL?

 

Method-2: mysql -u root –p command

Another way to open MySQL command line is to use mysql –uroot –p command on command line terminal of windows , following are the steps

1. Open command line terminal using cmd command

How to import SQL file using CLI in MySQL?

 

2. Change current working directory to SQL Server 8.0 which reside in  MySQL directory of Program Files using cd command

Advertisement

How to import SQL file using CLI in MySQL?

 

3. Switch current directory to bin with cd command and then execute following command

 mysql –u  root  –p 
  • Once we execute above command, it will ask to enter password of MySQL default user. (Default user is root).
  • After entering correct password you are able to work on MySQL terminal

 

How to change active database in MySQL

To change or switch DATABASE, run the same USE database_name query with the new database name that you wish to work on

USE database_name

How to import SQL file using CLI in MySQL?

 

Different methods to import .sql file in MySQL database

There are two ways to import .sql file in active database

  1. Import Files Using MySQL Shell
  2. Import Files Using source command

 

Method-1: Import Files Using MySQL Shell

First step is to change current directory on command line to bin folder where the database is reside in which the file to be imported using ‘cd’ command

 

Syntax to import .sql file using MySQL shell

mysql -u username -p database_name < path_to_mysql_file.sql

Example 1 : Import SQLQuery1.sql file which is located in D drive into dbcompany database

Advertisement
Mysql 	–u 	root	 –p 	dbcompany 	< 	“D:\SQLQuery1.sql”

How to import SQL file using CLI in MySQL?

  • -u: is the flag that specifies the MySQL
  • -p: is the flag indicates that you should be prompted to enter a password once the command is executed.
  • <: this symbol indicates that we want to import the .sql file to the specified MySQL database
  • dbcompany: is the name of the database in which .sql files will be imported
  • “D:\SQLQUERY1.sql” is the complete path to the SQL dump file to be imported

OUTPUT:

How to import SQL file using CLI in MySQL?

 

Method-2: Import Files Using source command

In MySQL we can import file using source command also. We can execute an SQL script file using the source command or \. Command.

The source command is a command line client specific command which MySQL Workbench does not support. Source command is used to load the .sql file and to run it

First step is to open MySQL command line client

How to import SQL file using CLI in MySQL?

Example 2: Import SQLQuery1.sql file in dbcompany database using source command

Use dbcompany

Source d:/SQLQuery1.sql

dbcompany: is the database in which .sql file will be imported

Advertisement

D:/SQLQuery1.sql : is the .sql file to be imported

OUTPUT:

How to import SQL file using CLI in MySQL?

 

Summary

In this article on MySQL import sql file using command line , We are covered overview of .sql file , steps to import .sql file , how to open MySQL command line , how to change current database using USE command , and also explained two ways to import .sql files import using MySQL shell and import using MySQL source command  with practical examples.

 

References

Getting started with SQL

 

Read More

MySQL Batch Commands

 

Categories SQL

Didn't find what you were looking for? Perform a quick search across GoLinuxCloud

If my articles on GoLinuxCloud has helped you, kindly consider buying me a coffee as a token of appreciation.

Buy GoLinuxCloud a Coffee

For any other feedbacks or questions you can either use the comments section or contact me form.

Thank You for your support!!

Leave a Comment

X