MySQL8搭建Innodb一主两从集群

一、说明

MySQL InnoDB Cluster（简称MIC）是MySQL推出的整套解决方案，由几个部分组成：

MySQL Server，核心是Group Replication（组复制），简称MGR。
MySQL Shell，可编程的高级客户端，支持标准SQL语法、JavaScript语法、Python语法，以及API接口，可以更方便的管理和使用MySQL服务器。
MySQL Router，轻量级中间件，支持透明路由规则（读写分离及读负载均衡）。

分布式MySQL主要有InnoDB和NDB模式, NDB是基于集群的引擎-数据被自动切分并复制到数个机器上（数据节点），适合于那些需要极高查询性能和高可用性的应用，原来是为爱立信的电信应用设计的。 NDB提供了高达99.999%的可靠性，在读操作多的应用中表现优异。对于有很多并发写操作的应用，还是推荐用InnoDB。

NDB和InnoDB存储引擎之间的特性差异

InnoDB(MySQL 5.7)特性:

InnoDB版本：InnoDB 5.7.20；
NDB Cluster版本：不支持；
最大存储长度：64TB；
事物：所有标准事物类型；
多版本并发控制：支持；
数据压缩：支持；
大行支：VARBINARY、VARCHAR、BLOB；
同步支持：半同步、异步；
块读取：支持；
块写入：需要使用水平分区；
高可用性：高；

NDB 7.5/7.6特性：

InnoDB版本：InnoDB 5.7.20；
NDB Cluster版本： NDB 7.5.8/7.6.4；
最大存储长度：128TB；
事物：读提交；
多版本并发控制：不支持；
数据压缩支持：不支持；
大行支持：BLOB、 TEXT；
同步支持半：自动同步；
块读取：支持；
块写入：支持；
高可用性：非常高；

主机规划

主机名	IP地址	安装组件
mysql-s01	192.168.100.101	mysql,shell,router
mysql-s02	192.168.100.102	mysql,shell
mysql-s03	192.168.100.103	mysql,shell

二、主机初始化设置

# 关闭防火墙
systemctl stop firewalld.service && systemctl disable firewalld.service

# 关闭selinux
sudo sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config

# 设置时区
timedatectl set-timezone Asia/Shanghai

# 重启主机
reboot

三、安装mysql、shell

MySQL数据节点s1-s3安装mysql、shell

yum install -y net-tools perl
yum localinstall -y mysql-community-common-8.0.16-2.el7.x86_64.rpm
yum localinstall -y mysql-community-libs-8.0.16-2.el7.x86_64.rpm
yum localinstall -y mysql-community-client-8.0.16-2.el7.x86_64.rpm
yum localinstall -y mysql-community-server-8.0.16-2.el7.x86_64.rpm
yum localinstall -y mysql-shell-8.0.16-1.el7.x86_64.rpm

四、配置及启动mysql服务

1. MySQL数据节点s1-s3安装mysql配置

vi /etc/my.cnf

[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

# 服务器唯一标识符
server_id=1
# 忽略大小写
lower_case_table_names=1
# 修改MySQL8的验证插件为mysql_native_password
default_authentication_plugin=mysql_native_password
# 启用了全局事务标识符（GTID）模式，这是一种复制模式，可以跟踪每个事务的执行情况，而不需要使用二进制日志文件名和位置
gtid_mode=ON
# 强制GTID模式的一致性，即只允许那些可以安全地使用GTID复制的语句执行
enforce_gtid_consistency=ON
# 禁用了二进制日志的校验和功能，即不在二进制日志事件中添加额外的数据来验证事件的完整性
binlog_checksum=NONE
# 启用了二进制日志，并指定了二进制日志文件的基本名称为binlog
log_bin=binlog
# 使得从服务器在接收到主服务器的更新后，也将这些更新记录到自己的二进制日志中，这对于链式复制或备份从服务器很有用
log_slave_updates=ON
#  指定了二进制日志的格式为行格式，即记录每个被修改的行的变化，而不是记录执行的语句
binlog_format=ROW
# 指定了从服务器存储主服务器信息（如主服务器的主机名、端口号、用户名、密码等）的位置为一个表（mysql.slave_master_info），而不是一个文件（master.info）
master_info_repository=TABLE
# 指定了从服务器存储中继日志信息（如当前正在执行的中继日志文件名和位置等）的位置为一个表（mysql.slave_relay_log_info），而不是一个文件（relay-log.info）
relay_log_info_repository=TABLE
# 指定了事务写集提取算法为XXHASH64，这是一种快速的哈希算法，用于生成每个事务写集的唯一标识符，以便在群组复制中检测和避免冲突
transaction_write_set_extraction=XXHASH64
# 指定了服务器启动时是否自动启动群组复制插件。如果设置为OFF，则需要手动执行START GROUP_REPLICATION语句来启动群组复制
loose-group_replication_start_on_boot=OFF
# 指定了群组复制中的群组名称，它是一个128位的无符号整数，用16进制表示，用于标识一个复制群组。所有参与群组复制的服务器必须使用相同的群组名称
loose-group_replication_group_name="aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee"
# 指定了本地服务器在群组复制中使用的地址，包括IP地址和端口号。这个地址用于与其他群组成员通信
loose-group_replication_local_address="192.168.100.101:33061"
# 指定了一组种子地址，用于在启动群组复制时发现其他群组成员。这些地址可以是本地服务器或远程服务器的地址
loose-group_replication_group_seeds='192.168.100.101:33061,192.168.100.102:33061,192.168.100.103:33061'
# 指定了是否将本地服务器作为引导服务器，用于创建一个新的群组或恢复一个已有的群组。只有在没有其他可用的群组成员时，才需要设置这个参数为ON
loose-group_replication_bootstrap_group=OFF

2. MySQL数据节点s1-s3启动mysql

# 创建数据目录
mkdir -p /data/mysql
chown mysql:mysql /data/mysql -R

# 启动服务
systemctl start mysqld

# 创建sock的软连接
ln -s /data/mysql/mysql.sock /var/lib/mysql/mysql.sock

3. MySQL数据节点s1-s3设置root账户密码


# 查看密码
grep 'temporary password' /var/log/mysqld.log
# 登录mysql
mysql -u root -p

# 设置root密码并开启远程登录
# 关闭binlog
SET SQL_LOG_BIN=0;
ALTER user 'root'@'localhost' identified by '[密码]';
use mysql;
UPDATE user set host='%' where user='root';
FLUSH PRIVILEGES;
ALTER user 'root'@'%' IDENTIFIED WITH mysql_native_password BY '[密码]';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

SET SQL_LOG_BIN=1;

五、开启MGR组复制

1. 主从节点执行

# 安装组复制插件
INSTALL PLUGIN group_replication SONAME 'group_replication.so';


# 创建组复制账户
# 关闭binlog
SET SQL_LOG_BIN=0;
CREATE user 'repl'@'%' IDENTIFIED BY '[密码]';
FLUSH PRIVILEGES;
ALTER user 'repl'@'%' IDENTIFIED WITH mysql_native_password BY '[密码]';
GRANT REPLICATION SLAVE ON *.* TO repl@'%';
FLUSH PRIVILEGES;

SET SQL_LOG_BIN=1;

# 设置组复制所需的账户密码
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='[密码]' FOR CHANNEL 'group_replication_recovery';

2. 主节点启动组复制

SET GLOBAL group_replication_bootstrap_group=ON; 
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
SELECT * FROM performance_schema.replication_group_members;

3. 从节点启动组复制

START GROUP_REPLICATION;


# 查看状态
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 2e1f430f-2acb-11ee-9eb3-000c29686f9a | mysql-s03   |        3306 | RECOVERING   | SECONDARY   | 8.0.16         |
| group_replication_applier | 5148c8e8-2ac9-11ee-9e41-000c2942577d | mysql-s02   |        3306 | RECOVERING   | SECONDARY   | 8.0.16         |
| group_replication_applier | bcc341ad-2ac5-11ee-ae6f-000c2979d7ce | mysql-s01   |        3306 | ONLINE       | PRIMARY     | 8.0.16         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.01 sec)

注意：tail -f /var/log/mysqld.log查看报错日志，运行一段时间无任何报错才表示搭建成功

至此，在主节点上创建数据库、表、导入数据，从节点会自动同步。
也可以使用ProxySQL+MGR组复制，实现读写分离。

六、创建innodb集群

1. 停止组复制

所有数据节点暂时停止组复制

mysql> STOP GROUP_REPLICATION;

2. 初始化实例信息

所有数据节点均执行

mysqlsh root@localhost:3306
 MySQL  localhost:3306 ssl  JS > dba.configureLocalInstance()
Configuring local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as mysql-s03
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.

The instance 'localhost:3306' is valid for InnoDB cluster usage.
The instance 'localhost:3306' is already ready for InnoDB cluster usage.

3. 启动组复制

主节点

mysql> SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (3.08 sec)

mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)

从节点

mysql> STOP GROUP_REPLICATION;

4. 创建集群

主节点执行

 MySQL  localhost:3306 ssl  JS > var cluster = dba.createCluster('mysqlCluster', {adoptFromGR: true});
A new InnoDB cluster will be created based on the existing replication group on instance 'root@localhost:3306'.

Creating InnoDB cluster 'mysqlCluster' on 'root@localhost:3306'...

Adding Seed Instance...
Adding Instance 'mysql-s01:3306'...
Adding Instance 'mysql-s02:3306'...
Adding Instance 'mysql-s03:3306'...
Cluster successfully created based on existing replication group.


 MySQL  localhost:3306 ssl  JS > cluster.status();
{
    "clusterName": "mysqlCluster",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "mysql-s01:3306",
        "ssl": "DISABLED",
        "status": "OK",
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
        "topology": {
            "mysql-s01:3306": {
                "address": "mysql-s01:3306",
                "mode": "R/W",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.16"
            },
            "mysql-s02:3306": {
                "address": "mysql-s02:3306",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.16"
            },
            "mysql-s03:3306": {
                "address": "mysql-s03:3306",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE",
                "version": "8.0.16"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "mysql-s01:3306"
}

七、 mysql-router实现读写分离、从库负载均衡

以下操作均在主节点执行

1. 安装mysql-route

rpm -ivh mysql-router-community-8.0.16-2.el7.x86_64.rpm

2. 配置mysql-route

vi /etc/mysqlrouter/mysqlrouter.conf
尾部追加

[routing:read_writer]
bind_address = 192.168.100.101
bind_port = 13306
mode = read-write
destinations = 192.168.100.101:3306
max_connections = 1024
protocol = classic

[routing:read_only]
bind_address = 192.168.100.101
bind_port = 23306
mode = read-only
destinations = 192.168.100.102:3306,192.168.100.103:3306
max_connections = 1024
protocol = classic

3. 启动mysql-router

配置系统service

vi /lib/systemd/system/mysql-router.service


[Unit]
Description=mysql-router

[Service]
WorkingDirectory=/etc/mysqlrouter
PrivateTmp=false
Restart=always
Type=simple
ExecStart=mysqlrouter -c /etc/mysqlrouter/mysqlrouter.conf
ExecStop=/usr/bin/kill -15 $MAINPID

[Install]
WantedBy=multi-user.target


# 启动
systemctl start mysql-router

# 查看日志
tail -f /var/log/mysqlrouter/mysqlrouter.log

八、使用

读写节点连接地址：[主节点IP]:13306
只读节点连接地址：[主节点IP]:23306

在Java软件开发中，有以下几种方案使用读写分离

mybatis-plus多数据源+注解指定数据源
mybatis-plus多数据源+AOP指定数据源

如果是不想动代码，可使用ProxySQL

其它

参考文章：https://blog.csdn.net/qq_37726021/article/details/129835251