mysql的主从同步

主从同步使得数据可以从一个数据库服务器复制到其他服务器上，在复制数据时，一个服务器充当主服务器（master），其余的服务器充当从服务器（slave）。因为复制是异步进行的，所以从服务器不需要一直连接着主服务器，从服务器甚至可以通过拨号断断续续地连接主服务器。通过配置文件，可以指定复制所有的数据库，某个数据库，甚至是某个数据库上的某个表。

liulanba

1705人浏览 · 2024-06-13 13:06:15

liulanba · 2024-06-13 13:06:15 发布

什么是MySQL的主从同步

为什么要使用MySQL主从同步

高可用性
实时备份：从数据库实时同步主数据库的数据，可以作为热备份。即使主数据库发生故障，从数据库也能保证数据的完整性和可用性。
快速恢复：如果主数据库发生故障，可以迅速将从数据库提升为主数据库，保证业务的连续性。
负载均衡
读写分离：在主从同步架构中，主数据库处理写请求，从数据库处理读请求。这种方式可以显著减轻主数据库的压力，提高整体系统的性能。
并发处理：多个从数据库可以同时处理读请求，提高系统的并发处理能力。
扩展性
分布式架构：通过添加更多的从数据库，可以轻松地扩展系统的读性能，适应业务增长的需求。
跨地域部署：从数据库可以部署在不同的地理位置，减少延迟，为全球用户提供更快的访问速度。
数据分析
数据仓库：从数据库可以用来执行数据分析、报表生成等操作，而不影响主数据库的性能。
数据隔离：避免分析操作对生产环境的干扰，提高系统的稳定性。
灾难恢复
异地备份：从数据库可以部署在不同的地理位置，当发生灾难性事件时（如火灾、地震等），可以快速切换到异地的从数据库，保证数据的安全和业务的连续性。
定期演练：通过定期切换主从角色，确保灾难恢复方案的有效性和可操作性。
数据分片
数据分片：在大型系统中，可以通过主从同步结合数据分片，将数据分布到不同的服务器上，实现分布式存储，提高系统的性能和扩展性。
容错与容灾
数据冗余：多台从数据库保存相同的数据，提供数据冗余，增强系统的容错能力。
快速切换：当主数据库故障时，可以快速切换到从数据库，减少停机时间。

怎么实现MySQL的主从同步

MySQL 主从复制是基于主服务器在二进制日志跟踪所有对数据库的更改。因此，要进行复制，必须在主服务器上启用二进制日志。
每个从服务器从主服务器接收已经记录到日志的数据。当一个从服务器连接到主服务器时，它通知主服务器从服务器日志中读取最后一个更新成功的位置。
主服务器记录二进制日志：主服务器上的数据变化（如INSERT、UPDATE、DELETE操作）被记录到二进制日志（binlog）中。
从服务器读取二进制日志：从服务器通过I/O线程从主服务器读取二进制日志并写入本地的中继日志（relay log）。
从服务器应用中继日志：从服务器的SQL线程读取中继日志并执行其中的SQL语句，使从服务器的数据与主服务器的数据保持同步。

从服务器接收从那时发生起的任何更新，并在主机上执行相同的更新。然后封锁等待主服务器通知的更新。
从服务器执行备份不会干扰主服务器，在备份过程中主服务器可以继续处理更新。

工作过程
MySQL 的主从复制工作过程大致如下：
1.从库生成两个线程，一个 I/O 线程，一个 SQL 线程；
2.I/O 线程去请求主库的 binlog，并将得到的 binlog 日志写到 relay log(中继日志) 文件中；
3.主库会生成一个 log dump 线程，用来给从库 I/O 线程传 binlog；
4.SQL 线程会读取 relay log 文件中的日志，并解析成具体操作，来实现主从的操作一致，而最终数据一致；
在这里插入图片描述

主从同步的方式

1. 异步复制（Asynchronous Replication）

工作原理
主数据库：将所有修改数据的操作记录到二进制日志（Binary Log）中。
从数据库：异步读取主数据库的二进制日志并重放这些操作。
优点
简单易用，配置较为简单。
对网络延迟和中断的容忍度较高。
缺点
可能导致数据不一致。在主数据库发生故障时，从数据库可能会有数据滞后。

2. 半同步复制（Semi-Synchronous Replication）

工作原理
主数据库：在提交事务后，等待至少一个从数据库确认收到并写入Relay Log（中继日志）后，才向客户端确认事务提交。
从数据库：收到主数据库的二进制日志后，立即写入Relay Log，并向主数据库确认。
优点
提高数据一致性，减少数据丢失的风险。
主数据库确认事务提交前至少一个从数据库已接收数据。
缺点
增加了事务提交的延迟。
配置和管理稍复杂，需要额外的插件（如MySQL 5.5+的 rpl_semi_sync_master 和 rpl_semi_sync_slave 插件）。
半同步复制确保至少一个从服务器收到主服务器的二进制日志后，主服务器才会提交事务，提高数据的可靠性。

[mysqld]
rpl_semi_sync_master_enabled=1  # 启用主服务器上的半同步复制
rpl_semi_sync_slave_enabled=1   # 启用从服务器上的半同步复制

3. 全同步复制（Synchronous Replication）

工作原理
主数据库：在提交事务后，等待所有从数据库确认收到并写入Relay Log后，才向客户端确认事务提交。
从数据库：收到主数据库的二进制日志后，立即写入Relay Log，并向主数据库确认。
优点
保证所有数据库实例的数据一致性。
提高数据的可靠性。
缺点
事务提交延迟较大，影响性能。
配置和管理复杂，网络故障或从数据库故障时可能影响主数据库的可用性。

4. 延迟复制（Delayed Replication）

工作原理
从数据库：在读取到主数据库的二进制日志后，延迟指定时间再执行日志中的操作。
优点
提供数据灾难恢复的时间窗口。在出现错误操作或数据损坏时，可以在延迟时间内切换到从数据库避免损失。
较为简单的配置和管理。
缺点
从数据库数据滞后，无法提供实时数据查询。
不适用于需要高实时性的数据场景。
可以通过设置从服务器的 MASTER_DELAY 参数实现延迟复制，用于防止主服务器上的误操作立即同步到从服务器。

CHANGE MASTER TO MASTER_DELAY = 3600;  # 设置延迟时间为3600秒（1小时）

5. GTID复制（Global Transaction Identifier Replication）

工作原理
使用全局事务标识符（GTID）来跟踪和管理复制。每个事务都有一个唯一的GTID，保证了事务在不同的服务器之间的一致性。
优点
简化了主从切换和故障恢复。
减少了复制管理的复杂性。
提供更强的数据一致性和完整性。
缺点
需要MySQL 5.6+版本支持。
对性能有一定的影响，需要额外的资源来维护GTID。
配置示例
异步复制配置示例
在主数据库上设置：

[mysqld]
log-bin=mysql-bin
server-id=1

CREATE USER 'replica_user'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'%';
FLUSH PRIVILEGES;

在从数据库上设置：

[mysqld]
server-id=2

CHANGE MASTER TO
MASTER_HOST='master_host_ip',
MASTER_USER='replica_user',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS= 123;

START SLAVE;
SHOW SLAVE STATUS\G;

半同步复制配置示例
在主数据库上启用插件并配置：

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
SET GLOBAL rpl_semi_sync_master_enabled = 1;

在从数据库上启用插件并配置：

INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

在主数据库上确认：

SHOW VARIABLES LIKE 'rpl_semi_sync%';

GTID复制配置示例
在主数据库和从数据库上设置：

[mysqld]
gtid_mode = ON
enforce_gtid_consistency = ON
log-bin=mysql-bin
server-id=1  # 主数据库和从数据库需要不同的ID

在主数据库上创建复制用户：

CREATE USER 'replica_user'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'%';
FLUSH PRIVILEGES;

在从数据库上配置复制：

CHANGE MASTER TO
MASTER_HOST='master_host_ip',
MASTER_USER='replica_user',
MASTER_PASSWORD='password',
MASTER_AUTO_POSITION = 1;

START SLAVE;
SHOW SLAVE STATUS\G;

总结
MySQL主从同步有多种方式，每种方式适用于不同的应用场景。选择适合自己业务需求的同步方式，可以提高系统的可用性、性能和扩展性。无论选择哪种方式，都需要根据具体需求进行合理配置和管理，确保系统的稳定运行。

怎么配置MySQL主从同步

在主服务器上进行配置
修改配置文件（通常是 my.cnf 或 my.ini）：

[mysqld]
log-bin=mysql-bin       # 启用二进制日志
server-id=1             # 主服务器ID，需唯一

创建用于复制的用户：

CREATE USER 'replica'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
FLUSH PRIVILEGES;

查看二进制日志位置：

SHOW MASTER STATUS;

记录输出中的 File 和 Position 值。

在从服务器上进行配置
修改配置文件（通常是 my.cnf 或 my.ini）：

[mysqld]
server-id=2             # 从服务器ID，需唯一且不同于主服务器

配置从服务器连接主服务器：

CHANGE MASTER TO
    MASTER_HOST='master_host_ip',
    MASTER_USER='replica',
    MASTER_PASSWORD='password',
    MASTER_LOG_FILE='mysql-bin.000001',   # 在上一步中记录的File值
    MASTER_LOG_POS=12345;                 # 在上一步中记录的Position值

启动复制：

START SLAVE;

检查主从同步状态
在从服务器上运行以下命令检查同步状态：

SHOW SLAVE STATUS \G

查看输出，确保 Slave_IO_Running 和 Slave_SQL_Running 都为 Yes。另外，检查 Last_IO_Error 和 Last_SQL_Error 确保没有错误。

常见问题与解决

从服务器延迟：如果从服务器处理日志的速度赶不上主服务器，可以考虑提升从服务器的硬件配置或优化数据库查询。

网络问题：网络不稳定可能导致同步中断，确保主从服务器之间的网络连接稳定。

主从不一致：如果主从数据不一致，可以使用 pt-table-checksum 和 pt-table-sync 工具进行数据校验和同步。

二进制日志溢出：主服务器的二进制日志会占用磁盘空间，需要定期清理旧的二进制日志。可以在主服务器的配置文件中设置 expire_logs_days 参数自动清理过期日志。

高级配置

多线程复制：从MySQL 5.7开始，支持多线程复制，通过设置 slave-parallel-workers 参数可以启用多线程，提高从服务器的同步效率。

[mysqld]
slave-parallel-workers=4  # 设置从服务器使用4个SQL线程并行处理

主从同步的缺点

数据一致性问题：在某些情况下，主从数据可能会不一致，需要定期检查和修复。
网络开销：主从同步依赖网络传输，网络状况不佳可能影响同步效率。
配置复杂：主从同步需要对主从服务器进行配置和管理，增加了系统复杂性。

主从同步的应用场景

高可用架构：在高可用系统中，通过主从同步实现故障转移，保证系统的稳定性。
读写分离：在高并发的应用中，通过主从同步实现读写分离，提高系统的并发处理能力。
数据备份与恢复：通过从服务器实现实时数据备份，防止数据丢失，并在需要时快速恢复数据。
MySQL主从同步是一种重要的数据复制技术，通过合理的配置和管理，可以实现高可用性、负载均衡和数据备份等目标。在实际应用中，需要根据具体需求和环境进行优化和调整，以达到最佳效果。

参考链接：
https://juejin.cn/post/6967224081410162696
https://www.cnblogs.com/kylinlin/p/5258719.html