Big MySQL dump import

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.