Import large MySQL .sql file on Windows with Force

Question

I would like to import a 350MB MySQL .sql file on a Windows 7 machine. I usually do this by using

mysql -uuser -p -e "source c:/path/to/file.sql" database

since < doesn't work in Powershell.

My .sql file has an error in it this time. I'd prefer to just skip the bad row and continue the import. How can I force the import to continue on Windows?

On a unix/linux based system, I could use

mysql --force ... < file.sql

but --force doesn't seem to work with the -e "source ..." command (understandably so).

Thanks, Mike

I'm trying to import from my linux box with the host option (-h). While it will probably work, I'd prefer to do this locally. — getWeberForStackExchange
– getWeberForStackExchange, Commented Jun 15, 2011 at 22:35
Importing from the linux computer worked well. Still interested in a Windows only solution though! — getWeberForStackExchange
– getWeberForStackExchange, Commented Jun 15, 2011 at 23:11

Joel B Fant · Accepted Answer · 2011-06-16 00:18:15Z

14

You're probably going to have to have Powershell execute this in the standard console in order to use < properly. Technically you could use get-content and pipe the output to mysql, but I've always found that to be slow, and it somehow still keeps the file contents in memory of the Powershell session.

This is how I would execute it from the Powershell prompt (changed file path to include spaces to demonstrate inner quotes, just in case):

cmd /C 'mysql -uuser -p --force < "C:\path\with spaces\to\file.sql"'

[GC]::collect() would apparently clear it up the memory, but you can't do that until after it's done anyway. When it comes to mysql and mysqldump, I don't bother with Powershell. The default encoding used in > is Unicode, making dump files twice as big out of Powershell as out of cmd unless you remember to write | out-file dump.sql -enc ascii instead of > dump.sql.

edited Jun 16, 2011 at 0:18

answered Jun 15, 2011 at 23:16

Joel B Fant

24.8k4 gold badges70 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

getWeberForStackExchange Over a year ago

I noticed that with Get-Content and Powershell too. I was at 1.4 GB of RAM used before it ran out of memory.

Satya Prakash Over a year ago

For me --force is not working whatever i do. It stops

Joel B Fant Over a year ago

@SatyaPrakash: --force makes it continue if it encounters an error, but there are probably classes of errors from which it can't continue. You may have to actually fix your error instead of trying to ignore it.

Satya Prakash Over a year ago

Ya, for me it was phpmyadmin was not exporting correctly. I used mysql dump for export and import using mysql ... and promlem solved.

Community · Accepted Answer · 2017-05-23 12:17:54Z

2

I'd suggest to also have a look at this SO answer, that takes advantage of source SQL command:

edited May 23, 2017 at 12:17

CommunityBot

11 silver badge

answered Mar 7, 2014 at 14:43

superjos

12.8k6 gold badges94 silver badges143 bronze badges

Collectives™ on Stack Overflow

Import large MySQL .sql file on Windows with Force

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related