Executing SQL from a File — The source Command

Writing SQL interactively is fine for quick queries, but real-world development means writing scripts in .sql files — version-controlled, repeatable, and shareable. This chapter covers every way to run a SQL file against a MySQL database, from the interactive source command to the powerful mysql CLI approach used in CI/CD pipelines.

Why Use SQL Script Files?

Benefits of Script-Based SQL Execution
①
Repeatability
Run the same setup script on dev, staging, and production with identical results.
②
Version Control
Store .sql files in Git — track every schema change with a commit message and timestamp.
③
Automation
Execute scripts in CI/CD pipelines, Docker entrypoints, and deployment scripts with zero manual steps.
④
Team Collaboration
Share migrations and seed data as files — anyone can run them to get an identical database state.

Method 1 — The source Command (MySQL CLI)

The source command (shortcut: \.) is used inside an active MySQL interactive session. It reads a .sql file from disk and executes every statement in it sequentially.

Syntax
source /path/to/your/script.sql
— or shorthand:
\. /path/to/your/script.sql
MySQL CLI — source Command 
 1-- First, connect to MySQL and select your database
 2-- mysql -u root -p
 3
 4USE ecommerce_db;
 5
 6-- Execute a script using the full path (Linux/macOS)
 7source /home/user/projects/ecommerce/schema.sql
 8
 9-- Windows path (use forward slashes or escape backslashes)
10source C:/Users/Rahul/Desktop/schema.sql
11
12-- Shorthand alias — identical to source
13\. /home/user/projects/ecommerce/seed_data.sql
14
15-- Relative paths work from wherever you launched the mysql client
16source ./migrations/001_add_orders_table.sql

How source executes: MySQL reads the file line by line, sending each complete statement (terminated by ;) to the engine. If any statement fails, MySQL prints the error and continues with the next statement — it does not stop on error unless you have configured otherwise.

Method 2 — mysql CLI Pipe (Non-Interactive)

The most powerful and widely-used method for automation. You run the mysql binary from your operating system terminal — no interactive session required. This is how CI/CD pipelines, Docker setup scripts, and deployment tools execute SQL.

Terminal (Bash / CMD) — mysql CLI 
 1# Basic: run a .sql file against a specific database
 2mysql -u root -p ecommerce_db < schema.sql
 3
 4# Pass the password inline (convenient, but avoid in scripts — security risk)
 5mysql -u root -pYourPassword ecommerce_db < schema.sql
 6
 7# Using --host for a remote MySQL server
 8mysql -h 192.168.1.100 -u admin -p ecommerce_db < schema.sql
 9
10# Redirect output to a log file for review
11mysql -u root -p ecommerce_db < schema.sql > output.log 2>&1
12
13# If the .sql file already contains USE db_name; you can omit the db arg
14mysql -u root -p < full_setup.sql
15
16# Stop on first error (important for migrations!)
17mysql -u root -p --force=false ecommerce_db < migration.sql

The Three Methods at a Glance

source / \.
Used inside an active MySQL session.
Good for ad-hoc script execution during development.
Example: source ./schema.sql
mysql < file
Used from the OS terminal, no open session needed.
Best for automation, CI/CD, Docker.
Example: mysql -u root -p db < file.sql
Workbench / GUI
Open a .sql file in MySQL Workbench via File → Open SQL Script, then click Execute (⚡). Great for visual development.

A Complete Example Script

Here’s a typical setup.sql file structured as a rerunnable database setup script:

SQL — setup.sql (Rerunnable Script) 
 1-- ============================================================
 2-- setup.sql — ecommerce_db full setup
 3-- Run: mysql -u root -p < setup.sql
 4-- ============================================================
 5
 6-- Create database (safe to re-run)
 7CREATE DATABASE IF NOT EXISTS ecommerce_db
 8    CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
 9
10USE ecommerce_db;
11
12-- Disable FK checks during bulk table creation
13SET FOREIGN_KEY_CHECKS = 0;
14
15DROP TABLE IF EXISTS orders;
16DROP TABLE IF EXISTS customers;
17
18CREATE TABLE customers (
19    customer_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
20    email       VARCHAR(255) NOT NULL UNIQUE
21);
22
23-- Re-enable FK checks
24SET FOREIGN_KEY_CHECKS = 1;
25
26SELECT 'Setup complete!' AS status;

⚠️ Tip: Always include USE database_name; at the top of every .sql file. When the file is executed via mysql < file.sql without specifying a database on the command line, it will know exactly where to run.

Key Takeaways

  • The source /path/to/file.sql command executes a SQL file from within an active MySQL interactive session. The shorthand is \.
  • mysql -u user -p database < file.sql runs a script non-interactively from the OS terminal — ideal for automation and CI/CD.
  • Always start scripts with USE database_name; and use IF EXISTS / IF NOT EXISTS to make scripts safely rerunnable.
  • MySQL continues executing after errors by default when using source — review output carefully for any errors that occurred mid-script.
  • Use SET FOREIGN_KEY_CHECKS = 0; before bulk table drops in a setup script, and re-enable with = 1 at the end.

What’s Next?

We’ve completed Section 2. In Section 3 — Data Types in MySQL, we dive into the full type system — numeric, string, binary, date/time, JSON, spatial, and more — so you can choose the right column type for every situation.