MySQL8搭建Innodb一主两从集群

mtain 2023年07月21日 287次浏览

一、说明

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-s01192.168.100.101mysql,shell,router
mysql-s02192.168.100.102mysql,shell
mysql-s03192.168.100.103mysql,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

image.png

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

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

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

其它

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