All posts by Todd Farmer

Improved ALTER USER syntax support in 5.7

Complimenting the expanded CREATE USER syntax introduced in MySQL Server 5.7.6 is more useful ALTER USER syntax.  Before MySQL Server 5.7.6, ALTER USER could only be used to expire a user’s password.  That’s pretty limited.  With changes made in MySQL Server 5.7.6, a better distinction is made between privilege-level attributes (those which are managed via GRANT and REVOKE statements) and account-level attributes (those managed using CREATE USER and ALTER USER statements).  MySQL has a long history of confusing these – for example, requiring a GRANT command to set account resource limits or require SSL.  This all changes for the better in MySQL Server 5.7 – here’s how: Continue reading Improved ALTER USER syntax support in 5.7

Implications of TLS plans for third-party products

As I wrote earlier, we want the default experience in MySQL 5.7 to be secure by default.  Part of this includes securing connections by automatically creating key material and using TLS for connections where possible.  This may have some significant implications for third-party software – especially products which depend upon capturing, evaluating and/or redirecting client/server traffic at the network level.  This blog post is intended to highlight for developers and users of such products potential issues they may want to consider or address during the pre-GA period for MySQL Server 5.7. Continue reading Implications of TLS plans for third-party products

SSL/TLS in MySQL 5.7

MySQL 5.7 aims to be the most secure MySQL Server release ever, and that means some significant changes in SSL/TLS.  This post aims to tie together individual enhancements introduced over the span of several Development Milestone Releases (DMRs) into the larger initiative.  In the simplest terms, we hope to have a minimal TLS configuration enabled by default, and for connections to prefer TLS by default.  Let’s dig into the various aspects of this:

Continue reading SSL/TLS in MySQL 5.7

Building a better CREATE USER command

Prior to MySQL 5.7, the CREATE USER command had a number of limitations:

  • No way to set both authentication plugin and password
  • No way to disable a user
  • No way to define user resource limitations
  • No way to set a non-default password expiration policy
  • No way to require SSL/x509

All of these things could be done through other means, but typically involved other statements, such as GRANT commands.  Starting with MySQL 5.7.6, these can all be done through a new and improved CREATE USER syntax:

Continue reading Building a better CREATE USER command

Emulating roles with expanded proxy user support in 5.7.7

MySQL has provided support for proxy users since version 5.5, but the roles-like capabilities offered have been largely unnoticed until recently.  Part of that has been due to limitations on which types of accounts could leverage proxy user capabilities.  This changes with the release of MySQL Server 5.7.7 (Release Candidate), which includes support for proxy user mapping for the standard mysql_native_password and sha256_password authentication plugins.  This post will introduce the new functionality and explain how to leverage it to emulate certain features of roles without any need for PAM or external authentication systems.

Continue reading Emulating roles with expanded proxy user support in 5.7.7

Command line prompt

The mysql> command-line prompt is iconic, shown in countless documentation pages, forum posts, tutorials and manuals. It’s immediately identifiable – one look, and you immediately know the context in which commands are being executed. So it’s only with good reason that we would consider changing the prompt value to something else, and Daniël van Eeden provided a compelling suggestion to modify this to provide user, host and database context. Because the mysql prompt is user-configurable, this is easy to do dynamically:

mysql> prompt something>
PROMPT set to 'something> '
something> select 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

something>

Using the special character sequences defined at the bottom of one of the documentation pages, the prompt can supply with a number of different and useful information which provides context in the command line:

something> prompt \u@\h [\d]>
PROMPT set to '\u@\h [\d]> '
root@localhost [(none)]> prompt \h:\p [\d]>
PROMPT set to '\h:\p [\d]> '
localhost:3310 [(none)]> prompt \D>
PROMPT set to '\D> '
Mon Mar 23 17:09:58 2015>

The prompt can also be set as a command-line argument to the client, or via the configuration file read by the client at startup. Users who manage many servers and need information identifying to which instance a given command line client is connected might want to leverage the configuration file option, as this will affect all client instances started by default.

We actually changed the default prompt to “\u@\h [\d]> ” as Daniël proposed, but pulled it back out of 5.7 after using it internally for a few days and realizing a few issues. This post aims to document why we reverted to the original mysql> prompt, the options we’ve considered as alternatives, and a call for feedback.

Copying SQL

The mysql> prompt length aligns with the prompt length for continuation prompts, where queries span multiple lines. This makes it easy to copy SQL commands from terminals:

mysql> SELECT 1,
    -> NOW();

When using prompts with differing length, copy operations pick up extraneous characters, requiring users to manipulate the contents to transform it back into executable SQL:

root@localhost [(none)] > SELECT 1,
    -> NOW();

An option would be to make the buffer the continuation prompt to the same length as the original prompt. With larger and dynamic prompts, the results might be unsatisfactory – the example above takes up 25 characters of my 80-character-wide terminal.

(None)

MySQL allows users to connect without specifying a default database, and that’s not going to change anytime soon. This results in “[(none)]” being displayed in the prompt. When I first encountered the prompt change, I had to ask what the “none” meant – I had no idea what that meant. Informed users will only need to be told once (or never, if they are smarter than me), but it’s bound to confuse less-experienced users.

Misleading user name

The MySQL ACL system makes displaying user names an interesting exercise. With wildcards, anonymous users and proxy accounts, there’s any number of possible values for “user name” to display. Do you want to know the user name you supplied to the client, the user name portion of the account used to authenticate, or the user name portion of the account associated with the privileges you have? They are different:

shell> bin\mysql.exe -ublah -P3310
Welcome to the MySQL monitor.  Commands end with ; or \g.
...
blah@localhost [(none)] > show grants;
+--------------------------------------+
| Grants for @localhost                |
+--------------------------------------+
| GRANT USAGE ON *.* TO ''@'localhost' |
+--------------------------------------+
1 row in set (0.00 sec)

Similarly for proxy users:

pa@localhost [(none)] > SELECT USER(), CURRENT_USER(), @@session.proxy_user;
+--------------+----------------+----------------------+
| USER()       | CURRENT_USER() | @@session.proxy_user |
+--------------+----------------+----------------------+
| pa@localhost | pb@localhost   | 'pa'@'localhost'     |
+--------------+----------------+----------------------+
1 row in set (0.00 sec)

pa@localhost [(none)] > show grants;
+----------------------------------------+
| Grants for pb@localhost                |
+----------------------------------------+
| GRANT USAGE ON *.* TO 'pb'@'localhost' |
+----------------------------------------+
1 row in set (0.00 sec)

Like the [(none)] value, experienced MySQL users will understand the difference between USER(), CURRENT_USER() and @@session.proxy_user, but this can easily confuse users new to MySQL.

Server vs. client host

I’m used to seeing user@host define MySQL accounts, but that’s not what \u@\h actually provides. As noted above, the user is the user name supplied to the client, rather than the account. But the host is the server host, not the host of the account.

shell> ipconfig
...
Ethernet adapter Local Area Connection:

   Connection-specific DNS Suffix  . :
   IPv4 Address. . . . . . . . . . . : 192.168.2.64
...
shell> bin\mysql -utodd -h192.168.2.54
Welcome to the MySQL monitor.  Commands end with ; or \g.
...
todd@192.168.2.54 [(none)] > select user();
+---------------+
| user()        |
+---------------+
| todd@GRAPHITE |
+---------------+
1 row in set (0.02 sec)

Note that using the \U option shows the full user name/host returned from USER():

todd@192.168.2.54 [(none)] > prompt \U >
PROMPT set to '\U > '
todd@GRAPHITE >

It makes sense to display the host a client is connected to, rather than the host component of the account used to connect. But it does allow for confusion about what is being displayed.

Other common use cases

Daniël’s suggestion works great for DBAs who manage multiple MySQL installations and need to ensure they are executing commands against the intended instance.  It’s less useful for single-host deployments, or for use cases like MySQL Sandbox, where multiple instances may all exist locally.  I frequently have 5+ MySQL instances running locally for various testing purposes, and the proposed default prompt wouldn’t help me distinguish between them at all.  Such use cases would be better served by a prompt value like “\h:\p > “:

 

root@localhost [(none)] > prompt \h:\p >
PROMPT set to '\h:\p > '
localhost:3310 >

Alternatives

The need Daniël identified is something we want to address, but the issues listed above gave us second thoughts about changing the default prompt. We’ve identified some possible alternatives, which I’m listing below:

Add prompt help

The definition of special sequences isn’t easy to find in the documentation, and even some experienced MySQL users are unfamiliar with the options that exist. Those that do know the possibilities for customizing prompts sometimes find it difficult to quickly recall the special sequences they might need. We’d like to add this information to the client, so that it is readily available:

mysql> prompt ?

*** PROMPT FORMAT OPTIONS ***
\d - The default database
\u - Your user name
\h - The server host
...

Define prompt macros

We know that \u@\h [\d]> reflects the needs of a common use case, as does \h:\p >. Another common use case likely involves outputting the date/time in the prompt for tracking purposes. Another common use case might be to simply include the default database.

We can provide macros which are easily remembered and cycled through, so that users don’t have to build their own prompts for these common use cases. For example:

mysql> prompt \1
PROMPT set to '\u@\h using \d> '
root@localhost using (none)> prompt \2
PROMPT set to '\h:\p> '
localhost:3310> prompt \3
PROMPT set to '\D> '
Fri Mar 20 14:08:59 2015> prompt
Returning to default PROMPT of mysql>
mysql>

Multi-line prompts

One possible solution is to leverage multi-line prompts, so that contextual information is provided on one line, while the familiar mysql> prompt is retained:

root@localhost [(none)] > prompt \u@\h [\d]\nmysql>
PROMPT set to '\u@\h [\d]\nmysql> '
root@localhost [(none)]
mysql> SELECT 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

root@localhost [(none)]
mysql>

This makes copying SQL easy, but still displays context information for a specific use case, and which may confuse less-experienced users.

Per-instance custom prompt definitions

As noted earlier, it’s easy enough to define a custom prompt dynamically, as a command-line option, or even globally within a configuration file. It could be useful if the custom prompts could be defined on a per-connection basis, and stored with other connection options in mysql_config_editor. Doing so would allow users to connect to a specific instance, and have a specific (non-default) prompt automatically associated.

Conclusion

Please let us know if you think the reasoning behind our decision to leave the prompt as-is is unsound in any way, or if you have particular affinity for any of the proposed alternatives. Thanks!

MySQL High Availability with Oracle Clusterware

MySQL has an extensive range of high-availability solutions to suit many different use cases and deployment needs.  This list spans from the time-tested – yet continuously-improved – MySQL replication to the just-released MySQL Fabric, giving users many certified solutions for highly available MySQL deployments.  The list is growing yet again, with Oracle Clusterware adding support for MySQL.

Oracle’s Clusterware product is the foundation for the Oracle RAC, and has been battle-tested for high availability support for Oracle database, as well as other Oracle applications.  This technology is now available as part of the MySQL Enterprise subscription, and – like all Oracle commercial products – is freely available for evaluation purposes.  This post will explain Oracle Clusterware architecture and the benefits to MySQL users, and will be followed by a later post focusing on how to deploy Clusterware agents with MySQL.

A very flexible architecture gives Oracle Clusterware the ability to support various consistency mechanisms.  The initial release of the Clusterware agent for MySQL uses a shared resource approach, where essential resources – such as the data directory – are deployed on a shared disk.  A similar strategy is employed in other high-availability solutions (OVM High Availability Template for MySQL, Oracle Solaris Clustering, MySQL with Windows Cluster Failover).  The flexibility of Clusterware doesn’t dictate a specific shared resource implementation – anything from a simple NFS mount to a high-performance SAN may be used.  The recommended and tested solution leverages the Oracle ACFS filesystem.  As with other shared-disk high availability solutions for MySQL, an Oracle Clusterware-based solution requires only one MySQL instance be using a shared MySQL data directory at any one time.

While no high availability solution for MySQL is truly transparent, the Clusterware system provides useful infrastructure to minimize downtime.   The agent performs periodic health checks of the running MySQL Server using mysqladmin, and applications connect through a managed virtual IP address.  The use of a managed virtual IP address directs application traffic to a failover host without requiring configuration changes at the application layer. Failover time is bounded by the interval of agent health checks (every second by default) plus the time required to start the MySQL Server on the failover host (including any necessary crash recovery processing).

A big thanks goes out to the Oracle Clusterware team who did the heavy lifting in adding MySQL support!

 

 

Batch mode and expired passwords

A series of related discussions triggered by difficulty in setting passwords via scripts using the mysql command-line client when an account has an expired password caused me to look into the interaction between expired passwords and batch mode, and this blog post resulted.  I hope it’s a useful explanation of the behavior and the workaround to those troubled by it, and amplifies the excellent documentation in the user manual. Continue reading Batch mode and expired passwords

Password expiration policy in MySQL Server 5.7

I’ve previously noted my wish to have a comprehensive password policy in MySQL Server.  MySQL Server 5.7.4 takes a significant step towards this goal by adding native support for enforcing password lifetime policy.  This complements the validate_password plugin introduced in MySQL Server 5.6, which helps ensure adequate password complexity, and builds on the password expiration mechanism also introduced in MySQL Server 5.6.  This new feature has a new documentation page, and is covered in the MySQL Server 5.7.4 change logs, which state:

MySQL now enables database administrators to establish a policy for automatic password expiration: Any user who connects to the server using an account for which the password is past its permitted lifetime must change the password.

Good stuff – let’s look at it in some detail. Continue reading Password expiration policy in MySQL Server 5.7