There was already an article in ORDIX News 3/2012 that dealt with the provision of test environments (sandboxes). Of course, the MySQL Shell didn't exist back then. This tool (unfortunately still often unknown to DBAs) is very suitable for quickly creating test environments of various colors as sandboxes.

Sandbox toys: What is a sandbox and what can the MySQL Shell do?

Before we deal with the provision of test environments, let's quickly clarify some basics.
A sandbox environment, is an isolated MySQL environment that can operate independently of many other environments. It has independent binaries, physical directories, processes and configuration files and can be started and stopped independently. 

The MySQL Shell is a comprehensive client tool which can be used for different purposes, e.g., for the administration (including provisioning & decommissioning) of instances, clusters and replication systems. The shell can also be used to script (JS, Python) or create reports. In addition, APIs and small useful helpers (such as the upgrade checker or a JSON importer) are available and make the DBAs life more convenient.

--> https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-features.html

My sandbox, your sandbox ...

Among other things, the shell can also be used to set up test environments, the so-called sandbox instances. This can be implemented either interactively in the shell with a command or, in the case of several / more complex setups, also with a script:

In our example, an instance is started on port 3399. All files belonging to the instance (data & configuration files, start & stop scripts) are managed in the directory "/opt/mysql/sand/3399":

As can be seen, the specified path ("/opt/mysql/sand/3399") contains the binaries ("./bin"), the database files ("./sandboxdata"), the configuration file ("my.cnf ") and the start and stop scripts (" ./start.sh "," ./stop.sh ")

Using a script (JS), two instances could be set up as follows:

Of course, more complex structures such as clusters and / or replication systems can also be built from sandbox systems via scripts. The necessary commands (e.g. "dba.createCluster()") can be used in the script in the same way as the instructions above for structure.

Here is another example (JS script) that can be used to destroy a sandbox:

Of course, this could have been done again in a dialogue-oriented manner.

What to do with all the sand?

The shell can be configured extensively. The current configuration status can be queried with the following command:

The variable "sandboxDir" is of course relevant for this blog. This defines where our "sandboxes" are set up in the file system. Of course, this can be changed. The "--persist" flag ensures that this change is applied permanently (beyond the current MySQL shell session):

Conclusion: Is it just a gimmick?

The MySQL Shell is quite suitable to provide test environments (also on a larger scale). The environments are set up and operated completely independently. As a DBA, you only have to ensure that the port is unequivocally selected. Using the shell is easy and quick to learn. Do you have any questions about the operation of MySQL? Then contact us.