mysql8innodbcluster部署文档
张世城2019.11.24
介绍InnoDB集群
什么是MySQLInnoDB集群？
以下是官方文档的介绍：
MySQLInnoDB集群为MySQL提供了完整的高可用性解决方案。MySQLShell包含AdminAPI，可让您轻松配置和管理一组至少三个MySQL服务器实例，以充当InnoDB集群。每个MySQL服务器实例都运行MySQLGroupReplication，它提供了具有内置故障转移功能的InnoDB群集内复制数据的机制。AdminAPI消除了直接在InnoDB群集中使用组复制的需要。MySQL路由器可以根据所部署的群集自动进行自我配置，从而将客户端应用程序透明地连接到服务器实例。如果服务器实例发生意外故障，则群集将自动重新配置。在默认的单主服务器模式下，InnoDB集群具有单个读写服务器实例-主服务器。多个辅助服务器实例是主服务器的副本。如果主服务器发生故障，则辅助服务器将自动升级为主服务器角色。MySQLRouter会检测到此情况，并将客户端应用程序转发到新的主服务器。高级用户还可以将集群配置为具有多个主数据库。
（翻译自：https://dev.mysql.com/doc/refman/8.0/en/mysql-innodb-cluster-introduction.html）
从官方文档可知：
它是Mysql官方提供的一个完整的高可用性解决方案；
至少需要三个数据库实例（用于paxos算法的仲裁）；
支持内置故障转移功能和集内复制数据；
采用MySQL路由器进行连接数据库、故障重新配置；
故障后自动升级从为主；
自动进行业务转发；
高级用户还支持多主数据库。
基本组件
MySQLInnoDB集群由以下几部分组成：
-MySQLServerswithGroupReplication：向集群的所有成员复制数据，同时提供容错、自动故障转移和弹性。MySQLServer5.7.17或更高的版本。
-MySQLRouter：确保客户端请求是负载平衡的，并在任何数据库故障时路由到正确的服务器。MySQLRouter2.1.3或更高的版本。
-MySQLShell：通过内置的管理API创建及管理Innodb集群。MySQLShell1.0.9或更高的版本。
架构图
（组件视角）
（服务器视角）
（业务视角）
创建一个InnoDB集群
安装介质
CentOS-7-x86_64-DVD-1708.iso
mysql-8.0.18-linux-glibc2.12-x86_64.tar.xz
mysql-router-8.0.18-linux-glibc2.12-x86_64.tar.xz
mysql-shell-8.0.18-linux-glibc2.12-x86-64bit.tar.gz
安装规划
ip 主机名 角色 说明 192.168.100.134 vm01 mysql、mysql-shell、mysql-router 安装、管理、路由 192.168.100.135 vm02 mysql、mysql-shell 从库 192.168.100.136 vm03 mysql、mysql-shell 从库
系统安装过程
略，最小化安装即可。
mysql
mysql安装过程
1、修改主机名
对于主机vm01:
hostnamectl--transientset-hostnamevm01
hostnamectl--staticset-hostnamevm01
对于主机vm02:
192.168.100.135
hostnamectl--transientset-hostnamevm02
hostnamectl--staticset-hostnamevm02
对于主机vm03:
192.168.100.136
hostnamectl--transientset-hostnamevm03
hostnamectl--staticset-hostnamevm03
2、修改hosts文件
vi/etc/hosts
192.168.100.134vm01
192.168.100.135vm02
192.168.100.136vm03
说明：修改所有主机的hosts文件。
3、关闭防火墙
systemctlstopfirewalld
chkconfigfirewalldoff
说明：生产可考虑放通相关端口。
4、开启网卡自启动
ifupens33
vi/etc/sysconfig/network-scripts/ifcfg-ens33
ONBOOT=yes
说明：最小化安装ONBOOT默认为no。
5、创建组、用户
groupaddmysql
useradd-r-gmysql-s/bin/falsemysql
6、安装mysql
先上传安装包到/tmp/，然后执行
cd/tmp/
tarxvJfmysql-8.0.18-linux-glibc2.12-x86_64.tar.xz
mvmysql-8.0.18-linux-glibc2.12-x86_64/usr/local/
cd/usr/local/
mvmysql-8.0.18-linux-glibc2.12-x86_64mysql
chownmysql:mysqlmysql
chmod750mysql
cd/usr/local/mysql/
bin/mysqld--initialize--user=mysql--basedir=/usr/local/mysql--datadir=/usr/local/mysql/data--lc_messages_dir=/usr/local/mysql/share--lc_messages=en_US
7、配置my.cnf
mv/etc/my.cnf/etc/my.cnf.bak
vi/etc/my.cnf
[mysqld]
character_set_server=utf8
init_connect=''SETNAMESutf8''
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
8、配置系统服务
/etc/init.d/目录下须有启动脚本
cp./support-files/mysql.server/etc/init.d/mysqld
vi/etc/init.d/mysqld
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
添加服务，以便让chkconfig、systemctl指令管理它
chkconfig--addmysqld
设置开机运行该服务，默认是设置2345等级开机运行服务
chkconfigmysqldon
启动mysql服务
systemctlstartmysqld
9、配置mysql环境变量
vi/etc/profile
exportPATH=$PATH:/usr/local/mysql/bin
使配置生效
source/etc/profile
10、登陆测试
mysql-uroot-p
11、修改root密码
alteruser''root''@''localhost''identifiedby''123456'';
status
至此，mysql安装完成。
说明：请参照上述过程完成vm02、vm03节点的安装。
mysql-shell
1、安装mysql-shell
先上传安装包到/tmp/，然后执行
cd/tmp
tarzxvfmysql-shell-8.0.18-linux-glibc2.12-x86-64bit.tar.gz
mvmysql-shell-8.0.18-linux-glibc2.12-x86-64bit/usr/local/
cd/usr/local/
mvmysql-shell-8.0.18-linux-glibc2.12-x86-64bitmysql-shell
说明：请参照上述过程完成vm02、vm03节点的安装。
2、修改my.cnf文件
在创建集群的节点上修改
vi/etc/my.cnf
[mysql]
#设置mysql客户端默认字符集
default-character-set=utf8
socket=/tmp/mysql.sock
[mysqld]
#skip-name-resolve
#skip-grant-tables
#设置3306端口
port=3306
socket=/tmp/mysql.sock
#设置mysql的安装目录
basedir=/usr/local/mysql
#设置mysql数据库的数据的存放目录
datadir=/usr/local/mysql/data
#允许最大连接数
max_connections=200
#服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
init_connect=''SETNAMESutf8''
#创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
max_allowed_packet=16M
#高可用
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
binlog_checksum=NONE
enforce-gtid-consistency
gtid-mode=ON
server-id=134
transaction_write_set_extraction=XXHASH64
#主从复制配置
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
#注意：这里的地址不能用vm01:24901这种写法，要用IP，否则无法通信
loose-group_replication_local_address="192.168.100.134:24901"
loose-group_replication_group_seeds="192.168.100.134:24901,192.168.100.135:24901,192.168.100.136:24901"
loose-group_replication_single_primary_mode=TRUE
#loose-group_replication_bootstrap_group=off
#loose-group_replication_enforce_update_everywhere_checks=FALSE
#loose-group_replication_start_on_boot=off
disabled_storage_engines=MyISAM,BLACKHOLE,FEDERATED,CSV,ARCHIVE
report_port=3306
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
重启mysql服务
systemctlrestartmysqld
3、创建与配置集群
3.1、创建mysql账号
创建集群远程访问用户，各个节点都要创建
每个节点都要创建一个从管理节点访问的账号，例如这里的管理节点是100.134：
vm01（在此节点创建集群）
createuserroot@''192.168.100.134''identifiedby''123456'';
GRANTALLPRIVILEGESON.TOroot@''192.168.100.134''WITHGRANTOPTION;
createuserroot@''192.168.100.135''identifiedby''123456'';
GRANTALLPRIVILEGESON.TOroot@''192.168.100.135''WITHGRANTOPTION;
createuserroot@''192.168.100.136''identifiedby''123456'';
GRANTALLPRIVILEGESON.TOroot@''192.168.100.136''WITHGRANTOPTION;
vm02
createuserroot@''192.168.100.134''identifiedby''123456'';
GRANTALLPRIVILEGESON.TOroot@''192.168.100.134''WITHGRANTOPTION;
createuserroot@''192.168.100.135''identifiedby''123456'';
GRANTALLPRIVILEGESON.TOroot@''192.168.100.135''WITHGRANTOPTION;
vm03
createuserroot@''192.168.100.134''identifiedby''123456'';
GRANTALLPRIVILEGESON.TOroot@''192.168.100.134''WITHGRANTOPTION;
createuserroot@''192.168.100.136''identifiedby''123456'';
GRANTALLPRIVILEGESON.TOroot@''192.168.100.136''WITHGRANTOPTION;
检查创建结果
selectuser,hostfrommysql.user;
3.2、创建集群
cd/usr/local/mysql-shell/
bin/mysqlsh
shell.connect(''root@localhost:3306'');
dba.configureLocalInstance();
输出提示：
Theinstance''localhost:3306''isvalidforInnoDBclusterusage.
Theinstance''localhost:3306''isalreadyreadyforInnoDBclusterusage.
看到Theinstance''hostname:port''isalreadyreadyforInnoDBclusterusage.代表配置没问题。
#1–参数未正确配置时可以输入1自动配置
创建cluster
varcluster=dba.createCluster(''myCluster'');
查看cluster状态
cluster.status();
退出集群
\quit
再次连接cluster
varcluster=dba.getCluster(''myCluster'');
3.3、添加节点vm02
修改配置文件
编辑配置文件my.cnf，内容与vm01上的一样，需要修改2处，新增1处：
#server_id修改为本机的唯一标识即可
server_id=135
#loose-group_replication_local_address修改为本机ip
loose-group_replication_local_address="192.168.100.135:24901"
#在末尾添加
group_replication_allow_local_disjoint_gtids_join=ON
初始化本机实例
通过本机（vm02）mysql-shell对mysql进行配置
cd/usr/local/mysql-shell/
[root@vm02mysql-shell]#bin/mysqlsh
MySQLJS>shell.connect(''root@localhost:3306'');
#配置服务器为集群模式
MySQLlocalhost:3306sslJS>dba.configureLocalInstance();
加入集群
通过主节点（这里上vm01）的mysql-shell添加vm02到cluster
在vm01上登陆mysql-shell
[root@vm01mysql-shell]#bin/mysqlsh
MySQLJS>shell.connect(''root@localhost:3306'');
#添加实例
MySQLlocalhost:3306sslJS>varcluster=dba.getCluster(''myCluster'');
MySQLlocalhost:3306sslJS>cluster.addInstance(''root@vm02:3306'');
这里需要选择克隆，如：
MySQLlocalhost:3306sslJS>cluster.addInstance(''root@vm02:3306'');
Pleaseprovidethepasswordfor''root@vm02:3306'':
Savepasswordfor''root@vm02:3306''?[Y]es/[N]o/Ne[v]er(defaultNo):
WARNING:AGTIDsetcheckoftheMySQLinstanceat''vm02:3306''determinedthatit
containstransactionsthatdonotoriginatefromthecluster,whichmustbe
discardedbeforeitcanjointhecluster.
vm02:3306hasthefollowingerrantGTIDsthatdonotexistinthecluster:
e70a4d7e-0eb8-11ea-b869-000c29ac59eb:1-5
WARNING:DiscardingtheseextraGTIDeventscaneitherbedonemanuallyorbycompletely
overwritingthestateofvm02:3306withaphysicalsnapshotfromanexisting
clustermember.Tousethismethodbydefault,setthe''recoveryMethod''option
to''clone''.
HavingextraGTIDeventsisnotexpected,anditisrecommendedtoinvestigate
thisfurtherandensurethatthedatacanberemovedpriortochoosingthe
clonerecoverymethod.
Pleaseselectarecoverymethod[C]lone/[A]bort(defaultAbort):c
Validatinginstanceatvm02:3306...
Thisinstancereportsitsownaddressasvm02:3306
Instanceconfigurationissuitable.
AnewinstancewillbeaddedtotheInnoDBcluster.Dependingontheamountof
dataontheclusterthismighttakefromafewsecondstoseveralhours.
Addinginstancetothecluster...
Monitoringrecoveryprocessofthenewclustermember.Press^Ctostopmonitoringandletitcontinueinbackground.
Clonebasedstaterecoveryisnowinprogress.
NOTE:Aserverrestartisexpectedtohappenaspartofthecloneprocess.Ifthe
serverdoesnotsupporttheRESTARTcommandordoesnotcomebackaftera
while,youmayneedtomanuallystartitback.
Waitingforclonetofinish...
NOTE:vm02:3306isbeingclonedfromvm01:3306
StageDROPDATA:Completed
CloneTransfer
FILECOPY############################################################100%Completed
PAGECOPY############################################################100%Completed
REDOCOPY############################################################100%Completed
NOTE:vm02:3306isshuttingdown...
Waitingforserverrestart...ready
vm02:3306hasrestarted,waitingforclonetofinish...
StageRESTART:Completed
Cloneprocesshasfinished:59.56MBtransferredin2sec(29.78MB/s)
Staterecoveryalreadyfinishedfor''vm02:3306''
Theinstance''vm02:3306''wassuccessfullyaddedtothecluster.
#创建成功后，查看cluster状态
mysql-js>cluster.status();
正常输出如下：
MySQLlocalhost:3306sslJS>cluster.status();
{
"clusterName":"myCluster",
"defaultReplicaSet":{
"name":"default",
"primary":"vm01:3306",
"ssl":"REQUIRED",
"status":"OK_NO_TOLERANCE",
"statusText":"ClusterisNOTtoleranttoanyfailures.",
"topology":{
"vm01:3306":{
"address":"vm01:3306",
"mode":"R/W",
"readReplicas":{},
"replicationLag":null,
"role":"HA",
"status":"ONLINE",
"version":"8.0.18"
},
"vm02:3306":{
"address":"vm02:3306",
"mode":"R/O",
"readReplicas":{},
"replicationLag":null,
"role":"HA",
"status":"ONLINE",
"version":"8.0.18"
}
},
"topologyMode":"Single-Primary"
},
"groupInformationSourceMember":"vm01:3306"
}
3.4、添加节点vm03
添加过程与vm02的一样，先编辑配置文件my.cnf，修改server_id和loose-group_replication_local_address，然后初始化本机实例，最后加入集群。
最终集群状态如下：
MySQLlocalhost:3306sslJS>cluster.status();
{
"clusterName":"myCluster",
"defaultReplicaSet":{
"name":"default",
"primary":"vm01:3306",
"ssl":"REQUIRED",
"status":"OK",
"statusText":"ClusterisONLINEandcantolerateuptoONEfailure.",
"topology":{
"vm01:3306":{
"address":"vm01:3306",
"mode":"R/W",
"readReplicas":{},
"replicationLag":null,
"role":"HA",
"status":"ONLINE",
"version":"8.0.18"
},
"vm02:3306":{
"address":"vm02:3306",
"mode":"R/O",
"readReplicas":{},
"replicationLag":null,
"role":"HA",
"status":"ONLINE",
"version":"8.0.18"
},
"vm03:3306":{
"address":"vm03:3306",
"mode":"R/O",
"readReplicas":{},
"replicationLag":null,
"role":"HA",
"status":"ONLINE",
"version":"8.0.18"
}
},
"topologyMode":"Single-Primary"
},
"groupInformationSourceMember":"vm01:3306"
}
在任意一节点通过mysql工具查看集群状态
mysql>SELECTFROMperformance_schema.replication_group_members;
mysql-router
1、安装mysql-router
先上传安装包到vm02的/tmp/，然后执行
cd/tmp
tarxvJfmysql-router-8.0.18-linux-glibc2.12-x86_64.tar.xz
mvmysql-router-8.0.18-linux-glibc2.12-x86_64/usr/local/
cd/usr/local/
mvmysql-router-8.0.18-linux-glibc2.12-x86_64mysql-router
说明：本次演示将mysql-router安装在集群中的随机一台机器上（vm02）。
2、配置mysql-router
设置集群路由
cdmysql-router
./bin/mysqlrouter--bootstraproot@vm01:3306-dmyrouter--user=root
说明
[root@vm02mysql-router]#./bin/mysqlrouter--bootstraproot@vm01:3306-dmyrouter--user=root
PleaseenterMySQLpasswordforroot:
#BootstrappingMySQLRouterinstanceat''/usr/local/mysql-router/myrouter''...
-CheckingforoldRouteraccounts
-NopriorRouteraccountsfound
-Creatingmysqlaccount''mysql_router1_leyo9v76od8h''@''%''forclustermanagement
-Storingaccountinkeyring
-Adjustingpermissionsofgeneratedfiles
-Creatingconfiguration/usr/local/mysql-router/myrouter/mysqlrouter.conf
#MySQLRouterconfiguredfortheInnoDBcluster''myCluster''
AfterthisMySQLRouterhasbeenstartedwiththegeneratedconfiguration
$./bin/mysqlrouter-c/usr/local/mysql-router/myrouter/mysqlrouter.conf
thecluster''myCluster''canbereachedbyconnectingto:
##MySQLClassicprotocol
-Read/WriteConnections:localhost:6446
-Read/OnlyConnections:localhost:6447
##MySQLXprotocol
-Read/WriteConnections:localhost:64460
-Read/OnlyConnections:localhost:64470
修改配置文件
mv/usr/local/mysql-router/myrouter/mysqlrouter.conf{,.bak}
vi/usr/local/mysql-router/myrouter/mysqlrouter.conf
修改为
#FileautomaticallygeneratedduringMySQLRouterbootstrap
[DEFAULT]
user=root
logging_folder=/usr/local/mysql-router/myrouter/log
runtime_folder=/usr/local/mysql-router/myrouter/run
data_folder=/usr/local/mysql-router/myrouter/data
keyring_path=/usr/local/mysql-router/myrouter/data/keyring
master_key_path=/usr/local/mysql-router/myrouter/mysqlrouter.key
connect_timeout=15
read_timeout=30
dynamic_state=/usr/local/mysql-router/myrouter/data/state.json
[logger]
level=INFO
[routing:read_write]
#指定mysqlrouter绑定的服务器
bind_address=192.168.100.135
#指定绑定的端口
bind_port=7001
#读写模式(read-write,read_only)
mode=read-write
#指定mysqlserver列表(ip:port格式，使用逗号分隔)
destinations=192.168.100.134:3306
#最大连接数
max_connections=1000
#最大错误连接数
#max_connect_errors=100
#连接超时时间
connect_timeout=9
[routing:read_only]
bind_address=192.168.100.135
bind_port=7002
mode=read-only
destinations=192.168.100.135:3306,192.168.100.136:3306
max_connections=1000
#max_connect_errors=100
connect_timeout=9
然后重启mysql-router
mysqlrouter--config/usr/local/mysql-router/myrouter/mysqlrouter.conf&
3、连接mysql-router
通过mysql-router连接mysql：
连接读写库
[root@vm02~]#mysql-uroot-p-h192.168.100.135-P7001
Enterpassword:
WelcometotheMySQLmonitor.Commandsendwith;or\g.
YourMySQLconnectionidis66
Serverversion:8.0.18MySQLCommunityServer-GPL
Copyright(c)2000,2019,Oracleand/oritsaffiliates.Allrightsreserved.
OracleisaregisteredtrademarkofOracleCorporationand/orits
affiliates.Othernamesmaybetrademarksoftheirrespective
owners.
Type''help;''or''\h''forhelp.Type''\c''toclearthecurrentinputstatement.
mysql>createdatabasedb3;
QueryOK,1rowaffected(0.01sec)
mysql>showdatabases;
+-------------------------------+
|Database|
+-------------------------------+
|db1|
|db2|
|db3|
|information_schema|
|mysql|
|mysql_innodb_cluster_metadata|
|performance_schema|
|sys|
+-------------------------------+
8rowsinset(0.01sec)
连接只读库
[root@vm02~]#mysql-uroot-p-h192.168.100.135-P7002
Enterpassword:
WelcometotheMySQLmonitor.Commandsendwith;or\g.
YourMySQLconnectionidis502
Serverversion:8.0.18MySQLCommunityServer-GPL
Copyright(c)2000,2019,Oracleand/oritsaffiliates.Allrightsreserved.
OracleisaregisteredtrademarkofOracleCorporationand/orits
affiliates.Othernamesmaybetrademarksoftheirrespective
owners.
Type''help;''or''\h''forhelp.Type''\c''toclearthecurrentinputstatement.
mysql>createdatabasedb4;
ERROR1290(HY000):TheMySQLserverisrunningwiththe--super-read-onlyoptionsoitcannotexecutethisstatement
mysql>showdatabases;
+-------------------------------+
|Database|
+-------------------------------+
|db1|
|db2|
|db3|
|information_schema|
|mysql|
|mysql_innodb_cluster_metadata|
|performance_schema|
|sys|
+-------------------------------+
8rowsinset(0.01sec)
至此，基于一主两从的mysqlinnodbcluster部署完成。
4、架构优化
现在已实现可以通过1个业务ip的不同端口进行读写分离。
ip 组件 角色 192.168.100.134 mysql-server、mysql-shell
mysql-router 主库、
路由 192.168.100.135 mysql-server、mysql-shell 从库 192.168.100.136 mysql-server、mysql-shell 从库 192.168.100.134:7001 读、写 192.168.100.134：7002 读 现在的架构组成存在一个问题，mysql-router路由是部署在cluster其中的一台机器上，如果该机器宕机，那么mysql-router服务就中断了。
为了解决上述问题，所以要用额外的一台集群来单独部署mysql-router功能。
此时，还将面临mysql-router单点的问题。
为了保证mysql-router的高可用，可部署多一台mysql-router角色。
然后在所有的mysql-router角色上部署keepalived，生成一个vip对外提供服务，实现业务ip的高可用。
优化后的架构组成如下：
ip 组件 角色 192.168.100.134 mysql-server、mysql-shell 主库 192.168.100.135 mysql-server、mysql-shell 从库 192.168.100.136 mysql-server、mysql-shell 从库 192.168.100.137 mysql-shell、mysql-router、
keepalived、mysql-client 路由、ip高可用 192.168.100.138 mysql-shell、mysql-router、
keepalived、mysql-client 路由、ip高可用 192.168.100.139:7001 读、写 192.168.100.139：7002 读
维护集群
dba.checkInstanceConfiguration("root@hostname:3306")#检查节点配置实例，用于加入cluster之前
dba.rebootClusterFromCompleteOutage(''myCluster'');#重启，在主节点上执行，用于集群中所有服务器重启，所有节点都offline，直接获取集群信息失败
dba.dropMetadataSchema();#删除schema
varcluster=dba.getCluster(''myCluster'');#获取当前集群
cluster.status();#检查集群总体状态
cluster.checkInstanceState("root@hostname:3306");#检查cluster里节点状态
cluster.rejoinInstance("root@hostname:3306");#重新加入节点
addcluster.dissolve({force：true});#删除集群
cluster.addInstance("root@hostname:3306");#增加节点
cluster.removeInstance("root@hostname:3306");#删除节点
cluster.removeInstance(''root@host:3306'',{force:true});#强制删除节点
cluster.dissolve({force:true});#解散集群
cluster.describe();#集群描述
问题解决
问题
MySQLJS>dba.rebootClusterFromCompleteOutage(''myCluster'');
Dba.rebootClusterFromCompleteOutage:Anopensessionisrequiredtoperformthisoperation.(RuntimeError)
解决
需要先连接到集群实例
MySQLJS>shell.connect(''root@localhost:3306'');
问题
MySQLlocalhost:3306sslJS>dba.configureLocalInstance();
ConfiguringlocalMySQLinstancelisteningatport3306foruseinanInnoDBcluster...
Thisinstancereportsitsownaddressasvm03:3306
Clientsandotherclustermemberswillcommunicatewithitthroughthisaddressbydefault.Ifthisisnotcorrect,thereport_hostMySQLsystemvariableshouldbechanged.
ERROR:User''root''canonlyconnectfrom''localhost''.Newaccount(s)withpropersourceaddressspecificationtoallowremoteconnectionfromallinstancesmustbecreatedtomanagethecluster.
1)Createremotelyusableaccountfor''root''withsamegrantsandpassword
2)CreateanewadminaccountforInnoDBclusterwithminimalrequiredgrants
3)Ignoreandcontinue
4)Cancel
Pleaseselectanoption[1]:4
Canceling...
Dba.configureLocalInstance:Cancelled(RuntimeError)
解决
需要创建集群内部用户
以本机ip创建
如createuserroot@''192.168.100.136''identifiedby''123456'';
然后授权
如GRANTALLPRIVILEGESON.TOroot@''192.168.100.136''WITHGRANTOPTION;
问题
解决
SETGLOBALBINLOG_CHECKSUM=NONE;
SETGLOBALENFORCE_GTID_CONSISTENCY=''ON'';
SETGLOBALGTID_MODE=''ON'';
SETGLOBALSERVER_ID=1;
虽然设置了global变量、session变量，但是在mysql服务重启之后，数据库的配置又会重新初始化，一切按照my.ini的配置进行初始化。global和session的配置都会失效了。
y.cnf的[mysqld]节点下配置：
vi/etc/my.cnf
binlog_checksum=NONE
enforce-gtid-consistency
gtid-mode=ON
server-id=n[各节点需不一样]
重启mysql服务
systemctlrestartmysqld
问题
[ERROR][MY-012574][InnoDB]Unabletolock./ibdata1error:11
解决
[root@localhostlocal]#ps-ef|grepmysql
root90021006:01?00:00:00/bin/sh/usr/local/mysql/bin/mysqld_safe--datadir=/usr/local/mysql/data--pid-file=/usr/local/mysql/data/localhost.localdomain.pid
mysql92049002106:01?00:01:18/usr/local/mysql/bin/mysqld--basedir=/usr/local/mysql--datadir=/usr/local/mysql/data--plugin-dir=/usr/local/mysql/lib/plugin--user=mysql--log-error=/var/log/mysqld.log--pid-file=/usr/local/mysql/data/localhost.localdomain.pid--socket=/tmp/mysql.sock
root99958740007:33pts/100:00:00grep--color=automysql
[root@localhostlocal]#kill-99002
[root@localhostlocal]#kill-99204
问题
修改root用户密码后报错：
Cluster.status:SELECTcommanddeniedtouser''root''@''vm01''fortable''threads''(RuntimeError)
解决
通过mysql-uroot-p-hvm01进行登陆测试。
然后showdatabases;查看能否正常看到系统数据库：
如果不能，那么说明有可能是root权限丢失，那么，需求重新给root授权：
vi/etc/my.cnf
加入一行：
skip-grant-tables
然后重启mysql服务，重新进入mysql
mysql-uroot-p
GRANTALLPRIVILEGESON.TOroot@''192.168.100.134''WITHGRANTOPTION;
如果出现报错
ERROR1290(HY000):TheMySQLserverisrunningwiththe--skip-grant-tablesoptionsoitcannotexecutethisstatement
那么先执行
flushprivileges;
然后重新授权即可
授权成功后需要将my.cnf文件的skip-grant-tables注释或删除，然后重启mysql服务。
问题
MySQLvm01:3306sslJS>a.status();
Cluster.status:MySQLserverhasgoneaway(MySQLError2006)
解决
退出尝试重连。
问题
MySQLvm01:3306sslJS>a=dba.getCluster(''vm01:3306'');
Dba.getCluster:Thisfunctionisnotavailablethroughasessiontoastandaloneinstance(metadataexists,instancebelongstothatmetadata,butGRisnotactive)(RuntimeError)
解决
MySQLvm01:3306sslJS>dba.rebootClusterFromCompleteOutage();
问题
MySQLvm01:3306sslJS>a=dba.getCluster(''vm01:3306'');
Dba.getCluster:Theclusterwiththename''vm01:3306''doesnotexist.(MYSQLSH51101)
解决
集群名称有误。
问题
MySQLvm02:3306sslJS>dba.configureLocalInstance();
ConfiguringlocalMySQLinstancelisteningatport3306foruseinanInnoDBcluster...
ERROR:Unabletocheckprivilegesforuser''root''@''192.168.100.135''.UserrequiresSELECTprivilegeonmysql.toobtaininformationaboutallroles.
Dba.configureLocalInstance:Unabletogetrolesinformation.(RuntimeError)
解决
通过mysql-uroot–p给root用户授权
GRANTALLPRIVILEGESON.TOroot@''192.168.100.135''WITHGRANTOPTION;
问题
MySQLlocalhost:3306sslJS>b.addInstance(''root@vm02:3306'');
Pleaseprovidethepasswordfor''root@vm02:3306'':
Savepasswordfor''root@vm02:3306''?[Y]es/[N]o/Ne[v]er(defaultNo):
WARNING:AGTIDsetcheckoftheMySQLinstanceat''vm02:3306''determinedthatit
containstransactionsthatdonotoriginatefromthecluster,whichmustbe
discardedbeforeitcanjointhecluster.
vm02:3306hasthefollowingerrantGTIDsthatdonotexistinthecluster:
80a070e2-0d16-11ea-a59c-000c29ac59eb:1
WARNING:DiscardingtheseextraGTIDeventscaneitherbedonemanuallyorbycompletely
overwritingthestateofvm02:3306withaphysicalsnapshotfromanexisting
clustermember.Tousethismethodbydefault,setthe''recoveryMethod''option
to''clone''.
HavingextraGTIDeventsisnotexpected,anditisrecommendedtoinvestigate
thisfurtherandensurethatthedatacanberemovedpriortochoosingthe
clonerecoverymethod.
Pleaseselectarecoverymethod[C]lone/[A]bort(defaultAbort):C
Validatinginstanceatvm02:3306...
Thisinstancereportsitsownaddressasvm02:3306
Instanceconfigurationissuitable.
AnewinstancewillbeaddedtotheInnoDBcluster.Dependingontheamountof
dataontheclusterthismighttakefromafewsecondstoseveralhours.
Addinginstancetothecluster...
Cluster.addInstance:Recoveryuser''''notcreatedbyInnoDBCluster(RuntimeError)
解决
怀疑是集群配置信息出现问题（待验证）
这里通过重新创建集群来解决
1、先溶解（即删除）
MySQLlocalhost:3306sslJS>cluster.dissolve({force:true});
2、检查
MySQLlocalhost:3306sslJS>varcluster=dba.getCluster();
Dba.getCluster:Thisfunctionisnotavailablethroughasessiontoastandaloneinstance(metadataexists,instancedoesnotbelongtothatmetadata,andGRisnotactive)(RuntimeError)
3、重建
MySQLlocalhost:3306sslJS>varcluster=dba.createCluster(''myCluster'');
问题
MySQLvm01:3306sslJS>a.addInstance(''vm02:3306'');
Pleaseprovidethepasswordfor''root@vm02:3306'':
Savepasswordfor''root@vm02:3306''?[Y]es/[N]o/Ne[v]er(defaultNo):
WARNING:AGTIDsetcheckoftheMySQLinstanceat''vm02:3306''determinedthatit
containstransactionsthatdonotoriginatefromthecluster,whichmustbe
discardedbeforeitcanjointhecluster.
vm02:3306hasthefollowingerrantGTIDsthatdonotexistinthecluster:
80a070e2-0d16-11ea-a59c-000c29ac59eb:1-4
WARNING:DiscardingtheseextraGTIDeventscaneitherbedonemanuallyorbycompletely
overwritingthestateofvm02:3306withaphysicalsnapshotfromanexisting
clustermember.Tousethismethodbydefault,setthe''recoveryMethod''option
to''clone''.
HavingextraGTIDeventsisnotexpected,anditisrecommendedtoinvestigate
thisfurtherandensurethatthedatacanberemovedpriortochoosingthe
clonerecoverymethod.
Pleaseselectarecoverymethod[C]lone/[A]bort(defaultAbort):
解决
通过克隆Clone解决。
也可以输入c
问题
[root@vm02mysql-shell]#tail-f/var/log/mysqld.log
2019-11-23T13:54:41.827285Z0[ERROR][MY-011735][Repl]Plugingroup_replicationreported:''[GCS]Erroronopeningaconnectionto192.168.100.134:24901onlocalport:24901.''
2019-11-23T13:54:44.832266Z0[ERROR][MY-011735][Repl]Plugingroup_replicationreported:''[GCS]Erroronopeningaconnectionto192.168.100.136:24901onlocalport:24901.''
解决
由于还没加入集群？
得到加入集群后应该就没问题了。
问题
"vm04:3306":{
"address":"vm04:3306",
"mode":"n/a",
"readReplicas":{},
"role":"HA",
"shellConnectError":"MySQLError2005(HY000):UnknownMySQLserverhost''vm04''(0)",
"status":"ONLINE",
"version":"8.0.18"
}
解决
在主节点创建账号
createuserroot@''192.168.100.137''identifiedby''123456'';
GRANTALLPRIVILEGESON.TOroot@''192.168.100.137''WITHGRANTOPTION;
检查hosts文件
保证所有的主机正确解析。
问题
"vm04:3306":{
"address":"vm04:3306",
"mode":"R/O",
"readReplicas":{},
"role":"HA",
"status":"(MISSING)"
}
解决
在主节点重新加入vm04：
shell.connect(''cluster@localhost:3306'');
varcluster=dba.getCluster();
cluster.rejoinInstance("root@vm04:3306")
问题
MySQLlocalhost:3306sslJS>dba.rebootClusterFromCompleteOutage();
Reconfiguringthedefaultclusterfromcompleteoutage...
Theinstance''vm01:3306''waspartoftheclusterconfiguration.
Wouldyouliketorejoinittothecluster?[y/N]:y
Theinstance''vm03:3306''waspartoftheclusterconfiguration.
Wouldyouliketorejoinittothecluster?[y/N]:y
Theinstance''vm04:3306''waspartoftheclusterconfiguration.
Wouldyouliketorejoinittothecluster?[y/N]:y
Dba.rebootClusterFromCompleteOutage:Theactivesessioninstanceisn''tthemostupdatedincomparisonwiththeONLINEinstancesoftheCluster''smetadata.Pleaseusethemostuptodateinstance:''vm01:3306''.(RuntimeError)
解决
根据提示，在vm01:3306上进行重启集群
如：
[root@vm01mysql-shell]#./bin/mysqlsh
MySQLShell8.0.18
Copyright(c)2016,2019,Oracleand/oritsaffiliates.Allrightsreserved.
OracleisaregisteredtrademarkofOracleCorporationand/oritsaffiliates.
Othernamesmaybetrademarksoftheirrespectiveowners.
Type''\help''or''\?''forhelp;''\quit''toexit.
MySQLJS>shell.connect(''root@localhost:3306'');
Creatingasessionto''root@localhost:3306''
Pleaseprovidethepasswordfor''root@localhost:3306'':
Savepasswordfor''root@localhost:3306''?[Y]es/[N]o/Ne[v]er(defaultNo):
Fetchingschemanamesforautocompletion...Press^Ctostop.
YourMySQLconnectionidis37
Serverversion:8.0.18MySQLCommunityServer-GPL
Nodefaultschemaselected;type\usetosetone.
MySQLlocalhost:3306sslJS>
MySQLlocalhost:3306sslJS>dba.rebootClusterFromCompleteOutage();
Reconfiguringthedefaultclusterfromcompleteoutage...
Theinstance''vm02:3306''waspartoftheclusterconfiguration.
Wouldyouliketorejoinittothecluster?[y/N]:y
Theinstance''vm03:3306''waspartoftheclusterconfiguration.
Wouldyouliketorejoinittothecluster?[y/N]:y
Theinstance''vm04:3306''waspartoftheclusterconfiguration.
Wouldyouliketorejoinittothecluster?[y/N]:y
Thesafestandmostconvenientwaytoprovisionanewinstanceisthrough
automaticcloneprovisioning,whichwillcompletelyoverwritethestateof
''localhost:3306''withaphysicalsnapshotfromanexistingclustermember.To
usethismethodbydefault,setthe''recoveryMethod''optionto''clone''.
Theincrementaldistributedstaterecoverymaybesafelyusedifyouaresure
allupdateseverexecutedintheclusterweredonewithGTIDsenabled,there
arenopurgedtransactionsandthenewinstancecontainsthesameGTIDsetas
theclusterorasubsetofit.Tousethismethodbydefault,setthe
''recoveryMethod''optionto''incremental''.
Incrementaldistributedstaterecoverywasselectedbecauseitseemstobesafelyusable.
Theclusterwassuccessfullyrebooted.
MySQLlocalhost:3306sslJS>
|
|
