MySQL-InnodbCluster安装部署

前言:

本文章为InnodbCluster集群安装文档，采用MySQL官方的InnodbCluster架构(MySQLRouter+MySQLShell+MGR),并通过KeepAlived实现MySQLRouter的高可用。

集群架构:

环境信息:

安装步骤:

1 安装并初始化3个数据库实例，这里步骤省略，跟安装部署MySQL步骤一样

主:30.1.15.45/3306备:30.1.15.46/3306备:30.1.15.47/3306

2 安装MySQLShell

---解压并配置tar xvf /tmp/mysql-shell-8.0.22-linux-glibc2.12-x86-64bit.tar.gz chown -R mysql.mysql mysql-shell-8.0.22-linux-glibc2.12-x86-64bit/ln -s mysql-shell-8.0.22-linux-glibc2.12-x86-64bit/ mysqlsh---设置环境变量vi /etc/profileexport PATH=$MYSQL_HOME/bin:/usr/local/mysqlsh/bin: $PATH---登录方式---mysqlx 用33060端口mysqlsh --mysqlx -uicadmin -h 127.0.0.1  -P 33060 -p---mysql协议连接方式mysqlsh --mysql -uicadmin -h 127.0.0.1  -P 3306 -p

3 安装MySQLRouter

---解压并配置tar xvf /tmp/mysql-router-8.0.22-linux-glibc2.12-x86_64.tar.xz chown -R mysql.mysql mysql-router-8.0.22-linux-glibc2.12-x86_64/ln -s mysql-router-8.0.22-linux-glibc2.12-x86_64/ mysqlrouter---设置环境变量vi /etc/profileexport PATH=$MYSQL_HOME/bin:/usr/local/mysqlsh/bin:/usr/local/mysqlrouter/bin:$PATH

4 通过MySQLShell搭建MGR

节点配置检查

---每个节点都执行mysqlsh --mysql -uicadmin -h 127.0.0.1  -P 3306 -pMySQL  127.0.0.1:3306 ssl  JS > dba.configureInstance();Configuring local MySQL instance listening at port 3306 for use in an InnoDB cluster...​This instance reports its own address as 30.1.15.45:3306​The instance '30.1.15.45:3306' is valid to be used in an InnoDB cluster.The instance '30.1.15.45:3306' is already ready to be used in an InnoDB cluster.​

创建集群实例

---默认为单主模式，第一个加入为写节点---第一个节点创建集群实例(30.1.15.45)mysqlsh --mysql -uicadmin -h 127.0.0.1  -P 3306 -pMySQL  127.0.0.1:3306 ssl  JS > var cluster = dba.createCluster('TestCluster')A new InnoDB cluster will be created on instance '127.0.0.1:3306'.Validating instance configuration at 127.0.0.1:3306...This instance reports its own address as 30.1.15.45:3306Instance configuration is suitable.NOTE: Group Replication will communicate with other members using '30.1.15.45:33061'. Use the localAddress option to override.​Creating InnoDB cluster 'TestCluster' on '30.1.15.45:3306'...​Adding Seed Instance...Cluster successfully created. Use Cluster.addInstance() to add MySQL instances.At least 3 instances are needed for the cluster to be able to withstand up toone server failure.---添加其他节点30.1.15.46/47mysqlsh --mysql -uicadmin -h 127.0.0.1  -P 3306 -pvar cluster=dba.getCluster()cluster.addInstance('icadmin@30.1.15.46:3306')cluster.addInstance('icadmin@30.1.15.47:3306')

创建成功后，查看集群状态

MySQL  localhost:3306 ssl  JS > var cluster=dba.getCluster()MySQL  localhost:3306 ssl  JS > cluster.status();{    "clusterName": "TestCluster",     "defaultReplicaSet": { "name": "default",  "primary": "30.1.15.45:3306",  "ssl": "REQUIRED",  "status": "OK",  "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",  "topology": {     "30.1.15.45:3306": {  "address": "30.1.15.45:3306",   "mode": "R/W",   "readReplicas": {},   "replicationLag": null,   "role": "HA",   "status": "ONLINE",   "version": "8.0.22"     },      "30.1.15.46:3306": {  "address": "30.1.15.46:3306",   "mode": "R/O",   "readReplicas": {},   "replicationLag": null,   "role": "HA",   "status": "ONLINE",   "version": "8.0.22"     },      "30.1.15.47:3306": {  "address": "30.1.15.47:3306",   "mode": "R/O",   "readReplicas": {},   "replicationLag": null,   "role": "HA",   "status": "ONLINE",   "version": "8.0.22"     } },  "topologyMode": "Single-Primary"    },     "groupInformationSourceMember": "30.1.15.46:3306"} MySQL  localhost:3306 ssl  JS >

5 配置MySQLRouter路由

路由初始化

root@c-innodb1 myrouter]# mysqlrouter --bootstrap icadmin@127.0.0.1:3306 --directory /opt/myrouter --conf-use-sockets  --user mysql Please enter MySQL password for icadmin: # Bootstrapping MySQL Router instance at '/opt/myrouter'...​Executing statements failed with: 'Error executing MySQL query "INSERT INTO mysql_innodb_cluster_metadata.v2_routers (address, product_name, router_name) VALUES ('c-innodb1', 'MySQL Router', '')": The MySQL server is running with the --super-read-only option so it cannot execute this statement (1290)' (1290), trying to connect to another nodeFetching Cluster Membersdisconnecting from mysql-servertrying to connect to mysql-server at 30.1.15.46:3306- 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 /opt/myrouter/mysqlrouter.conf​# MySQL Router configured for the InnoDB Cluster 'TestCluster'​After this MySQL Router has been started with the generated configuration​    $ mysqlrouter -c /opt/myrouter/mysqlrouter.conf​the cluster 'TestCluster' can be reached by connecting to:​## MySQL Classic protocol​- Read/Write Connections: localhost:6446, /opt/myrouter/mysql.sock- Read/Only Connections:  localhost:6447, /opt/myrouter/mysqlro.sock​## MySQL X protocol​- Read/Write Connections: localhost:64460, /opt/myrouter/mysqlx.sock- Read/Only Connections:  localhost:64470, /opt/myrouter/mysqlxro.sock---启动路由cd /opt/myrouter ./start.sh

查看路由配置策略

写路由转发策略:first-available读路由转发策略:round-robin-with-fallback[root@c-innodb2 myrouter]# cat /opt/myrouter/mysqlrouter.conf # File automatically generated during MySQL Router bootstrap[DEFAULT]user=mysqllogging_folder=/opt/myrouter/logruntime_folder=/opt/myrouter/rundata_folder=/opt/myrouter/datakeyring_path=/opt/myrouter/data/keyringmaster_key_path=/opt/myrouter/mysqlrouter.keyconnect_timeout=15read_timeout=30dynamic_state=/opt/myrouter/data/state.json​[logger]level = INFO​[metadata_cache:TestCluster]cluster_type=grrouter_id=5user=mysql_router5_23414s21p6pgmetadata_cluster=TestClusterttl=0.5auth_cache_ttl=-1auth_cache_refresh_interval=2use_gr_notifications=0​[routing:TestCluster_rw]bind_address=0.0.0.0bind_port=6446socket=/opt/myrouter/mysql.sockdestinations=metadata-cache://TestCluster/?role=PRIMARYrouting_strategy=first-availableprotocol=classic​[routing:TestCluster_ro]bind_address=0.0.0.0bind_port=6447socket=/opt/myrouter/mysqlro.sockdestinations=metadata-cache://TestCluster/?role=SECONDARYrouting_strategy=round-robin-with-fallbackprotocol=classic​[routing:TestCluster_x_rw]bind_address=0.0.0.0bind_port=64460socket=/opt/myrouter/mysqlx.sockdestinations=metadata-cache://TestCluster/?role=PRIMARYrouting_strategy=first-availableprotocol=x​[routing:TestCluster_x_ro]bind_address=0.0.0.0bind_port=64470socket=/opt/myrouter/mysqlxro.sockdestinations=metadata-cache://TestCluster/?role=SECONDARYrouting_strategy=round-robin-with-fallbackprotocol=x​[http_server]port=8443ssl=1ssl_cert=/opt/myrouter/data/router-cert.pemssl_key=/opt/myrouter/data/router-key.pem​[http_auth_realm:default_auth_realm]backend=default_auth_backendmethod=basicname=default_realm​[rest_router]require_realm=default_auth_realm​[rest_api]​[http_auth_backend:default_auth_backend]backend=metadata_cache​[rest_routing]require_realm=default_auth_realm​[rest_metadata_cache]require_realm=default_auth_realm

6 配置keepalive

配置监控脚本

vi /usr/local/keepalived/check_mysqlrouter.sh​#!/bin/bash  CHECK_TIME=3  #mysql router is working STATUS_OK is 1 ,  down STATUS_OK is 0  STATUS_OK=1 function check_mysqlrouter_health (){ MYROUTER_PROCESS=`ps -ef|grep -i mysqlrouter.pid |grep -v grep | awk '{print $2}'|wc -l ` if [[ ${MYROUTER_PROCESS} -eq  1 ]] ;thenSTATUS_OK=1 elseSTATUS_OK=0 fireturn $STATUS_OK  }  while [[ $CHECK_TIME -ne 0 ]]dolet "CHECK_TIME-=1"check_mysqlrouter_healthif [[ $STATUS_OK = 1 ]] ; then     CHECK_TIME=0    exit 0fi if [[ $STATUS_OK -eq 0 ]] &&  [[ $CHECK_TIME -eq 0 ]]then   systemctl stop keepalived.serviceexit 1 fisleep 1  done---授权执行权限chmod u+x  /usr/local/keepalived/check_mysqlrouter.sh

配置keepalived.conf(主)

vi /etc/keepalived/keepalived.conf​global_defs {     notification_email {     dba@163.com    }   notification_email_from dba@dbserver.com     smtp_server 127.0.0.1   smtp_connect_timeout 30 router_id MYROUTER-HA}​vrrp_script check_running {script "/usr/local/keepalived/check_mysqlrouter.sh"  interval 2weight 20   timeout 3      }​vrrp_instance VI_1 {   state BACKUP  interface ens192   virtual_router_id 51      priority 98advert_int 1nopreempt authentication {auth_type PASS     auth_pass 1024 }    virtual_ipaddress {     30.1.15.48/24 brd 30.1.15.255 dev ens192 label ens192:1 }   track_script { check_running   }}

配置keepalived.conf(备)

vi /etc/keepalived/keepalived.confglobal_defs {     notification_email {     dba@163.com  }   notification_email_from dba@dbserver.com     smtp_server 127.0.0.1   smtp_connect_timeout 30 router_id MYROUTER-HA   }​vrrp_script check_running {      script "/usr/local/keepalived/check_mysqlrouter.sh"  interval 2     weight 20 timeout 3    }​vrrp_instance VI_1 {state BACKUP    interface ens192virtual_router_id 51   priority 99     advert_int 1    nopreempt     authentication {    auth_type PASS  auth_pass 1024     }    virtual_ipaddress { 30.1.15.48/24 brd 30.1.15.255 dev ens192 label ens192:1      }   track_script { check_running   }}​

启动keepalived服务

--通常情况下，先启动MySQLRouter，再启动keepalived---先启动主节点keepalived的服务，再启备节点---主启动Systemctl start keepalived.service---查看vip 是否挂载ip a

高可用测试:

1 MGR主备手动切换

当前所在的节点在30.1.15.45:3306

 手动切换到30.1.15.46:3306，Cluster.setPrimaryInstance(instance)

 测试新会话，自动连接到30.1.15.46

2 MGR主备故障切换

当前所在主节点为30.1.15.46，直接kill主节点进程

 kill操作30.1.15.46

30.1.15.45节点被选举为主节点

 mysqlrouter自动更新集群成员状态

 测试新会话，自动连接到主节点30.1.15.45