InnoDB Cluster Sandbox
To create a sandbox environment with three instances using different ports on the same machine...
Pre-Reqs
Install MySQL, MySQL Shell and MySQL Router...
Deploy Sandbox Cluster
mysqlsh
shell.options.sandboxDir='/mysql/mysql-sandboxes'
If you don't set this, it defaults to $HOME/mysql-sandboxesdba.deploySandboxInstance(3310)
dba.deploySandboxInstance(3320)
dba.deploySandboxInstance(3330)
\connect root@localhost:3310
cluster = dba.createCluster("myCluster")
cluster.addInstance("root@localhost:3320")
cluster.addInstance("root@localhost:3330")
cluster.status()
{ "clusterName": "myCluster", "defaultReplicaSet": { "name": "default", "primary": "127.0.0.1:3310", "ssl": "REQUIRED", "status": "OK", "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", "topology": { "127.0.0.1:3310": { "address": "127.0.0.1:3310", "memberRole": "PRIMARY", "mode": "R/W", "readReplicas": {}, "replicationLag": null, "role": "HA", "status": "ONLINE", "version": "8.0.25" }, "127.0.0.1:3320": { "address": "127.0.0.1:3320", "memberRole": "SECONDARY", "mode": "R/O", "readReplicas": {}, "replicationLag": null, "role": "HA", "status": "ONLINE", "version": "8.0.25" }, "127.0.0.1:3330": { "address": "127.0.0.1:3330", "memberRole": "SECONDARY", "mode": "R/O", "readReplicas": {}, "replicationLag": null, "role": "HA", "status": "ONLINE", "version": "8.0.25" } }, "topologyMode": "Single-Primary" }, "groupInformationSourceMember": "127.0.0.1:3310"}
\q
Bootstrap Router
mysqlrouter --bootstrap root@localhost:3310 --user=root --directory /mysql/myrouter
# Bootstrapping MySQL Router instance at '/mysql/myrouter'...
- Creating account(s) (only those that are needed, if any)- Verifying account (using it to run SQL queries that would be run by Router)- Storing account in keyring- Adjusting permissions of generated files- Creating configuration /mysql/myrouter/mysqlrouter.conf
# MySQL Router configured for the InnoDB Cluster 'myCluster'
After this MySQL Router has been started with the generated configuration
$ mysqlrouter -c /mysql/myrouter/mysqlrouter.conf
the cluster 'myCluster' can be reached by connecting to:
## MySQL Classic protocol
- Read/Write Connections: localhost:6446- Read/Only Connections: localhost:6447
## MySQL X protocol
- Read/Write Connections: localhost:6448- Read/Only Connections: localhost:6449
cd /mysql/myrouter
./start.sh
Router Testing
mysql -u root -h 127.0.0.1 -P 6446 -p
SELECT @@port;
quit
mysql -u root -h 127.0.0.1 -P 6447 -p
SELECT @@port;
quit
mysql -u root -h 127.0.0.1 -P 6447 -p
SELECT @@port;
quit
Should return 3310
Should return 3320
Should return 3330
Failover Testing
Session #1
mysqlsh --uri root@127.0.0.1:6446
dba.killSandboxInstance(3310)
Should return 3320 ->
Should return 3330 ->
dba.startSandboxInstance(3310)
Session #2
mysql -u root -h 127.0.0.1 -P 6446 -p
SELECT @@port;
quit
mysql -u root -h 127.0.0.1 -P 6447 -p
SELECT @@port;
quit
Remove Sandbox
dba.stopSandboxInstance(3310)
dba.stopSandboxInstance(3320)
dba.stopSandboxInstance(3330)
dba.deleteSandboxInstance(3310)
dba.deleteSandboxInstance(3320)
dba.deleteSandboxInstance(3330)