Memor-X Memor-X - 2 years ago 415
MySQL Question

How to Prevent Connection Timeouts For Large MySQL Imports

During development, how out local wamp serves get up tp date data from the test server is that a dump of the database is made and we upload that dump using the source command to load the .sql file

recently, at the very end of the import we been getting errors about the @old variables which stored the original settings like foreign key constraints before their changed (so turning off foreign key constraints so that the import does throw errors when it recreates tables and attempts to create foreign keys when one of the tables have yet to be created), i have work out the cause being that the product table is getting more and more data and at a point the session has timeout during the import

i'm wondering, what setting can i set (either via query on in the my.ini file) that will stop all timeouts in effect making a session last forever while we are signed in

Answer Source

Strategies for importing large MySQL databases

PHPMyAdmin Import

Chances are if your reading this, PHPMyAdmin was not an option for your large MySQL database import. Nonetheless it is always worth a try right? Most common causes of failure for PHPMyAdmin imports is exceeding the import limit. If your working locally or have your own server, you can try changing the MySQL ini settings usually found in the my.ini file located in the MySQL install folder. If your working with WAMP on Windows, you can access that file using the WAMP control panel under MySQL > my.ini. Remember to restart WAMP so your new settings will be used. Settings you may want to increase here include:


Even with enhanced MySQL import settings you may still find that imports time out due to PHP settings. If you have access to PHP.ini, you can make edits to the maximum execution time and related settings. In WAMP access the PHP.ini file under the WAMP control panel at PHP > php.ini. Consider raising the limits on the following settings while trying large MySQL imports:


Using Big Dump staggered MySQL dump importer

If basic PHPMyAdmin importing does not work, you may want to try the Big Dump script from for staggered MySQL imports. What this useful script does is run your import in smaller blocks, which is exactly what is often needed to successfully import a large MySQL dump. It is a free download available at

The process of using Big Dump is fairly simply, you basically position your SQL import file and the Big Dump script together on the server, set a few configs in the Big Dump script and then run the script. Big Dump handles the rest!

On key point about this otherwise great option, is that it will not work at all on MySQL exports that contain extended inserts. So if you have the option to prevent extended inserts, try it. Otherwise you will have to use another method for importing your large MySQL file.

Go command line with MySQL console

If your running WAMP (and even if your not) there is always the option to cut to the chase and import your large MySQL database using the MySQL console. I'm importing a 4GB database this way as I write this post. Which is actually why I have some time to spend writing, because even this method takes time when you have a 4GB SQL file to import!

Some developers (usually me) are intimidated by opening up a black screen and typing cryptic commands into it. But it can be liberating, and when it comes to MySQL databases it often the best route to take. In WAMP we access the MySQL console from the WAMP control panel at MySQL > MySQL Console. Now let's learn the 2 simple MySQL Console commands you need to import a MySQL database, command-line style:

use db_name Command "use" followed by the database name will tell the MySQL console which database you want to use. If you have already setup the database you are importing to, then you start by issuing the use command. Suppose your database is named "my_great_database". In this case, issue the following command in the MySQL Console. Note that commands must end with a semi-colon. mysql-> use my_great_database;

source sql_import_file.sql Command "source" followed by the location of a SQL file will import the SQL file to the database you previously specified with the "use" command. You must provide the path, so if you using WAMP on your local server, start by putting the SQL file somewhere easy to get at such as C:\sql\my_import.sql. The full command with this example path would be:

mysql-> source C:\sql\my_import.sql;

After you run that command the SQL file should begin to be imported. Let the queries run and allow the import to complete before closing the MySQL console.

Further documentation for MySQL command line can be found here:

Another solution is to use MySQL Workbench.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download