Site icon GetPageSpeed

MySQLTuner fix for “Reduce or eliminate unclosed connections and network issues”

Danila Vershinin
MySQL

MySQL

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:

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).

Check your MySQL error log now. You might have a clue on how to fix things depending on the errors you see:

Step 2. Fix aborted connections caused by 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.

Solution for Access denied error immediately after MySQL service restart

MySQL service unit uses a special script upon restart. That script uses mysqladmin to check whether service startup was successful.

The issue is that mysqladmin program fails to use our credentials file at /root/.my.cnf. We need to supply HOME environment variable to the script which runs it. I’ve submitted the bug report (migrated) to Percona MySQL.

Run sudo systemctl edit mysqld and paste in:

[Service]

# Don't signal startup success before a ping works (with shell)
ExecStartPost=
ExecStartPost=/bin/bash -c "HOME=/root /usr/bin/mysql-systemd post"

Now we have just created an override for the unit service file of MySQL. It sets the HOME environment variable so that mysqladmin knows how to expand ~/.my.cnf and use the password supplied inside that file.

You can now restart MySQL service and the error should not be logged.

Step 3. Fix aborted MySQL connections caused by monitoring software

The monitoring software might be configured to do the check on the port 3306 or MySQL socket file. Whichever it is, it should check MySQL protocol as opposed to simple TCP check to make sure that the it’s not flagged as aborted connection.

If you use Monit, configure your program check properly. Note the use of protocol mysql in the check as well as supplying special credentials created for the purpose:

check process mysql with pidfile /var/run/mysqld/mysqld.pid
    start program = "/sbin/start mysql"
    stop program = "/sbin/stop mysql"
if failed
    port 3306
    protocol mysql username "foo" password "bar"
then alert

More details about Monit MySQL test.

Exit mobile version