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
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
mysql -uroot -p
Next, enter the password for your
Once inside, use the
\. 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
/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
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 (
<) operator as follows:
mysql -uroot -p database_name < /path/to/file.sql
For example, here’s how to run the same
main.sql script without connecting to the server:
mysql -uroot -p school_db < /Users/nsebhastian/Desktop/test/main.sql
Once again, the command line client will ask for a password to run the operation.
Here’s an example of the output in my terminal:
mysql -uroot -p school_db < /Users/nsebhastian/Desktop/test/main.sql Enter password: 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
As you can see from the output above, the
SELECT statement result set is a bit messy compared to when you run the script while being connected to the server.
But you won’t see any difference if your SQL script contains
Also, if you have a
USE database_name statement in your SQL file, you can omit the
database_name from the command line as shown below:
mysql -uroot -p < /Users/nsebhastian/Desktop/test/main.sql
The above example works because the
main.sql file contains the
USE school_db; statement.
USE statement, MySQL will throw the
No database selected error.
You have learned how to run SQL files or scripts from the terminal. Great job! 👍