Solution for importing SQL files of large amounts of data from MYSQL database, mysqlsql

Solution for importing SQL files of large amounts of data from MYSQL database, mysqlsql

1. Before discussing this question, we will first introduce what is "large data SQL file ".

Export the SQL file. Select database ----- right-click and select "Dump SQL file" ----- select "structure and data ". Save the db_mras. SQL file.

2. Import the SQL file. Create a database db_mras in MYSQL. Select database ----- right-click and select "run SQL file" ----- select file db_mras. SQL to run.

If the running fails, the error "MySQL server has gone away" is displayed ". The following solutions are provided to address this problem:

This error indicates that the connection between the client and mysql is disconnected because the SQL running time is too long or the SQL file is too large.

Troubleshoot:

(1) mysql service is down

Run the command: show global status like 'uptime'; if the value of uptime is large, it indicates that the mysql service has not restarted recently. If there is no relevant information in the log, it indicates that the service has not been restarted, and this possibility can be ruled out.

(2) mysql connection timeout

Run the command: show global variables like '% timeout'; check the value of wait_timeout in the running result, generally 28800. It indicates that the link will be closed after 28800 seconds of misoperation.

(3) The mysql file is too large.

Run the command: show global variables like 'max _ allowed_packet '; view the value of max_allowed_packet in the running result. If the value is too small, you need to increase the value.

Solution:

Add the following sentence to the end of mysql my. ini file: wait_timeout = 2880000; interactive_time = 2880000; max_allowed_packet = 16 M;

Max_allowed_packet indicates the maximum length of the cache. Wait_timeout indicates no operation link wait time.

After modifying the preceding parameters, restart the mysql service.

Check whether the modification is successful: run the command: show global variables like '% timeout'; show global variables like 'max _ allowed_packet ';

TIPS: If the my. ini file cannot be found, run 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 whether your mysql file installation disk space is sufficient.