作者简介

沈嵬,现就职于北京海天起点技术服务股份有限公司,具有多年oracle数据库维护经验,有11G OCP认证,致力于帮助客户解决生产环境出现的各种问题。

MySQL组复制功能集成了组管理服务,当primary节点意外宕机或者下线,组内会发起选举,把一个可用的读节点提升为primary节点。虽然实现了组内成员的自动化管理,但存在数据库IP地址发生变化的问题。

MySQL Router为数据库集群提供一个虚拟IP作为应用程序单一连接点,实现了负载均衡, 读写分离, 故障转移的数据库高可用功能,但自身没有HA。

keepalived软件能解决MySQL Router的Failover问题。

数据库安装以及组复制过程略,mgr1、mgr2、mgr3三台主机配置了single-primary模式MGR。

mysql> SELECT * FROM performance_schema.replication_group_members;+---------------------------+--------------------------------------+-------------+-------------+--------------+| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |+---------------------------+--------------------------------------+-------------+-------------+--------------+| group_replication_applier | 0b80fbcf-571f-11e8-875e-000c2983e36f | mgr1 | 3306 | ONLINE || group_replication_applier | 2137a15e-5-11e8-8adb-000c29840ba8 | mgr3 | 3306 | ONLINE || group_replication_applier | b5f1913f-da20-11e3-913c-08002788310b | mgr2 | 3306 | ONLINE |+---------------------------+--------------------------------------+-------------+-------------+--------------+3 rows in set (0.00 sec)MySQL Shell配置

解压缩软件:

# tar zxvf mysql-router-8.0.11-el7-x86-64bit.tar.gz# tar zxvf mysql-shell-8.0.11-linux-glibc2.12-x86-64bit.tar.gz# chown -R mysql:mysql mysql-router-8.0.11-el7-x86-64bit# chown -R mysql:mysql mysql-shell-8.0.11-linux-glibc2.12-x86-64bit# mv mysql-router-8.0.11-el7-x86-64bit mysql-router# mv mysql-shell-8.0.11-linux-glibc2.12-x86-64bit mysql-shell

创建数据库用户:

mysql> CREATE USER 'mysql_shell'@'192.168.1.%' IDENTIFIED BY 'oracle';mysql> GRANT FILE, PROCESS, RELOAD, SHUTDOWN, SUPER ON *.* TO mysql_shell@'192.168.1.%' WITH GRANT OPTION;mysql> GRANT SELECT ON sys.* TO mysql_shell@'192.168.1.%' WITH GRANT OPTION;mysql> GRANT SELECT ON performance_schema.* TO mysql_shell@'192.168.1.%' WITH GRANT OPTION;mysql> GRANT DELETE, INSERT, SELECT, UPDATE ON mysql.* TO mysql_shell@'192.168.1.%' WITH GRANT OPTION;mysql> GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata.* TO mysql_shell@'192.168.1.%' WITH GRANT OPTION;mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT,CREATE USER ON *.* TO mysql_shell@'192.168.1.%' WITH GRANT OPTION;mysql> FLUSH PRIVILEGES;

创建集群:

[root@mgr4 bin]# pwd/app/mysql-shell/bin [root@mgr4 bin]# ./mysqlsh --uri mysql_shell@192.168.1.31:3306Creating a session to 'mysql_shell@192.168.1.31:3306'Enter password: ******Fetching schema names for autocompletion... Press ^C to stop.Your MySQL connection id is 10906Server version: 5.7.22-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)No default schema selected; type \use <schema> to set one.MySQL Shell 8.0.11 Copyright (c) 2016, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners. Type '\help' or '\?' for help; '\quit' to exit. MySQL  192.168.1.31:3306 ssl  JS > dba.createCluster('myCluster')You are connected to an instance that belongs to an unmanaged replication group.Do you want to setup an InnoDB cluster based on this replication group? [Y/n]: yA new InnoDB cluster will be created based on the existing replication group on instance 'mysql_shell@192.168.1.31:3306'. Creating InnoDB cluster 'myCluster' on 'mysql_shell@192.168.1.31:3306'...Adding Seed Instance...Adding Instance 'mgr3:3306'...Adding Instance 'mgr2:3306'... Cluster successfully created based on existing replication group. MySQL  192.168.1.31:3306 ssl  JS > var cluster = dba.getCluster('myCluster'); MySQL  192.168.1.31:3306 ssl  JS > cluster.status();{    "clusterName": "myCluster",    "defaultReplicaSet": {        "name": "default",        "primary": "mgr1:3306",        "ssl": "DISABLED",        "status": "OK",        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",        "topology": {            "mgr1:3306": {                "address": "mgr1:3306",                "mode": "R/W",                "readReplicas": {},                "role": "HA",                "status": "ONLINE"            },            "mgr2:3306": {                "address": "mgr2:3306",                "mode": "R/O",                "readReplicas": {},                "role": "HA",                "status": "ONLINE"            },            "mgr3:3306": {                "address": "mgr3:3306",                "mode": "R/O",                "readReplicas": {},                "role": "HA",                "status": "ONLINE"            }        }    },    "groupInformationSourceMember": "mysql://mysql_shell@192.168.1.31:3306"}

执行var cluster = dba.createCluster(‘myCluster’)成功后会自动创建一个新的数据库: mysql_innodb_cluster_metadata, 用于存放集群的元数据,该元数据会被MySQL Router用到, 以实现高可用性。

[root@mgr4 mysql-router]# pwd/app/mysql-router [root@mgr4 bin]# ./mysqlrouter --bootstrap mysql_shell@192.168.1.31:3306 --directory /app/myrouter --user=mysql --conf-use-socketsPlease enter MySQL password for mysql_shell: Reconfiguring MySQL Router instance at '/app/myrouter'...MySQL Router  has now been configured for the InnoDB cluster 'myCluster'. The following connection information can be used to connect to the cluster. Classic MySQL protocol connections to cluster 'myCluster':- Read/Write Connections: localhost:6446- Read/Write Connections: /app/myrouter/mysql.sock- Read/Only Connections: localhost:6447- Read/Only Connections: /app/myrouter/mysqlro.sock X protocol connections to cluster 'myCluster':- Read/Write Connections: localhost:64460- Read/Write Connections: /app/myrouter/mysqlx.sock- Read/Only Connections: localhost:64470- Read/Only Connections: /app/myrouter/mysqlxro.sock [root@mgr4 myrouter]# sh /app/myrouter/start.sh [root@mgr4 myrouter]# netstat -anpt |grep routertcp        0      0 0.0.0.0:64460           0.0.0.0:*               LISTEN      1945/mysqlroutertcp        0      0 0.0.0.0:6446            0.0.0.0:*               LISTEN      1945/mysqlroutertcp        0      0 0.0.0.0:6447            0.0.0.0:*               LISTEN      1945/mysqlroutertcp        0      0 0.0.0.0:64470           0.0.0.0:*               LISTEN      1945/mysqlroutertcp        0      0 192.168.1.34:52418      192.168.1.31:3306       ESTABLISHED 1945/mysqlrouter

mgr5做同样操作,这样这两台机器都提供了集群统一访问入口

λ mysql -uroot -h192.168.1.34 -P6446 -p -e "select @@hostname"Enter password: ******+------------+| @@hostname |+------------+| mgr1       |+------------+ λ mysql -uroot -h192.168.1.35 -P6446 -p -e "select @@hostname"Enter password: ******+------------+| @@hostname |+------------+| mgr1       |+------------+

接下来通过keepalived实现MySQL Router的HA,提供一个虚拟IP作为应用统一访问地址。

在mgr4,mgr5上安装keepalived

tar zxvf keepalived-1.4.4.tar.gzcd /app/keepalived-1.4.4# ./configure# ./make && make install# cp /app/keepalived-1.4.4/keepalived/etc/init.d/keepalived /etc/rc.d/init.d/# cp /usr/local/etc/keepalived/keepalived.conf /etc/keepalived/# vim /etc/keepalived/keepalived.conf

内容分别如下:

[root@mgr4 ]# cat /etc/keepalived/keepalived.conf! Configuration File for keepalived global_defs {   router_id mysqlrouterha} vrrp_instance VI_1 {    state BACKUP    interface ens33    #指定虚拟ip的网卡接口    virtual_router_id 51    #路由器标识,MASTER和BACKUP必须是一致的    priority 50    #定义优先级,数字越大,优先级越高,在同一个vrrp_instance下,MASTER的优先级必须大于BACKUP的优先级。这样MASTER故障恢复后,就可以将VIP资源再次抢回来    advert_int 1    nopreempt    authentication {        auth_type PASS        auth_pass 1111    }    virtual_ipaddress {        192.168.1.36    }} virtual_server 192.168.1.36 6446 {     delay_loop 2     lb_algo wrr     lb_kind DR     persistence_timeout 60     protocol TCP     real_server 192.168.1.34 6446 {         weight 3         notify_down /etc/keepalived/startrouter.sh         TCP_CHECK {             connect_timeout 10             nb_get_retry 3             delay_before_retry 3             connect_port 6446         }     }} virtual_server 192.168.1.36 6447 {     delay_loop 2     lb_algo wrr     lb_kind DR     persistence_timeout 60     protocol TCP     real_server 192.168.1.34 6447 {         weight 3         notify_down /etc/keepalived/startrouter.sh         TCP_CHECK {             connect_timeout 10             nb_get_retry 3             delay_before_retry 3             connect_port 6447         }     }}

[root@mgr5 ]# cat /etc/keepalived/keepalived.conf! Configuration File for keepalived global_defs {   router_id mysqlrouterha} vrrp_instance VI_1 {    state MASTER    interface ens33    #指定虚拟ip的网卡接口    virtual_router_id 51    #路由器标识,MASTER和BACKUP必须是一致的    priority 100    #定义优先级,数字越大,优先级越高,在同一个vrrp_instance下,MASTER的优先级必须大于BACKUP的优先级。这样MASTER故障恢复后,就可以将VIP资源再次抢回来    advert_int 1    nopreempt    authentication {        auth_type PASS        auth_pass 1111    }    virtual_ipaddress {        192.168.1.36    }} virtual_server 192.168.1.36 6446 {     delay_loop 2     lb_algo wrr     lb_kind DR     persistence_timeout 60     protocol TCP     real_server 192.168.1.35 6446 {         weight 3         notify_down /etc/keepalived/startrouter.sh         TCP_CHECK {             connect_timeout 10             nb_get_retry 3             delay_before_retry 3             connect_port 6446         }     }} virtual_server 192.168.1.36 6447 {     delay_loop 2     lb_algo wrr     lb_kind DR     persistence_timeout 60     protocol TCP     real_server 192.168.1.35 6447 {         weight 3         notify_down /etc/keepalived/startrouter.sh         TCP_CHECK {             connect_timeout 10             nb_get_retry 3             delay_before_retry 3             connect_port 6447         }     }}

生成136.160.1.36虚拟IP

正常通过VIP可以通过不同端口连接读写和只读节点

λ mysql -uroot -h192.168.1.36 -P6446 -p -e "select @@hostname"Enter password: ******+------------+| @@hostname |+------------+| mgr1 |+------------+λ mysql -uroot -h192.168.1.36 -P6447 -p -e "select @@hostname"Enter password: ******+------------+| @@hostname |+------------+| mgr3 |+------------+

如果mgr2和mgr3全关闭了使用6447端口连接那就报错了

λ mysql -uroot -h192.168.1.36 -P6447 -p -e "select @@hostname"Enter password: ******ERROR 2003 (HY000): Can't connect to remote MySQL server for client connected to '0.0.0.0:6447'

两台部署了MySQL Router的主机任意一台关闭不影响访问,高可用实现。

报错处理1

[root@mgr4 bin]# ./mysqlrouter --bootstrap mysql_shell@192.168.1.31:3306 --directory /app/myrouter --user=mysql --conf-use-socketsPlease enter MySQL password for mysql_shell: Bootstrapping MySQL Router instance at '/app/myrouter'...Error: It appears that a router instance named '' has been previously configured in this host. If that instance no longer exists, use the --force option to overwrite it.

mysql> delete FROM mysql_innodb_cluster_metadata.routers;Query OK, 2 rows affected (0.00 sec) mysql> delete FROM mysql_innodb_cluster_metadata.hosts where host_name='mgr4';Query OK, 1 row affected (0.00 sec) mysql> commit;Query OK, 0 rows affected (0.00 sec)

报错处理2

/app/myrouter/start.sh 执行无效,当前目录无法生成.sock文件,ps -ef|grep router显示无进程

删除mysqlrouter.pid后重新执行

报错处理3

[root@mgr4 myrouter]# mysql -uroot -h192.168.1.34 -P6446 -pEnter password:ERROR 1129 (HY000): Too many connection errors from 192.168.1.34

重启router