With MySQL 5.6 just gone GA, I thought it would be good to take a look at the changes in options and variables that comes with the new release.

First of all, several of the existing options have get new default values. As James Day already have written a good post about that in his blog, I will refer to that instead of going through the changes. For a general overview of the new features and improvements, the recent blogs by Rob Young and Peter Saitsev are good starting points

With a handful of exceptions, few people deliberately choose to deploy MySQL on read-only media – but there are cases where being able to access InnoDB data that way comes in handy.  As it happened, I had exactly this need a few months back, and the excellent InnoDB development team at Oracle has recently implemented this feature in MySQL 5.6.

First, some background.  We had a need to migrate  legacy systems from a 3rd party data center into new corporate data centers.  These systems were redundant, but we wanted to retain access to the data for archival purposes.  All went well, except one small detail:  Because the machines were originally housed outside the new data center, we weren’t allowed to stand up the servers inside the new data center – except in read-only mode.

Therein was the problem – we had many GB worth of

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:

Moved all created objects out of mysql system database

The mysql database is meant for system tables, and I try to keep everything not directly managed by the MySQL server out of that database.  The initial proof-of-concept implementation violated this principal – the

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:

You can use ALTER USER … PASSWORD EXPIRE in prepared statements as of 5.6.8-rc

This is important because there’s no other way to dynamically bind ALTER USER statements to a user name and host, which is necessary if you are trying to automate anything related to password policies.  This wasn’t the case with earlier 5.6 releases, but was fixed in 5.6.8-rc:

mysql> SELECT password_expired
-> FROM mysql.user
-> WHERE user = 'root' AND host = 'localhost';
+------------------+
| password_expired |

I’ve noted previously that the new transportable tablespaces for InnoDB in MySQL 5.6 are a big step forward, and newly-released 5.6.9-rc makes importing tablespaces a bit easier.  In previous versions, you had to have a .cfg file to import the InnoDB tablespace.  That file is produced during FLUSH TABLE <tbl> FOR EXPORT operations, and contains InnoDB metadata that’s not contained in the .ibd tablespace file itself.  I filed a feature request requesting the .cfg file be made optional, and Sunny implemented it:

mysql> create table tt (a

In a previous post, I noted that the new new password verification plugin in MySQL 5.6 provides a basis for implementing a more comprehensive password policy.  Most notably, password policies include requirements around password strength, duration, and reuse.  While the password validation plugin focuses on password strength policy components, there are ways to roll your own processes in support of password expiration and reuse policy components.  Unlike the password verification plugin, the tools I will describe below don’t hook directly into account maintenance commands.

You can download the full .SQL file (in a .ZIP package)

If you’re anything like me,  your initial reaction upon hearing about transportable tablespaces for InnoDB in 5.6 was to imagine it like MyISAM, where you can copy the .frm, .myi and .myd files around to your heart’s content, and everything will be great.  You might have read Sunny’s excellent blog, and realized that there’s a bit more to it than that – you have to explicitly prepare the tablespace for copying using FLUSH TABLES FOR EXPORT.  That’s perfectly acceptable for the bulk of use cases, such as single-table InnoDB backups, and opens up exciting new possibilities for moving or copying InnoDB data at the filesystem level.

But for situations where the need is a little different, you might really

The recent MySQL 5.6.6 release includes a new password verification plugin.  This is a great tool for enforcing passwords that meet certain strength thresholds.  Quoting the options from the manual, there are three different criteria levels that can be applied:

• WEAK policy tests password length only. Passwords must be at least 8 characters long.
• MEDIUM policy adds the conditions that passwords must contain at least 1 numeric character, 1 lowercase and uppercase character, and 1 special (nonalphanumeric) character.
• STRONG policy adds the condition that password substrings of length 4 or longer must not match words in the dictionary file, if one has been

Back when MySQL 5.1 was first released, Oracle (which didn’t at the time own Sun or MySQL) wanted to add new InnoDB functionality faster than MySQL could effectively incorporate it into the server, and the InnoDB plugin was introduced.  It provided 5.1 users the option of deploying a more advanced version of InnoDB, or using the more conservative built-in version baked into MySQL.  By the time the plugin reached GA status, though, Oracle had announced the acquisition of Sun (and MySQL), and things started to change.  The coordination between the InnoDB development team at Oracle and the MySQL development team increased once we were all “under one roof”.  The strategic directions of InnoDB and MySQL were aligned, and the results can be seen in the tremendous work done for MySQL 5.5 and 5.6.  In fact, the

Members of the MySQL Support Team wear a number of different hats here at Oracle.  Obviously, our top priority is to provide amazing technical support that makes customers rave (http://www.mysql.com/support/quotes.html" target="_blank).  We also have a team dedicated to processing bug reports from the MySQL Community.  Some of us are active bloggers or assist on mailing lists or forums, while others find other ways to contribute to the MySQL Community.  We help out with QA and product planning, write

The recent release of 5.6.6 includes a new utility, mysql_config_editor, which makes it easier to interact with MySQL clients securely.  At the same time, it’s easy to overstate the security benefits of using this new tool, and unfortunately, I think a couple of statements in the release notes and documentation go a step too far (something we’re in the process of correcting).  Ronald quoted one of them in his blog:

MySQL now provides a method for storing authentication credentials securely in an option file named .mylogin.cnf.

This

Windows users of MySQL may find something missing when they first download MySQL 5.6.6.  The .MSI package of MySQL for Windows has been eliminated in favor of the MySQL Installer for Windows.  You can still download MySQL in .ZIP format (as well as a source code .ZIP for the GPL-licensed Community edition), but the quickest way to get running MySQL on Windows is the MySQL Installer.  You can download the version of MySQL Installer that includes MySQL 5.6.6 on dev.mysql.com.

The MySQL Installer has a number of improvements over the old .MSI installer and configuration wizard.  Running the 5.6.6 installer on my machine, it detected that ports 3306 and 3307 were already used by other MySQL installations, and suggested port 3308 for the new installation.  It also recognized conflicts

Back in April, I issued a challenge to identify any compelling use case for the YEAR(2) data type.  Months later, I still don’t have such a use case, and the release of 5.6.6 brings deprecation of support for the YEAR(2) datatype.  Trying to create a table with a YEAR(2) column in 5.6.6 or greater will result in conversion to YEAR(4) and a warning:

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.6.6-m9  |
+-----------+
1 row in set (0.00 sec)

mysql> CREATE TABLE y (y YEAR(2));
Query OK, 0 rows affected, 1 warning (0.13 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
Level: Warning
Code: 1818
Message: YEAR(2) column type is deprecated. Creating YEAR(4) column instead.
1 row

As stated in the 5.6.6 release notes, the default value of –bind-address has changed from IPv4-specific “0.0.0.0″ to “*” – the latter value which allows MySQL to bind to IPv6 interfaces by default.  There are a few implications to this change.

First, make sure you’ve hardened both your IPv4 and your IPv6 access points to your MySQL server.  If you are running IPv4 interfaces only, you can also change the –bind-address value back to the previous default value of “0.0.0.0″.  Because MySQL now listens by default on both IPv4 and IPv6 interfaces, an installation that has only hardened IPv4 interfaces may

In my last post, I described a specific problem with prepared statements into which PERFORMANCE_SCHEMA can give visibility.  That made me wonder whether PERFORMANCE_SCHEMA can also be used to identify other areas where prepared statements run into problems.  The most significant problem tends to be leakage of prepared statements.  This can inflate memory usage, both on the server and application side, and it’s not uncommon to find applications which fail to close prepared statements.

So the question is, what can PERFORMANCE_SCHEMA tell us about how connections close (or more importantly, fail to close) prepared statements?

At the most basic level, one can check the number of PREPARE statements executed compared to DEALLOCATE PREPARE, and you can do that using global status variables.  You’re shooting for general equality between