Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to solve the problem of Mysql importing large-capacity SQL file data, which has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to know about it.

Mysql may have the following two problems when importing sql files:

1. If the sql file is too large, there will be a "MySQL server has gone away" problem

two。 If the sql file data is in Chinese, garbled www.2cto.com will appear.

Resolve the problem:

Problem 1: the MySQL server has gone away problem occurs because the default "max_allowed_packet" variable of mysql is too small.

View current configuration

Show VARIABLES like'% max_allowed_packet%'

The result displayed is:

+-+ +

| | Variable_name | Value |

+-+ +

| | max_allowed_packet | 1048576 | |

+-+ +

The current configuration is 1048576, 1024, 1024, 1024, 1m.

Modify the max_allowed_ packet value:

Method 1: SET GLOBAL max_allowed_packet = 500,1024,1024; (invalid by test)

Method 2: directly modify the configuration file and restart mysql www.2cto.com

Modify the my.ini file in the, and modify the my.cnf file in linux.

C:\ Documents and Settings\ All Users\ Application Data\ MySQL\ MySQL Server 5.5\ my.ini (specified data file directory at installation time)

After restarting mysql, check the modified max_allowed_ packet value

Question 2: specify character set encoding when logging in

Mysql-uroot-P3308-p123456-- default-character-set=utf8 (- P means the specified port number)

Finally, you can successfully import through the source command:

Source E:ydj\ test.sql

Thank you for reading this article carefully. I hope the article "how to solve the problem of importing large-capacity SQL file data into Mysql" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.