MySQL Script: 3 Easy Ways to Run SQL Commands in a File

Do you want to run a MySQL script from a file? Creating and running a SQL script is pretty routine. You will do this sooner or later in your job as a MySQL enthusiast.

So, budding MySQL administrators and developers will find this helpful.

By the end of this article, you will learn the following:

Creating a MySQL Script
How to Run a .SQL Script File in MySQL
Method 1: Run it From the Terminal
Method 2: Run it From the MySQL Prompt
Method 3: Use a MySQL GUI Client

So, let's dive in.

Creating a MySQL Script

A SQL script in MySQL is a series of SQL statements in a file with a .SQL extension. You can use any SQL statement under the sun like:

CREATE DATABASE,
USE ,
SELECT,
INSERT,
UPDATE,
DELETE,
CALL
and others

The file is readable from any text editor because it is a text file.

And when MySQL reads the file, it will execute each statement. So, instead of typing each statement, save the commands in a file for later execution.

But like any statement with the wrong syntax, MySQL will throw an error if it encounters an error in your .SQL file.

Here's an example of a MySQL script that will add a new record in a table and query it afterward:

USE sakila;

INSERT INTO actor (first_name, last_name, last_update)

VALUES ('Chris', 'Evans', NOW());

SELECT * FROM actor ORDER BY actor_id DESC LIMIT 1;

The script above uses the actor table in the sakila database. The USE statement switches to that database. Then, MySQL adds a record to the actor table. And finally, it queries the record to verify the successful insert.

Creating a file with the statements above is easy using your favorite text editor. Here's an example using the GNU nano text editor in Ubuntu 22.04.

MySQL Script: 3 Easy Ways to Run SQL Commands in a File

And I named the script insert-actor.sql. Let's try running this script later when we get to the different methods.

Another option in various operating systems is to use MySQL Workbench. Here's a screenshot of the same script:

Better yet, you can use dbForge Studio for MySQL. Here's a screenshot of the same script using this GUI tool:

When the file is ready, it's time to run the script.

How to Run a .SQL Script File in MySQL

There are various ways to run the SQL script we made earlier. You can run it from any operating system's Terminal. MySQL also has a command-line utility in different operating systems supported. You can run the script from there.

But if you are not a fan of the command-line interface, you can use MySQL database editor. You can run the script with a few clicks using GUI tools.

Next, let's discuss the 3 methods to see how this works.

Method 1: Run MySQL Script From the Terminal

It's easy to run a script in a terminal. You can use this in repetitive runs in batch mode.

So, here's how to do it. First, open the Terminal of your operating system where you installed MySQL. Then, type and run:

$ mysql -u mysql_user -p < sql_script_file

Supply the mysql_user with a valid MySQL user in your database server. And use an existing .SQL file with the full path and filename for the sql_script_file. And because of the -p option, MySQL will ask you for the password for the local server. Then, after you supply the correct password, the script will run.

See a sample below using Ubuntu 22.04:

(Photo : MySQL Script: 3 Easy Ways to Run SQL Commands in a File)

The output is the new row in the actor table.

If you need to run a script against another host, use the -h option. Here's an example:

$ mysql -h host -u mysql_user -p < sql_script_file

Where the host is an IP address or a hostname.

Let's have another example. The output will also display the statement to run using the -verbose option.

You can also output the rows into an XML file with the -X option. Here's the code:

$ mysql -u root -p -X < ~/Source/sql/script.sql > ~/Source/sql/actors.xml

Here it is in action:

(Photo : MySQL Script: 3 Easy Ways to Run SQL Commands in a File)

The final frames show the XML file opened from a text editor.

Finally, if your SQL script has a lot of text output, you can use a pager to avoid scrolling off the top of your screen. See an example below:

$ mysql < batch-file-with-lots-of-output.sql | more

Method 2: Run a Script From the MySQL Prompt

This method still uses the Terminal. But the difference is running the MySQL Client command-line utility first. And then run the script from there using the source command. This method is useful if you want to run further MySQL commands to check your output.

First, open the Terminal then run:

$ mysql -u mysql_user -p

Replace mysql_user with a valid user in the MySQL server.

Then, enter the password. Once you see the MySQL prompt, run:

mysql> source sql_script_file

Replace sql_script_file with the full path and filename of the SQL script. Here's an example:

(Photo : MySQL Script: 3 Easy Ways to Run SQL Commands in a File)

Notice the difference in the output compared to Method #1. Result sets are within a table.

Method #3: Use a MySQL GUI Client

In this method, we will use 2 known MySQL GUI tools: MySQL Workbench and dbForge Studio for MySQL.

Using MySQL Workbench

This is the MySQL GUI tool by Oracle. MySQL Workbench has basic features needed by administrators, developers, and designers. It would be best if you use the MySQL Workbench that will work for your version of MySQL server.

The following are the steps to run SQL scripts in MySQL Workbench:

Open MySQL Workbench.
Connect to your MySQL Server. You can create a new MySQL connection or click an existing connection.
Open the .SQL file.
Finally, press CTRL-Shift-Enter to run. Or click the Query menu then select Execute (All or Selection). Or click the lightning icon of the SQL window.

See it in action below:

(Photo : MySQL Script: 3 Easy Ways to Run SQL Commands in a File)

Using dbForge Studio for MySQL

dbForge Studio for MySQL is a top-of-the-line GUI tool from Devart. Creating SQL scripts is a breeze with this tool. You have autocompletion and code suggestions. Also, a data generator, a query profiler, and more. This tool is a lifesaver for administrators, developers, and database designers.

The following are the steps to run scripts in dbForge Studio for MySQL:

Open dbForge Studio for MySQL.
You have 2 options to run a SQL script:
- From the Start Page
  1. Click SQL Development -> Execute Script...
  2. Specify the MySQL connection, database (optional), and the SQL file you want.
  3. Click Execute.
- From the File menu
  1. Click File -> Open File
  2. Select the folder and .SQL file.
  3. Click Open.
  4. Press F5 or click Execute from the toolbar.