How do you use MySQL s source command to import large files in windows

Question

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  lt  file name sql or source file name sql but I can t get either of these to work   Using  lt  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

User · Answer

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.

User · Answer

With xampp I think you need to use the full path at the command line  something like this  perhaps   C  xampp mysql bin mysql -u  username  -p  databasename   lt  file name sql

User · Answer

C  xampp mysql bin mysql -u root -p testdatabase  lt  C  Users Juan Desktop databasebackup sql   That worked for me to import 400MB file into my database

User · Answer

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    lt  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    lt  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

User · Answer

Username as root without password  mysql -h localhost -u root databasename  lt  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

User · Answer

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   lt   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

User · Answer

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

User · Answer

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         gt   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

User · Answer

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  lt  D  yoursqlfile sql   Option 1 for wampp  D  wamp64 bin mysql mysql5 7 14 bin mysql exe -u root -p databse name lt  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- gt use test  where database name test     then put in source sql in bin folder   then last command will be   mysql- gt  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- gt  use test  mysql- gt source test sql

User · Answer

use mysql source command to avoid redirection failures  especially on windows   mysql  -u  lt username gt    -p lt password gt    lt databasename gt  -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

User · Answer

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    Make sure MySQL is running  Create your database via phpMyAdmin or the MySQL shell  Then  run cmd exe  and change to the directory your sql file is located in  Execute  mysql -u root -p database name here  lt  dump file name here sql Substitute in your database name and dump file name  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

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

Examples related to mysql

Examples related to import

Examples related to xampp