MySQL turns 8.0 and the technical preview integrates a new “InnoDB Cluster”. The overall architecture reminds MongoDB:
– group replication with a single master, similar to replica-sets;
– a mysqlsh able to create replication group and local instances supporting js and python;
– a MySQL Router as a gateway to appservers, to be deployed on each client machine like the mongos.
Once installed, you can create a RG with a few commands:
su - rpolli
mysqlsh
\py # enable python mode. Create 3 instances in ~/sandbox-dir/{3310,3320,3330}
for port in (3310, 3320, 3330, 3340, 3350):
dba.deploy_local_instance(port,{"password":"secret"});
Now we have 5 mysql instances listening on various ports. Create a cluster and check the newly created mysql_innodb_cluster_metadata schema.
\connect root:root@localhost:3310
cluster = dba.create_cluster('polli', 'pollikey');
\sql # switch to sql mode
SHOW DATABASES;
| Database |
+-------------------------------+
| information_schema |
| mysql |
| mysql_innodb_cluster_metadata |
| performance_schema |
| sys |
Go back to the python mode and add the remaining instances to the cluster.
\py # return to python mode again
# Eventually re-get the cluster.
cluster = dba.get_cluster('polli',{'masterKey':'pollikey'}) # masterKey is a shared secret between nodes.
# Add the other nodes
for port in ports[1:]:
cluster.add_instance('root@localhost:' + str(port),'secret');
# Check status
cluster.status() # BEWARE! The output is a str :( not a dict
{
"clusterName": "polli",
"defaultReplicaSet": {
"status": "Cluster tolerant to up to 2 failures.",
"topology": {
"localhost:3310": {
"address": "localhost:3310",
"status": "ONLINE",
"role": "HA",
"mode": "R/W",
"leaves": {
"localhost:3320": {
"address": "localhost:3320",
"status": "ONLINE",
"role": "HA",
"mode": "R/O",
"leaves": {}
},
"localhost:3330": {
"address": "localhost:3330",
"status": "ONLINE",
"role": "HA",
"mode": "R/O",
"leaves": {}
}
....
}
}
}
}
}
Now check the failover feature.
dba.kill_local_instance(3310) # Successfully killed
# Parse the output with...
import json
json.loads(cluster.status())["defaultReplicaSet"]["topology"].keys() # localhost:3320 WOW!
Once set up, created users will span the whole group.
\sql
CREATE USER 'admin'@'%' IDENTIFIED BY 'secret';
GRANT ALL ON *.* TO 'admin'@'%' WITH GRANT OPTION;
Now let’s connect to different cluster nodes.
mysql -uadmin -P3310 -psecret -e 'create database this_works_on_master;' # OK
mysql -uadmin -P3320 -psecret -e 'create database wont_work_on_slave_even_if_admin;'
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement
The default setup allows writings only on master *even for admin|super users* that can be overriden as usual.
mysql> SHOW VARIABLES LIKE '%only'
mysql> show variables like '%only';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
...
| read_only | ON |
| super_read_only | ON |
...
+-------------------------------+-------+
mysql> set global super_read_only = OFF; -- just for root
mysql> set global super_read_only = ON;
mysql> set global read_only = OFF; -- for all allowed users
mysql>
Mongodb python driver is topology-aware. MySQL connectors instead rely on mysql-router for connecting to the right primary.