Recently i faced problem “How to upload over 100 MB mysql dump into database”. Obviously it cannot be done using phpMyAdmin, because file is to big, i could open the file and execute query after query, but it would take a lot of time, besides opening such a file is a problem by itself. So i started to search for a solution and i found wonderful solution.
Fortunately i was working on my localhost, so the easiest way to do it was to use mysql client from a command line it can be done with both Windows and Linux (don’t know how about Mac OS but probably this solution can work as well for them).
Big MySQL database import tutorial
On windows open command line and go to mysql “bin” directory (it is located there where MySQL is installed, also make sure that mysql.exe file is there). On Linux just open command line, in either case you are ready to go.
Now at the command line type:
mysql -u root
obviously you should replace ‘root’ with your username you use to connect to MySQL, if you are connecting to MySQL from PHP then credentials you use there should work just fine.
Using mysql command line allows to execute queries, so first we need to select database in case there is no such statement in the dump file. It is done with:
USE `database`
where `database` is a name of your database.
The last thing you need to do is import dump file, the fastest way is by using command ‘source’:
source /home/greg/dump.sql
I do not know where MySQL starts to look for a dump file so it is best to use absolute path.
Remember that, this solution works only and only if you have access to the command line, some hosts allow it some not, on a localhost you have it available by default.
7 Comments on "Big MySQL dump import"
Thanks. How would you do this for Linux?
Hi Jeff, this article is all about Linux, just open command line and type all three commands that are listed in the post.
“Now at the command line type:
mysql -u root”
This command won’t do unless there is no password for the root mysql user. Use “mysql -u root -p” isntead.
Lifesaver. Thanks!
Hey, tnx you very much !
Its real good post!
Very Good!
I’m from Brazil and I think I’ll translate it (in Portuguese) and post it in my blog (http:\\denecir.blogspot.com).
I’ll add a link for this post OK?
Very good, it’s been years since I did windows command line anything and you just saved my rear
Thanks!