Just a quick post to note that the content from my hands-on lab at MySQL Connect (“MySQL Enterprise Features in Practice”) has been uploaded to the content catalog, and can be found here. This includes the 36-page lab manual and example commands and programs (mostly in Java; the package includes both compiled and source code). For those who attended the lab, this is an opportunity to complete the exercises we didn’t get to in the 2.5 hours, and for those who missed it, an opportunity to learn more about the features and capabilities of key MySQL Enterprise products and features such as MySQL Enterprise Audit plugin, MySQL Enterprise Monitor and MySQL Enterprise Security (PAM plugin). I hope to expand on the lab content to demonstrate other features such as MySQL Enterprise Backup, MySQL Enterprise High Availability and MySQL Enterprise Scalability, and will post updates via my blog.
Quite some time ago, I published scripts to implement password policies for MySQL, and promised to show how to expose violations of that policy via MySQL Enterprise Monitor (MEM). That stalled somewhat with other objectives, but I want to revisit it now that MEM 3.0 is GA. If you haven’t tried MEM 3.0 yet, consider doing so – it’s quick and easy to set up.
Many people don’t realize that MEM can be extended to monitor things beyond MySQL Server health, including visibility into application state as observed from the database. In part of the hands-on-lab I recently led at MySQL Connect, we implemented simple application state monitoring to alert – via MEM – when the number of active application sessions exceeded certain thresholds. This isn’t a new feature in MEM 3.0 – it was possible in earlier versions as well, and the process I’ll describe here in adding MEM alerting to password policy violations can be applied with only a few modifications to MEM 2.3 deployments.
When I saw Shlomi’s recent post which asked (in part) for blacklist support in MySQL, I started thinking about ways in which this could be done using the tools we have today. Here’s the example requirements Shlomi noted:
Speaking of whitelist, it would be great to have a host blacklist. If I wanted to grant access to ‘gromit’@’192.168.%’ except for ‘192.168.10.%’ — well, I would have to whitelist all the possible subnets. I can’t exclude a set of hosts.
I think that’s entirely possible without the overhead of whitelisting all possible subnets – let’s give it a go!
To only slightly misquote one of the greatest movies of all times:
You keep using that option. I do not think it means what you think it means.
Perhaps like many users, I had certain assumptions about what max_connect_errors really does – but in looking closely as part of investigating the new PERFORMANCE_SCHEMA.HOST_CACHE table in MySQL 5.6, I learned that some very fundamental elements had escaped my notice. I’m writing this blog post to help others who hold similar misconceptions of what this option does.
The much-improved PERFORMANCE_SCHEMA in MySQL 5.6 provides visibility into MySQL’s host cache, including the ability to monitor for impending blocked hosts. You can do this with the following query:
mysql> SELECT -> ip, -> host, -> host_validated, -> sum_connect_errors -> FROM performance_schema.host_cache\G *************************** 1. row *************************** ip: 192.168.2.4 host: TFARMER-MYSQL.wh.oracle.com host_validated: YES sum_connect_errors: 3 1 row in set (0.02 sec)
That’s helpful information, and allows DBAs to identify problematic hosts before they are blocked. Due to Bug#69807, it’s also something MySQL 5.6 users will want to do. This bug causes the counter maintained in the host cache for failed connections – against which max_connect_errors is compared – to never be reset by a valid connection. The end result is that over time, hosts may reach the max_connect_errors threshold and be blocked.
MySQL Server has an aborted_connect status counter which will show you the number of failed attempts to establish a new connection. The manual describes potential causes as follows:
It goes on to make the following statement:
If these kinds of things happen, it might indicate that someone is trying to break into your server! Messages for these types of problems are logged to the general query log if it is enabled. Continue reading Practical P_S: From which hosts are connections being attempted?
Starting with MySQL 5.6, MySQL commercial-license builds use OpenSSL. yaSSL – previously used as the default SSL library for all builds – remains the implementation for Community (GPL) builds, and users comfortable building from source can choose to build with OpenSSL instead. Daniel van Eeden recently requested a global variable to indicate which SSL library was used to compile the server (bug#69226), and it’s a good request. It’s something I’ve previously requested as well, having been fooled by the use of have_openssl as a synonym for have_ssl (I’m sure it made sense at the time, right?). Continue reading How to tell whether MySQL Server uses yaSSL or OpenSSL
The MySQL general query log can be a useful debugging tool, showing commands received from clients. In versions through MySQL 5.5, you could count on the GQL to log every command it received – the logging happened before parsing. That can be helpful – for example, the GQL entries might have records of somebody unsuccessfully attempting to exploit SQL injection vulnerabilities that result in syntax exceptions.
Here’s a sample, which I’ll run in both 5.5 and 5.6 and show the resulting GQL: Continue reading MySQL 5.6 general query log behavior change
I previously published stored programs to help implement a (more) comprehensive password policy in MySQL 5.6, building on the password complexity plugin now available in MySQL 5.6. This proof-of-concept has been expanded recently, and the updated package is available here. There’s a few notable changes to the earlier version: Continue reading Improved password policy utility for MySQL 5.6
I’ve been looking at the new ALTER USER … PASSWORD EXPIRE command as I try to implement a comprehensive password policy for MySQL 5.6. There’s a few aspects of this feature that I found interesting, and thought others might benefit from what I’ve learned. Here’s a quick summary: