MySQL 8.0 GTID半同步主从架构部署实践

1. MySQL 8.0 GTID半同步主从架构部署指南在数据库高可用架构设计中主从复制是最基础也是最关键的环节。今天我将分享在Ubuntu 25.10系统上通过二进制方式安装MySQL 8.0并配置GTID半同步主从复制的完整过程。这个方案特别适合需要保证数据零丢失的生产环境我在多个金融级项目中验证过其可靠性。先看下我们的目标架构1个主节点Masterdb101 (10.0.0.101)2个从节点Replicadb102 (10.0.0.102) 和 db103 (10.0.0.103)全部采用GTID复制模式启用增强半同步复制AFTER_SYNC模式2. 环境准备与基础配置2.1 系统环境检查首先确认三台服务器的基本配置# 检查CPU和内存 lscpu free -h # 检查磁盘空间 df -h /data建议生产环境至少满足4核CPU8GB内存/data目录50GB以上空间2.2 系统参数优化在Ubuntu 25.10上需要调整的关键参数# 禁用THP透明大页 echo never /sys/kernel/mm/transparent_hugepage/enabled echo never /sys/kernel/mm/transparent_hugepage/defrag # 调整文件描述符限制 echo * soft nofile 65535 /etc/security/limits.conf echo * hard nofile 65535 /etc/security/limits.conf # 关闭swap生产环境建议 swapoff -a sed -i /swap/s/^/#/ /etc/fstab注意这些优化需要在所有节点上执行特别是关闭THP对MySQL性能影响很大。2.3 创建专用用户和目录为MySQL创建专用用户和目录结构# 创建mysql用户禁止登录 useradd -r -s /usr/sbin/nologin mysql # 创建数据目录 mkdir -p /data/3306/data chown -R mysql:mysql /data/33063. MySQL 8.0二进制安装3.1 下载与解压从MySQL官网下载8.0.36版本的二进制包wget https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.36-linux-glibc2.12-x86_64.tar.xz tar xvf mysql-8.0.36-linux-glibc2.12-x86_64.tar.xz -C /usr/local/ ln -s /usr/local/mysql-8.0.36-linux-glibc2.12-x86_64 /usr/local/mysql3.2 解决依赖问题常见依赖问题解决方案# 解决libaio依赖 ln -s /lib/x86_64-linux-gnu/libaio.so.1t64 /lib/x86_64-linux-gnu/libaio.so.1 # 解决ncurses依赖 ln -s /lib/x86_64-linux-gnu/libncurses.so.6 /lib/x86_64-linux-gnu/libncurses.so.5 ln -s /lib/x86_64-linux-gnu/libtinfo.so.6 /lib/x86_64-linux-gnu/libtinfo.so.5 ldconfig3.3 初始化数据库采用不安全模式初始化生产环境不建议/usr/local/mysql/bin/mysqld --initialize-insecure \ --usermysql \ --datadir/data/3306/data \ --basedir/usr/local/mysql安全初始化方式推荐生产使用/usr/local/mysql/bin/mysqld --initialize \ --usermysql \ --datadir/data/3306/data \ --basedir/usr/local/mysql初始化后会生成临时密码记录在error log中。4. 配置MySQL服务4.1 基础配置文件创建/etc/my.cnf基础配置[mysqld] usermysql basedir/usr/local/mysql datadir/data/3306/data socket/tmp/mysql.sock log-error/data/3306/mysql-error.log pid-file/data/3306/mysql.pid # 性能相关 innodb_buffer_pool_size2G innodb_log_file_size1G max_connections5004.2 服务管理配置配置systemd服务cat /usr/lib/systemd/system/mysqld.service EOF [Unit] DescriptionMySQL Server Afternetwork.target [Service] Usermysql Groupmysql ExecStart/usr/local/mysql/bin/mysqld --defaults-file/etc/my.cnf LimitNOFILE65535 Restarton-failure RestartSec5 [Install] WantedBymulti-user.target EOF启动MySQL服务systemctl daemon-reload systemctl enable --now mysqld systemctl status mysqld4.3 设置root密码mysqladmin -uroot password YourSecurePassword123!5. GTID半同步主从配置5.1 主节点配置编辑主节点db101的my.cnf[mysqld] server_id101 log_binmysql-bin binlog_formatROW binlog_row_imageFULL sync_binlog1 gtid_modeON enforce_gtid_consistencyON log_slave_updatesON # 半同步配置 plugin-loadrpl_semi_sync_sourcesemisync_source.so rpl_semi_sync_source_enabled1 rpl_semi_sync_source_timeout1000 rpl_semi_sync_source_wait_for_replica_count1 rpl_semi_sync_source_wait_pointAFTER_SYNC5.2 从节点配置从节点db102/db103配置[mysqld] server_id102 # db103改为103 log_binmysql-bin binlog_formatROW gtid_modeON enforce_gtid_consistencyON log_slave_updatesON # 半同步配置 plugin-loadrpl_semi_sync_replicasemisync_replica.so rpl_semi_sync_replica_enabled15.3 创建复制账号在主节点执行CREATE USER repl% IDENTIFIED BY ComplexPassword123!; GRANT REPLICATION SLAVE ON *.* TO repl%; ALTER USER repl% REQUIRE SSL; # 生产环境建议启用SSL5.4 配置主从关系在每个从节点执行CHANGE REPLICATION SOURCE TO SOURCE_HOST10.0.0.101, SOURCE_PORT3306, SOURCE_USERrepl, SOURCE_PASSWORDComplexPassword123!, SOURCE_AUTO_POSITION1, GET_SOURCE_PUBLIC_KEY1; START REPLICA;6. 验证与监控6.1 复制状态检查在主节点检查SHOW STATUS LIKE Rpl_semi_sync%; --------------------------------------------------- | Variable_name | Value | --------------------------------------------------- | Rpl_semi_sync_source_clients | 2 | | Rpl_semi_sync_source_net_avg_wait_time | 0 | | Rpl_semi_sync_source_net_wait_time | 0 | | Rpl_semi_sync_source_net_waits | 5 | | Rpl_semi_sync_source_no_times | 1 | | Rpl_semi_sync_source_no_tx | 0 | | Rpl_semi_sync_source_status | ON | | Rpl_semi_sync_source_timefunc_failures | 0 | | Rpl_semi_sync_source_tx_avg_wait_time | 512 | | Rpl_semi_sync_source_tx_wait_time | 2563 | | Rpl_semi_sync_source_tx_waits | 5 | | Rpl_semi_sync_source_wait_pos_backtraverse | 0 | | Rpl_semi_sync_source_wait_sessions | 0 | | Rpl_semi_sync_source_yes_tx | 5 | ---------------------------------------------------在从节点检查SHOW REPLICA STATUS\G *************************** 1. row *************************** Replica_IO_State: Waiting for source to send event Source_Host: 10.0.0.101 Source_User: repl Source_Port: 3306 Connect_Retry: 60 Source_Log_File: mysql-bin.000002 Read_Source_Log_Pos: 196 Relay_Log_File: db102-relay-bin.000002 Relay_Log_Pos: 409 Relay_Source_Log_File: mysql-bin.000002 Replica_IO_Running: Yes Replica_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Source_Log_Pos: 196 Relay_Log_Space: 623 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Source_SSL_Allowed: No Source_SSL_CA_File: Source_SSL_CA_Path: Source_SSL_Cert: Source_SSL_Cipher: Source_SSL_Key: Seconds_Behind_Source: 0 Source_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Source_Server_Id: 101 Source_UUID: 444c6e4f-ef8a-11f0-8bdc-000c29012c7f Source_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates Source_Retry_Count: 86400 Source_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Source_SSL_Crl: Source_SSL_Crlpath: Retrieved_Gtid_Set: 444c6e4f-ef8a-11f0-8bdc-000c29012c7f:1-5 Executed_Gtid_Set: 444c6e4f-ef8a-11f0-8bdc-000c29012c7f:1-5 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Source_TLS_Version: Source_public_key_path: Get_source_public_key: 1 Network_Namespace:6.2 半同步功能测试在两个从节点停止IO线程STOP REPLICA IO_THREAD;在主节点执行DDL操作CREATE DATABASE sync_test;此时操作会挂起约1秒配置的超时时间然后自动降级为异步复制。恢复从节点IO线程START REPLICA IO_THREAD;7. 关键注意事项性能影响半同步复制会增加约30%的写入延迟网络质量对性能影响很大建议主从节点在同一个机房监控Rpl_semi_sync_source_tx_avg_wait_time指标故障处理当从库宕机超过超时时间主库会自动降级为异步复制从库恢复后需要手动执行START REPLICA重新建立连接定期检查Last_IO_Error和Last_SQL_Error监控建议# 监控半同步状态 SELECT * FROM performance_schema.replication_group_member_stats; # 监控复制延迟 SHOW REPLICA STATUS\G # 监控GTID执行情况 SHOW MASTER STATUS; SHOW SLAVE STATUS;生产环境优化建议设置sync_binlog1和innodb_flush_log_at_trx_commit1调整rpl_semi_sync_source_timeout根据业务需求通常500-2000ms考虑使用MySQL Router实现自动故障转移这套架构我在多个生产环境部署过最长的已经稳定运行3年多。关键是要做好监控和定期演练确保故障时能快速恢复。对于金融级应用建议再配合MGR使用实现自动故障转移。