1.INNODB CLUSTER 结构图
2.环境规划
(1).keepalived+HAProxy192.168.18.70vip(18.71/72)(2).MYSQL ROUTER192.168.18.89/90 读端口6447 写端口6446
(3).MGR 集群192.168.18.85/86/87/88 端口3306
3.创建innodb cluster集群
(1).连接到不同主机检查状态
mysqlsh -uroot -prootroot -h192.168.18.86 -P3306
shell.connect('root@192.168.18.85:3306');
dba.configureInstance('root@192.168.18.85:3306');
dba.checkInstanceConfiguration("root@192.168.18.85:3306");
shell.connect('root@192.168.18.86:3306');
dba.configureInstance('root@192.168.18.86:3306');
dba.checkInstanceConfiguration("root@192.168.18.86:3306");
shell.connect('root@192.168.18.87:3306');
dba.configureInstance('root@192.168.18.87:3306');
dba.checkInstanceConfiguration("root@192.168.18.87:3306");
shell.connect('root@192.168.18.88:3306');
dba.configureInstance('root@192.168.18.88:3306');
dba.checkInstanceConfiguration("root@192.168.18.88:3306");
shell.connect('root@192.168.18.86:3306')
(2).创建新集群时
var cluster = dba.createCluster('fgeducluster01');
cluster.addInstance('root@192.168.1.85:3306')
cluster.addInstance('root@192.168.1.87:3306')
OR:
cluster.addInstance(‘<user>@<host>:<port>’, {recoveryMethod: 'clone'})
cluster.addInstance('root@192.168.1.85:3306', {recoveryMethod: 'clone'})
cluster.addInstance('root@192.168.1.87:3306', {recoveryMethod: 'clone'})
(3).添加已存在的MGR集群
shell.connect('root@192.168.18.86:3306');
var cluster = dba.createCluster('fgeducluster01', {adoptFromGR: true});
dba.getCluster("fgeducluster01").status();
(4).删除集群
#删除从节点
cluster.removeInstance('root@192.168.18.87:3306')
cluster.removeInstance('root@192.168.18.85:3306')
#删除整个集群
cluster.dissolve({force:true})
(5).查看集群状态
var cluster = dba.getCluster('fgeducluster01');
cluster.status();
cluster.options();
cluster.describe();
ONLINE：实例处于联机状态并且正在参与集群。
OFFLINE：实例已失去与其他实例的连接。
RECOVERING：实例正在尝试通过使其成为ONLINE成员之前检索其所需的事务来与集群同步。
UNREACHABLE：实例已失去与集群的通信。
ERROR：实例在恢复阶段或应用事务时遇到错误。
MISSING：实例的状态，该实例是已配置集群的一部分，但当前不可用
4.innodb cluster主从切换
var cluster = dba.getCluster('fgeducluster01');
cluster.setPrimaryInstance('fgedu85:3306');
#切换多主和单主模式
var cluster = dba.getCluster()
cluster.switchToSinglePrimaryMode('192.168.1.85:3306')
var cluster = dba.getCluster()
cluster.switchToMultiPrimaryMode()
5.集群停止与启动
(1).先停从库，再停主库。
var cluster = dba.getCluster('fgeducluster01');
cluster.status();
mysql-js> shell.connect('root@192.168.18.86:3306')
mysql-js> \sql
mysql-sql> stop group_replication;
mysql-js> shell.connect('root@192.168.18.87:3306')
mysql-js> \sql
mysql-sql> stop group_replication;
mysql-js> shell.connect('root@192.168.18.85:3306')
mysql-js> \sql
mysql-sql> stop group_replication;
(2).先启主库再启从库
shell.connect('root@192.168.18.85:3306');
\sql
set global group_replication_bootstrap_group=on;
start group_replication;
set global group_replication_bootstrap_group=off;
shell.connect('root@192.168.18.86:3306');
\sql
start group_replication;
shell.connect('root@192.168.18.87:3306')
\sql
start group_replication;
6.mysql router
(1).安装
cd /mysql/app
tar Jxvf /soft/mysql-router-8.4.8-linux-glibc2.28-x86_64.tar.xz
ln -s mysql-router-8.4.8-linux-glibc2.28-x86_64 mysqlrouter
mkdir /mysql/log/mysqlrouter/
mkdir /mysql/app/mysqlrouter/etc/
chown -R mysql:mysql /mysql/app/mysqlrouter
chown -R mysql:mysql /mysql/log/mysqlrouter
(2).配置文件
mysqlrouter --bootstrap root@192.168.18.85:3306 --user=mysql
#mysqlrouter --bootstrap root@192.168.18.86:3306 --user=mysql
#mysqlrouter --bootstrap root@192.168.18.87:3306 --user=mysql
#mysqlrouter --bootstrap root@192.168.18.88:3306 --user=mysql
# Bootstrapping system MySQL Router 8.4.8 (MySQL Community - GPL) instance...
- 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/app/mysql-router-8.4.8-linux-glibc2.28-x86_64/mysqlrouter.conf
# MySQL Router configured for the InnoDB Cluster 'fgeducluster01'
After this MySQL Router has been started with the generated configuration
$ /etc/init.d/mysqlrouter restart
or
$ systemctl start mysqlrouter
or
$ mysqlrouter -c /mysql/app/mysql-router-8.4.8-linux-glibc2.28-x86_64/mysqlrouter.conf
InnoDB Cluster 'fgeducluster01' can be reached by connecting to:
## MySQL Classic protocol
- Read/Write Connections: localhost:6446
- Read/Only Connections: localhost:6447
- Read/Write Split Connections: localhost:6450
## MySQL X protocol
- Read/Write Connections: localhost:6448
- Read/Only Connections: localhost:6449
可用策略：
first-available：新连接从目标列表路由到第一个可用服务器。如果失败，则使用下一个可用服务器。
此循环将持续到所有服务器都不可用。
next-available：喜欢 first-available，因为新连接从目的地列表路由到第一个可用服务器。不同的是first-available，
如果服务器被标记为无法访问，则它将被丢弃，并且永远不会再次用作目标。
round-robin：对于负载平衡，每个新连接都以循环方式进入下一个可用服务器。
round-robin-with-fallback：对于负载平衡，每个新连接以循环方式对下一个可用的辅助服务器进行。
如果辅助服务器不可用，则主列表中的服务器将以循环方式使用。
(3).启动停止
chown -R mysql:mysql /mysql/app/mysqlrouter
chmod -R 775 /mysql/app/mysqlrouter
chmod 600 /mysql/app/mysqlrouter/mysqlrouter.key
chmod 600 /mysql/app/mysqlrouter/var/lib/mysqlrouter/keyring
cat > /usr/lib/systemd/system/mysqlrouter.service << EOF
[Unit]
Description=MySQL Router
After=syslog.target
After=network.target
[Service]
Type=simple
User=mysql
Group=mysql
ExecStart=/mysql/app/mysqlrouter/bin/mysqlrouter -c /mysql/app/mysqlrouter/mysqlrouter.conf
PrivateTmp=true
[Install]
WantedBy=multi-user.target
EOF
systemctl enable mysqlrouter
systemctl start mysqlrouter
systemctl stop mysqlrouter
systemctl status mysqlrouter
(4).查看
JS > cluster.listRouters();
{
"clusterName": "fgeducluster01",
"routers": {
"pghost01::system": {
"hostname": "pghost01",
"lastCheckIn": null,
"roPort": "6447",
"roXPort": "6449",
"rwPort": "6446",
"rwSplitPort": "6450",
"rwXPort": "6448",
"version": "8.4.8"
},
"pghost02::system": {
"hostname": "pghost02",
"lastCheckIn": null,
"roPort": "6447",
"roXPort": "6449",
"rwPort": "6446",
"rwSplitPort": "6450",
"rwXPort": "6448",
"version": "8.4.8"
}
}
