How to import an SQL file using the command line in MySQL?

To import an SQL file into MySQL using the command line, follow these steps:

Step-by-Step Guide

1. Open a terminal (Linux/macOS) or Command Prompt/PowerShell (Windows).
2. Use the mysql command with the following syntax:
   • bash：mysql -h [host] -P [port] -u [username] -p [database_name] < [path/to/file.sql]
     • Replace placeholders with your details:
       • [host]: MySQL server host (default: localhost).
       • [port]: Port number (default: 3306).
       • [username]: MySQL user with privileges to execute the SQL file.
       • [database_name]: Target database name (omit if the SQL file includes CREATE DATABASE).
       • [path/to/file.sql]: Full path to your SQL file.
3. Enter your password when prompted.

Examples

Basic Import (Local Server)

bash

mysql -u root -p my_database < ~/Downloads/backup.sql

• Imports backup.sql into the my_database database. You’ll be prompted for the MySQL root password.

Import Without Specifying a Database

bash

mysql -u root -p < /path/to/full_dump.sql

• Use this if the SQL file includes CREATE DATABASE and USE statements.

Remote Server Import

bash

mysql -h 192.168.1.100 -P 3306 -u admin -p remote_db < data.sql

• Connects to a MySQL server at 192.168.1.100:3306 and imports data.sql into remote_db.

Key Notes

• Input Redirection (<): Directs the SQL file’s contents into the mysql client.
• Security: Avoid embedding passwords in the command (use -p to prompt securely).
• Large Files: Add --max_allowed_packet=1G to the command for big files (adjust size as needed).

Troubleshooting

• Access Denied: Ensure the user has privileges for the database.
• File Not Found: Use absolute paths (e.g., /home/user/file.sql or C:\path\to\file.sql).
• Syntax Errors: Validate the SQL file separately (e.g., via MySQL Workbench).

Alternative: Import from MySQL Shell

1. Log into MySQL:
   • bash：mysql -u root -p
2. Select the database and import:
   • sql：USE my_database; SOURCE /path/to/file.sql;

Choose the method that best fits your workflow!