When you need to run a saved .sql file directly from the terminal, you can use the mysql command line client.
You can run SQL scripts with or without opening a connection to the MySQL server.
First, let’s see how to run SQL files while connected to a MySQL server
Run SQL file while connected to the server
For example, suppose you have a file named main.sql
with the following content:
USE school_db;
SELECT * FROM students;
The script will select a database named school_db and retrieve all rows from the students table.
To run SQL files from the terminal, you can use the source or the backslash and dot command [\.]
First, you need to connect to your MySQL database server using the mysql command. Here’s an example of connecting with the root user:
Next, enter the password
for your root user.
Once inside, use the source or \. command followed by the absolute path to the SQL file as shown below:
mysql> source /Users/nsebhastian/Desktop/test/main.sql
# or
mysql> \. /Users/nsebhastian/Desktop/test/main.sql
The path /Users/nsebhastian/Desktop/test/main.sql above needs to be changed to the SQL file path on your computer.
MySQL will print the output in the command line if any. Here’s an example of running the main.sql file in my terminal:
mysql> source /Users/nsebhastian/Desktop/test/main.sql
Database changed
+----+---------------+---------+-------+--------+
| id | name | topic | score | gender |
+----+---------------+---------+-------+--------+
| 1 | Mark Crane | Math | 7.00 | male |
| 2 | Natalia Smith | Math | 8.00 | female |
| 3 | Gary Anderson | Math | 0.01 | male |
| 4 | Joe Natsume | English | 2.50 | male |
| 5 | Sarah | Math | NULL | female |
| 6 | Peter | English | 6.00 | male |
| 7 | Nathan | English | 8.00 | male |
+----+---------------+---------+-------+--------+
7 rows in set [0.00 sec]
And that’s how you run SQL files from the terminal while being connected to MySQL database server.
Let’s see how you can run SQL files without having to connect to the server next.
Run SQL file while not connected to the server
The mysql command line client has the ability to run SQL scripts without needing to connect to MySQL database server.
You only need to provide the database_name option followed by the smaller than [