Complete Guide: Import SQL File Using MySQL Command Line

To import an SQL file using MySQL command line on Windows, you need to use the mysql command with proper syntax. The correct format is mysql -u username -p database_name < file.sql, and you should indeed create the database first before importing the data. The error you encountered was because Windows uses different command syntax than Linux systems.

Contents

• Basic Import Syntax
• Creating the Database First
• Alternative Import Methods
• Common Errors and Troubleshooting
• Advanced Import Options
• Best Practices

Basic Import Syntax

The fundamental command to import an SQL file into MySQL from the Windows command line requires using the mysql utility with specific parameters. Unlike Linux systems where you can use direct input redirection (<), Windows provides several options for importing SQL files.

Standard Import Command

The most basic syntax for importing an SQL file is:

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

For your specific case with the file on C drive:

mysql -u root -p my_database < C:\file.sql

Important note: The above command assumes you have MySQL in your system PATH. If not, you’ll need to use the full path to the MySQL executable.

Using Full Path to MySQL

If MySQL is not in your system PATH, use the full path to the executable:

"C:\Program Files\MySQL\MySQL Server X.X\bin\mysql.exe" -u username -p database_name < "C:\file.sql"

Creating the Database First

Yes, you must create the database before importing the data. An SQL import file typically contains CREATE TABLE statements and INSERT statements, but not CREATE DATABASE statements unless it specifically includes them.

Step-by-Step Database Creation

1. Connect to MySQL without specifying a database:

   cmd

   mysql -u root -p
   

2. Create the database:

   sql

   CREATE DATABASE your_database_name;
   

3. Select the database:

   sql

   USE your_database_name;
   

4. Exit MySQL:

   sql

   EXIT;
   

5. Now import the SQL file:

   cmd

   mysql -u root -p your_database_name < C:\file.sql
   

Alternative: Create Database During Import

Some SQL files include CREATE DATABASE statements. If your file doesn’t, you can create the database and import in one command:

mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS your_database_name;" && mysql -u root -p your_database_name < C:\file.sql

Alternative Import Methods

Method 1: Using Source Command (Inside MySQL)

If you prefer to work within the MySQL client:

mysql -u root -p

Then within the MySQL prompt:

USE your_database_name;
SOURCE C:/file.sql;

Note: The forward slash (/) works better in MySQL on Windows for paths.

Method 2: Using MySQL Utility

MySQL provides a dedicated import utility that can be more robust:

mysqlimport --ignore-lines=1 --local -u root -p your_database_name "C:\file.sql"

Method 3: For Large Files

For very large SQL files, consider using the mysql command with specific options:

"C:\Program Files\MySQL\MySQL Server X.X\bin\mysql.exe" --force -u username -p database_name < "C:\file.sql"

The --force option continues execution even if it encounters an error.

Common Errors and Troubleshooting

Error 1049: Unknown Database

This occurs when the database doesn’t exist. Always create the database first or ensure your SQL file contains a CREATE DATABASE statement.

Error 1045: Access Denied

This indicates incorrect username or password. Verify your credentials:

mysql -u your_username -p

Error 2002: Can’t Connect to MySQL Server

This typically means MySQL isn’t running or the connection parameters are wrong. Check:

• MySQL service status
• Host address (use localhost for local connections)
• Port number (default is 3306)

Path Issues on Windows

Windows paths with spaces can cause problems. Always enclose paths in quotes:

mysql -u root -p database_name "C:\Program Files\MySQL\my_backup.sql"

Character Set Issues

If you encounter encoding problems, specify the character set:

mysql --default-character-set=utf8 -u root -p database_name < "C:\file.sql"

Advanced Import Options

Performance Optimization for Large Files

For large SQL files, consider these performance-enhancing options:

"C:\Program Files\MySQL\MySQL Server X.X\bin\mysql.exe" -u root -p --max_allowed_packet=512M --net_buffer_length=16384 database_name < "C:\large_file.sql"

Logging Import Progress

To monitor import progress:

mysql -u root -p database_name < "C:\file.sql" > import_log.txt 2>&1

Skipping Lines or Comments

If your file contains comments or lines to skip:

mysql -u root -p database_name --init-command="SET SESSION FOREIGN_KEY_CHECKS=0;" < "C:\file.sql"

Best Practices

Before Importing

1. Backup your current database (if any):

   cmd

   mysqldump -u root -p current_database > backup.sql
   

2. Check the SQL file:

   • Open it in a text editor
   • Look for CREATE DATABASE statements
   • Check for syntax errors in the file itself

3. Verify MySQL is running:

   cmd

   net start | findstr MySQL
   

During Import

1. Use the --force option for problematic files:

   cmd

   mysql --force -u root -p database_name < "C:\file.sql"
   

2. Monitor system resources during large imports

After Importing

1. Verify the import:

   cmd

   mysql -u root -p -e "SHOW TABLES;" database_name
   

2. Check data integrity:

   sql

   SELECT COUNT(*) FROM important_table;
   

3. Test application functionality with the new database

Conclusion

Importing SQL files in MySQL on Windows requires understanding the correct command syntax and proper preparation. Always create the target database first unless your SQL file includes database creation statements. The most reliable method is using mysql -u username -p database_name < file.sql with proper path handling. For large files, use performance optimization options and monitoring. Remember to handle Windows-specific path issues by enclosing paths in quotes and using the correct MySQL executable path if it’s not in your system PATH. Always verify your import was successful by checking the database structure and data after completion.

Sources

1. MySQL Documentation - mysqlimport Utility
2. MySQL Documentation - mysql Client Options
3. MySQL Documentation - Database Import Methods
4. Stack Overflow - MySQL Import on Windows
5. DigitalOcean - How to Import and Export MySQL Databases