Tag Archives: MySQL

Creating custom rules in MySQL Enterprise Monitor

MySQL, , , , , ,

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.

Continue reading Creating custom rules in MySQL Enterprise Monitor →

New MySQL 5.6 Developer Certification Exam

MySQL, ,

You may have already seen the news: Oracle is launching an updated certification for MySQL Developers, based on MySQL Server 5.6.  This is exciting to me for several reasons that I want to share:

Only one exam

Earlier versions of the MySQL Developer certification required sitting for two separate exams.  One goal in redefining the certification process was to make it more accessible to candidates, and reducing the cost – in both time and money – developers need to invest in examinations.  This obviously presented a challenge to cover the same material – actually more, when you consider MySQL 5.6 features – in fewer questions, but we feel we’ve done it.

Content from the experts

Content creation for this exam has drawn from many different volunteers from a wide range of MySQL teams including Training, Engineering and Sales Consultants – but it drew most heavily from the amazing MySQL Support Team, who assist developers (and DBAs) daily with MySQL questions, and who understand better than just about anybody what these developers need to know in order to successfully develop applications against MySQL Server 5.6.  The covered exam topics are listed here.

Better question format

As we wrote new exam questions and reviewed those from the earlier version of the MySQL Developer certification exams, we ensured that the questions were written in such a way to test the candidate’s knowledge, rather than ability to decipher the question.  In particular, that means no more open-ended multiple-guess questions, where it’s unclear whether one … or two … or three … or four of the six possible answers are correct.  As somebody who has taken his fair share of MySQL certification exams in the past, this was a pet peeve of mine with earlier exams.

Development focus

Because this is a developer-focused certification, we want successful candidates to be able to demonstrate ability to write MySQL-backed applications in several common languages.  One does not need to be an expert at all languages to pass the exam, but understanding common language-specific idioms and best practices are important.  That’s a step beyond the earlier certification, which focused almost exclusively on SQL statements.  There’s more to writing good MySQL-backed applications than writing sound SQL, and this exam expands scope to include more of that necessary knowledge.

Alignment with MySQL Developer Training

Naturally, the certification process covers the same ground as Oracle’s MySQL Training for Developers course, making that an ideal opportunity to get up to speed on the topics which will help you pass the exam, but more importantly, develop successful applications that use MySQL Server 5.6 as a data store.

A huge thanks to the MySQL Support Team and Training Team at Oracle for helping drive this update, as well as those from other MySQL teams here at Oracle who contributed time and content.

Implementing a host blacklist with MySQL privileges

MySQL, ,

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!

Continue reading Implementing a host blacklist with MySQL privileges →

MySQL 5.6 users – prevent host blocked errors

MySQL, , ,

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.

Continue reading MySQL 5.6 users – prevent host blocked errors →

Practical P_S: Fixing gaps in GLOBAL STATUS

Java, MySQL, , , ,

Over three years ago, I noticed that there was no STATUS counter for COM_PING commands – something that is useful for ensuring proper configuration of JDBC connection pools.  Mark Leith even provided a patch, but it’s never been incorporated.  With the advances PERFORMANCE_SCHEMA makes in MySQL 5.6, that’s OK – a STATUS counter becomes somewhat redundant:

mysql> SELECT SUM(count_star) as pings
    -> FROM events_statements_summary_global_by_event_name
    -> WHERE event_name = 'statement/com/Ping';
+-------+
| pings |
+-------+
|    12 |
+-------+
1 row in set (0.02 sec)

Continue reading Practical P_S: Fixing gaps in GLOBAL STATUS →

Practical P_S: How idle are your connections?

Java, MySQL, ,

Idle connections can cause problems both at the application side, increasing the risk of connection timeouts for applications where persistent connections are used, and the server side, where resources remain allocated to idle connections.  Any application with persistent connections, such as a JDBC application using a connection pool, will have periods where connections are idle – but it’s good to know how much time is spent idle.  Too much idle time might mean connections pools configured to allow too many connections to sit idle in a connection pool, or not properly doing connection pool maintenance.

PERFORMANCE_SCHEMA in MySQL 5.6 makes it trivial to measure absolute time spent waiting.  This will show total, average and maximum idle times by account:
Continue reading Practical P_S: How idle are your connections? →

Practical P_S: How old are your connections?

MySQL, ,

I’ve often wished that PROCESSLIST exposed when a connection was first established, and I find myself wishing for this information more now with MySQL 5.6.  Improvements to PERFORMANCE_SCHEMA make it trivial to see how much time is being spent in various operations for a given connection – but it would make some analysis (“what percentage of connection time is spent doing X?”) easier.

That said, it is possible to approximate connection age with PERFORMANCE_SCHEMA in MySQL 5.6.  I say “approximate” because results will vary based on what instrumentation exists, is enabled, and is collecting timing data.  That’s because we’re just doing a SUM() on the SUM_TIMER_WAIT column for all instrumented waits.  Here’s an example (FYI, I’m using the format_time() function from Mark Leith’s awesome ps_helper scripts to convert from picoseconds to something meaningful to me):
Continue reading Practical P_S: How old are your connections? →

Practical P_S: From which hosts are connections being attempted?

MySQL, , , , ,

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? →