一、MySQL 主从同步简介
MYSQL主从同步是一种用于数据库复制的技术，它允许一个MYSQL数据服务器（主服务器）的数据变更复制到一个或者多个其他MYSQL数据服务器（从服务器）。
二、主从同步的工作原理
主要是基于MYSQL二进制日志
主要包括三个线程（2个I/O线程，1个SQL线程）
1.mysql将数据变化记录到二进制日志中
2.slave将MYSQL的二进制日志拷贝到slave的中继日志中
3.slave将中继日志的事件在做一次，将数据变化，反应到自身的（slave）的数据库
二进制日志（Binlog）
主服务器上的记录：主服务器在执行任何会修改数据的 SQL 语句（如 INSERT、UPDATE、DELETE）时，都会将这些操作记录到二进制日志（Binlog）中。Binlog是一种基于事件的日志，每个事件对应一个数据库操作。
格式类型：Binlog 有三种格式，分别是STATEMENT、ROW和MIXED。
STATEMENT 格式：记录执行的 SQL 语句。这种格式的优点是日志文件相对较小，因为相同类型的操作可以用一条 SQL 语句表示。但如果 SQL 语句中包含了不确定因素（如使用了 UUID () 函数、CURRENT_TIMESTAMP 等），可能会导致从服务器执行的结果与主服务器不一致。
ROW 格式：记录每一行数据的修改**。这种格式能更精确地记录数据的变化，保证主从数据的一致性，但日志文件会比较大，因为每个数据行的修改都会产生一个事件。
MIXED 格式：混合了 STATEMENT 和 ROW 两种格式**。MySQL 会根据具体的 SQL 语句自动选择合适的记录方式，例如对于简单的、确定的 SQL 语句采用 STATEMENT 格式，对于可能导致不一致的语句则采用 ROW 格式。
从服务器的复制过程
连接主服务器：从服务器首先会通过配置信息（如主服务器的 IP 地址、端口号、用户名和密码等）连接到主服务器。这个连接是通过一个专门的复制线程（I/O 线程）来实现的**。
请求 Binlog 信息：连接成功后，从服务器的 I/O 线程会向主服务器发送请求，获取主服务器的 Binlog 信息。主服务器会根据从服务器的请求位置（通常是通过记录已经读取到的 Binlog 文件名和位置来确定），将新的 Binlog 事件发送给从服务器。
本地中继日志（Relay Log）存储：从服务器接收到主服务器发送的 Binlog 事件后，会将这些事件存储到本地的中继日志（Relay Log）中**。Relay Log 的作用类似于一个中间缓存，用于存储从主服务器获取的 Binlog 事件，以便后续进行处理。
SQL 线程执行：从服务器有另一个线程（SQL 线程），它会读取 Relay Log 中的事件，并按照事件在 Relay Log 中的顺序依次执行这些事件**。执行的结果就是在从服务器上重现主服务器上的数据变更操作，从而实现主从数据的同步。
三、主从同步的优势
数据备份与灾难恢复
从服务器可以作为主服务器数据的实时备份。在主服务器发生故障（如硬件故障、软件崩溃等）时，可以快速将从服务器提升为新的主服务器，继续提供服务，从而减少数据丢失和业务中断的风险。
负载均衡
通过将读操作分配到多个从服务器上，可以减轻主服务器的读负载，提高整个系统的并发处理能力。例如，对于一个高流量的网站，大量的用户查询（读操作）可以由从服务器来处理，而主服务器主要负责处理写操作，这样可以提高系统的响应速度和性能。
数据分析和报表生成
可以将从服务器用于数据分析、报表生成等对数据实时性要求不是特别高的任务。这样可以避免这些复杂的查询操作对主服务器性能的影响，同时保证主服务器专注于处理核心业务的读写操作。
四.配置 MySQL 主从同步
主服务器配置
修改配置文件（my.cnf）：在主服务器上打开 MySQL 的配置文件（通常是 /etc/my.cnf 或 /etc/mysql/my.cnf），添加或修改以下配置项：
[mysqld] server - id = 1 log - bin = /var/log/mysql/mysql - bin.log binlog - format = ROW
server - id：是主服务器的唯一标识符，每个服务器的server - id都不能相同。log - bin：指定二进制日志文件的存储路径和文件名。binlog - format：选择二进制日志的格式，这里选择 ROW 格式以保证数据的准确性。重启主服务器：完成配置文件的修改后，需要重启主服务器使配置生效。
从服务器配置
修改配置文件（my.cnf）：在从服务器上打开 MySQL 的配置文件，添加或修改以下配置项：
[mysqld] server - id = 2 relay - log = /var/log/mysql/mysql - relay.log read - only = 1
server - id：设置从服务器的唯一标识符，不能与主服务器相同。relay - log：指定中继日志的存储路径和文件名。read - only：将从服务器设置为只读模式，这样可以防止在从服务器上意外修改数据，保证主从数据的一致性。不过，需要注意的是，具有 SUPER 权限的用户仍然可以在从服务器上进行写操作。重启从服务器：同样，修改配置文件后需要重启从服务器。
创建用于复制的用户
在主服务器上创建一个用于从服务器连接的用户，并授予 REPLICATION SLAVE 权限。可以使用以下 SQL 语句：
CREATE USER'repl_user'@'%' IDENTIFIED BY 'password'; GRANT REPLICATION SLAVE ON *.* TO'repl_user'@'%';
这里创建了一个名为
repl_user，密码为password的用户，并且允许该用户从任何主机（%表示任意主机）连接到主服务器进行复制操作。在实际应用中，应该根据安全策略限制从服务器的 IP 地址范围。
获取主服务器状态信息
在主服务器上执行以下 SQL 语句获取二进制日志文件名和位置信息：
SHOW MASTER STATUS;
这条语句会返回当前主服务器的二进制日志文件名（例如
mysql - bin.000001）和位置（例如120），从服务器需要这些信息来确定从哪里开始复制。
配置从服务器连接主服务器
在从服务器上执行以下 SQL 语句来配置复制连接：
CHANGE MASTER TO MASTER_HOST = '主服务器IP地址', MASTER_USER = 'repl_user', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = '主服务器二进制日志文件名', MASTER_LOG_POS = 主服务器二进制日志位置;
将上述语句中的
主服务器IP地址、repl_user、password、主服务器二进制日志文件名和主服务器二进制日志位置替换为实际的值。
启动从服务器复制
在从服务器上执行以下 SQL 语句启动复制线程：
START SLAVE;
可以通过执行
SHOW SLAVE STATUS\G来查看从服务器的复制状态，重点关注Slave_IO_Running和Slave_SQL_Running两个字段，这两个字段的值都为Yes表示主从同步正常运行。
MYSQL基于gtid的主从同步配置实验
实验目的--掌握如何在 MySQL 中配置基于 GTID（Global Transaction Identifier全局事务标识符）的主从复制
GTID--以 source_id:transaction_id 的格式唯一标识服务器上提交的事务，其在主从复制中通过为事务分配唯一标识并自动识别未执行事务，避免事务重复执行或遗漏问题。
二、实验环境
操作系统：rhel9
MySQL 版本：MySQL 8.0
主服务器 IP：192.168.65.131
从服务器1 IP：192.168.65.132
从服务器2 IP：192.168.65.133
1.安装配置MYSQL服务器
yum list | grep mysql
yum install mysql-server.x86_64
sudo systemctl start mysqld
ALTER USER 'root'@'localhost' IDENTIFIED BY 'your_new_password';
2.配置主服务器
-----修改主服务器配置文件my.cnf
/etc/my.cnf /etc/mysql/my.cnf中添加配置
[mysqld] server-id = 1 log-bin = mysql-bin gtid_mode = ON enforce_gtid_consistency = ON binlog_format = ROW
保存并退出配置文件，然后重启 MySQL 服务：
sudo systemctl restart mysqld
--------创建复制用户
登录到 MySQL 主服务器：
mysql -u root -p
在 MySQL 命令行中创建复制用户并授予复制权限：
CREATE USER 'repl'@'192.168.65.132' IDENTIFIED BY 'repl_password'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.65.132'; FLUSH PRIVILEGES; CREATE USER 'repl'@'192.168.65.133' IDENTIFIED BY 'repl_password'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.65.133'; FLUSH PRIVILEGES;
GRANT REPLICATION SLAVE 语句授予了从服务器用户rep1 从192.168.65.133 登录的复制权限，允许该用户从主服务器读取二进制日志（binlog），并将其应用在从从服务器上，实现数据同步
----获取主服务器的状态 SHOW MASTER STATUS;
记录下 File 和 Position 的值，在后续从服务器配置中会用到
3.配置从服务器
-----修改从服务器配置文件（my.cnf）
编辑从服务器的配置文件（通常位于 /etc/my.cnf 或 /etc/mysql/my.cnf），添加以下配置：
从服务器1
[mysqld] server-id = 2 gtid_mode = ON enforce_gtid_consistency = ON binlog_format = ROW
从服务器2
[mysqld] server-id = 3 gtid_mode = ON enforce_gtid_consistency = ON binlog_format = ROW
保存并退出配置文件，然后重启 MySQL 服务：
systemctl restart mysqld
配置从服务器复制
登录到从服务器的 MySQL：
mysql -u root -p
在 MySQL 命令行中配置从服务器复制，使用在主服务器上获取的 File 和 Position 信息：
CHANGE MASTER TO MASTER_HOST='192.168.65.131', MASTER_USER='repl', MASTER_PASSWORD='repl_password', MASTER_AUTO_POSITION = 1, get_master_public_key=1;
get_master_public_key=1 参数用于在主从服务器之间使用基于 RSA 密钥对的安全密码交换。如果你的 MySQL 配置启用了安全连接（例如 SSL/TLS），这个参数是很有用的
启动从服务器复制
start slave;
4.检查从服务器复制状态
show slave status\G
查看 Slave_IO_Running 和 Slave_SQL_Running 字段的值，它们应该都为 Yes，表示复制正常进行
4.测试主从同步
在主服务器的 MySQL 中插入一些数据：
CREATE DATABASE test_db;
USE test_db;
CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
department VARCHAR(50),
salary DECIMAL(10, 2)
);
INSERT INTO employees (name, department, salary) VALUES
('Alice', 'HR', 5000.00),
('Bob', 'IT', 6000.00),
('Charlie', 'Finance', 5500.00);
在从服务器上验证数据同步
登录到从服务器的 MySQL 并检查数据是否同步：
USE test_db; SELECT * FROM test_table;
MySQL 基于 Binlog 的主从同步实验
实验目的--- MySQL 基于二进制日志（Binlog）的主从同步机制，掌握主从服务器的配置方法，实现主服务器数据变更在从服务器上的实时同步
二、实验环境
操作系统：两台虚拟机，均安装 CentOS 7.9 64 位操作系统，分别作为主服务器（Master）和从服务器（Slave）。
MySQL 版本：MySQL 8.0.32，在两台虚拟机上均安装此版本，确保版本兼容性和功能一致性。
硬件配置：每台虚拟机配置 2 核 CPU、4GB 内存、20GB 硬盘空间，满足 MySQL 运行及数据存储需求。
三、实验准备
（一）关闭防火墙和 SELinux
在主服务器和从服务器上执行以下命令，关闭防火墙和 SELinux，避免其对 MySQL 主从同步的网络连接产生阻碍。
# 关闭防火墙 systemctl stop firewalld systemctl disable firewalld # 临时关闭SELinux setenforce 0 # 永久关闭SELinux，修改配置文件 sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
（二）安装 MySQL
在主服务器和从服务器上，通过官方 Yum 源安装 MySQL 8.0.32。
# 下载MySQL官方Yum源配置文件 wget https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm #安装Yum源配置文件 rpm -ivh mysql80-community-release-el7-1.noarch.rpm #安装mysql yum install mysql-community-server -y
(三）初始化 MySQL
安装完成后，在主服务器和从服务器上分别初始化 MySQL，并设置 root 用户密码。
# 初始化MySQL
mysqld --initialize --user=mysql
# 启动MySQL服务
systemctl start mysqld
# 获取初始密码
initial_password=$(grep 'temporary password' /var/log/mysqld.log | awk '{print $NF}')
# 修改root用户密码，设置为MyNewPassword123
mysqladmin -u root -p"$initial_password" password 'MyNewPassword123'
四、主服务器配置
（一）修改 MySQL 配置文件
打开主服务器的 MySQL 配置文件/etc/my.cnf，添加或修改以下配置项。
[mysqld] # 主服务器唯一ID，取值范围为1到2^32 - 1，确保与从服务器不同 server-id = 1 # 开启二进制日志，指定日志文件路径和基本文件名 log-bin=/var/log/mysql/mysql-bin
修改完成后，保存并退出文件。
（二）重启 MySQL 服务
使配置文件生效，执行以下命令。
systemctl restart mysqld
（三）创建用于复制的用户
登录主服务器的 MySQL，创建一个用于从服务器连接的用户，并授予其REPLICATION SLAVE权限。
CREATE USER 'replicator'@'%' IDENTIFIED BY 'your_password'; GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%'; FLUSH PRIVILEGES;
REPLICATION SLAVE权限：这是一种特殊的权限，允许用户进行复制操作。具体来说，授予 replicator 用户从主服务器复制数据的权限，这是进行主从复制的关键权限。
（四）获取主服务器状态信息
执行以下 SQL 语句，获取主服务器的二进制日志文件名和位置信息。
SHOW MASTER STATUS;
记录下File字段的值（如mysql-bin.000002）和Position字段的值（如868），后续从服务器配置时会用到。
五、从服务器配置
（一）修改 MySQL 配置文件
打开从服务器的 MySQL 配置文件/etc/my.cnf，添加或修改以下配置项。
[mysqld] # 从服务器唯一ID，不能与主服务器相同 server-id = 2
修改完成后，保存并退出文件。
（二）重启 MySQL 服务
执行以下命令，使配置文件生效。
systemctl restart mysqld
（三）配置从服务器连接主服务器
登录从服务器的 MySQL，执行以下 SQL 语句，配置从服务器连接主服务器的相关信息。
CHANGE MASTER TO MASTER_HOST = '主服务器IP地址', MASTER_USER ='replicator', MASTER_PASSWORD = 'you_password', MASTER_LOG_FILE = '主服务器二进制日志文件名', MASTER_LOG_POS = 主服务器二进制日志位置， get_master_public_key=1;
MASTER_HOST = '主服务器IP地址'：指定主服务器的 IP 地址，从服务器将通过该 IP 地址连接到主服务器。MASTER_USER ='replicator'：指定用于连接主服务器的用户名，该用户需要在主服务器上具有REPLICATION SLAVE权限，以便从服务器能够读取主服务器的二进制日志。MASTER_PASSWORD = 'you_password'：指定连接主服务器的用户密码，需要确保该密码与主服务器上设置的用户密码一致。MASTER_LOG_FILE = '主服务器二进制日志文件名'：指定主服务器上的二进制日志文件的名称，从服务器将从该日志文件开始读取数据。这个文件名可以通过在主服务器上执行SHOW MASTER STATUS;命令获取，它是主服务器正在使用的二进制日志文件的名称。MASTER_LOG_POS = 主服务器二进制日志位置：指定主服务器二进制日志文件中的位置，从服务器将从该位置开始读取数据。这个位置也可以通过SHOW MASTER STATUS;命令获取，它是主服务器正在使用的二进制日志文件的当前位置。get_master_public_key = 1：这是 MySQL 8.0 引入的一个选项，当使用基于 RSA 的密码交换时，设置该选项为 1 可以让从服务器在连接主服务器时请求主服务器的公钥，以提高安全性。
（四）启动从服务器复制
执行以下 SQL 语句，启动从服务器的复制线程。
START SLAVE;
（五）检查从服务器复制状态
执行以下 SQL 语句，查看从服务器的复制状态。
SHOW SLAVE STATUS \G;
重点关注Slave_IO_Running和Slave_SQL_Running两个字段，若它们的值均为Yes，且Seconds_Behind_Master的值为 0 或接近 0，则表示主从同步正常运行。同时，确保Last_IO_Error和Last_SQL_Error字段均为空，若不为空，需根据错误提示排查问题。
（一）在主服务器上创建测试数据库和表
登录主服务器的 MySQL，执行以下 SQL 语句，创建一个测试数据库test_db和一张测试表test_table，并插入一些测试数据。
CREATE DATABASE test_db;
USE test_db;
CREATE TABLE test_table (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
age INT
);
INSERT INTO test_table (name, age) VALUES ('Alice', 25), ('Bob', 30);
（二）在从服务器上验证数据同步
登录从服务器的 MySQL，执行以下 SQL 语句，查看test_db数据库和test_table表是否存在，以及数据是否同步。
USE test_db; SELECT * FROM test_table;
若能正确查询到在主服务器上插入的数据，则表示主从同步成功。
MySQL 主从复制的高级应用，包括多源复制、半同步复制、级联复制、过滤复制、并行复制和组复制。
二、多源复制
多源复制允许一个从服务器同时从多个主服务器接收事务，将多个数据源的数据合并到一个实例中，方便数据汇总、报表生成、数据仓库和数据迁移等操作。
配置多个主服务器
确保每个主服务器都已开启二进制日志，并设置唯一的 server-id。
配置从服务器
在从服务器上，使用 CHANGE MASTER TO 语句添加多个复制通道，每个通道连接一个主服务器。例如：
在从服务器上，使用
CHANGE MASTER TO语句添加多个复制通道，每个通道连接一个主服务器。例如：
-- 第一个复制通道，连接主服务器 1 CHANGE MASTER TO MASTER_HOST='主服务器 1 的 IP', MASTER_USER='复制用户 1', MASTER_PASSWORD='复制用户 1 的密码', MASTER_PORT=主服务器 1 的端口, MASTER_AUTO_POSITION = 1, FOR CHANNEL 'channel_1'; -- 第二个复制通道，连接主服务器 2 CHANGE MASTER TO MASTER_HOST='主服务器 2 的 IP', MASTER_USER='复制用户 2', MASTER_PASSWORD='复制用户 2 的密码', MASTER_PORT=主服务器 2 的端口, MASTER_AUTO_POSITION = 1, FOR CHANNEL 'channel_2';
启动复制通道
使用以下命令启动每个复制通道：
START SLAVE FOR CHANNEL 'channel_1'; START SLAVE FOR CHANNEL 'channel_2';
三、半同步复制
半同步复制在异步复制的基础上，确保至少有一个从服务器接收到事务并写入中继日志后，主服务器的事务才会提交
1.用户线程写入完成后master中的dump会把日志推送到slave端
2.slave中的io线程接收后保存到relaylog中继日志
3.保存完成后slave向master端返回ack
4.在未接受到slave的ack时master端时不做提交的，一直处于等待当收到ack后提交到存储引擎
5.在5.6版本中用到的时after_commit模式，after_commit模式时先提交在等待ack返回后输出ok
配置步骤
在主服务器和从服务器上分别安装半同步复制插件：
-- 主服务器 INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; -- 从服务器 INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
启用插件
在主服务器上启用半同步复制插件：
SET GLOBAL rpl_semi_sync_master_enabled = 1;
在从服务器上启用半同步复制插件：
SET GLOBAL rpl_semi_sync_slave_enabled = 1;
可以根据需要调整一些半同步复制的参数，例如设置等待从服务器确认的超时时间：
SET GLOBAL rpl_semi_sync_master_timeout = 10000; -- 超时时间为 10 秒
（三）监控和管理
可以使用以下命令查看半同步复制的状态：
SHOW STATUS LIKE 'Rpl_semi_sync_master_status'; SHOW STATUS LIKE 'Rpl_semi_sync_slave_status';
四、级联复制
（一）概念
级联复制是一种复制拓扑结构，从服务器可以作为另一个从服务器的主服务器，形成级联结构，减轻主服务器的复制负载。
（二）配置步骤
配置基础的主从复制
首先配置一个标准的主从复制，确保主从服务器之间可以正常复制数据。
配置二级从服务器
在二级从服务器上，将其配置为从一级从服务器复制数据，而不是直接从主服务器复制：
CHANGE MASTER TO MASTER_HOST='一级从服务器的 IP', MASTER_USER='复制用户', MASTER_PASSWORD='复制用户的密码', MASTER_AUTO_POSITION = 1;
启动复制
在二级从服务器上启动复制：
START SLAVE;
（三）监控和管理
使用 SHOW SLAVE STATUS\G 命令查看二级从服务器的复制状态
五、过滤复制
（一）概念
过滤复制允许从服务器选择性地复制部分数据，可以通过配置参数来复制或忽略某些数据库或表。
（二）配置步骤
配置主从复制
先建立基本的主从复制架构。
配置过滤规则
在从服务器的配置文件（my.cnf）中添加以下参数：
-- 只复制指定的数据库 replicate-do-db = your_database_name -- 忽略指定的数据库 replicate-ignore-db = another_database_name -- 只复制指定的表 replicate-do-table = your_database_name.your_table_name -- 忽略指定的表 replicate-ignore-table = your_database_name.another_table_name
重启从服务器
修改配置文件后，需要重启从服务器以使过滤规则生效：
sudo systemctl restart mysql
（三）监控和管理
使用 SHOW SLAVE STATUS\G 查看复制状态，确保过滤规则正常工作。
六、并行复制
（一）概念
默认情况下slave中使用的是sql单线程回放 ，在master中时多用户读写，如果使用sql单线程回放那么会造成组从延迟严重 。并行复制允许从服务器使用*多个 SQL 执行线程并行执行*复制的事务，提高复制性能，特别是在高并发环境中。
（二）配置步骤
MySQL 5.7 及以上版本
在 MySQL 5.7 及以上版本中，可以使用基于逻辑时钟或组提交的并行复制。在配置文件（my.cnf）中添加以下配置：
[mysqld] slave-parallel-type = LOGICAL_CLOCK slave-parallel-workers = 4 -- 设置并行执行的线程数，可根据实际情况调整
重启从服务器
修改配置文件后，需要重启从服务器以使并行复制配置生效：
sudo systemctl restart mysql
（三）监控和管理
使用 SHOW SLAVE STATUS\G 查看复制状态，观察 Slave_parallel_workers 和 Slave_parallel_type 等字段，确保并行复制正常工作。
七、组复制
（一）概念
多个服务器组成一个复制组，组内的服务器可以同时处理读写操作，通过基于 Paxos 协议的一致性算法保证数据一致性。
（二）配置步骤
在组内的每个服务器上安装组复制插件：
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
配置组复制
在每个服务器上进行组复制的参数设置，例如：
SET GLOBAL group_replication_group_name = 'group_name'; SET GLOBAL group_replication_start_on_boot = OFF; SET GLOBAL group_replication_local_address = '服务器 IP:端口'; SET GLOBAL group_replication_group_seeds = '组内服务器 IP 列表';
启动组复制
在每个服务器上启动组复制：
START GROUP_REPLICATION;
（三）监控和管理
使用以下命令查看组复制的状态：
SHOW STATUS LIKE 'group_replication%'; SELECT * FROM performance_schema.replication_group_members;