How to execute MySQL scripts (.sql) files

How to create a MySQL script file (.sql)

The MySQL script file is just a series of MySQL statements in a .sql file. The statements will get executed in order. For example, the following script will create a database called school, a table called students, and it will insert some data into the students table. It simply contains all of the statements that I’d otherwise execute normally in a MySQL prompt.

CREATE DATABASE school;
USE school;
 
CREATE TABLE students (
    ID MEDIUMINT NOT NULL AUTO_INCREMENT,
    FirstName varchar(255),
    LastName varchar(255),
    PRIMARY KEY (id)
);

INSERT INTO students (FirstName, LastName) VALUES ('Mike', 'Williams');

How to run a .sql script file

There are three (and probably more) methods of running a .sql script.

Method 1: Run it from the terminal

We can read MySQL statements from a script with (<) operator. Login to MySQL host server and run the school.sql like this

mysql -u root -p < school.sql

It will prompt you for the MySQL root password.

If you want to run your script against a specific database, add the database name before (<) operator:

mysql -u root -p database-name < yourscript.sql

Let’s verify our changes by logging in to our MySQL server:

mysql -u root -p

Check if the database is created:

SHOW DATABASES;

Output:

As we can see the school database has been created.

Let’s see what’s inside the table we created

SELECT * FROM school.students;

Output:

Method 2: Run it from the MySQL prompt

You can also run a .sql file from a MySQL prompt using the MySQL source command. Note that the script is located under /root/.

source /root/school.sql

output:

NOTE: If you want to run a script against a specific database, you change the database first and run source:

use school;
source /root/school.sql

Method 3: Using a MySQL visual client like Workbench

Applications like MySQL Workbench offer editers to write or import scripts for execution.

Download MySQL workbench and connect to your MySQL database server.

Go to File > Run SQL script.. choose the .sql file to execute, and click Run.

Conclusion

Using .sql scripts comes handy when executing long SQL statements. In this tutorial, we learned how to create and execute .sql files using different methods.