How to import more than 100000 records into a mysql database?

Question

I have a sql dump file. which has 185458 records in the table. for example

INSERT INTO `cities` (`city_id`, `city_name`, `countryId`, `stateid`, `countryCode`, `latitude`, `longitude`, `zip_code`) VALUES
(2, 'Djelfa', 67, 1, 'DZA', 34.67, 3.25, ''),
(3, 'Valenza', 115, 2, 'ITA', 45.02, 8.63, '15048'),
...
...
...
(185452, 'Bosendurnbach', 17, 771, 'AUT', 48.5, 15.77, ''),
(185453, 'Môlay', 79, 937, 'FRA', 47.73, 3.94, ''),
(185454, 'Miloszyce', 183, 422, 'POL', 51.05, 17.31, ''),
(185455, 'Lovce', 212, 698, 'SVK', 48.45, 18.37, ''),
(185456, 'Winchester', 174, 74, 'NZL', -44.2, 171.28, ''),
(185457, 'Wohlde', 62, 402, 'DEU', 54.4, 9.3, ''),
(185458, 'Chiavazza', 115, 2, 'ITA', 45.58, 8.07, '');

After long time i'm getting this error

Fatal error: Maximum execution time of 300 seconds exceeded in C:\xampp\phpMyAdmin\libraries\dbi\DBIMysqli.class.php on line 290

How can i import ?

arkascha arkascha · Accepted Answer · 2015-11-07T07:29:17

The error message is quite clear and points you into what the issue is: the limitation of execution time in your php environment. So some approaches to solve this are obvious:

increase the time limit of your environment

You can do that by either raising the limit in your php configuration or, if permitted, by dynamically increasing it inside your import script. So something like ini_set('max_execution_time', 3000). Both options are documented. The php documentation should always be the first location where you should start to look for an answer to such a question.

use a different php environment

Typically such limitation is chosen for a web environment to reduce the risks of serving requests from anyone out there. However nothing speaks against using a different configuration for another environment. Import jobs are typically not processed by using web requests, but by using php in the command line (CLI). For such typically a separate configuration is used. Again, this is documented. That is what you can use here to configure both environments different from each other according to your needs. However for this you need access to php on CLI. That is no issue on your own system, but usually not available on a cheap web hosting service.

split your import into smaller chunks

Since the data you import is stored in a sql file, so a simple text file, you can use any ordinary text editor to modify that file. Note: a text editor, not a word processor. You can split the big INSERT statement contained in there into several chunks. The syntax is quite obvious.

use many separate insert statements instead of one big one

Depending on the tool you use to create that dump file you are trying to import now you have an option to create the dump such that it uses many separate INSERT statements (one for each row) instead of one big, combined one. mysqldump for example offers the --skip-extended-insert flag for this. With such a dump file it is trivial to split the import into several smaller chunks by simply splitting the file.

bypass php for the import altogether

If you have a direct access to your database server (MySQL in this case), then you can simply interact directly with it instead of using the phpMyAdmin tool inbetween. You can simply load your dumpfile directly by means of MySQLs source command. That way you are completely independent from the php limitations.

How to import more than 100000 records into a mysql database?

4 Answers