MySQL::Sandbox 3.0.66 - improved usability and support for newest releases

The latest MySQL Sandbox, version 3.0.66 is out. It has a few new features (as always, when I find myself doing the same thing many times, I script it) and improved support for latest releases of MySQL. You can now install, among other versions, MySQL 5.7.8 and MariaDB 10.1.x

Some notable additions in this release are in the scripts that are created and customized for each sandbox. There are many of them and when one more arrives, it's easy to overlook it. So, here are the new arrivals.

./show_binlog

When I am troubleshooting replication behavior, I often need to inspect the latest binary log. The sandbox has a shortcut that gives me the right version of mysqlbinlog for the deployment:

./my sqlbinlog data/mysql-bin.000002 |less

(Notice the blank between “my” and “sqlbinlog”.)

However, this shortcut is still long to type, and requires that I find the latest binlog. So, now, there is a show_binlog script that does exactly that. It gets the latest binary log and shows it using mysqlbinlog –verbose.

By default, the script gets the files from ./data/mysql-bin.[0–9]*, but I can indicate something different.

I can invoke the script with a number, and get the corresponding binlog

./show_binlog 000002

(Will show ./data/mysql-bin.000002)

And I can then pipe it through a pager

./show_binlog | less  
./show_binlog | vim -        

Or pipe it to something else:

./show_binlog | grep -i 'create table'

./show_relaylog

This script is similar to show_binlog, but it shows, you can guess it, a relay log instead.
It accepts two optional arguments. The first is the base name of the relay log (by default "mysql-relay") and the second a number to identify the log, same as we have seen for show_binlog.
This is particularly useful when we are dealing with multi-source replication, where the fan-in slave has several relay-groups.

./show_relaylog mysql-relay-node2 | grep -i create

./add_option

The sandbox offers already the ability of restarting the server with a new parameter.

./restart --gtid-mode=ON --master-info-repository=table \  
    --relay-log-info-repository=table \  
    --gtid_mode=ON  \  
    --enforce-gtid-consistency

This is convenient and easy, but the option is used only once. When you restart the sandbox, it’s lost. The script add_option solves this problem.

./add_option master-info-repository=table \  
    relay-log-info-repository=table \  
    gtid_mode=ON  \  
    enforce-gtid-consistency  
# option 'master-info-repository=table' added to configuration file  
# option 'relay-log-info-repository=table' added to configuration file  
# option 'gtid_mode=ON' added to configuration file  
# option 'enforce-gtid-consistency' added to configuration file  
. sandbox server started

./json_in_db

This script has been around for some time. It’s a script that loads the contents of the sandbox JSON description into a table.

The new thing about this script is that, if you use MySQL 5.7.8 or later, the table will have a JSON column instead of a plain TEXT.

Let’s have a look.

$ ./json_in_db  
connection.json saved to test.connection_json

This does not look like much. But inside the table we will find something interesting.

$ ./use test  
Reading table information for completion of table and column names  
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.  
Your MySQL connection id is 12  
Server version: 5.7.8-rc MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its  
affiliates. Other names may be trademarks of their respective  
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql [localhost] {msandbox} (test) > desc connection_json;  
+-------+------+------+-----+---------+-------+  
| Field | Type | Null | Key | Default | Extra |  
+-------+------+------+-----+---------+-------+  
| t     | json | YES  |     | NULL    |       |  
+-------+------+------+-----+---------+-------+  
1 row in set (0.00 sec)    

mysql [localhost] {msandbox} (test) > select json_extract(t, '$.users.admin') from connection_json\G  
*************************** 1. row ***************************  
json_extract(t, '$.users.admin'): {"password": "msandbox", "username": "root@localhost", "privileges": "all, with grant option"}  
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (test) > select json_extract(t, '$.users.read_write') from connection_json\G  
*************************** 1. row ***************************  
json_extract(t, '$.users.read_write'): {"password": "msandbox", "username": "msandbox_rw@127.%", "privileges": "SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,INDEX,ALTER,SHOW DATABASES,CREATE TEMPORARY TABLES,LOCK TABLES, EXECUTE"}  
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (test) > select json_extract(t, '$.origin') from connection_json\G  
*************************** 1. row ***************************  
json_extract(t, '$.origin'): {"binaries": "/Users/gmax/opt/mysql/5.7.8", "mysql_version": "5.7", "mysql_sandbox_version": "3.0.62"}  
1 row in set (0.00 sec)

As you can see, you can use JSON extraction syntax to manipulate the data inside.

./mycli and $MYSQL_EDITOR

mycli is a convenient command line client for MySQL that has many useful features. It would be nice to use it with MySQL Sandbox. You can, of course, say something like

mycli --user=msandbox --pass=msandbpx --port=5708 --socket=/tmp/mysql_sandbox_5708.sock

But to do this you need to remember the port number and there is way too much to write. Starting with version 3.0.66, MySQL Sandbox includes in each sandbox a ./mycli script, which you can invoke instead of ./use.
Just type ./mycli, and , provided that mycli is installed, it will be used with the appropriate options.
And there is another addition, since we are dealing with MySQL clients. The ./use script now recognizes a MYSQL_EDITOR variable. If you set this variable to point to your favorite client, it will be used instead of the "mysql" client from the sandbox version. Whatever you put in that variable, though, should recognize the option --defaults-file, because this is how the client is invoked.
Thanks to Morgan Tocker for suggesting these two features.
Update: the author of mycli responded to a feature request, and made mycli able to use a mysql configuration file (with --defaults-file). Thus, now you can use 'mycli' by setting the MYSQL_EDITOR variable.

$ export MYSQL_EDITOR=mycli
$ ./use
Version: 1.2.0
Chat: https://gitter.im/dbcli/mycli
Mail: https://groups.google.com/forum/#!forum/mycli-users
Home: http://mycli.net
Thanks to the contributor - Nathan Taggart
mysql msandbox@localhost:(none)>

However, be aware that mycli is an interactive client. It does not support "-e" and batch mode. If you use both, it's better to stick to the two scripts: ./use to invoke the default MySQL client for batch mode and "-e", and ./mycli for interactive usage.