一、部署三台MySQL8.0机器
MySQL,MySQL-shell、MySQL-Router | 192.168.203.10 |
MySQL | 192.168.203.20 |
MySQL | 192.168.203.30 |
部署MySQL一主两从
在/etc/my.cnf中的[mysqld]下添加以下内容, 三台server_id不能相同
server_id=xxx #唯一
report_host=当前服务器ip
report_port=数据库端口
loose-group_replication_ip_whitelist="ip1,ip2,ip3"
修改完成后重启mysql服务(三台都操作)
安装mysql-shell和mysql-router(shell和router都只需要装一台)
二、利用MySQL-shell 搭建集群
用root用户分别在三台数据库上建用户并授权
create user tiancai identified by 'Baixiong@2023';
GRANT BACKUP_ADMIN, CLONE_ADMIN, CREATE USER, EXECUTE, FILE, PERSIST_RO_VARIABLES_ADMIN, PROCESS, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SELECT, SHUTDOWN, SUPER, SYSTEM_VARIABLES_ADMIN ON *.* TO 'baixiong'@'%' WITH GRANT OPTION;
GRANT DELETE, INSERT, UPDATE ON mysql.* TO 'baixiong'@'%' WITH GRANT OPTION;
GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata.* TO 'baixiong'@'%' WITH GRANT OPTION;
GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata_bkp.* TO 'baixiong'@'%' WITH GRANT OPTION;
GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata_previous.* TO 'baixiong'@'%' WITH GRANT OPTION;
grant all on *.* to 'baixiong'@'%';
grant all privileges on *.* to qianfeng with grant option;
搭建集群
cd mysql-shell-8.0.23-linux-glibc2.12-x86-64bit/bin/
./mysqlsh
连接192.168.203.10(连主)
\c baixiong@192.168.203.30:3306
输入密码
检查配置信息
dba.checkInstanceConfiguration()配置持久化,用于集群管理
dba.configureInstance('baixiong@192.168.203.10:3306')
dba.configureInstance('baixiong@192.168.203.20:3306')
dba.configureInstance('baixiong@192.168.203.30:3306')创建一个mysqlcluster的集群(三台服务器都需要是空白的)
var cluster=dba.createCluster('mysqlcluster');
.
查看集群信息
cluster.status()(利用shell连接变量是一次性的,如果退出重连,则需如下命令)
dba.getCluster().status()
.
在集群中添加实例(两个从库都执行)
cluster.addInstance('qianfeng@192.168.203.20:3306')(同上,退出后重新连接得用如下命令)
var cluster=dba.getCluster();cluster.addInstance('bonree@192.168.203.20:3306')
.
添加实例过程中,会通过clone方式将10上的数据clone到20上,clone之后会重启20上的mysql
如果添加过程中添加失败,可以 执行cluster.rescan()重新扫描加入集群
实例添加完成后,查看进行信息
cluster.status();
三个数据库节点信息都显示,则成功
利用mysql-router做故障恢复
mysql-router对接innodb cluster集群
cd mysql-router-8.0.19-linux-glibc2.12-x86_64/bin
./mysqlrouter --bootstrap qianfeng@192.168.203.10:3306 --user=root
执行完信息如下,提示执行启动命令,配置文件是mysqlrouter.conf,先不启动。
修改配置文件 vim mysqlrouter.conf
将dynamic_state进行注释,添加bootstrap_server_addresses信息。保存后启动mysqlrouter
bootstrap_server_addresses=mysql://192.168.203.10:3306,mysql://192.168.203.20:3306,mysql://192.168.203.30:3306
.启动命令
cd mysql-router-8.0.23-linux-glibc2.12-x86_64/bin
./mysqlrouter -c ../mysqlrouter.conf &.
默认6446端口为提供服务端口,可以修改mysqlrouter.conf修改
对外提供的mysql访问地址:mysqlrouter所在的ip:6446