Todd's MySQL Blog

Tiny tidbits of trivia from Todd

Archive for the category “MySQL”

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)


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.


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. . . . . . . . . . . :
shell> bin\mysql -utodd -h192.168.2.54
Welcome to the MySQL monitor.  Commands end with ; or \g.
todd@ [(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@ [(none)] > prompt \U >
PROMPT set to '\U > '

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 >


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 ?

\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>

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)]

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.


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. Read more…

Redefining –ssl option

MySQL clients have long had a –ssl option.  Casual users may think specifying this option will cause clients to secure connections using SSL.  That is not the case: Read more…

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. Read more…

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.

Read more…

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:

Read more…

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)

Read more…

Post Navigation