A cluster problem case solved by the MySQL slave_net_timeout parameter, slavenettimeout

A cluster problem case solved by the MySQL slave_net_timeout parameter, slavenettimeout

Background]
After upgrading from 5.5 to 5.6 for a set of database clusters, alter. log reports a warning exception.
Copy codeThe Code is as follows:
15:44:51 19633 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. please consider using the USER and PASSWORD connection options for start slave; see the \ 'start SLAVE Syntax \ 'in the MySQL Manual for more information.

Database Business pressure qps 1 tps is almost 0 4-10 seconds or longer, there will be write operations.
[Analysis]
1. Master-slave replication information host address, port, copied user, binlog file location, and other information are stored in master.info. version 5.6 has improved security, we do not recommend that you specify a password when changing the master. If the password is set when the master and slave nodes are set up, MySQL 5.6 will prompt the above warning information. This is also the reason why the cluster does not report an error in version 5.5.
 
2. reconnection mechanism of MySQL Replication

In a system that has established a master-slave replication relationship, normally, after the slave database sends a COM_BINLOG_DUMP command to the master database, the master database has a new binlog event and sends a binlog to the slave database. However, the connection between the master database and the slave database is disconnected due to network faults or other reasons, or the master database does not send binlog to the slave database for a long time. In this example, if the database cluster has not been written for about 10 s and exceeds the 4 s set by slave_net_timeout, the slave database sends a request to the master database. MySQL checks whether the user name and password are in plaintext when a 5.6 slave request is sent. If yes, the above information is sent to error. log.

[Solution]
In this case, you can try to adjust slave_net_timeout to 25. Slave_net_timeout is the number of seconds after the Binary Logs events sent from the master database are not received. The Slave database considers the network timeout and the Slave IO thread reconnects to the master database. The default value of this parameter is 3600 s. However, a long time may cause database delay or an exception in the direct connection between the master and slave databases. Setting slave_net_timeout to a short time will cause heavy time-frequency connection of the Master without data updates. Generally, it is set to 5 s online.
Copy codeThe Code is as follows:
Set global slave_net_timeout = 25

Of course, you can also communicate with the business side to deprecate the business line with almost no access traffic, saving the company resources.