Sandbox games 2.0*: How to deploy MySQL test environments with the MySQL Shell

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:

MySQL  JS > dba.deploySandboxInstance(3399) 
A new MySQL sandbox instance will be created on this host in 
/opt/mysql/sand/3399 

Warning: Sandbox instances are only suitable for deploying and 
running on your local machine for testing purposes and are not 
accessible from external networks. 

Please enter a MySQL root password for the new instance: **** 

Deploying new MySQL instance... 

Instance localhost:3399 successfully deployed and started. 
Use shell.connect('root@localhost:3399') to connect to the instance.  

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":

[root@localhost 3399]# pwd 
/opt/mysql/sand/3399 

[root@localhost 3399]# ls -la 
insgesamt 20 
drwxr-xr-x  6 root root  127 17. Mär 10:00 . 
drwxr-xr-x. 8 root root   78 17. Mär 10:23 .. 
-rw-r-----  1 root root    6 17. Mär 10:00 3399.pid 
drwxr-xr-x  2 root root   20 17. Mär 09:59 bin 
drwxr-xr-x  3 root root   21 17. Mär 09:59 lib 
-rw-------  1 root root  815 17. Mär 09:59 my.cnf 
drwxr-xr-x  2 root root    6 17. Mär 09:59 mysql-files 
drwxr-x---  6 root root 4096 17. Mär 10:00 sandboxdata 
-rwx------  1 root root  262 17. Mär 10:00 start.sh 
-rwx------  1 root root  194 17. Mär 10:00 stop.sh  

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:

bash> mysqlsh -f sandbox.js 
Setup of two sandbox instances. 
Port 3350 
Port 3360 
Enter a password for root account: **** 
Setup instance on port: 3350 
A new MySQL sandbox instance will be created on this host in 
/opt/mysql/sand/3350 


Warning: Sandbox instances are only suitable for deploying and 
running on your local machine for testing purposes and are not 
accessible from external networks. 
... 
Instance localhost:3360 successfully deployed and started. 
Use shell.connect('root@localhost:3360') to connect to the instance. 
... 

bash> cat sandbox.js 

print('Setup of two sandbox instances.\n'); 
print('Port 3350\n'); 
print('Port 3360\n'); 

var Pass = shell.prompt('Enter a password for root account: ', {type:"password"});  

try { 
    print('Setup instance on port: 3350\n'); 
    dba.deploySandboxInstance(3350, {password: Pass}); 
    print('Setup instance on port: 3360\n'); 
    dba.deploySandboxInstance(3360, {password: Pass});  
} catch { 
print('Error: ' + e.message + '\n'); 
}  

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:

bash> cat deletebox.js 

print('Which box should be destroyed?\n');  

var PortNo = shell.prompt('Enter the port of sandbox: ');  

try { 
        print('Stop instance on port: ' + PortNo + '\n'); 
        dba.stopSandboxInstance(PortNo); 
        print('Destroy instance on port: ' + PortNo + '\n'); 
        dba.deleteSandboxInstance(PortNo);  
} 
catch(e) { 
print('Error: ' + e.message + '\n'); 
}  

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:

MySQL  JS > \option -l 
 autocomplete.nameCache          true 
 batchContinueOnError            false 
 ... 
 sandboxDir                      /opt/mysql/sand 
 showColumnTypeInfo              false 
 showWarnings                    true 
 useWizards                      true 
 verbose                         0  

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

MySQL  JS > \option --persist sandboxDir /tmp 
... 
sandboxDir                      /tmp 
...  

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.