MySQLTuner fix for “Reduce or eliminate unclosed connections and network issues”
I have a weekly reminder to tune all MySQL servers which are enjoying my Linux server management offer.
Naturally, I use MySQLTuner script for the job. One of the recommendations I have stumbled upon looks like this: Reduce or eliminate unclosed connections and network issues. How to address this?
Some of the most common reasons for unclosed MySQL connections include:
- Scripts which provide invalid authentication data to MySQL
- Monitoring software
Step 1. Increase verbosity in error log
By default, MySQL 5.6 does not include information on unclosed connections. You should update your MySQL configuration to include:
# to pinpoint aborted connection we need this:
This will make sure that information on unclosed connections is included to your MySQL error log.
Restart the server to apply the changes, e.g.:
systemctl restart mysqld (CentOS 7, Percona MySQL 5.6).
Step 2. Fix unclosed connections
Check your MySQL error log now. You might have a clue on how to fix things depending on the errors you see:
2.1. Bad credentials
For this type of unclosed connection you would see something like this:
2017-10-21 11:13:51 25616 [Warning] Access denied for user 'example'@'localhost' (using password: YES)
All you have to do to account for it, is find the scripts or programs which provided invalid credentials, and update the password.
In my case, the error was a bit different:
2017-10-21 11:13:51 25616 [Warning] Access denied for user 'root'@'localhost' (using password: NO)
I had no script which would use root MySQL user (of course, for security) so I was a little puzzled why this connection took place at all.
The error was logged only once, each time after MySQL server restarts. Eventually, I have hunted it down to a ping check inside the MySQL post start script.
2.1.2 Solution for Access denied error immediately after MySQL service restart
For full post, see original at GetPageSpeed.