Tag Archives: MySQL

MySQL 5.6.17 – now with better encryption

Joro wrote recently about MySQL 5.6.17‘s new support for AES-256 encryption, and it’s a great improvement for people need to encrypt their data at rest.  The new session block_encryption_mode variable controls what variant of AES is used, and for backwards compatibility, the default behavior remains 128-bit key length with ECB block cipher mode.  If you’re happy with that level of encryption, nothing changes – your existing code will work the same on 5.6.17 as it has on earlier versions (note that users of statement-based replication will experience new warnings).  There are good examples of how to use the new functionality in the updated public test cases, but this blog post is intended to help orient users to the new options as well.

Continue reading MySQL 5.6.17 – now with better encryption

MySQL 5.6 Certification Now GA!

The MySQL 5.6 certification exams (Developer and DBA) are now GA!  A big thanks to all those who sat for the extended length beta exams – and subsequently waited for several months for your results.  Your feedback and the data collected from many beta testers helped ensure the final exams are high quality. A huge thanks also goes to the MySQL staff at Oracle who produced, edited and verified exam questions, in addition to their normal job responsibilities.  In particular, these exams would have never materialized without a massive investment from the MySQL Support Team – thanks for the great job!

If you missed the reduced-fee beta period, the exams are now available for $245 (USD) each.  While not as inexpensive as the beta exams, this is still a substantial savings from earlier certification offerings, as each certification has collapsed content from two exams (and two fees) into a single exam.  And you’ll get the benefits of a GA test – quick feedback on results and a shorter exam with higher-quality, beta-vetted questions.

The exams are tough – I wasn’t sure whether I would pass (like Dave Stokes, happy to report I did).  The passing percentage for both exams is set at 64%.   If you’re feeling unsure about whether you’re prepared, consider a MySQL Training course to assist you.

Now that I know that I passed both exams, I’ll try to complete the promised study guide blog posts.

SF MySQL Meetup Presentation: Changes in MySQL 5.7

Last Wednesday, I spoke at the San Francisco MySQL Meetup on the topic of changes coming in MySQL 5.7 (and later).  We actually went through two different slide decks; the first on features being considered for deprecation in MySQL 5.7 (or later), and the second set providing a brief overview of the new features and benefits already introduced in MySQL 5.7 via the development milestone releases (DMRs) published to date.  A big thanks to the entire SF Meetup group, and in particular the organizers (Erin, Mike and Darren), for having me.  The event was streamed and recorded, and you can view the full presentation on YouTube.  The slide deck can be found here.

The discussion around proposed deprecation was good, and this blog serves to document my own notes about what was said – giving others an opportunity to provide additional feedback.  Feel free to comment either to reinforce or offer alternative perspectives on the feedback noted.  There’s also some post-presentation clarification mixed in:

Continue reading SF MySQL Meetup Presentation: Changes in MySQL 5.7

MySQL Support is Hiring!

My old team here at Oracle looking for talented, experienced MySQL experts to join the amazing MySQL Support Team.  I was lucky enough to join this great group of people over seven years ago, and while my responsibilities have shifted to other areas with the MySQL team at Oracle, I still join the team’s chat channel daily (where they’ve revoked my vowel and address me as “t*dd” to highlight my treachery in leaving the team).   I don’t know that it’s possible to find a better group of colleagues to work with than can be found in the MySQL Support Team.

The work itself is exciting – if you like solving problems, this is an incredible job.  I’m not talking about finding and quoting applicable sections of the MySQL reference manual.  I’m talking about owning customer problems like they are your own, taking the time to understand what’s really needed, and investing the (sometimes considerable) energy into finding adequate solutions for customers – whether the problem be a bug, optimizing a schema or a query, making deployment or architecture recommendations, or just pointing people to the right download for their needs.

Beyond the primary responsibility of caring for customers in need, the MySQL Support Team performs a number of other critical roles in Oracle.  They process and prioritize community-reported bugs, they wrote many of the questions on the updated MySQL 5.6 certification exams, they evaluate development releases and provide early feedback on pre-GA software, they share their knowledge at conferences, they contribute code patches, they write features that meet customer needs, they participate in product planning teams.  If you want to have a meaningful, lasting influence on MySQL products and community, this is a great opportunity.

The team is particularly looking for candidates in Japan, Russia, India, China, Mexico, Portugal or the U.S., but would love to see resumes of qualified individuals elsewhere.  If you’re interested, please let me know via the private contact form below:

Live reconfiguration of replication topography in Connector/Java

As noted in a previous post, MySQL Connector/Java supports multi-master replication topographies as of version 5.1.27, allowing you to scale read load to slaves while directing write traffic to multi-master (or replication ring) servers.  The new release of version 5.1.28 builds upon this, allowing live management of replication host (single or multi-master) topographies.  This parallels functionality that has long existed for load-balanced connections, and enables users to add or remove hosts – or now promote slaves – for Java applications without requiring application restart.  This post aims to explain how to leverage this functionality (the TL;DR/fun demo is found in the examples section)

Continue reading Live reconfiguration of replication topography in Connector/Java

Multi-master support in MySQL Connector/Java

MySQL Connector/Java has long had support for replication-aware deployment, allowing a single logical Connection object to effectively “pool” connections to both a master and (potentially multiple) slaves.  This allowed scale-out of read load by distribution of read traffic to slaves, while routing write load to the master.  The JDBC specification provides a nice hook to know what’s read-only traffic – Connection.setReadOnly().  When a boolean value of true is passed, a ReplicationConnection will route further commands a selected slave instance, while values of false trigger routing to the master.  This is sufficient for many simple replication topographies, but not all – most notably, it has been difficult to handle multi-master deplyment.  MySQL Connector/Java 5.1.27 aims to solve that, and a number of related problems.

Continue reading Multi-master support in MySQL Connector/Java

My Favorite Hidden Docs Page

There’s a lot to love about the MySQL product documentation – a lot of hard work from a number of very talented Oracle staff goes into it (not to mention the helpful suggestions and feedback from the community).  There is, however, one page I find myself coming back to again and again, despite the fact that it’s somewhat hard to find.  This is the MySQL Server options/variable reference page.  It’s a helpful table that lists every MySQL Server option or variable, what version it was introduced or deprecated in, whether it’s dynamic or not, whether variables have session/global/both scope, and links to the version-specific documentation for that variable (useful to know when default values changed, for example).

It always takes me a while to find this page again when I’m looking for it, as it’s outside of the version-specific Server manual.  Maybe you already know about this page and have it bookmarked; if not, give it a look – I find it very useful.  That said, I wish it had filtering capabilities, so I wrote this bug report.  I like being able to quickly answer questions like:

  • Which options were introduced in MySQL 5.6?
  • Which options and variables contain “ssl”?
  • Which options cannot be set dynamically?

There’s a functional but ugly form and working JavaScript code attached to the bug report, if you ever have similar needs.  All it needs is JQuery and the <div> contents that surround the table.

MySQL Connect HOL content posted

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.

Why you should register for MySQL 5.6 certification exams now

I’ve previously shared my excitement over the coming Oracle Certified Professional exams for MySQL 5.6 (both Developer and DBA), and I’m putting my (employer’s) money where my mouth is – I’ve registered to sit for the DBA exam in early November (some test sites have limited opportunities; interested candidates should consider booking now).  Here’s why I think others should do the same:

Low cost

During the beta period (through December 14), the cost of the exam is only $50 USD.  That’s barely more than the cost of a book or study guide these days, and a significant savings from the normal exam prices.

Full certification

Those who pass the exam will be given a full certification – not some lesser “beta certification.”  There’s no distinction between those who pass the exam during the beta period and those who pass later.

Help produce better exams

Beta test takers contribute to better exams – the results are analyzed to find the best (and worst) questions to indicate success.  Those questions which don’t correlate to overall success on the exam are left off the production exams.

This last point raises the only real drawbacks in taking a beta exam.  Because questions are being screened as well as candidates, exams consist of 150 questions instead of 70, making the time commitment larger (3h).  Since certain questions will be discarded based on low correlation to overall success, it also means candidates must wait until the end of the beta period before results can be provided.  If you can deal with a longer test and subsequent wait for results, this beta exam is a great, low-risk way to get your MySQL 5.6 certification – all while helping ensure the final certification exams contain the most relevant questions.

Consider registering today!

Creating custom graphs in MySQL Enterprise Monitor

As a follow-up to my earlier post describing the process to create custom Advisors for MySQL Enterprise Monitor, this post will demonstrate how to create custom graphs to track metrics over time. The password policy scripts I introduced earlier will again be the basis for the data used in this post.

Collecting new data

Similar to the custom Advisor created in the earlier post, creating a custom graph starts with custom data collection.  Why can’t I just use the data collections I defined in the earlier example?  Because there’s a restriction on using multiple custom data types (defined as namespace+classname combinations) in a single graph, and in the earlier example, I used a different classname for each password policy definition.  I want to graph all three password policy violation counts as distinct lines on the same graph, so I need change how I collect data so that I get it all back in a single data type.  Here’s what I’ve added to custom.xml:

	
    <class>
                <namespace>password_policy</namespace>
                <classname>graph</classname>
        <precondition><![CDATA[
  SELECT COUNT(*) 
    FROM information_schema.tables 
    WHERE table_schema = 'password_policy' 
    AND table_name = 'user_password_policy_violation'
            ]]></precondition>
                <query><![CDATA[
  SELECT SUM(IF(violation_type = 1, 1, 0)) no_password_count,
      SUM(IF(violation_type = 2, 1, 0)) expired_password_count,
      SUM(IF(violation_type = 3, 1, 0)) reused_password_count
    FROM password_policy.user_password_policy_violation
    WHERE violation_resolved = 0
                ]]></query>
                <attributes>
                        <attribute name="no_password_count" counter="false" type="INTEGER"/>
                        <attribute name="expired_password_count" counter="false" type="INTEGER"/>
                        <attribute name="reused_password_count" counter="false" type="INTEGER"/>
                </attributes>
    </class>

As before, make sure you test your SQL! It’s a real headache to get to the end of this process only to find your graph doesn’t work, and spend time tracking down the problem as bad SQL. That’s one of my wish requests for custom rules and graph creation – making it easier to diagnose problems.

Save the custom.xml file, and restart the Agent to reload the custom data collection definitions.

Define the custom graph

This also involves XML, and you can find a sample in the MEM documentation.  I’ve pretty much copied that example and tweaked it.  Here’s the resulting XML:

<?xml version="1.0"?>
<com_mysql_merlin_server_graph_Design>
    <version>1.0</version> 
    <uuid>a77cd4f0-d3a4-4f69-a346-6f36fb6a2f56</uuid>
    <name>Password Policy Violations</name>
    <rangeLabel>Accounts</rangeLabel>
    <series>
        <label>blank passwords</label>
        <expression>blank_count</expression>
    </series>
    <series>
        <label>expired passwords</label>
        <expression>expired_count</expression>
    </series>
    <series>
        <label>reused passwords</label>
        <expression>reused_count</expression>
    </series>
    <variables>
        <name>blank_count</name>
        <dcItem>
            <nameSpace>password_policy</nameSpace>
            <className>graph</className>
            <attribName>no_password_count</attribName>
        </dcItem>
        <instance>/</instance>
    </variables>
    <variables>
        <name>expired_count</name>
        <dcItem>
            <nameSpace>password_policy</nameSpace>
            <className>graph</className>
            <attribName>expired_password_count</attribName>
        </dcItem>
        <instance>/</instance>
    </variables>
    <variables>
        <name>reused_count</name>
        <dcItem>
            <nameSpace>password_policy</nameSpace>
            <className>graph</className>
            <attribName>reused_password_count</attribName>
        </dcItem>
        <instance>/</instance>
    </variables>
</com_mysql_merlin_server_graph_Design>

Let’s break down the components.  First, you need to define a UUID.  I’ve previously just incremented the UUID from the example, but you can use any UUID generator you like.  I used PowerShell (thanks Josh!):

D:\>powershell -Command "[guid]::NewGuid().ToString()"
a77cd4f0-d3a4-4f69-a346-6f36fb6a2f56

The <name> tag defines how this graph will be named and displayed in the dashboard.  This must be unique.  The <rangeLabel> should define what is displayed as the label for the Y-axis of the graph, but that seems to be affected by a bug (Oracle Bug#17556299) with custom graphs today.

For this graph, I’ve defined three different <series> – one representing each password policy.  Each series has a <label> element, which defines the label for the series, and an expression.  Like custom rules, you can do some transformations and computations here – the documentation has a good example, converting raw byte counts to megabytes for graphing purposes.  We’re not using that here – our expressions are simply the number of violators of a given password policy.  The values used – blank_count, expired_count, reused_count – are all defined in <variables> elements below.  The <name> element of the <variables> tag should correspond to the value used in the <expression> tag of <series> elements above, and the <nameSpace>, <className> and <attribName> should map to the custom data collection definition, where they are found in <namespace>, <classname> and <attribute name=”…”> tags, respectively.

Upload the graph definition

Once the graph definition is complete, save it.  The name doesn’t matter, as we’re going to upload it to the MEM dashboard.  Finding where to upload it can be a bit tricky, though – you’ll find it by clicking the “Advisors” option under the “Configuration” menu item.  Right next to the “Create Advisor” button used to create a custom rule, you’ll find the “Import / Export” button.  That’s the one you want.  This will take you to a page where the important elements look like below:

Import

Use the panel on the right to import the XML file containing the custom graph definition.

Schedule the graph

You might be tempted at this point to go directly to the graphs page and look for your graph.  If you do, you won’t find it.  The graph must first be scheduled.  On the same “Advisors” page (under “Configuration” again), notice that the “Graphing” header shows something like, “Configured:  94 of 95”.  That’s letting you know that your newly-created graph hasn’t yet been configured – expand the “Graphing” header and find the custom graph name (“Password Policy Violations”).  Drill down until you find the appropriate MySQL Server instance against which to schedule the graph, click on the menu icon next to the server name, and schedule it.  I chose to collect data every 1 minute; in a production environment, you may want to do this less often.

Graph SchedulingOnce the graph has been scheduled, the Service Manager will tell the Agent to start collecting the necessary data the next time it checks in.  Because the graphing components need at least two data points to generate a graph, you’ll want to wait a couple of minutes before looking for results in the graphs.

Finding the custom graph

After waiting the requisite couple of minutes, navigate to the “All Timeseries Graphs” item under the “Reports & Graphs” menu item.  Select the appropriate host from the asset selector on the left-hand side, and expand the filter options by clicking the magnifying glass icon towards the top-right of the page.  Here, you can select the “Password Policy Violations” graph, push the “Filter” button, and be rewarded with a view of your custom graph:

GraphNote that I manipulated the password_policy.user_password_policy_violation table to show something more interesting than flat lines.

Conclusion

Custom graphs can give you visibility into trends over time for certain characteristics.  While the example presented here uses custom data elements, you can create your own custom graphs using either standard data elements MEM already collects or extend MEM’s data collection to expose metrics useful for monitoring your applications or deployments.