Sandboxes in MySQL Shell
![](https://res.cloudinary.com/strozstuff/image/fetch/https://idmqjyw9i2ib.objectstorage.us-ashburn-1.oci.customer-oci.com/n/idmqjyw9i2ib/b/blog/o/2024/mysql-shell-sandboxes/header.jpg)
Over the last few years, I have become quite smitten with MySQL Shell. For those who may not be familiar with MySQL Shell, it is a new(ish) command line interface (CLI) for connecting to and managing MySQL instances. During a recent episode of Inside MySQL: Sakila Speaks, Fred and I talked to Miguel Araujo about many of the helpful (and lesser known) features of MySQL Shell. This post is the fourth in a series about these “hidden gem” features.
The Problem
Sometimes, we might need additional instances of MySQL running to test configurations for read replicas, InnoDB clustering, InnoDB cluster sets, etc. In the past, this might have involved spinning up new instances of MySQL on other systems in your environment, which can be time-consuming and expensive. MySQL Shell has a feature that can make this easier, less time-consuming, and less expensive.
The Solution
MySQL Shell allows us to spin up MySQL Sandbox instances on our local machines. We can then use these instances to test and verify the architecture setup before deploying them to production. Creating a new instance is as easy as running a single command.
Creating a Sandbox
A version of MySQL must be running on the local system to run sandbox instances
To create a sandbox instance, make sure you are in JavaScript mode and run the command:
dba.deploySandboxInstance({port number})
The value of {port number}
is the port we want the sandbox instance to run. For example, if we’re going to run a sandbox instance on port 3336
, we would run the command:
dba.deploySandboxInstance(3336)
When we run this command, we will be asked to provide a password for the root
user. Once the instance is up and running, we will see a message that it has been successfully deployed and started.
By default, any sandbox instances will be created in a directory named mysql-sandboxes
in the home directory of the user who ran the command.
Connecting to Our New Sandbox
We can use the following command to connect to our new instance.
\c root@localhost:3336
When we execute this command, we will be prompted to provide the password for the root
user. Once we provide the password, we will be connected to the new instance.
Configuration Options
When we deploy a new sandbox instance, we can provide options for the sandbox when we call deploySandBoxInstance()
. Here are some of the options we can use.
password
- The password for the root user on the new instancesandboxDir
- The path to a directory where we want the new instance to be createdportx
- The port to use for the X-Protocol. By default, this value will be calculated as 10 times the value of the provided port number.
If you want to see more information about these options, run the command:
\? dba.deploySandboxInstance
Sandbox Management
There are other commands we can use to help us manage sandbox instances.
Stopping an Instance
We can use dba.stopSandboxInstance({port number})
to stop an instance gracefully. The value of {port number}
is the port number the instance runs on. To stop the instance we created above, run the command:
dba.stopSandboxInstance(3336)
When we stop a sandbox instance, we will be asked for the root
password.
Starting an Instance
We can use dba.startSandboxInstance({port number})
to start a previously deployed instance. The value of {port number}
is the port number the instance was initially deployed on. To start the instance we created above, run the command:
dba.startSandboxInstance(3336)
Killing an Instance
We can use dba.killSandboxInstance({port number})
to stop an instance without doing so gracefully. This process can be useful in mimicking unexpected halts of the system. The value of {port number}
is the port number the instance runs on. To kill the instance we created above, run the command:
dba.killSandboxInstance(3336)
Deleting an Instance
We can use dba.deleteSandboxInstance({port number})
to delete a sandbox instance. The value of {port number}
is the port number the instance runs on. To delete the instance we created above, run the command:
dba.deleteSandboxInstance(3336)
A sandbox instance must be stopped or killed before we can delete it.
Wrap-Up
MySQL sandbox instances can make testing the development and deployment of various MySQL architectures much easier and less time-consuming. MySQL Shell allows us to manage these instances through easy-to-use commands. To learn more about sandboxes and how we can use them for setting up MySQL architectures, check out the documentation.
Photo by Markus Spiske on Unsplash
Related Entries
- Running External Scripts in MySQL Shell
- Executing System Commands in MySQL Shell
- Getting Help in MySQL Shell
- Server Upgrade Checks with MySQL Shell
- MySQL Connection Status with MySQL Shell
- Managing MySQL Shell Configuration Options
- Advanced Data Dump with MySQL Shell
- Advanced Data Load with MySQL Shell
- MySQL Shell CLI Integration
- MySQL Shell Table Export Utility
- Copy Data Directly To A MySQL Instance With MySQL Shell
- Create an InnoDB Replica Set With MySQL Shell
- Using MySQL Shell to Get MySQL Diagnostic Information
- Customizing The MySQL Shell Prompt
- Extending MySQL Shell with Startup Scripts
- Extending MySQL Shell with Plugins