SQL 基础及 MySQL DBA 运维实战 – 5：MySQL 集群搭建（M-S/GTID/ 双主双从）
|
10
|
Linux基础,MySQL

1812 字
|
9 分钟

引言

在当今高并发、高可用的互联网应用场景下，MySQL数据库的集群技术已成为DBA必备的核心技能。本文将基于MySQL复制技术，详细介绍集群的搭建、配置与实战应用，帮助DBA和开发人员掌握MySQL集群的关键技术点。

MySQL集群技术概述

集群的核心目的

MySQL集群技术主要解决以下三个核心问题：

  • 负载均衡：通过多节点分担访问压力，解决高并发场景下的性能瓶颈
  • 高可用（HA）：确保服务持续可用，避免单点故障
  • 远程灾备：通过数据复制，保证数据的安全性和有效性
  • 图示：

复制技术原理

MySQL复制技术的工作原理包含三个关键步骤：

  1. 二进制日志记录：主库将数据更改（DDL、DML、DCL）记录到二进制日志（Binary Log）
  2. 中继日志复制：备库I/O线程将主库的二进制日志复制到自己的中继日志（Relay Log）
  3. 日志重放：备库SQL线程读取中继日志中的事件，将其重放到备库数据库
  4. 图示1：
  5. 图示2：
    image-20260310100713074

集群类型

根据架构不同，MySQL集群主要分为以下几种类型：

  • M：单实例（非集群）
  • M-S：一主一从
  • **M-S-S…**：主从从（级联复制）
  • M-M：双主（互为主从）
  • M-M-S-S：双主双从（高可用架构）

集群实战案例

环境准备

在开始搭建集群前，需要准备以下环境：

  1. 全新服务器：多台服务器，确保互相通信
  2. MySQL安装：逐一安装MySQL 5.7
  3. 域名解析：配置主机名与IP的映射关系（可使用host文件或DNS服务器）

我准备的环境如下，共四台虚拟机分别为：

  • master1：192.168.88.25
  • master2：192.168.88.26
  • slave1：192.168.88.27
  • slave2：192.168.88.28

且已经安装完成数据库、配置密码、域名解析等操作（我的数据库密码分别为Abc@+IP重复后两位，例如master1：Abc@2525）

一主一从（M-S）搭建

主库配置（master1）

  1. 准备测试数据：


    create database master1db;
    create table master1db.master1tab(name char(50));
    insert into master1db.master1tab values (1111);
    insert into master1db.master1tab values (2222);

  2. 开启二进制日志：


    vim /etc/my.cnf
    # 添加以下配置
    log_bin
    server-id=1
    # 重启服务
    systemctl restart mysqld

  3. 创建复制用户：


    grant replication slave, replication client on *.* to 'rep'@'192.168.88.%' identified by 'Abc@666666';

  4. 备份数据：


    mysqldump -p'Abc@2525' --all-databases --single-transaction --master-data=2 --flush-logs > `date +%F`-mysql-all.sql
    # 发送给从库
    scp -r 2026-03-10-mysql-all.sql  master2:/tmp

  5. 查看记录二进制日志位置：


    vim 2026-03-10-mysql-all.sql
    # 观察二进制日志分割点
    -- CHANGE MASTER TO MASTER_LOG_FILE='master1-bin.000002', MASTER_LOG_POS=154;

从库配置（slave1）

  1. 测试复制用户：


    mysql -h master1 -urep -p'Abc@666666'

    预防账户问题，如果可以正常登录且看到 information_schema 库说明该用户可以正常使用


  2. 配置server-id：


    vim /etc/my.cnf
    # 添加以下配置（服务器ID是必须设置的。）
    server-id=2
    # 重启服务
    systemctl restart mysqld

    # 测试服务器是否修改正确。能否正常登陆。
    mysql -uroot -p'Abc@2727'

    不用在从设备上开启二进制日志，因为没有人会向slave1请求日志


  3. 手动同步数据（登录数据库后操作）：


    set sql_log_bin=0;
    source /tmp/source /tmp/2026-03-10-mysql-all.sql

  4. 设置主服务器：

    注意，二进制日志的位置，应该参照主服务器备份时生成的新位置。

     change master to
    master_host='master1',
    master_user='rep',
    master_password='Abc@666666',
    master_log_file='master1-bin.000002',
    master_log_pos=154;

    • master_host=’master1′
      作用：指定主数据库（Master）的 IP 地址或主机名。
      说明：从库（Slave）通过此地址与主库建立 TCP/IP 连接。


    • master_user=’rep’


    • 作用：指定用于主从复制的数据库账号。

      说明：

      • 该账号需在主库上创建，并授予 REPLICATION SLAVE 权限。
      • 权限示例：GRANT REPLICATION SLAVE ON *.* TO 'rep'@'%' IDENTIFIED BY '密码';

    • master_password=’Abc@2525′
      作用：指定复制账号的密码。


    • master_log_file=’localhost-bin.000002′


    • 作用：指定从库开始复制的 Binlog 文件名。

      说明：

      • Binlog（二进制日志）是主库记录数据变更的文件，从库通过读取并重放 Binlog 实现数据同步。
      • 该值通常来自主库的 SHOW MASTER STATUS; 输出，或全量备份文件（如 mysqldump --master-data=2 生成的注释）。

    • master_log_pos=154

      作用：指定从库开始复制的 Binlog 位置偏移量（Position）。

      说明：

      • 与 master_log_file 配合使用，精确告诉从库从 Binlog 文件的哪个字节位置开始读取。
      • 该值同样来自 SHOW MASTER STATUS; 或备份文件，确保从库能接上主库的增量数据。

  5. 启动从设备：


    start slave;

  6. 查看同步状态：


    show slave status\G
       
    --输出示例
    mysql> show slave status\G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: master1
                      Master_User: rep
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: master1-bin.000002
              Read_Master_Log_Pos: 154
                   Relay_Log_File: slave1-relay-bin.000002
                    Relay_Log_Pos: 322
            Relay_Master_Log_File: master1-bin.000002
                 Slave_IO_Running: Yes
                Slave_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_Master_Log_Pos: 154
                  Relay_Log_Space: 530
                  Until_Condition: None
                   Until_Log_File: 
                    Until_Log_Pos: 0
               Master_SSL_Allowed: No
               Master_SSL_CA_File: 
               Master_SSL_CA_Path: 
                  Master_SSL_Cert: 
                Master_SSL_Cipher: 
                   Master_SSL_Key: 
            Seconds_Behind_Master: 0
    Master_SSL_Verify_Server_Cert: No
                    Last_IO_Errno: 0
                    Last_IO_Error: 
                   Last_SQL_Errno: 0
                   Last_SQL_Error: 
      Replicate_Ignore_Server_Ids: 
                 Master_Server_Id: 1
                      Master_UUID: 600f2157-1c29-11f1-b5c5-000c298ac06c
                 Master_Info_File: /var/lib/mysql/master.info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
               Master_Retry_Count: 86400
                      Master_Bind: 
          Last_IO_Error_Timestamp: 
         Last_SQL_Error_Timestamp: 
                   Master_SSL_Crl: 
               Master_SSL_Crlpath: 
               Retrieved_Gtid_Set: 
                Executed_Gtid_Set: 
                    Auto_Position: 0
             Replicate_Rewrite_DB: 
                     Channel_Name: 
               Master_TLS_Version:

    确保 Slave_IO_Running 和 Slave_SQL_Running 都为 Yes。


验证配置

返回master1更新数据，观察slave1是否同步

使用GTID模式的一主一从

GTID（全局事务ID）模式可以自动记录position位置，无需手动指定，简化了主从复制的配置。

这里我恢复master1和slave1的快照恢复到修改数据库密码完成

主库配置（master1）

  1. 修改配置文件：


    vim /etc/my.cnf
    # 添加以下配置
    log_bin
    server-id=1
    gtid_mode=ON
    enforce_gtid_consistency=1
    # 重启服务
    systemctl restart mysqld

  2. 授权复制用户：


    grant replication slave, replication client on *.* to 'rep'@'192.168.88.%' identified by 'Abc@666666';
    flush privileges;

  3. 准备数据并插入数据


    create database master1db;
    create table master1db.master1tab(name char(50));
    insert into master1db.master1tab values (123);
    insert into master1db.master1tab values (456);

  4. 备份并发送数据


    mysqldump -p'Abc@2525' --all-databases --single-transaction --master-data=2 --flush-logs > `date +%F-%H`-mysql-all.sql

    scp 2026-03-10-14-mysql-all.sql  slave1:/tmp/

  5. 模拟数据变化


    mysql> insert into master1db.master1tab values (6666666666);

从库配置（slave1）

  1. 修改配置文件：


    vim /etc/my.cnf
    # 添加以下配置
    log_bin
    server-id=2
    gtid_mode=ON
    enforce_gtid_consistency=1
    # 重启服务
    systemctl restart mysqld

  2. 测试rep用户


    mysql -h master1 -urep -p'Abc@666666'

  3. 设置主服务器（使用GTID）：


    change master to
    master_host='master1',
    master_user='rep',
    master_password='Abc@666666',
    master_auto_position=1;

    和前一个实验对比少了两行


  4. 登录数据库恢复手动同步数据


    set sql_log_bin=0;
    source /tmp/2026-03-10-14-mysql-all.sql

  5. 启动从设备并查看状态


    start slave;

    show slave status\G;

验证配置

可以直观的看到该模式配置方式的数据也会自动的同步更新

双主双从（MM-SS）搭建

前面的实验，主服务器单节点设置。假如主服务器故障会影响全局的写入事件。

双主双从架构提供了更高的可用性和负载能力。

主库配置（master1和master2）

  1. master1****修改配置文件：


    vim /etc/my.cnf
    # 添加以下配置
    log_bin
    server-id=1
    gtid_mode=ON
    enforce_gtid_consistency=1
    # 重启服务
    systemctl restart mysqld

  2. master2****修改配置文件：


    vim /etc/my.cnf
    # 添加以下配置
    log_bin
    server-id=2
    gtid_mode=ON
    enforce_gtid_consistency=1
    # 重启服务
    systemctl restart mysqld

  3. master1授权复制用户：


    grant replication slave, replication client on *.* to 'rep'@'192.168.88.%' identified by 'Abc@666666';
    flush privileges;

  4. 在master2上授权rep用户


    grant replication slave, replication client on *.* to 'rep'@'192.168.2.%' identified by 'QianFeng@123';
    flush privileges;

双主配置

  1. master1和2互相测试rep用户


    mysql -h master1 -urep -p'Abc@666666'

    mysql -h master2 -urep -p'Abc@666666'

  2. master2设置主服务器


    change master to
    master_host='master1',
    master_user='rep',
    master_password='Abc@666666',
    master_auto_position=1;

  3. master1设置主服务器：


    change master to
    master_host='master2',
    master_user='rep',
    master_password='Abc@666666',
    master_auto_position=1;

  4. 在master1和2 上启动从设备


    start slave;

    show slave status\G

  5. 执行 SHOW SLAVE STATUS\G 后，重点检查：

    • Slave_SQL_Running: Yes
    • Slave_IO_Running: Yes
    • Seconds_Behind_Master: 0
    • Last_Error 为空
    • 如果有不同步的问题可以尝试重启master2的mysqld服务或者start slave; stop slave;
    • 重启mysqld服务后如果登录密码报错，说明密码也和其中一个节点的同步了，属于正常现象。

  6. 测试双主同步：

    • 在master1上更新数据，在master2上观察
    • 在master2上更新数据，在master1上观察

双从配置（slave1和slave2）

  1. 同步现有数据库：


    # 在master1上备份
    mysqldump -p'Abc@2525' --all-databases --single-transaction --master-data=2 --flush-logs > `date +%F`-mysql-all.sql
    # 发送给从库
    scp -r 2026-03-10-mysql-all.sql slave1:/tmp/
    scp -r 2026-03-10-mysql-all.sql slave2:/tmp/
       
    # 在从库slave1上恢复
    mysql -p'Abc@2727' < /tmp/2026-03-10-mysql-all.sql

    # 在从库slave1上恢复
    mysql -p'Abc@2828' < /tmp/2026-03-10-mysql-all.sql

  2. 配置从库：


    # slave1配置
    vim /etc/my.cnf
    server-id=3
    gtid_mode=ON
    enforce_gtid_consistency=1
    master-info-repository=TABLE
    relay-log-info-repository=TABLE
    systemctl restart mysqld
       
    # slave2配置
    vim /etc/my.cnf
    server-id=4
    gtid_mode=ON
    enforce_gtid_consistency=1
    master-info-repository=TABLE
    relay-log-info-repository=TABLE
    systemctl restart mysqld

    从机把主的信息存在主信息仓库里。主信息库可以是文件也可以上表，具体由—master-info-repository参数值决定。


    —master-info-repository＝file时 会生成master.info 和 relay-log.info2个文件。


    如果—master-info-repository＝table，信息就会存在mysql.master_slave_info表中。


  3. 设置主服务器：


    -- slave1设置两个主服务器
    change master to
    master_host='master1',
    master_user='rep',
    master_password='Abc@666666',
    master_auto_position=1 for channel 'master1';
       
    change master to
    master_host='master2',
    master_user='rep',
    master_password='Abc@666666',
    master_auto_position=1 for channel 'master2';

    start slave;

    -- slave2设置两个主服务器（同上）

  4. 观察配置状态


    -- 输出示例
    mysql> show slave status\G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: master1
                      Master_User: rep
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: master1-bin.000002
              Read_Master_Log_Pos: 194
                   Relay_Log_File: slave1-relay-bin-master1.000002
                    Relay_Log_Pos: 371
            Relay_Master_Log_File: master1-bin.000002
                 Slave_IO_Running: Yes
                Slave_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_Master_Log_Pos: 194
                  Relay_Log_Space: 587
                  Until_Condition: None
                   Until_Log_File: 
                    Until_Log_Pos: 0
               Master_SSL_Allowed: No
               Master_SSL_CA_File: 
               Master_SSL_CA_Path: 
                  Master_SSL_Cert: 
                Master_SSL_Cipher: 
                   Master_SSL_Key: 
            Seconds_Behind_Master: 0
    Master_SSL_Verify_Server_Cert: No
                    Last_IO_Errno: 0
                    Last_IO_Error: 
                   Last_SQL_Errno: 0
                   Last_SQL_Error: 
      Replicate_Ignore_Server_Ids: 
                 Master_Server_Id: 1
                      Master_UUID: 600f2157-1c29-11f1-b5c5-000c298ac06c
                 Master_Info_File: mysql.slave_master_info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
               Master_Retry_Count: 86400
                      Master_Bind: 
          Last_IO_Error_Timestamp: 
         Last_SQL_Error_Timestamp: 
                   Master_SSL_Crl: 
               Master_SSL_Crlpath: 
               Retrieved_Gtid_Set: 
                Executed_Gtid_Set: 4f064e3e-1c29-11f1-b369-000c29063ff7:1-7,
    600f2157-1c29-11f1-b5c5-000c298ac06c:1-3
                    Auto_Position: 1
             Replicate_Rewrite_DB: 
                     Channel_Name: master1
               Master_TLS_Version: 
    *************************** 2. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: master2
                      Master_User: rep
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: master2-bin.000001
              Read_Master_Log_Pos: 1781
                   Relay_Log_File: slave1-relay-bin-master2.000002
                    Relay_Log_Pos: 420
            Relay_Master_Log_File: master2-bin.000001
                 Slave_IO_Running: Yes
                Slave_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_Master_Log_Pos: 1781
                  Relay_Log_Space: 636
                  Until_Condition: None
                   Until_Log_File: 
                    Until_Log_Pos: 0
               Master_SSL_Allowed: No
               Master_SSL_CA_File: 
               Master_SSL_CA_Path: 
                  Master_SSL_Cert: 
                Master_SSL_Cipher: 
                   Master_SSL_Key: 
            Seconds_Behind_Master: 0
    Master_SSL_Verify_Server_Cert: No
                    Last_IO_Errno: 0
                    Last_IO_Error: 
                   Last_SQL_Errno: 0
                   Last_SQL_Error: 
      Replicate_Ignore_Server_Ids: 
                 Master_Server_Id: 2
                      Master_UUID: 4f064e3e-1c29-11f1-b369-000c29063ff7
                 Master_Info_File: mysql.slave_master_info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
               Master_Retry_Count: 86400
                      Master_Bind: 
          Last_IO_Error_Timestamp: 
         Last_SQL_Error_Timestamp: 
                   Master_SSL_Crl: 
               Master_SSL_Crlpath: 
               Retrieved_Gtid_Set: 
                Executed_Gtid_Set: 4f064e3e-1c29-11f1-b369-000c29063ff7:1-7,
    600f2157-1c29-11f1-b5c5-000c298ac06c:1-3
                    Auto_Position: 1
             Replicate_Rewrite_DB: 
                     Channel_Name: master2
               Master_TLS_Version: 
    2 rows in set (0.00 sec)

验证配置

如下图，分别在master1和2上更新数据观察四个主机上的表是否同步更新

LinuxMySQL
暂无评论

发送评论 编辑评论 


				

			

						

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
							
													

					

				

			

			
			

				

					
				

			

				

											

							
							
						

																					

							
							
						

										
					
											
						

	

		
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
（╯‵□′）╯︵┴─┴
￣﹃￣
(/ω＼)
∠( ᐛ 」∠)＿
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ｀)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ(￣∇￣o)
ヾ(´･ ･｀｡)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò｡)
Σ(っ °Д °;)っ
( ,,´･ω･)ﾉ"(´っω･｀｡)
╮(╯▽╰)╭
o(*////▽////*)q
＞﹏＜
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
	

	

		
颜文字
Emoji
小恐龙
花!
	


									

			

			
			
		

	






上一篇

                    

                    
			        推荐文章
		            

		            

							
SQL 基础及 MySQL DBA 运维实战 – 4：MySQL 备份与恢复全实战（XtraBackup和mysqldump）

							
							

							
SQL基础及MySQL DBA运维实战-3： DCL 权限管理 + 日志管理实战教程（DBA 必备）

							
							

							
MySQL多表查询

							
							

							
SQL基础及MySQL DBA运维实战-2：DML增删改与DQL查询全解析