MySQL Sandbox supports latest MySQL releases, has more metadata and docs

MySQL Sandbox has been updated again. The latest version is 3.0.38, which was just released. There were four releases in the space of one week, and this last one is just a polished edition.

Cherry-picking from the Change log:

• Added option --bind_address to complement the effects of --remote_access;
• The script 'enable_gtid' (for MySQL 5.6 +) now is durable. Previously the changes did not survive a restart.
• Now you can install MariaDB with its bizarre version '10.0'
• It also works well with MySQL 5.7. A bug prevented the creation of 'enable_gtid', but it is now fixed.
• Enhanced the 'check_slaves' script in replication sandboxes. Now it includes the output of 'show master status', so you can see at a glance if replication is working well;
• Added README file to each sandbox (simple or multiple)
• Added connection.json to each sandbox (simple or multiple) The file contains information to use the sandbox with third party applications.
• Added connection samples (for PHP, Perl, Python, Java, shell) to 'connection.json';
• Added about 80 new tests to the test suite. Now the package has more than 2,000 tests (~290 tests running 7 times, each with a different MySQL version)

Documentation and metadata

The changes that probably are most visible to users involve metadata and documentation.

When you install a single sandbox, you will find a README file, containing a summary of the actions that you can do with the sandbox, and instructions to find more information.

Composite sandboxes (replication, multiple, circular, custom) have their own customised README.

While README is a pleasant addition for first time users, the metadata files could be beneficial also to old timers. In each sandbox there are two files:

• connection.json
• default_connection.json

If it is a single sandbox, you will see something like this:

{
    "origin": {
        "mysql_sandbox_version" : "3.0.38",
        "mysql_version":    "5.6",
        "binaries":         "/Users/gmax/opt/mysql/5.6.11"
    },
    "connection": {
        "host":             "127.0.0.1",
        "port":             "5611",
        "socket":           "/tmp/mysql_sandbox5611.sock",
        "bind_address":     "127.0.0.1"
    },
    "users": {
       "admin": {
            "username":     "root@localhost",
            "password":     "msandbox",
            "privileges":   "all, with grant option"
        },
       "all_privileges": {
            "username":     "msandbox@127.%",
            "password":     "msandbox",
            "privileges":   "all, no grant option"
        },
        "read_write": {
            "username":     "msandbox_rw@127.%",
            "password":     "msandbox",
            "privileges":   "SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,INDEX,ALTER,SHOW DATABASES,CREATE TEMPORARY TABLES,LOCK TABLES, EXECUTE"
        },
        "read_only": {
            "username":     "msandbox_ro@127.%",
            "password":     "msandbox",
            "privileges":   "SELECT,EXECUTE"
        },
        "replication": {
            "username":     "rsandbox@127.%",
            "password":     "rsandbox",
            "privileges":   "REPLICATION SLAVE"
        }
    },
    "samples": {
        "php": {
            "mysqli" : "$mysqli = new mysqli('127.0.0.1', 'msandbox', 'msandbox', 'test', '5611');",
            "pdo"    : "$dbh = new PDO('mysql:host=127.0.0.1;port=5531', 'msandbox', 'msandbox');"
        },
        "perl" : {
            "dbi" : "$dbh=DBI->connect( 'DBI:mysql:host=127.0.0.1;port=5611', 'msandbox', 'msandbox')"
        },
        "python" : {
            "mysql.connector" : "cnx = mysql.connector.connect(user='msandbox', password='msandbox', host='127.0.0.1', port=5611, database='test')"
        },
        "java" : {
            "DriverManager" : "con=DriverManager.getConnection(\\\"jdbc:mysql://127.0.0.1:5611/test\\\", \\\"msandbox\\\", \\\"msandbox\\\")"
        },
        "shell" : {
            "generic": "/Users/gmax/opt/mysql/5.6.11/bin/mysql -h 127.0.0.1 -P 5611 -u msandbox -pmsandbox"
        }
    }
}

The file contains most of the information needed to use a sandbox with third party applications, and even ready-made connection strings for some popular programming languages.

If you safe in a hurry, you may look at default_connection.json, instead:

{
    "host":     "127.0.0.1",
    "port":     "5611",
    "socket":   "/tmp/mysql_sandbox5611.sock",
    "username": "msandbox@127.%",
    "password": "msandbox"
}

In composite sandboxes, these files collect the json objects for the underlying sandboxes

$ cat ~/sandboxes/rsandbox_5_6_11/default_connection.json
{
"master":
    {
        "host":     "127.0.0.1",
        "port":     "18876",
        "socket":   "/tmp/mysql_sandbox18876.sock",
        "username": "msandbox@127.%",
        "password": "msandbox"
    }
,
"node1":
    {
        "host":     "127.0.0.1",
        "port":     "18877",
        "socket":   "/tmp/mysql_sandbox18877.sock",
        "username": "msandbox@127.%",
        "password": "msandbox"
    }
,
"node2":
    {
        "host":     "127.0.0.1",
        "port":     "18878",
        "socket":   "/tmp/mysql_sandbox18878.sock",
        "username": "msandbox@127.%",
        "password": "msandbox"
    }
}

There is another addition that was not emphasised before. In each single sandbox, there is a script named 'json_in_db'.

This script loads the contents of connection.json into the database server. This could be useful if you want to access the metadata through a SQL interface. I was also thinking of loading the same data into a table by default, but I did not want to introduce database objects without user acknowledgement. It's probably a behaviour that can be authorised with another installation option.

UPDATE: I have just found out that in the replication README, the head credits are included twice. Oh, well. I will fix it as soon as there are some more changes. Three releases in two days are enough.

Access sandboxes from other hosts

By default, a MySQL sandbox instance can be accessed by localhost only.

This access is regulated by an option, "--remote_access", which is set to '127.%', and it is used to create the default users. You can see the resulting instructions in the file 'grants.mysql' inside each sandbox.

grant all on *.* to msandbox@'127.%' identified by 'msandbox';
  grant all on *.* to msandbox@'localhost' identified by 'msandbox';
  grant SELECT,EXECUTE on *.* to msandbox_ro@'127.%' identified by 'msandbox';
  grant SELECT,EXECUTE on *.* to msandbox_ro@'localhost' identified by 'msandbox';
  grant REPLICATION SLAVE on *.* to rsandbox@'127.%' identified by 'rsandbox';

If you want to access the sandbox remotely, you can change "--remote_access" to include your specific subnet, or to open it completely:

--remote_access='192.168.1.%'
  --remote_access='%'

Starting with MySQL::Sandbox 3.0.34, you can also define the bind address for your MySQL server. By default it is '127.0.0.1'. It will be changed to '0.0.0.0' if you choose a customized --remote_access. If you change both --remote_access and --bind_address, No adjustment will be made.

Happy hacking!