搭建MySQL innodb cluster集群模式

机器信息

机器	角色
192.168.124.132	master
192.168.124.130	slave
192.168.124.133	slave

三台机器都是centos7.5，数据库为MySQL5.7

搭建模式

本次我们使用的是MySQL组复制的Single-Primary Mode:因为此模式对于我们也够用，后续我们会讲一下组复制的限制和优缺点

搭建环境配置

• 取消防火墙或者开通配置的端口
• 取消selinux
• 设置hostname及hostname和ip的映射关系
  hostname的设置方式如下：

echo "HOSTNAME=node8130" >> /etc/sysconfig/network
echo "node8130" > /etc/hostname

退出会话再次登录即可看到新的主机名
hotsname和ip的映射(非常重要！)

image.png

每台机器都需要在/etc/hosts文件下写入三台机器的ip和hostname,注意是三台机器都需要写，每台机器写的内容都一样。

• 安装MySQL，假如您是centos系统，则直接可以使用我写的shell脚本进行安装，简单方便，脚本地址如下：
  https://github.com/cheungfeifu/install_uninstall_mysql
  如上环境我们已经搭建完毕。下面开始进行组复制配置。

组复制配置

MySQL组复制类似于mongo的复制集，提供了故障转移(failover)和故障恢复(failback)等功能，相比较主从复制模式，具备更高的高可用性。
搭建步骤：

• 搭建主节点
  在你的主节点机器修改MySQL配置文件
  vim /etc/my.cnf
  在原配置文件基础上输入如下信息：

validate_password = OFF
character_set_server = utf8mb4
server_id = 1
gtid_mode = ON
enforce_gtid_consistency = ON
master_info_repository = TABLE 
relay_log_info_repository = TABLE 
binlog_checksum = NONE 
log_slave_updates = ON 
log_bin = mysql-bin
binlog_format= ROW
transaction_write_set_extraction = XXHASH64 
loose-group_replication_group_name = '5a421130-2674-11ea-bbce-00505639ee45'
loose-group_replication_start_on_boot = off 
loose-group_replication_local_address = 'box9077.server.node:33061' 
loose-group_replication_group_seeds = 'box9077.server.node:33061,box9062.slave2.node:33061,box9069.slave3.node:33061' 
loose-group_replication_bootstrap_group = off

注意配置文件里面的loose-group_replication_local_address 和loose-group_replication_group_seeds 要换成你自己的hostname,也就是上面设置的主机名和IP映射的那个。
重启MySQL
systemctl restart mysqld
获取MySQL初始密码
grep 'temporary password' /var/log/mysqld.log
登录MySQL后，需要进行如下操作：

ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!'; 
修改root用户密码
CREATE USER 'root'@'%' IDENTIFIED by 'MyNewPass4!';  
创建一个root@%用户
GRANT ALL on *.* to root@'%' with grant option;
给root@%授权

组复制命令，依次执行即可

install plugin group_replication soname 'group_replication.so';

show plugins;

set SQL_LOG_BIN=0;
grant replication slave on *.* to repl@'%' identified by 'MyNewPass4!';
flush privileges;
set SQL_LOG_BIN=1;
change master to master_user='repl',master_password='MyNewPass4!' for channel 'group_replication_recovery';


set global group_replication_bootstrap_group=ON;
start group_replication;
set global group_replication_bootstrap_group=OFF;

查询是否复制成功
SELECT * FROM performance_schema.replication_group_members
假如出现如下操作即是主节点操作成功

image.png

• 搭建两个从节点
  把主节点的my.cnf文件直接scp到其他两台从节点机器上，然后修改server_id = 1和loose-group_replication_local_address = 'box9077.server.node:33061' ，其他无需修改
  重启MySQL后，在日志文件获取root密码登录
  执行如下命令

ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!'; 
修改root用户密码
CREATE USER 'root'@'%' IDENTIFIED by 'MyNewPass4!';  
创建一个root@%用户
GRANT ALL on *.* to root@'%' with grant option;
给root@%授权

再进行从节点的复制操作依次执行命令即可

install plugin group_replication soname 'group_replication.so';
show plugins;
set SQL_LOG_BIN=0;
grant replication slave on *.* to repl@'%' identified by 'MyNewPass4!';
flush privileges;
set SQL_LOG_BIN=1;
change master to master_user='repl',master_password='MyNewPass4!' for channel 'group_replication_recovery';

start group_replication;

假如出现

image.png

则表示三台机器都已经配置完成组复制

可能遇到的报错信息：

mysql> start group_replication;                                                                                                                                             
ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.

mysqld.log错误信息如下

2020-06-28T14:51:36.520482Z 0 [ERROR] Plugin group_replication reported: 'The member contains transactions not present in the group. The member will now exit the group.'   

解决方式为：在出现该节点MySQL下执行：reset master;
然后再次执行start group_replication;即可。
至此MySQL组复制已经配置完成！

创建innodb cluster

由于我们是使用MySQL组复制来搭建innodb cluster，所以我们需要在主节点安装mysql shell
yum install mysql-shell -y
mysqlsh --help提示连接方式如下

Usage examples:                                                                                                                                                             
$ mysqlsh root@localhost/schema                                                                                                                                             
$ mysqlsh mysqlx://root@some.server:3307/world_x                                                                                                                            
$ mysqlsh --uri root@localhost --py -f sample.py sample param                                                                                                               
$ mysqlsh root@targethost:33070 -s world_x -f sample.js                                                                                                                     
$ mysqlsh -- util check-for-server-upgrade root@localhost --output-format=JSON                                                                                              
$ mysqlsh mysqlx://user@host/db --import ~/products.json shop  

我们使用mysqlsh root@localhost来进行登录，输入密码后，进入mysqlsh命令行，默认为js模式,执行如下命令创建集群
var cluster = dba.createCluster('prodCluster', {adoptFromGR: true});
假如出现如下信息，则表码集群创建完成

A new InnoDB cluster will be created based on the existing replication group on instance '/var%2Flib%2Fmysql%2Fmysql.sock'.

Creating InnoDB cluster 'prodCluster' on 'box9077.server.node:3306'...

Adding Seed Instance...
NOTE: Metadata schema found in target instance
Adding Instance 'box9077.server.node:3306'...
Adding Instance 'box9062.slave2.node:3306'...
Adding Instance 'box9069.slave3.node:3306'...
Resetting distributed recovery credentials across the cluster...
WARNING: Instance 'box9077.server.node:3306' cannot persist configuration since MySQL version 5.7.26 does not support the SET PERSIST command (MySQL version >= 8.0.11 required). Please use the dba.configureLocalInstance() command locally to persist the changes.
WARNING: Instance 'box9062.slave2.node:3306' cannot persist configuration since MySQL version 5.7.30 does not support the SET PERSIST command (MySQL version >= 8.0.11 required). Please use the dba.configureLocalInstance() command locally to persist the changes.
WARNING: Instance 'box9069.slave3.node:3306' cannot persist configuration since MySQL version 5.7.30 does not support the SET PERSIST command (MySQL version >= 8.0.11 required). Please use the dba.configureLocalInstance() command locally to persist the changes.
Cluster successfully created based on existing replication group.

此时我们使用cluster.status(); 可以看到创建成功的集群状态信息

{
    "clusterName": "prodCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "box9077.server.node:3306", 
        "ssl": "DISABLED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "box9062.slave2.node:3306": {
                "address": "box9062.slave2.node:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "box9069.slave3.node:3306": {
                "address": "box9069.slave3.node:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "box9077.server.node:3306": {
                "address": "box9077.server.node:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }, 
        "topologyMode": "Single-Primary"
    }, 
    "groupInformationSourceMember": "box9077.server.node:3306"
}
 MySQL  localhost  JS > 

设置mysqlrouter

当主节点挂掉后，组复制会自动选择两个从节点的某一个作为主节点，但是应用连接主节点的IP后，挂了需要手动切换，所以我们使用MySQL自带的中间件MySQL router来进行应用透明，该中间件最新版可以支持5000个连接，以前只支持500个。
建议在三台机器都安装mysql router
安装方式如下
yum install mysql-router -y

执行如下命令开始自动配置路由信息
mysqlrouter --bootstrap root@node8132:3306 --user=root --force
出现如下信息则代表路由设置完成。

mysqlrouter --bootstrap root@node8132:3306 --user=root --force
Please enter MySQL password for root: 
# Bootstrapping system MySQL Router instance...

- 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 /etc/mysqlrouter/mysqlrouter.conf

Existing configuration backed up to '/etc/mysqlrouter/mysqlrouter.conf.bak'

# MySQL Router configured for the InnoDB Cluster 'prodCluster'

After this MySQL Router has been started with the generated configuration

    $ /etc/init.d/mysqlrouter restart
or
    $ systemctl start mysqlrouter
or
    $ mysqlrouter -c /etc/mysqlrouter/mysqlrouter.conf

the cluster 'prodCluster' can be reached by connecting to:

## MySQL Classic protocol

- Read/Write Connections: localhost:6446
- Read/Only Connections:  localhost:6447

## MySQL X protocol

- Read/Write Connections: localhost:64460
- Read/Only Connections:  localhost:64470

可以看到，6446端口为读写，6447只可以读

mysqlrouter -c /etc/mysqlrouter/mysqlrouter.conf
开启路由，然后就可以使用路由进行数据库连接了。
至此MySQL innodb cluster集群就部署完成

假如需要更好的高可用性和负载均衡，可以考虑使用keepalived+haproxy组合。