Ubuntu 22.04 下 MySQL 8.0.38 MGR 搭建记录

2026.03.22

苦温集 · 部署教程 Linux · MySQL · MGR

提示：本笔记基于 Ubuntu 22.04 系统和 MySQL 8.0.38 版本，其他版本可能存在差异，请根据实际情况调整。

0. 下载

从 MySQL 官网下载 bundle.tar 压缩包并解压，准备安装文件。

1. 设置时区

确保所有节点时间一致。

timedatectl set-timezone Asia/Shanghai
systemctl restart systemd-timesyncd

2. 安装

2.1 更新源并安装依赖

apt update
apt install libmecab2

2.2 安装 MySQL 组件

按照依赖顺序安装 deb 包：

dpkg -i mysql-common_8.0.38-1ubuntu22.04_amd64.deb \
mysql-community-client-plugins_8.0.38-1ubuntu22.04_amd64.deb \
mysql-community-client-core_8.0.38-1ubuntu22.04_amd64.deb \
mysql-community-client_8.0.38-1ubuntu22.04_amd64.deb \
mysql-client_8.0.38-1ubuntu22.04_amd64.deb \
mysql-server_8.0.38-1ubuntu22.04_amd64.deb \
mysql-community-server_8.0.38-1ubuntu22.04_amd64.deb \
mysql-community-server-core_8.0.38-1ubuntu22.04_amd64.deb

2.3 验证状态

systemctl status mysql

3. 迁移 MySQL 数据目录至专用数据分区

注意：Ubuntu 系统自带 AppArmor 访问控制，仅修改 MySQL 配置文件更改数据路径会导致服务启动失败，必须同步修改 AppArmor 配置。

3.1 复制数据目录

停止服务并复制数据：

systemctl stop mysql
cp -ar /var/lib/mysql /data/mysql
chown -R mysql:mysql /data/mysql

3.2 修改 MySQL 配置文件

编辑 /etc/mysql/mysql.conf.d/mysqld.cnf，修改 datadir 路径：

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /data1/mysql  # 注意：此处笔记原文写的是 /data1/mysql，复制命令是 /data/mysql，请根据实际磁盘挂载路径调整
log-error       = /var/log/mysql/error.log

3.3 修改 AppArmor 安全配置

编辑 /etc/apparmor.d/usr.sbin.mysqld，添加新目录的访问权限：

# /var/lib/mysql/ r,
# /var/lib/mysql/** rwk,
/data/mysql/ r,
/data/mysql/** rwk,

3.4 修改 AppArmor 访问控制文件

编辑 /etc/apparmor.d/abstractions/mysql：

# /var/lib/mysql{,d}/mysql{,d}.sock rw
/data/mysql{,d}/mysql{,d}.sock rw

提示：本步骤（数据迁移）三台机器都需要执行。

3.5 重启服务

systemctl restart apparmor.service
systemctl start mysql
systemctl status mysql

4. 编辑 MySQL 配置文件

重要：以下配置需在集群所有节点执行，注意 server-id 和 report_host 等参数在各节点需设为不同值。

4.1 配置 `client.cnf`

编辑 /etc/mysql/conf.d/client.cnf：

[client]
port                            = 3307
socket                          = /var/run/mysqld/mysqld.sock

4.2 配置 `mysql.cnf`

编辑 /etc/mysql/conf.d/mysql.cnf：

[mysql]
port                            = 3307
socket                          = /var/run/mysqld/mysqld.sock
default-character-set           = utf8mb4
prompt                          = \\u@\\h:\\d \\r:\\m:\\s >

4.3 配置 `mysqld.cnf` (MGR 核心)

编辑 /etc/mysql/mysql.conf.d/mysqld.cnf：

[mysqld]
# 基础配置
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /data1/mysql
log-error       = /var/log/mysql/error.log
# 服务器标识 (每个节点必须不同)
server-id                       = 205
port                            = 3307
report_host                     = 60.28.24.205  # 填写本机IP
# 存储引擎与字符集
default_storage_engine          = InnoDB
character_set_server            = utf8mb4
# Binlog 与 GTID (MGR 必需)
binlog_format                   = ROW
gtid_mode                       = ON
enforce_gtid_consistency        = ON
binlog_checksum                 = NONE
log_slave_updates               = ON
binlog_expire_logs_seconds      = 2592000
# 复制设置
relay_log_info_repository       = TABLE
master_info_repository          = TABLE
# 性能参数
transaction_isolation           = READ-COMMITTED
max-allowed-packet              = 512M
max-connections                 = 1000
max-connect-errors              = 1000000
innodb_buffer_pool_size         = 12G  # 建议设为系统总内存的 50%-60%
innodb_buffer_pool_chunk_size   = 1G
innodb_buffer_pool_instances    = 12
innodb_log_file_size            = 1G
innodb_log_files_in_group       = 6
innodb_data_file_path           = ibdata1:12M;ibdata2:12M:autoextend
innodb_temp_data_file_path      = ibtmp1:500M;ibtmp2:500M:autoextend:max:5120M
innodb_flush_method             = O_DIRECT
# 时区与日志
default-time-zone               = '+8:00'
log_timestamps                  = SYSTEM
default_authentication_plugin   = mysql_native_password
slow-query-log                  = 1
long_query_time                 = 1
# MGR 插件配置
plugin_load                      = 'group_replication.so'
group_replication_ip_whitelist     = "60.28.24.186,60.28.24.200,60.28.24.205,127.0.0.1/8"
group_replication_group_name    = 'ec9566ae-5bb7-11ea-b099-cb85388b10ae'
group_replication_start_on_boot   = off
group_replication_local_address   = "60.28.24.205:33071" # 修改为本机IP
group_replication_group_seeds    =  "60.28.24.186:33071,60.28.24.200:33071,60.28.24.205:33071"
group_replication_bootstrap_group = off

参数配置注意事项：

server_id：各节点不能重复。
report_host：填写本机 IP 地址。
group_replication_local_address：填写本机 IP 及 Group Replication 端口。
innodb_buffer_pool_size：根据操作系统总内存调整 (50%~60%)。

4.4 重启服务

systemctl restart mysql
systemctl status mysql

5. 在各节点创建集群管理用户

在每个节点上执行，创建用于复制的用户：

mysql> set sql_log_bin=0;
mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
mysql> GRANT all ON *.* TO 'repl'@'%' with grant option;
mysql> flush privileges;
mysql> set sql_log_bin=1;

6. 使用 MySQL Shell 进行集群搭建

6.1 安装 MySQL Shell

dpkg -i mysql-shell_8.0.38-1ubuntu22.04_amd64.deb

6.2 创建集群

连接到主节点：
```
mysqlsh --uri=repo@60.28.24.186:3307
```

配置实例并创建集群：

// 配置当前实例
MySQL  60.28.24.186:3307 ssl  JS > dba.configureInstance('repo@60.28.24.186:3307')

// 检查配置
MySQL  60.28.24.186:3307 ssl  JS > dba.checkInstanceConfiguration('repo@60.28.24.186:3307')

// 创建集群
MySQL  60.28.24.186:3307 ssl  JS > var rs = dba.createCluster('prodCluster')

// 添加其他节点
MySQL  60.28.24.186:3307 ssl  JS > rs.addInstance('ic@60.28.24.200:3307')
MySQL  60.28.24.186:3307 ssl  JS > rs.addInstance('ic@60.28.24.205:3307')

// 查看集群状态
MySQL  60.28.24.186:3307 ssl  JS > rs.status()

集群状态示例：

{
    "clusterName": "prodCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "60.28.24.186:3307", 
        "ssl": "REQUIRED", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "60.28.24.186:3307": {
                "address": "60.28.24.186:3307", 
                "memberRole": "PRIMARY", 
                "mode": "R/W", 
                "status": "ONLINE", 
                "version": "8.0.38"
            },
            "60.28.24.200:3307": {
                "address": "60.28.24.200:3307", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "status": "ONLINE", 
                "version": "8.0.38"
            },
            "60.28.24.205:3307": {
                "address": "60.28.24.205:3307", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "status": "ONLINE", 
                "version": "8.0.38"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "60.28.24.186:3307"
}

7. 数据库层面管理操作

7.1 查看成员状态

SELECT * FROM performance_schema.replication_group_members;

7.2 主节点启动复制组

仅在初次引导集群启动时，在主节点执行：

SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;

7.3 从节点启动复制组

从节点加入集群：

START GROUP_REPLICATION;

7.4 各节点查看 GTID（寻找主节点）

show master status;

7.5 关闭复制组

STOP GROUP_REPLICATION;

8. MySQL Router 安装

用于实现读写分离与高可用路由。

mysqlrouter --bootstrap repo@10.25.25.14:3307 --directory /data/vol/myrouter --conf-use-sockets --user=root