Use MySQL Shell Securely from Bash

mysql shell

mysql shellThis blog post discusses how to use MySQL shell securely from Bash.

The Bourne shell is everywhere. It is part of the most basic Linux install. You will find it on the biggest SPARC machines down to a Raspberry Pi. It is nice to know it will always be there. Unlike other, more complex scripting environments such as Perl and Python, it doesn’t require any additional dependencies to be installed.

Anyone that has automated a MySQL task using a Bourne shell such as Bash will be familiar with the following message:

Warning: Using a password on the command line interface can be insecure.

This semi-ominous warning describes a security flaw in passing credentials on a process command line. Any unprivileged user on the system can use a command like ps aux to find these credentials. While the MySQL shell has added some additional protections to hide these credentials, other information such as database user names, host names, ports and sockets can still be determined by process scanning.

The recommended approach to get around this warning is to use a configuration file to store these credentials. However, in the case of a self-contained script, we may not want to require the user to create a credential file. It would need to be maintained, and might interfere with other tools that use the MySQL shell. For creating automated tools in the Percona Development Services department, I came up with a couple of methods for integrating the MySQL shell into Bash (or any other Bourne Shell) securely.

This first script demonstrates the shell function mysql_exec(). This is for use with small queries that are normally passed to the MySQL shell via the -e parameter.

# call mysql client from shell script without
# passing credentials on command line
# This demonstrates small single queries using
# the -e parameter.   Credentials and connection
# info are sent through standard input.
# david . bennett @ percona . com - 9/24/2016
mysql_exec() {
  local query="$1"
  local opts="$2"
    printf "%sn"
      | mysql --defaults-file=/dev/stdin "${opts}" -e "${query}"
mysql_exec "select 'Hello World' as Message"
echo "${mysql_exec_result}"

The above script allows the specification of credentials and connection information via variables in the script. As with any other shell script, these can be moved into a configuration file and secured with chown/chmod, then included with the source or . command. The mysql_exec() function creates a default my.cnf [client] on the fly and passes it to the MySQL shell via –defaults-file=/dev/stdin. The configuration is never written to disk, which makes this method a bit more secure.

Sometimes, you need to process too many queries to pass on the command line. In this case, there is another technique for passing the credentials.

mysql_exec_from_file() {
  local query_file="$1"
  local opts="$2"
  local tmpcnf="$(mktemp)"
  chmod 600 "${tmpcnf}"
  printf "%sn"
    > "${tmpcnf}"
      mysql --defaults-file="${tmpcnf}" "$opts" < "${query_file}"
  rm "${tmpcnf}"

This technique uses a temporary file, which allows the queries to be passed from a file or input device. Restrictive permissions are set on the file before the configuration is written. The temporary configuration is removed immediately after the shell exits.

While other languages may offer cleaner ways to access your MySQL database, you’ll always know that you’ll be able to execute your shell-based MySQL job scripts across all of the Unix machines in your enterprise.

You can download these scripts directly from my github account.

Happy scripting!


MySQL shell prompt vs MongoDB shell prompt

Recently Todd Farmer shared an interesting story about the mysql command line prompt in MySQL 5.7: how it was changed to provide more context and why the change was finally reverted. This made me think that after using the command line client for MongoDB for awhile, I would love seeing a much more modern mysql shell prompt. Here are a few examples of what a modern command line client can do.

Add dynamic information to the prompt

If you use replication with MongoDB, you have probably noticed a nice feature of the prompt: it is replication aware. What I mean is that for a standalone instance, the prompt is simply:


When you configure this instance to be the primary of a replica set named RS, the prompt automatically becomes:


and for secondaries, you will see:


And of course if an election is triggered and roles are switched, the prompt is updated accordingly to reflect the change.

Such a feature may not be easily transposed to MySQL as the allowed replication topologies are more flexible: for instance with master-master replication or for chained replication, some servers may be both a master and a slave.

However if you look a bit deeper how the MongoDB shell prompt can be customized, it is very flexible as you can set the prompt variable to the result of any Javascript function. So for instance if you write this:

var prompt=function() {
    return db.getName()+"/"+ISODate().toLocaleTimeString()+"> ";

Your prompt will be changed to something like:


And the change can be persisted by defining the prompt variable in a ~/.mongorc.js file.

If I could do something similar with MySQL, I would for instance be able to know the replication lag of a slave or the uptime of MySQL and so on and so forth. That would be incredibly useful in some situations.

Better help system

Let’s assume I want to manually fail over to a secondary in a MongoDB replica set. I can simply instruct the master that it should no longer be the master. But what is the exact command?

I know that it is rs.xxx because all replica sets functions have this format, so let’s simply open the shell and type rs. and all the available options show up:

> rs.
rs.add(                   rs.constructor            rs.propertyIsEnumerable(  rs.syncFrom(
rs.addArb(                rs.debug                  rs.prototype              rs.toLocaleString(
rs.apply(                 rs.freeze(                rs.reconfig(              rs.toString(
rs.bind(                  rs.hasOwnProperty(        rs.remove(                rs.valueOf(
rs.call(                  rs.help(                  rs.slaveOk(
rs.conf(                  rs.initiate(              rs.status(
rs.config(                rs.isMaster(              rs.stepDown(

Here again, I’d love to see something similar with MySQL. For instance each time I need to convert a timestamp to a human readable date, I have to look at the documentation to find the exact function name.

Actually I could have used the built-in help system to find the name of the function, but it is much more convenient to look at the documentation.


What’s your opinion on which new features a modern mysql shell should provide? If many people agree on a set of features, it could be worth filing a feature request to get a better mysql command line client in a future version of MySQL.

The post MySQL shell prompt vs MongoDB shell prompt appeared first on MySQL Performance Blog.

Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com