[mysql] How do you use MySQL's source command to import large files in windows

I have a large (~150mb) sql file that I am trying to import. It is too large to do it through PHPMyAdmin even splitting it into many pieces and it is too large to import through a php script as it times out after 30 seconds of processing the script. So I'm looking for how to directly import the file from MySQL command line.

Searching online shows that I want to either use database_name < file_name.sql or source file_name.sql but I can't get either of these to work.

Using < gives the generic MySQL syntax error while using source give a slightly more promising failed to open file 'file_name.sql', error: 2 so I am inclined to think that the source command is on the right track.

I am in windows and am using xampp as a localhost server (note I'm only trying to import this file on the localhost so that I can execute the sql). I've tried placing the file in xampp\mysql\bin and xampp\mysql\data\database_name.

Any suggestions of how to import this .sql file into MySQL either from the MySQL command line or by any other means would be greatly appreciated.

This question is related to mysql import xampp

The answer is


C:\xampp\mysql\bin\mysql -u root -p testdatabase < C:\Users\Juan\Desktop\databasebackup.sql

That worked for me to import 400MB file into my database.


On Windows this should work (note the forwardslash and that the whole path is not quoted and that spaces are allowed)

USE yourdb;

SOURCE D:/My Folder with spaces/Folder/filetoimport.sql;


Option 1. you can do this using single cmd where D is my xampp or wampp install folder so i use this where mysql.exe install and second option database name and last is sql file so replace it as your then run this

D:\xampp\mysql\bin\mysql.exe -u root -p databse_name < D:\yoursqlfile.sql

Option 1 for wampp

D:\wamp64\bin\mysql\mysql5.7.14\bin\mysql.exe -u root -p databse_name< D:\yoursqlfile.sql

change your folder and mysql version

Option 2 Suppose your current path is which is showing command prompt

C:\Users\shafiq;

then change directory using cd.. then goto your mysql directory where your xampp installed. Then cd.. for change directory. then go to bin folder.

C:\xampp\mysql\bin;

C:\xampp\mysql\bin\mysql -u {username} -p {database password}.then please enter when you see enter password in command prompt.

choose database using

mysql->use test (where database name test) 

then put in source sql in bin folder.

then last command will be

mysql-> source test.sql (where test.sql is file name which need to import)

then press enter

This is full command

C:\Users\shafiq;
C:\xampp\mysql\bin
C:\xampp\mysql\bin\mysql -u {username} -p {database password}
 mysql-> use test
 mysql->source test.sql

Don't use "source", it's designed to run a small number of sql queries and display the output, not to import large databases.

I use Wamp Developer (not XAMPP) but it should be the same.

What you want to do is use the MySQL Client to do the work for you.

  1. Make sure MySQL is running.
  2. Create your database via phpMyAdmin or the MySQL shell.
  3. Then, run cmd.exe, and change to the directory your sql file is located in.
  4. Execute: mysql -u root -p database_name_here < dump_file_name_here.sql
  5. Substitute in your database name and dump file name.
  6. Enter your MySQL root account password when prompted (if no password set, remove the "-p" switch).

This assumes that mysql.exe can be located via the environmental path, and that sql file is located in the directory you are running this from. Otherwise, use full paths.


On windows: Use explorer to navigate to the folder with the .sql file. Type cmd in the top address bar. Cmd will open. Type:

"C:\path\to\mysql.exe" -u "your_username" -p "your password" < "name_of_your_sql_file.sql"

Wait a bit and the sql file will have been executed on your database. Confirmed to work with MariaDB in feb 2018.


On my Xampp set-up I was able to use the following to import a database into MySQL:

C:\xampp\mysql\bin\mysql -u {username goes here} -p {leave password blank} {database name} < /path/to/file.sql [enter]

My personal experience on my local machine was as follows:
Username: Root
Database Name: testdatabase
SQL File Location: databasebackup.sql is located on my desktop

C:\xampp\mysql\bin\mysql -u root -p testdatabase < C:\Users\Juan\Desktop\databasebackup.sql 

That worked for me to import my 1GB+ file into my database.


use mysql source command to avoid redirection failures, especially on windows.

mysql [-u <username>] [-p<password>] <databasename> -e "source /path/to/dump.sql"

where e for "Execute command"

On Windows, please remember to use double quote for sql command.

However, either backslash \ or slash / will work on Windows.


Username as root without password

mysql -h localhost -u root databasename < dump.sql

I have faced the problem on my local host as i don't have any password for root user. You can use it without -p password as above. If it ask for password, just hit enter.


Hello I had the same problem but I tried many different states and I came to it: SOURCE doesn't work with ; at the end in my case:

SOURCE D:\Barname-Narmafzar\computer programming's languages\SQL\MySQL\dataAug-12-2019\dataAug-12-2019.sql;

and the error was:

ERROR: Unknown command '\B'. '> it also didn't work with a quotation for the address. But it works without ; at the end:

SOURCE D:\Barname-Narmafzar\computer programming's languages\SQL\MySQL\dataAug-12-2019\dataAug-12-2019.sql

But remember to use USE database_name; before that. I think it's so because the SOURCE or USE or HELP are for the Mysql itself and they are not such query codes although when you write HELP it says:

"Note that all text commands must be first on line and end with ; ".

but here doesn't work.

I should say that I have done it in CMD and I didn't try it in Mysql Workbench. That was it

This is the result


For importing a large SQL file using the command line in MySQL.

First go to file path at the command line. Then,

Option 1:

mysql -u {user_name} -p{password} {database_name}  < your_file.sql

It's give a warning mesaage : Using a password on the command line interface can be insecure.

Done.Your file will be imported.

Option 2:

mysql -u {user_name} -p {database_name}  < your_file.sql

in this you are not provide sql password then they asked for password just enter password and your file will be imported.


Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to import

Import functions from another js file. Javascript The difference between "require(x)" and "import x" pytest cannot import module while python can How to import an Excel file into SQL Server? When should I use curly braces for ES6 import? How to import a JSON file in ECMAScript 6? Python: Importing urllib.quote importing external ".txt" file in python beyond top level package error in relative import Reading tab-delimited file with Pandas - works on Windows, but not on Mac

Examples related to xampp

Xampp localhost/dashboard phpMyAdmin ERROR: mysqli_real_connect(): (HY000/1045): Access denied for user 'pma'@'localhost' (using password: NO) Is there way to use two PHP versions in XAMPP? Where to find htdocs in XAMPP Mac phpMyAdmin access denied for user 'root'@'localhost' (using password: NO) How to downgrade php from 7.1.1 to 5.6 in xampp 7.1.1? How to turn on/off MySQL strict mode in localhost (xampp)? mysqli_connect(): (HY000/2002): No connection could be made because the target machine actively refused it How to install mcrypt extension in xampp Fatal error: Uncaught Error: Call to undefined function mysql_connect()