Mysql max_allowed_packet is automatically reset to 1024 final solution, and maxallowedpacket is reset

Mysql max_allowed_packet is automatically reset to 1024 final solution, and maxallowedpacket is reset

 

• Background:

One centOS machine in the test environment. Recently,"

Caused by: com.mysql.jdbc.PacketTooBigException: Packet for query is too large (1354 > 1024). You can change this value on the server by setting the max_allowed_packet' variable

", Record the ideas for solving the problem, and finally find the root cause of the problem: hacker intrusion, summed up experience.

 

• Ideas:

View max_allowed_packet:

Show global VARIABLES like '% max_allowed_packet %'; (Note: mysql system parameters are divided into session and global. session takes effect only for the current connection, and global connections take effect)

1). Through the mysql client, set global max_allowed_packet = 2*1024*1024*10; (after modification, the database will be restored to the default value after it is restarted)

2) modify my. cnf in the [mysqld] segment or mysql server configuration segment. (The final modification will restart the database after modification and take effect permanently)

As shown in the following figure: max_allowed_packet = 20 m takes effect through the client after method 2 is modified. However, after a while (sometimes several hours, sometimes 1 ~ 2 days), automatically changed to 1024. Thinking: google has discovered that hackers have been attacked and it is not believed because it is an Intranet environment. However, the situation becomes more and more frequent. After the change, it will change to 1024 in a short time. The following post inspired: The http://stackoverflow.com/questions/28979660/why-mysql-max-allowed-packet-reset-to-1m-automaticallymysql has general_log, which records all executed SQL commands, which are disabled by default because of performance consumption.

mysql> show variables like '%log%';+-----------------------------------------+---------------------------------+| Variable_name                           | Value                           |+-----------------------------------------+---------------------------------+| back_log                                | 50                              || binlog_cache_size                       | 32768                           || binlog_direct_non_transactional_updates | OFF                             || binlog_format                           | STATEMENT                       || expire_logs_days                        | 0                               || general_log                             | OFF                             || general_log_file                        | /var/run/mysqld/mysqld.log      |

Open general_log:

mysql> set global general_log = ON;

View general_log:

Tail-f/var/run/mysqld. log | grep max_allowed_packet (view the log, but print a large number of real-time SQL operations)

Tail-f/var/run/mysqld. log | grep max_allowed_packet> 1.txt (filter max_allowed_packet, and output to file 1.txt)

We found the following changes:

 

160804  8:59:41      172 Query    SET GLOBAL max_allowed_packet=1024          172 Query    SET GLOBAL max_allowed_packet=1024          173 Query    SET GLOBAL max_allowed_packet=1024160804  8:59:49      173 Query    SET GLOBAL max_allowed_packet=1024

172 Query set global max_allowed_packet = 1024

It is learned that in the general_log log, 172 is the user connection Id (mysql will assign a unique id for each connection), and the operation to filter the id as 172 in the full general log is as follows:

(Unfortunately, because the machine was attacked, the director asked to restore the system of the machine. When the log was written, the log was deleted.), probably as follows:

 connect root@someipaddress on   Query select 0x4D5A900..........(verylong)   Query select sys_exe('cmd /c  c:/windows/nbvqc4.vbs')   .........   set global max_allowed_packet 1024   ........

When you query an ip address in the United States, you can perform the following operations: Download the script from a website and execute the script. Open mysql-related security parameters and SET related variables.

So far, we are very sure that the mysql database has been attacked by hackers.

• Question:

1. How can I access mysql deployed on the Intranet?

Previously, other partners provided an Internet test environment for the network management to launch the Internet IP address. As a result, the machine is directly accessed through a public IP address.

Verification:

1). You can directly connect to the mysql service by using an Internet IP address and a mysql client.

2). Use the Internet IP address to log on successfully using xshell.

2. How do hackers know the user name/password?

Because it is a test machine, mysql uses a simple password (root/123456). It is guessed that it is too easy to crack.

3. What about the firewall?

Because firewall is enabled, various problems may occur during system testing. It is disabled. Service iptables status;

[root@bo bryant]# service iptables status;iptables: Firewall is not running.[root@bo bryant]# 

4. How did hackers discover vulnerabilities and why did they intrude into the system?

Guess the general process: scan the public ip Address by scanning the software, and test that the machine port is not closed, such as 3306 (response 1: Enable the firewall, only open the service port, disable Internet access from other ports ), try brute-force password Logon (Policy 2: complex password policy. You can create a whitelist to record and warn of connection failures ). The log shows that the hacker's main operation is to call the system commands (download remote files, add execution permissions, and execute them) in mysql and open relevant security parameters.

Check the machine logon history and logon Failure history, and find that a large number of Internet login failures have occurred in recent periods, such as oracle, svn, and apache users, hackers constantly try to log on to the system using the username/password of common applications:

producti ssh:notty    217.76.78.35     Mon Aug  1 10:49 - 10:49  (00:00)    producti ssh:notty    217.76.78.35     Mon Aug  1 10:49 - 10:49  (00:00)    swsoft   ssh:notty    217.76.78.35     Mon Aug  1 10:49 - 10:49  (00:00)    swsoft   ssh:notty    217.76.78.35     Mon Aug  1 10:49 - 10:49  (00:00)    iraf     ssh:notty    217.76.78.35     Mon Aug  1 10:49 - 10:49  (00:00)    iraf     ssh:notty    217.76.78.35     Mon Aug  1 10:49 - 10:49  (00:00)    svn      ssh:notty    217.76.78.35     Mon Aug  1 10:49 - 10:49  (00:00)    svn      ssh:notty    217.76.78.35     Mon Aug  1 10:49 - 10:49  (00:00)    oracle   ssh:notty    217.76.78.35     Mon Aug  1 10:49 - 10:49  (00:00)    oracle   ssh:notty    217.76.78.35     Mon Aug  1 10:49 - 10:49  (00:00)    root     ssh:notty    217.76.78.35     Mon Aug  1 10:49 - 10:49  (00:00)    lab      ssh:notty    217.76.78.35     Mon Aug  1 10:48 - 10:48  (00:00)    lab      ssh:notty    217.76.78.35     Mon Aug  1 10:48 - 10:48  (00:00)    root     ssh:notty    217.76.78.35     Mon Aug  1 10:48 - 10:48  (00:00)    root     ssh:notty    217.76.78.35     Mon Aug  1 10:48 - 10:48  (00:00)    apache   ssh:notty    217.76.78.35     Mon Aug  1 10:48 - 10:48  (00:00)    apache   ssh:notty    217.76.78.35     Mon Aug  1 10:48 - 10:48  (00:00)    root     ssh:notty    217.76.78.35     Mon Aug  1 10:48 - 10:48  (00:00)    root     ssh:notty    217.76.78.35     Mon Aug  1 10:48 - 10:48  (00:00)    root     ssh:notty    217.76.78.35     Mon Aug  1 10:48 - 10:48  (00:00)    

 

5. Why do hackers need to modifymax_allowed_packet 1024 ?


ModifiedMax_allowed_packet = 1024, which results in all data operations. If the returned result is greater than 1024, an error is returned. Modifying this parameter makes it easy for users to discover data problems. It is assumed that hackers intentionally expose themselves, perhaps just to show off.

• Summary:

1. Prove again that google is more reliable than Baidu in the face of complicated technical problems

2. log analysis is an essential way to solve the problem

3. the information security awareness is increased. hackers are not far away from us. If they do not intentionally expose themselves, we will not find this machine hacked. After hackers control this machine, they can easily use it, illegal activities.

 

Details:

1. For Internet machines, you must enable the firewall to only provide service ports and disable other ports. Develop related security policies, such as recording logon user ip addresses, regularly viewing logon user history and logon Failure records, and refusing to log on repeatedly.

2. The system user name should determine whether to use the root user as needed. It is best to use the common user permission for specific services. Because the root user has all the privileges of the system.

3. Password: Do not use a simple password for the user password. It is best to use the password generator to generate the password (case sensitive, special characters, length)

4. Data Security:

Mysql should create different users for different businesses and grant limited function permissions to prohibit root users from performing business operations.