MySQL database import large data volume SQL file failed solution

1. Before discussing this issue, first describe what is a "large data volume SQL file".

Export the SQL file. Select the database-----Right-click to select Dump SQL file-----Select structure and data. Save the file Db_mras.sql file.

2. Import the SQL file. Create a new database Db_mras in MySQL. Select Database-----Right-click to select "Run SQL File"-----Select File Db_mras.sql, run.

Now found that the run failed, prompting the error "MySQL server has gone away". The following solutions are proposed for this issue:

This error means that the link between the client and MySQL is broken, typically because SQL is running too long or the SQL file is too large.

Troubleshoot the problem cause:

(1) MySQL service is down.

Run command: Show global status like ' uptime ';  If the value of uptime is large, the MySQL service has not been restarted recently. If the log does not have the relevant information, indicating that the service has not been restarted, you can exclude this possibility.

(2) MySQL link timeout

Run command: Show global variables like '%timeout '; View the value of wait_timeout in the run result, typically 28800. On behalf of MySQL, the link closes after 28,800 seconds of mis-operation.

(3) MySQL file is too large

Run command: Show global variables like ' max_allowed_packet '; View the value of the run result Max_allowed_packet, if it is too small, you need to adjust the size.

Workaround:

At the end of MySQL My.ini file add the following words: wait_timeout=2880000;    interactive_time=2880000; max_allowed_packet=16m;

Where Max_allowed_packet represents the maximum length of the control buffer. Wait_timeout represents no action link wait time.

Restart the MySQL service after modifying the above parameters.

To see if the modification was successful: Run command: Show global variables like '%timeout '; Show global variables like ' max_allowed_packet ';

Tip: If you can't find the My.ini file, you can run the command: MySQL--help|grep my.ini to find the file path.

Note: If the above method does not solve your problem, you also need to check the space of your MySQL file installation disk is sufficient.

MySQL database import large data volume SQL file failed solution