Executing an SQL Script From the Command Line

1. Overview

MySQL is a popular open-source relational database management system that uses SQL (Structured Query Language) to manage data. Executing .sql scripts from Bash can be a useful way to automate database management tasks or migrate data from one server to another.

In this tutorial, we’ll discuss how to execute a .sql script for MySQL from Bash.

2. Connecting to the MySQL Server

Before executing a .sql script from Bash, we need to launch the MySQL client from the terminal. It allows us to interact with the MySQL server. Let’s run a command to connect to our server:

$ mysql -h 127.0.0.1 -P 3306 -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.32 MySQL Community Server - GPL
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

As part of the above command, we provided the hostname 127.0.0.1 and username root. Then, we’ll be prompted to enter the password so that it doesn’t appear in the command itself. Providing the correct credentials will allow us to connect to the server and start executing the SQL commands.

3. Executing the .sql Script

Executing .sql scripts in MySQL from the terminal is a very useful tool for database admins and developers. We can use this feature to run large numbers of DB queries from the command line all at once. Before proceeding, let’s create a .sql script containing an SQL command:

$ cat > /tmp/script.sql
show databases;

In this script.sql file, we add the SQL command, show databases. In the next sections, we’ll execute this script file in different ways.

3.1. Using the Native Method

The mysql command is generally used for interactive sessions, but it’s also capable of executing scripts from the terminal. More importantly, to run multiple commands, we need to put them into a script file. One key point to note is that all commands will be executed in the order that they appear in the script. To demonstrate, let’s examine the command to execute script.sql on the MySQL server running on 127.0.0.1:

$ mysql -h 127.0.0.1 -P 3306 -u root -p < /tmp/script.sql
Enter password: 
Database
information_schema
mysql
performance_schema
sys

The above command connects to the MySQL server using the user “root” and prompts for a password, which is essential to access the server. Upon successful connection, the script runs on the server. The < symbol redirects the contents of the file /tmp/script.sql to the MySQL command-line client so that the script commands can execute on the server. Overall, this command is a convenient way to execute a sequence of MySQL commands stored in a script file against a MySQL server.

3.2. Using the source Command

Another way to execute an SQL script in MySQL is to use the source command within the MySQL command-line interface. Using the source command, we can easily run the script without breaking the connection. Connecting to the server is the only requirement for the source command to run. Let’s first connect to the server:

$ mysql -h 127.0.0.1 -P 3306 -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
...
mysql> 

In the output of the above command, we can see that the connection is established successfully. Prior to executing the script, we should ensure that its commands are valid. Now, let’s run the source command:

mysql> source /tmp/script.sql
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

In the above command, we’ve provided the script file’s path. The source command lists all the databases present on the server.

4. Conclusion

In this article, we discussed how to execute a .sql script for MySQL from the Bash command line. First, we looked at the steps to connect to a MySQL server and also explored its various options. After that, we executed the .sql script using the native method and the source command.