环境介绍：
这里的MySQL Innodb Cluster搭建环境如下所示,总共有三台机器搭建MySQL InnoDB Cluster：
操作系统： Red Hat Enterprise Linux release 8.8 (Ootpa)
数据库版本：8.0.35 MySQL Community Server - GPL
第一步：环境检查与配置
1：防火墙配置
测试环境可以关闭防火墙，生产环境一般会开启防火墙。在防火墙上设置相关IP和端口。根据实际情况进行设置（需要root权限，一般由系统管理员设置）
关于如何设置防火墙限定IP地址，这里不做过多介绍，其实配置也很简单。端口号根据实际情况配置。
# systemctl status firewalld.service # systemctl stop firewalld.service # systemctl status firewalld.service # systemctl disable firewalld.service
2：关闭或配置SELinux
检查是否关闭selinux
# /usr/sbin/sestatus SELinux status: disabled
修改SELinux配置文件
如果没有关闭selinux的话，我们建议关闭selinux，可以通过修改selinux的配置文件，将SELINUX=enforcing改为SELINUX=disabled。
#vi /etc/selinux/config SELINUX=disabled
如果不想重启，如果想验证一下，可以重启一下服务器。
setenforce 0 getenforce
如果公司要求开启SELinux的话， 必须设置SELinux 策略，在SELinux 下允许MySQL连接
sudo setsebool -P mysql_connect_any 1
3：配置IP与主机名映射
编辑/etc/hosts
#MySQL InnoDB Cluster 192.168.9.200 mysqlu01 192.168.9.201 mysqlu02 192.168.9.202 mysqlu03
[注意]：三台MySQL服务器都必须设置，不能只设置一台服务器。请注意，不要遗漏。
4：修改内核参数
/etc/security/limits.conf mysql hard nofile 65536 mysql soft nofile 65536 或者 * hard nofile 65536 * soft nofile 65536
5：配置标准大页
grep -i huge /proc/meminfo
建议开启标准大页，这样更有利于性能。
6：禁用透明大页
第二步：安装MySQL实例
1：安装MySQL实例
使用mysql_auto_install.sh脚本自动安装MySQL单实例，如果常规的安装MySQL实例，参考文档“MySQL 8.0的二进制安装文档.docx” ，三台（多台）服务器上安装MySQL实例
$ cd /data/soft $ sh mysql_auto_install.sh
2：修改参数文件中MGR参数配置
修改server_id=xxxx #规则，取IP地址的最后一位,然后在my.cnf中添加下面配置
########################################################################################################################## # GTID SETTING ########################################################################################################################## plugin_dir=/opt/mysql/mysql8.0/lib/plugin #设置plugin的路径 enforce_gtid_consistency = ON #强制GTID的一致性 gtid-mode=ON #开启GTID,必须开启 master-info-repository=TABLE relay-log-info-repository=TABLE #记录同步的信息,便于管理和恢复 log-slave-updates = ON #需要记录事务的binlog,用作以后的恢复用,哪怕不是写入点,也需要 binlog-checksum=NONE #MGR本身不支持binlog的checksum校验 slave-parallel-workers=8 #GTID的SQL线程 slave_preserve_commit_order=ON #GTID配置，SQL线程按照顺序重放事物 #slave-parallel-type=LOGICAL_CLOCK #SQL线程工作模式。有两种。 disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY" ########################################################################################################################## ########################################################################################################################## # 组复制设置 ########################################################################################################################## #记录事务的算法,官网建议设置该参数使用 XXHASH64 算法 transaction_write_set_extraction = XXHASH64 plugin_load_add='group_replication.so' #是否随服务器启动而自动启动组复制,不建议直接启动,怕故障恢复时有扰乱数据准确性的特殊情况 #loose-group_replication_start_on_boot = OFF group_replication_start_on_boot = OFF #开启引导模式,添加组成员，用于第一次搭建MGR或重建MGR的时候使用,只需要在集群内的其中一台开启, #loose-group_replication_bootstrap_group = OFF group_replication_bootstrap_group = OFF #IP地址白名单,默认只添加127.0.0.1,不会允许来自外部主机的连接,按需安全设置 #loose-group_replication_ip_whitelist = '127.0.0.1/8,192.168.9.0/24' group_replication_ip_allowlist = '127.0.0.1/8,192.168.9.0/24' #是否启动单主模式，如果启动，则本实例是主库，提供读写，其他实例仅提供读,如果为off就是多主模式了 loose-group_replication_single_primary_mode = ON ##ssl for mgr group_replication_ssl_mode = REQUIRED group_replication_recovery_use_ssl = ON binlog_transaction_dependency_tracking=WRITESET ##########################################################################################################################
注意，这里使用mysql shell创建InnoDB Cluster时，不需要在参数文件中设置group_replication_group_name等参数，有些版本的参数也有所出入，请以实际情况为准。
修改后重启每一台MySQL服务。
$ sudo systemctl stop mysqld.service $ sudo systemctl start mysqld.service $ sudo systemctl status mysqld.service
安装MySQL Shell
# yum localinstall mysql-shell-8.0.35-1.el8.x86_64.rpm
这里使用root用户安装。因为mysql用户没有权限安装。可以只安装一台MySQL服务器，也可以三台都安装。
创建数据库用户
##手动创建需要设置log bin 为0，创建完成后设置回1，所有服务器执行
SET SQL_LOG_BIN=0; CREATE USER icadmin@'192.168.9.%' IDENTIFIED BY '******'; GRANT ALL ON *.* TO icadmin@'192.168.9.%' WITH GRANT OPTION; SET SQL_LOG_BIN=1;
注意：所有MySQL节点需要执行创建用户操作，此用户临时使用，配置完集群后删除。
mysqlsh \cicadmin@192.168.9.200:7306
或
mysqlsh-h192.168.9.200-P7306-uicadmin-p
检查实例是否符合InnoDB Cluster的参数及权限配置要求
dba.checkInstanceConfiguration('icadmin@192.168.9.200:7306')
dba.checkInstanceConfiguration('icadmin@192.168.9.201:7306')
dba.checkInstanceConfiguration('icadmin@192.168.9.202:7306')
检查符合InnoDB Cluster的参数、权限配置符合要求的话，则会返回status为OK，否则会提示不符合要求信息。
MySQL192.168.9.200:7306sslJS>dba.checkInstanceConfiguration('icadmin@192.168.9.201:7306')
Pleaseprovidethepasswordfor'icadmin@192.168.9.201:7306':****************
Savepasswordfor'icadmin@192.168.9.201:7306'?[Y]es/[N]o/Ne[v]er(defaultNo):yes
ValidatingMySQLinstanceatmysqlu02:7306foruseinanInnoDBcluster...
Thisinstancereportsitsownaddressasmysqlu02:7306
Clientsandotherclustermemberswillcommunicatewithitthroughthisaddressbydefault.Ifthisisnotcorrect,thereport_hostMySQLsystemvariableshouldbechanged.
CheckingwhetherexistingtablescomplywithGroupReplicationrequirements...
Noincompatibletablesdetected
Checkinginstanceconfiguration...
InstanceconfigurationiscompatiblewithInnoDBcluster
Theinstance'mysqlu02:7306'isvalidtobeusedinanInnoDBcluster.
{
"status":"ok"
}
MySQL192.168.9.200:7306sslJS>
如果MySQL的参数不符合要求，则上面输出信息"status"不为"ok",则必须修改相关参数参数，重新检测。
初始化InnoDB Cluster相关配置
dba.configureInstance('icadmin@192.168.9.200:7306',{clusterAdmin:"'icadmin'@'192.168.9.%'"});
dba.configureInstance('icadmin@192.168.9.201:7306',{clusterAdmin:"'icadmin'@'192.168.9.%'"});
dba.configureInstance('icadmin@192.168.9.202:7306',{clusterAdmin:"'icadmin'@'192.168.9.%'"});
或者
dba.configureInstance('root@192.168.9.200:7306',{clusterAdmin:"'icadmin'@'192.168.9.%'"},clusterAdminPassword:"*****"});
dba.configureInstance('root@192.168.9.201:7306',{clusterAdmin:"'icadmin'@'192.168.9.%'"},clusterAdminPassword:"*****"});
dba.configureInstance('root@192.168.9.202:7306',{clusterAdmin:"'icadmin'@'192.168.9.%'"},clusterAdminPassword:"*****"});
具体执行过程如下所示：
MySQL192.168.9.200:7306sslJS>dba.configureInstance('icadmin@192.168.9.200:7306',{clusterAdmin:"'icadmin'@'192.168.9.%'"});
ConfiguringlocalMySQLinstancelisteningatport7306foruseinanInnoDBcluster...
Thisinstancereportsitsownaddressasmysqlu01:7306
Clientsandotherclustermemberswillcommunicatewithitthroughthisaddressbydefault.Ifthisisnotcorrect,thereport_hostMySQLsystemvariableshouldbechanged.
User'icadmin'@'192.168.9.%'alreadyexistsandwillnotbecreated.
applierWorkerThreadswillbesettothedefaultvalueof4.
Theinstance'mysqlu01:7306'isvalidtobeusedinanInnoDBcluster.
Theinstance'mysqlu01:7306'isalreadyreadytobeusedinanInnoDBcluster.
Successfullyenabledparallelappliers.
MySQL192.168.9.200:7306sslJS>dba.configureInstance('icadmin@192.168.9.201:7306',{clusterAdmin:"'icadmin'@'192.168.9.%'"});
ConfiguringMySQLinstanceatmysqlu02:7306foruseinanInnoDBcluster...
Thisinstancereportsitsownaddressasmysqlu02:7306
Clientsandotherclustermemberswillcommunicatewithitthroughthisaddressbydefault.Ifthisisnotcorrect,thereport_hostMySQLsystemvariableshouldbechanged.
User'icadmin'@'192.168.9.%'alreadyexistsandwillnotbecreated.
applierWorkerThreadswillbesettothedefaultvalueof4.
Theinstance'mysqlu02:7306'isvalidtobeusedinanInnoDBcluster.
Theinstance'mysqlu02:7306'isalreadyreadytobeusedinanInnoDBcluster.
Successfullyenabledparallelappliers.
MySQL192.168.9.200:7306sslJS>dba.configureInstance('icadmin@192.168.9.202:7306',{clusterAdmin:"'icadmin'@'192.168.9.%'"});
ConfiguringMySQLinstanceatmysqlu03:7306foruseinanInnoDBcluster...
Thisinstancereportsitsownaddressasmysqlu03:7306
Clientsandotherclustermemberswillcommunicatewithitthroughthisaddressbydefault.Ifthisisnotcorrect,thereport_hostMySQLsystemvariableshouldbechanged.
User'icadmin'@'192.168.9.%'alreadyexistsandwillnotbecreated.
applierWorkerThreadswillbesettothedefaultvalueof4.
Theinstance'mysqlu03:7306'isvalidtobeusedinanInnoDBcluster.
Theinstance'mysqlu03:7306'isalreadyreadytobeusedinanInnoDBcluster.
Successfullyenabledparallelappliers.
MySQL192.168.9.200:7306sslJS>
创建集群
varcluster=dba.createCluster('gsp_cluster');
具体操作如下所示：
MySQL192.168.9.200:7306sslJS>varcluster=dba.createCluster('gsp_cluster');
AnewInnoDBClusterwillbecreatedoninstance'mysqlu01:7306'.
Validatinginstanceconfigurationat192.168.9.200:7306...
Thisinstancereportsitsownaddressasmysqlu01:7306
Instanceconfigurationissuitable.
NOTE:GroupReplicationwillcommunicatewithothermembersusing'mysqlu01:7306'.UsethelocalAddressoptiontooverride.
*CheckingconnectivityandSSLconfiguration...
CreatingInnoDBCluster'gsp_cluster'on'mysqlu01:7306'...
AddingSeedInstance...
Clustersuccessfullycreated.UseCluster.addInstance()toaddMySQLinstances.
Atleast3instancesareneededfortheclustertobeabletowithstandupto
oneserverfailure.
MySQL192.168.9.200:7306sslJS>
查看创建集群状态
varcluster=dba.getCluster() cluster.status()
具体输出如下所示：
MySQL192.168.9.200:7306sslJS>varcluster=dba.getCluster()
MySQL192.168.9.200:7306sslJS>cluster.status()
{
"clusterName":"gsp_cluster",
"defaultReplicaSet":{
"name":"default",
"primary":"mysqlu01:7306",
"ssl":"REQUIRED",
"status":"OK_NO_TOLERANCE",
"statusText":"ClusterisNOTtoleranttoanyfailures.",
"topology":{
"mysqlu01:7306":{
"address":"mysqlu01:7306",
"memberRole":"PRIMARY",
"mode":"R/W",
"readReplicas":{},
"replicationLag":"applier_queue_applied",
"role":"HA",
"status":"ONLINE",
"version":"8.0.33"
}
},
"topologyMode":"Single-Primary"
},
"groupInformationSourceMember":"mysqlu01:7306"
}
MySQL192.168.9.200:7306sslJS>
添加节点到集群
varcluster=dba.getCluster()
cluster.addInstance('icadmin@192.168.9.201:7306')
cluster.status()
cluster.addInstance('icadmin@192.168.9.202:7306')
cluster.status()
部分输出如下所示：
MySQL192.168.9.200:7306sslJS>cluster.addInstance('icadmin@192.168.9.202:7306')
NOTE:Thetargetinstance'mysqlu03:7306'hasnotbeenpre-provisioned(GTIDsetisempty).TheShellisunabletodecidewhetherincrementalstaterecoverycancorrectlyprovisionit.
Thesafestandmostconvenientwaytoprovisionanewinstanceisthroughautomaticcloneprovisioning,whichwillcompletelyoverwritethestateof'mysqlu03:7306'withaphysicalsnapshotfromanexistingclustermember.Tousethismethodbydefault,setthe'recoveryMethod'optionto'clone'.
TheincrementalstaterecoverymaybesafelyusedifyouaresureallupdateseverexecutedintheclusterweredonewithGTIDsenabled,therearenopurgedtransactionsandthenewinstancecontainsthesameGTIDsetastheclusterorasubsetofit.Tousethismethodbydefault,setthe'recoveryMethod'optionto'incremental'.
Pleaseselectarecoverymethod[C]lone/[I]ncrementalrecovery/[A]bort(defaultClone):C
Validatinginstanceconfigurationat192.168.9.202:7306...
Thisinstancereportsitsownaddressasmysqlu03:7306
Instanceconfigurationissuitable.
NOTE:GroupReplicationwillcommunicatewithothermembersusing'mysqlu03:7306'.UsethelocalAddressoptiontooverride.
*CheckingconnectivityandSSLconfiguration...
AnewinstancewillbeaddedtotheInnoDBCluster.Dependingontheamountof
dataontheclusterthismighttakefromafewsecondstoseveralhours.
Addinginstancetothecluster...
Monitoringrecoveryprocessofthenewclustermember.Press^Ctostopmonitoringandletitcontinueinbackground.
Clonebasedstaterecoveryisnowinprogress.
NOTE:Aserverrestartisexpectedtohappenaspartofthecloneprocess.Ifthe
serverdoesnotsupporttheRESTARTcommandordoesnotcomebackaftera
while,youmayneedtomanuallystartitback.
*Waitingforclonetofinish...
NOTE:mysqlu03:7306isbeingclonedfrommysqlu02:7306
**StageDROPDATA:Completed
**CloneTransferFILECOPY============================================================0%NotStartedPAGECOPY============================================================0%NotStartedREDOCOPY============================================================0%NotStarted**CloneTransferFILECOPY============================================================0%InProgressPAGECOPY============================================================0%NotStartedREDOCOPY============================================================0%NotStarted**CloneTransferFILECOPY############################################################100%CompletedPAGECOPY############################################################100%CompletedREDOCOPY############################################################100%Completed
NOTE:mysqlu03:7306isshuttingdown...
*Waitingforserverrestart...ready
*mysqlu03:7306hasrestarted,waitingforclonetofinish...
**StageRESTART:Completed
*Cloneprocesshasfinished:73.65MBtransferredinabout1second(~73.65MB/s)
Staterecoveryalreadyfinishedfor'mysqlu03:7306'
Theinstance'mysqlu03:7306'wassuccessfullyaddedtothecluster.
MySQL192.168.9.200:7306sslJS>cluster.status()
{
"clusterName":"gsp_cluster",
"defaultReplicaSet":{
"name":"default",
"primary":"mysqlu01:7306",
"ssl":"REQUIRED",
"status":"OK",
"statusText":"ClusterisONLINEandcantolerateuptoONEfailure.",
"topology":{
"mysqlu01:7306":{
"address":"mysqlu01:7306",
"memberRole":"PRIMARY",
"mode":"R/W",
"readReplicas":{},
"replicationLag":"applier_queue_applied",
"role":"HA",
"status":"ONLINE",
"version":"8.0.33"
},
"mysqlu02:7306":{
"address":"mysqlu02:7306",
"memberRole":"SECONDARY",
"mode":"R/O",
"readReplicas":{},
"replicationLag":"applier_queue_applied",
"role":"HA",
"status":"ONLINE",
"version":"8.0.33"
},
"mysqlu03:7306":{
"address":"mysqlu03:7306",
"memberRole":"SECONDARY",
"mode":"R/O",
"readReplicas":{},
"replicationLag":"applier_queue_applied",
"role":"HA",
"status":"ONLINE",
"version":"8.0.33"
}
},
"topologyMode":"Single-Primary"
},
"groupInformationSourceMember":"mysqlu01:7306"
}
MySQL192.168.9.200:7306sslJS>
检查集群状态
最后再检查一次集群的状态 varcluster=dba.getCluster() cluster.status();
MySQL Router安装
MySQL Router有两种配置方式，如下所示：
手工配置，手工填写后端 MGR 节点的地址，但是这样MySQL Router就没法感知 Primary 节点的变化，手工创建 MGR 时只能这么配置
引导模式自动进行配置,通过 mysql_innodb_cluster_metadata 元数据库动态感知 Primary 节点的变化,实现对应用的透明，这也是 InnoDB Cluster 的标准配置方法。
bootstrap模式
bootstrap模式支持failover，但是必须结合InnoDB Cluster使用，在--directory指定的路径下自动生成安装目录，配置文件里的端口为6446和6447
$cd/data/soft $tarxvfmysql-router-8.0.35-linux-glibc2.28-x86_64.tar.xz-C/opt/mysql $cd/opt/mysql/ $ln-smysql-router-8.0.35-linux-glibc2.28-x86_64/router
配置环境变量
#在mysql用户下编辑，加入下面配置信息
exportPATH=$PATH:/opt/mysql/router/bin
执行下面命令，使之生效。
$source~/.bash_profile #查看帮助信息 mysqlrouter--help #创建目录 mkdir-p/data/mysqlrouter
初始化脚本(例子1)
mysqlrouter--bootstrapicadmin@mysqlu01:7306--directory/data/mysqlrouter--name='icrouter'--force-password-validation
具体如下所示：
$mysqlrouter--bootstrapicadmin@mysqlu01:7306--directory/data/mysqlrouter--name='iicrouter'--force-password-validation PleaseenterMySQLpasswordforicadmin: #BootstrappingMySQLRouter8.0.35(MySQLCommunity-GPL)instanceat'/data/mysqlrouter'... -Creatingaccount(s)(onlythosethatareneeded,ifany) -Verifyingaccount(usingittorunSQLqueriesthatwouldberunbyRouter) -Storingaccountinkeyring -Adjustingpermissionsofgeneratedfiles -Creatingconfiguration/data/mysqlrouter/mysqlrouter.conf #MySQLRouter'iicrouter'configuredfortheClusterSet'yicticcset' AfterthisMySQLRouterhasbeenstartedwiththegeneratedconfiguration $mysqlrouter-c/data/mysqlrouter/mysqlrouter.conf ClusterSet'yicticcset'canbereachedbyconnectingto: ##MySQLClassicprotocol -Read/WriteConnections:localhost:6446 -Read/OnlyConnections:localhost:6447 ##MySQLXprotocol -Read/WriteConnections:localhost:6448 -Read/OnlyConnections:localhost:6449
初始化脚本(例子2)：
mysqlrouter--bootstrapicadmin@mysqlu01:7306\ --directory/data/mysqlrouter\ --accounticcrouter\ --usermysql--nameicrouter\ --conf-bind-address="0.0.0.0"\ --account-host="192.168.9.%"--force-password-validation
注意：--conf-bind-address，如果想任何其它机器都能访问mysql router的话，那么--conf-bind-address应该设置为0.0.0.0，如果设置为某个IP，表示只能在这个IP访问mysql router，根据具体情况设置。
具体输出如下所示：
[mysql@mysqlu01mysqlrouter]$mysqlrouter--bootstrapicadmin@mysqlu01:7306\ >--directory/data/mysqlrouter\ >--accounticcrouter\ >--usermysql--nameicrouter\ >--conf-bind-address="192.168.9.200"\ >--account-host="192.168.9.%"--force-password-validation PleaseenterMySQLpasswordforicadmin: #ReconfiguringMySQLRouter8.0.35(MySQLCommunity-GPL)instanceat'/data/mysqlrouter'... PleaseenterMySQLpasswordforiccrouter: -Creatingaccount(s)(onlythosethatareneeded,ifany) -Usingexistingcertificatesfromthe'/data/mysqlrouter/data'directory -Verifyingaccount(usingittorunSQLqueriesthatwouldberunbyRouter) -Storingaccountinkeyring -Adjustingpermissionsofgeneratedfiles -Creatingconfiguration/data/mysqlrouter/mysqlrouter.conf Existingconfigurationsbackedupto'/data/mysqlrouter/mysqlrouter.conf.bak' #MySQLRouter'icrouter'configuredfortheInnoDBCluster'gsp_cluster' AfterthisMySQLRouterhasbeenstartedwiththegeneratedconfiguration $mysqlrouter-c/data/mysqlrouter/mysqlrouter.conf InnoDBCluster'gsp_cluster'canbereachedbyconnectingto: ##MySQLClassicprotocol -Read/WriteConnections:localhost:6446 -Read/OnlyConnections:localhost:6447 ##MySQLXprotocol -Read/WriteConnections:localhost:6448 -Read/OnlyConnections:localhost:6449 [mysql@mysqlu01mysqlrouter]$
查看MySQL Router信息
varcluster=dba.getCluster() cluster.listRouters()
具体信息如下所示
配置MySQL Router的systemd服务，编辑配置/usr/lib/systemd/system/mysqlrouter.service
[Unit] Description=MySQLRouter After=syslog.target After=network.target [Service] Type=simple User=mysql Group=mysql PIDFile=/data/mysqlrouter/mysqlrouter.pid ExecStart=/opt/mysql/router/bin/mysqlrouter-c/data/mysqlrouter/mysqlrouter.conf Restart=on-failure PrivateTmp=true [Install] WantedBy=multi-user.target
然后执行下面命令
#systemctldaemon-reload #systemctlenablemysqlrouter.service #systemctlstatusmysqlrouter.service #systemctlstartmysqlrouter.service
启动MySQL Router后，可以通过下面命令查看/验证其监听端口是否开启。
$netstat-ntlp|grepmysqlrouter $ps-ef|grepmysqlrouter|grep-vgrep
然后依葫芦画瓢，在另外一台服务器上安装MySQL Router,关于MySQL Router的安装，一般应该在应用服务器或单独的服务器上安装MySQL Router，结合Keepalvied实现MySQL Router的高可用性，这里不做展开介绍。
