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:
log-warnings=2

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.

One clap, two clap, three clap, forty?

By clapping more or less, you can signal to us which stories really stand out.