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.

I'm not using database_name literally. It's just a place holder for the actual name of the database I'm wanting to import to. I've been trying to use the commands from the mysql console rather than cmd because I get errors relating to mysql not being a recognized command or < being reserved for future use when I try to execute from cmd.
– Daniel NillMay 28 '11 at 18:57

I get the same error mentioned by Daniel Nill: < is reserved for future versions. How can I get around that?
– AlexSep 29 '14 at 14:43

1

ERROR: ASCII '\0' appeared in the statement, but this is not allowed unless option --binary-mode is enabled and mysql is run in non-interactive mode. Set --binary-mode to 1 if ASCII '\0' is expected. Query: ''. Can you tell me a solution to this?
– Suraj JadhavJan 18 '15 at 7:06

Alternatively you can add C:\xampp\mysql\bin to your PATH and use mysql anywhere
– PiX06Aug 17 '16 at 14:34

If you encounter the error '< is reserved for future use' it is because of powershell. Try to run this command from the cmd (or launch a cmd interpreter from within the powershell by typing cmd). Worked in my case (win 10)
– ReirepDec 26 '18 at 0:40

Thanks. I was looking for how spaces were handled and your solution came up despite it being slightly off topic. Both forward / and backward `\` slashes worked just fine for me as well. No single quotes, no double quotes. Thanks.
– tresfJul 30 '18 at 20:22

For me, it gives help message and no error.
– Satya PrakashOct 14 '13 at 8:27

2

Ah!, it does not required a semi colon at the last. Now, it is back to same error in import. Is there any error suppressing method!
– Satya PrakashOct 14 '13 at 8:37

I remember that I was using command line more than these phpmyadmin etc. Wasted so many hrs. Just used mysqldump to export and then mysql ... to import and it worked like a charm. phpMyAdmin was not exporting correctly.
– Satya PrakashOct 14 '13 at 8:56

1

you are correct. I tried to insert 1.3M records using source command. It took more than 2 hours.
– JanathMay 14 '18 at 8:42

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

This saved my day. I had the problem that the mysql server had gone away while I tried to import the file the "normal way" by using: mysql -u username -p database < file.sql. So thanks for providing an alternative way.
– ThatMSGDec 1 '16 at 11:32