How to Run SQL File in MySQL from Command Line or Terminal

Running an SQL file in MySQL through the command line or terminal is a streamlined process that allows you to execute a series of SQL statements stored in a file. This method is particularly useful for applying scripts, batch processing, or managing databases without a graphical interface.

Accessing MySQL Command Line

To begin, access the MySQL command line interface:

mysql -u username -p

Replace username with your MySQL username. You will be prompted to enter your password.

Executing SQL File

Once logged in, use the source command to run the SQL file:

source path/to/your/file.sql;

Alternatively, you can execute the SQL file directly from the terminal without entering the MySQL shell:

mysql -u username -p database_name < path/to/your/file.sql

In this command, database_name is the name of the database where you want to execute the SQL file. The < symbol is used to redirect the content of your SQL file to the MySQL command.

Handling Large SQL Files

For large SQL files, you might encounter memory issues. To handle this, use the --max_allowed_packet flag:

mysql --max_allowed_packet=64M -u username -p database_name < path/to/your/file.sql

Adjust the 64M to a suitable value depending on your file size.

Verifying Execution

After running your SQL file, it’s good practice to verify the execution. You can do this by querying the affected tables or checking for expected changes in the database.

Error Handling

If errors occur during execution, MySQL will display them. For a more detailed error log, redirect the output to a file:

mysql -u username -p database_name < path/to/your/file.sql > output.log 2>&1

This command will capture both standard output and error messages in output.log.

Automating with Scripts

You can automate the execution of SQL files by incorporating these commands into shell scripts or batch files. This is particularly useful for routine database operations.

If this query pattern is part of recurring reporting, Basedash helps you turn it into reusable, AI-native BI workflows: prompt-to-SQL, shared dashboards, and trusted answers that stay aligned with your data model.

By following these guidelines, you can efficiently execute SQL files in MySQL using the command line or terminal, streamlining your database management tasks.