yumupgrades for production use, this is the repository for you.
Active subscription is required.
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).
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.
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.