MySQL: Running SQL Scripts From the Command Line

MySQL can be accessed from the command line interface (CLI). Here’s how to run SQL scripts from the CLI.

When the server for MySQL is installed on a system, a command line client application program is also installed mysql . On Windows systems this is mysql.exe. The client app, mysql.exe, provides a terminal emulator function, enabling interactive input of SQL statements. For more information on the MySQL terminal emulator and use with Windows Terminal, see MySQL and Windows Terminal .

The same app also provides the capability for it to be called and execute a SQL script, straight from the command line or in a script.

Executing SQL Scripts from the Command Line

PowerShell

Example of running an SQL script in MySQL from PowerShell:

1Get-Content myScript.sql | & 'C:\Program Files\MySQL\MySQL Server 8.0\bin\mysql.exe' --verbose --force --table --port=3306 --host=127.0.0.1 --local_infile=1 --user=root -pdigimark > myScriptResults.txt

CMD

Example of running an SQL script in MySQL from CMD:

1"C:\Program Files\MySQL\MySQL Server 8.0\bin\mysql.exe" --verbose --table --force --port=3306 --host=127.0.0.1 --local_infile=1 --user=root -pdigimark < myScript.sql > myScriptResults.txt

Explanation of Command Line Parameters

When calling mysql.exe, several parameters are available, see 4.5.1.1 mysql Client Options .

MySQL Password

In addition to the parameters discussed in MySQL and Windows Terminal , consideration needs to be given to the password required for the MySQL service. For an interactive MySQL terminal, the user can enter the password. To enable a fully automated execution of a script, where the user may not be present, interactive input of the password may not be practical.

It is possible to include password as a parameter, using ‘-p’. For example, if the password is digimark, the syntax would be ‘-pdigimark’. Clearly there are security implications with this method, you may want to consider if this is a suitable option in your use case.

Redirecting the Results of the SQL Script To a Text File or Log File

Appending the ‘>’ symbol along with a file name will direct the results of the SQL script to that file name, for example ‘> myScriptResults.txt’.

Including the SQL Script as a Parameter

This varies depending on if you are using PowerShell or CMD. For CMD, pipe the SQL script as input with ‘<’ How-to: Redirection , along with the name of the script, such as, ‘< myScript.sql’. For PowerShell, use the Get-Content command, for example ‘Get-Content myScript.sql |’.

Other Parameters

These are some optional parameters I use:

Example SQL Script

Here’s an example SQL script. To create it:

1. Open VSCode, any IDE or text editor (such as Notepad).
2. Copy the code below and paste it into the editor.
3. Save the file, with a “.sql” extension, for example “myScript.sql”

 1#######################################################
 2#
 3# This SQL script prepares database exampleDB for use.
 4# The existing database is dropped, a new db is
 5# created, tables are then added and populated with
 6# data.
 7#
 8#######################################################
 9
10# Output the date and time the script was run.
11select "Output from script, run at: " as 'Script Information',
12    NOW() as 'Date and Time Executed';
13
14# First, drop the database (if it exists).
15DROP DATABASE exampleDB;
16
17# Create a new, empty database.
18CREATE DATABASE exampleDB;
19
20# Use the new database.
21USE exampleDB;
22
23#######################################################
24# Create table: itemType
25#######################################################
26
27CREATE TABLE itemType (
28    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
29    description VARCHAR(100) NOT NULL,
30    loanDuration INT NOT NULL,
31    created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
32    PRIMARY KEY (id)
33    );
34
35# Add some item types to table: itemType
36INSERT INTO itemType(description, loanDuration)
37    VALUES
38    ('Raspberry Pi Kits', 28),
39    ('Raspberry Pi Hats & Microcontrollers', 28),
40    ('Raspberry Pi Books', 56)
41    ;
42
43SELECT * FROM itemType;
44#######################################################