一、InnoDB ReplicaSet 介绍
二、通过 MySQL Shell 部署 Sandbox 实例
# mysqlsh
MySQL JS > dba.deploySandboxInstance(3306)
MySQL JS > dba.deploySandboxInstance(3307)
MySQL JS > dba.deploySandboxInstance(3308)
指定 root 密码后自动创建 MySQL 实例,默认数据目录在 $HOME/mysql-sandboxes/port
三、通过 MySQL Shell 配置 InnoDB 副本集
3.1 创建集群管理账户
MySQL JS > dba.configureReplicaSetInstance('root@localhost:3306', {clusterAdmin: "'repl'@'%'", clusterAdminPassword: 'repl'});
3.2 创建 InnoDB 副本集
MySQL JS > \connect root@localhost:3306
MySQL localhost:3306 ssl JS > var rs = dba.createReplicaSet("renzy")
MySQL localhost:3306 ssl JS > rs.status()
{
"replicaSet": {
"name": "renzy",
"primary": "127.0.0.1:3306",
"status": "AVAILABLE",
"statusText": "All instances available.",
"topology": {
"127.0.0.1:3306": {
"address": "127.0.0.1:3306",
"instanceRole": "PRIMARY",
"mode": "R/W",
"status": "ONLINE"
}
},
"type": "ASYNC"
}
}
3.3 添加节点到副本集
MySQL localhost:3306 ssl JS > rs.addInstance('localhost:3307')
MySQL localhost:3306 ssl JS > rs.addInstance('localhost:3308')
MySQL localhost:3306 ssl JS > rs.status()
{
"replicaSet": {
"name": "renzy",
"primary": "127.0.0.1:3306",
"status": "AVAILABLE",
"statusText": "All instances available.",
"topology": {
"127.0.0.1:3306": {
"address": "127.0.0.1:3306",
"instanceRole": "PRIMARY",
"mode": "R/W",
"status": "ONLINE"
},
"127.0.0.1:3307": {
"address": "127.0.0.1:3307",
"instanceRole": "SECONDARY",
"mode": "R/O",
"replication": {
"applierStatus": "APPLIED_ALL",
"applierThreadState": "Slave has read all relay log; waiting for more updates",
"receiverStatus": "ON",
"receiverThreadState": "Waiting for master to send event",
"replicationLag": null
},
"status": "ONLINE"
},
"127.0.0.1:3308": {
"address": "127.0.0.1:3308",
"instanceRole": "SECONDARY",
"mode": "R/O",
"replication": {
"applierStatus": "APPLIED_ALL",
"applierThreadState": "Slave has read all relay log; waiting for more updates",
"receiverStatus": "ON",
"receiverThreadState": "Waiting for master to send event",
"replicationLag": null
},
"status": "ONLINE"
}
},
"type": "ASYNC"
}
}
3.4 副本集在线主从切换
MySQL localhost:3306 ssl JS > rs.setPrimaryInstance('127.0.0.1:3308')
MySQL localhost:3306 ssl JS > rs.status()
{
"replicaSet": {
"name": "renzy",
"primary": "127.0.0.1:3308",
"status": "AVAILABLE",
"statusText": "All instances available.",
"topology": {
"127.0.0.1:3306": {
"address": "127.0.0.1:3306",
"instanceRole": "SECONDARY",
"mode": "R/O",
"replication": {
"applierStatus": "APPLIED_ALL",
"applierThreadState": "Slave has read all relay log; waiting for more updates",
"receiverStatus": "ON",
"receiverThreadState": "Waiting for master to send event",
"replicationLag": null
},
"status": "ONLINE"
},
"127.0.0.1:3307": {
"address": "127.0.0.1:3307",
"instanceRole": "SECONDARY",
"mode": "R/O",
"replication": {
"applierStatus": "APPLIED_ALL",
"applierThreadState": "Slave has read all relay log; waiting for more updates",
"receiverStatus": "ON",
"receiverThreadState": "Waiting for master to send event",
"replicationLag": null
},
"status": "ONLINE"
},
"127.0.0.1:3308": {
"address": "127.0.0.1:3308",
"instanceRole": "PRIMARY",
"mode": "R/W",
"status": "ONLINE"
}
},
"type": "ASYNC"
}
}
3.5 副本集 Primary 节点故障
# ps -ef | grep 3308
root 18975 1 0 16:52 ? 00:01:23 /root/mysql-sandboxes/3308/bin/mysqld --defaults-file=/root/mysql-sandboxes/3308/my.cnf --user=root
# kill -9 18975
MySQL localhost:3306 ssl JS > rs.forcePrimaryInstance("127.0.0.1:3306")
四、通过 MySQL Router 使用副本集
mysqlrouter --user=root --bootstrap root@localhost:3308
五、MySQL Router 通过 R/W 自动连接到 Primary
mysqlrouter -c /usr/local/mysql-router-8.0.19-linux-glibc2.12-x86_64/mysqlrouter.conf &
// 通过 MySQL Router R/W 端口可以自动识别并连接到 Primary
# mysql -h10.186.63.158 -P6446 -uroot -proot -e 'select @@port;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------+
| @@port |
+--------+
| 3308 |
+--------+
# mysql -h10.186.63.158 -P6446 -uroot -proot -e 'show slave hosts;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+-----------+------+------------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+------------+-----------+------+------------+--------------------------------------+
| 1587786506 | 127.0.0.1 | 3307 | 2457151498 | cb569215-3b29-11ea-80f4-02000aba3f9e |
| 3616586416 | 127.0.0.1 | 3306 | 2457151498 | c23ee4be-3b29-11ea-815a-02000aba3f9e |
+------------+-----------+------+------------+--------------------------------------+
六、结论
1. MySQL Router 可以很好的兼容 InnoDB ReplicaSet,可自动识别到副本集主从切换,将新的 R/W 连接指向 Primary。
2. InnoDB ReplicaSet 当前还不完善,可作为新特性在测试环境试用,但因为不支持自动故障转移,Primary 宕机整个副本集将不可用。
3. InnoDB ReplicaSet 目前仅支持基于 GTID 的异步复制,哪怕支持自动切换,数据也有丢失风险,所以离真正部署到生产环境还有一段路要走。
4. InnoDB ReplicaSet 暂时还没有类似芒果 DB 完善的投票选举机制,故障切换时也会存在脑裂风险。
总之,InnoDB 副本集虽存在诸多不足之处,但作为 2020 年 Oracle 的开餐甜点其带来的效果也让众多 MySQL DBA 眼前一亮,既然有了副本集,相信 Sharding 也是未来可期,你觉得呢?