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

To import an SQL file into a MySQL database using the command line, use the mysql command-line tool. Here’s a step-by-step guide with examples:

Basic Command

mysql -u [username] -p [database_name] < [file.sql]

Replace [username] with your MySQL username.
Replace [database_name] with the target database name.
Replace [file.sql] with the path to your SQL file.

Example:

mysql -u root -p my_database < /path/to/backup.sql

You will be prompted to enter the password for the MySQL user.

Options & Scenarios

1. Create the Database First (If Not Exists)

If the database doesn’t exist, create it before importing:

mysql -u root -p -e "CREATE DATABASE my_database"
mysql -u root -p my_database < backup.sql

2. Import Without Specifying a Database

If the SQL file includes CREATE DATABASE and USE statements:

mysql -u root -p < backup.sql

3. Force a Character Set

Specify a character set (e.g., utf8mb4):

mysql -u root -p --default-character-set=utf8mb4 my_database < backup.sql

4. Verbose Mode (View Progress)

Print executed SQL statements to the console:

mysql -u root -p --verbose my_database < backup.sql

5. Import a Compressed SQL File

Use zcat (for .gz files) or gunzip:

zcat backup.sql.gz | mysql -u root -p my_database

6. Remote MySQL Server

Add host and port details:

mysql -u root -p -h 192.168.1.100 -P 3306 my_database < backup.sql

7. Include the Password in the Command (Not Recommended)

⚠️ Security Risk: Avoid exposing passwords in command history.

mysql -u root -pYourPassword my_database < backup.sql

Troubleshooting

1. “Access Denied” Error

Ensure the user has privileges for the database:

mysql -u root -p -e "GRANT ALL PRIVILEGES ON my_database.* TO 'user'@'localhost';"

2. “No Database Selected” Error

Specify the database name in the command.

3. Large File Import

Increase the max_allowed_packet size in my.cnf:

[mysqld]
max_allowed_packet = 1G

Example Workflow

Create a Database:

   mysql -u root -p -e "CREATE DATABASE new_db"

Import Data:

   mysql -u root -p new_db < /data/dump.sql

Verify:

   mysql -u root -p -e "SHOW TABLES IN new_db"

Key Notes

Always back up your database before importing.
Ensure the SQL file is compatible with your MySQL/MariaDB version.
Use pv to monitor import progress for large files:

  pv backup.sql | mysql -u root -p my_database

This method works for both MySQL and MariaDB.