MySQL and Dealing With Large Imports

How to get around the limits of MySQL and PHP when importing large SQL files.

Edited: 2012-07-19 19:50

Taking backups with phpMyAdmin by exporting the entire database to a single .sql file? Nothing wrong with that. It should work. But it just doesn't.

When your database reach a certain size, you won't be able to easily import it without changing a bunch of values. Which is what GUIs are for – but for whatever reason, MySQL, Apache, and PHP, insist on editing files manually. You either have to know where the settings are located in the configuration files, or know what to search for – the usability is in other words almost non-existent.

The settings you need to buff up are as follows.

max_allowed_packet – located in MySQL's my.ini, default is 1MB.

max_input_time – located in php.ini, default is 60 seconds.

post_max_size – located in php.ini, default is 8M

upload_max_filesize – located in php.ini

memory_limit – located in php.ini

max_allowed_packet is by far the most important setting, you should give this a good punch in the right direction – why not set it at 100M? Just change this so that you are sure the entire query will fit into the packet – you can always change it back when you are done importing. If you can't find it, add it to the mysqld section manually, I.e.

[mysqld]
max_allowed_packet = 100M

max_input_time is only important if you are uploading the file – I.e. From phpMyAdmin. You can also upload it to phpMyAdmin manually, which should get around this crazy limit, more about this later. It controls how long PHP should wait to receive the file.

post_max_size is also not important unless you are uploading the file. If uploading, give it a good punch as well, and limit the max post size in your upload scripts instead.

upload_max_filesize If you are uploading the file, be sure this is larger than the file you are uploading.

memory_limit should likely be large enough to contain the entire file, not relevant when importing from the console.

Importing From the Console

The easiest way to deal with the limits of PHP is to use the console – this will be cmd.exe on windows.

Launch cmd.exe, and type the following in the console, to logon to MySQL.

mysql -u USERNAME -pMYPASSWORD

Note. there is no space between -p and your password.

Then type the following two commands:

USE DatabaseName
SOURCE C:\filename.sql

This will run the inserts from your SQL file as if you had typed them manually.

Optional The below command might also work:

mysql -u USERNAME -pMYPASSWORD DATABASENAME < C:\filename.sql

Importing From phpMyAdmin

To get around most limits, you should configure the upload directory of phpMyAdmin. Search the config.inc.php file for $cfg['UploadDir'], and fill this variable out with the name of the uploads directory, placed in the root of your phpMyAdmin installation. I.e.

$cfg['UploadDir'] = 'uploads';

You should create the uploads directory in phpMyAdmin/uploads – files placed here will be available in a drop-down on the import page in phpMyAdmin.