How to Prevent Connection Timeouts During Large MySQL Imports?

How to Eliminate Connection Timeouts during Large MySQL Imports

When importing a large MySQL database dump, connection timeouts can often occur, especially towards the end of the process. This article presents several strategies to prevent such timeouts, ensuring a smooth and successful import.

PHPMyAdmin Import

• Adjust MySQL settings in my.ini: Increase max_allowed_packet, read_buffer_size.
• Modify PHP.ini settings: Increase max_execution_time, max_input_time, memory_limit (WAMP users: PHP > php.ini).

Big Dump Staggered MySQL Import

• Use the Big Dump script to import data in smaller increments, mitigating timeout issues.
• Note: Does not support extended inserts.

Command-Line Import via MySQL Console

• Access the MySQL console (WAMP users: MySQL > MySQL Console).

• Use the following commands:

  • use db_name; (Switch to the target database)
  • source sql_import_file.sql; (Import the SQL file)
• Provide the full path to the SQL file.

Other Options

• MySQL Workbench: A GUI tool that supports importing large databases.

• Disabling Timeout Settings:

  • Set innodb_lock_wait_timeout to 0 (MySQL 8 ) or interactive_timeout to 0 (MySQL 5.7) in the my.ini file.
  • Protect against accidental data loss by modifying these settings after the import is complete.

The above is the detailed content of How to Prevent Connection Timeouts During Large MySQL Imports?. For more information, please follow other related articles on the PHP Chinese website!