How to run big SQL script in MySQL?

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 (<) 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 INSERT, UPDATE, or DELETE statements.

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.

Without the 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! 👍