“Scripts are an incredible feature for a database administrator. They allow you to specify and execute a set of instructions in sequential order on a given server. This makes the process of automating repetitive tasks much more accessible and approachable.
However, when creating and executing SQL scripts, you will often carry out the operations in a development editor such as MySQL Workbench, Jetbrains DataGrip, etc. Therefore, it can be challenging to determine how to run SQL scripts in an environment where you can access a terminal session.”
In this short post, we will show you how you can execute SQL scripts stored in a file straight from the terminal.
Let’s jump in.
Method 1 – Run a SQL Script From an Active Session
Suppose we have a script that changes the database to sakila, sets the session timezone, and finally fetches the first ten records from the customer table.
Start by creating the file ending in .sql extension
We can then add the instructions of the script as shown:
set @@session.time_zone = 'America/Los_Angeles';
select * from sakila.customer limit 10;
Save the file and close the editor.
We can use the source or backlash command to run the script in an already active session. For example, start by connecting to the server:
Then run the script as shown:
You can also specify the path to the file using absolute or relative filepaths.
Running the command above will invoke the script and run all the instructions provided in the script.
Each instruction in the script is executed sequentially.
You can also use the backslash character to invoke the script as:
Similarly, if the file exists in a different location on your computer, you can specify the path in the command.
Method 2 – Run a SQL Script When Connecting to the Server
If you are not already connected to the server, you can run the script when connecting to the server by using the less-than-operator.
The command syntax is as shown:
For example, to run the script.sql file, we can run the command:
MySQL will take the instructions from the script and perform each instruction sequentially.
Conclusion
In this post, you discovered two main methods of executing SQL scripts from the terminal using the source and less than operators.
Thanks for reading!!
