If you are running a MySQL database server, you might be wondering how to see the current settings for the database. In MySQL they are called variables, and it’s very easy to see the current values.
The simplest way is to just use this command from the MySQL prompt, which will show every current configuration setting.
If you want to see only a specific variable, you can use this command. Obviously you’d want to replace the max_connect_errors in that command with the variable that you’re looking for.
SHOW VARIABLES LIKE '%max_connect_errors%';
If you want to change the current state of a variable, you can do so easily with a command similar to this one:
SET GLOBAL max_connect_errors=10000;
If you’re reading this post you might be wondering why we bothered to write a post about this subject, considering there’s a user manual for MySQL. Earlier tonight (as of the writing of this post), our web server had a problem connecting to the database. When looking through the logs, it became clear that the database server had blocked the web server from connecting because of some connection errors. The solution was even easier to figure out.
Host 'host_name' is blocked because of many connection errors.
Unblock with 'mysqladmin flush-hosts'
Turns out, our hosting provider is in the process of upgrading their switch firmware because of some issues — which are causing some connection errors. Because the default MySQL setting for maximum connection errors is a silly limit of 10, our database server started rejecting incoming connections. Thankfully we were able to fix it quickly with a FLUSH HOSTS command on the database server, but it was time to increase the variable. So… this post is just so we won’t forget what we did next time.
Programmer by day, geek by night, The Geek, also known as Lowell Heddings, spends all his free time bringing you fresh geekery on a daily basis. You can follow him on Google+ if you'd like.
- Published 03/14/13