MySQL主从复制详解
MySQL主从复制是数据库环境中常用的一种数据同步技术，用于实现数据的高可用性和负载均衡。通过主从复制，可以将一个MySQL数据库服务器的数据实时地复制到一个或多个从库中。本文将介绍MySQL主从复制的原理、配置步骤和常见问题。
一、MySQL主从复制的原理
MySQL主从复制基于二进制日志和中继日志实现。
-
主库：主库负责处理所有写入操作，并将这些操作记录在二进制日志文件中。每当主库上的数据发生变化时，都会更新二进制日志文件。
-
从库：从库负责复制主库上的数据。它首先连接到主库，请求主库上的二进制日志文件，然后将这些日志文件保存到本地的中继日志中。接着，从库读取并执行中继日志中的SQL语句，从而与主库保持数据一致。
二、MySQL主从复制的配置步骤
1. 配置主库
- 启用二进制日志：在MySQL配置文件中设置
log-bin选项，以启用二进制日志功能。 - 创建复制用户：创建一个用于复制的用户，并授权给从库访问主库的权限。
2. 配置从库
- 启用中继日志：在MySQL配置文件中设置
relay-log选项，以启用中继日志功能。 - 配置主库信息：在从库的配置文件中设置
change-master选项，指定主库的地址、端口、用户名和密码等信息。
3. 启动复制
- 启动从库：在从库上执行
START SLAVE命令，启动复制进程。 - 查看复制状态：使用
SHOW SLAVE STATUS命令查看从库的复制状态，确保复制正常运行。
三、MySQL主从复制的常见问题
1. 数据不一致
数据不一致通常是由于网络延迟或主库宕机导致的。解决方法是检查主库和从库之间的网络连接，确保主库正常工作。如果问题仍然存在，可以尝试重新同步数据。
2. 从库延迟
从库延迟是由于从库处理速度慢或网络延迟导致的。解决方法是优化从库的性能，例如增加CPU资源、调整缓存大小等。另外，可以考虑使用并行复制来提高复制速度。
3. 主库宕机
当主库宕机时，从库将无法继续同步数据。为了解决这个问题，可以使用主从切换技术，将其中一个从库升级为新的主库，并让其他从库连接到新主库进行数据同步。
四、主从同步原理
MySQL主从复制的原理基于二进制日志和中继日志实现。具体来说，主库负责处理所有写入操作，并将这些操作记录在二进制日志文件中。每当主库上的数据发生变化时，都会更新二进制日志文件。从库则负责复制主库上的数据。它首先连接到主库，请求主库上的二进制日志文件，然后将这些日志文件保存到本地的中继日志中。接着，从库读取并执行中继日志中的SQL语句，从而与主库保持数据一致。
在MySQL主从复制过程中，主要涉及三个线程：master上的binlog dump线程，以及slave上的I/O线程和SQL线程。当主库中有数据更新时，binlog dump线程会将更新事件按照binlog格式写入到主库的binlog文件中，并通知slave的I/O线程。I/O线程负责请求主库的binlog，并将得到的binlog日志写入到relay log（中继日志）文件中。SQL线程则读取relay log中的日志，并解析成具体操作，以实现主从数据的同步。
通过这种异步复制方式，即使在写操作时间较长的情况下，也不会影响读操作的进行，从而实现读写分离，提高整体服务性能。
binlog dump thread是MySQL主库上的一个线程，负责将二进制日志（binlog）中的数据发送给从库。当从库连接到主库并请求复制数据时，binlog dump thread会读取主库的binlog文件，并将指定位置之后的日志内容分批发送给从库。
IO thread是MySQL从库上的一个线程，负责连接主库并请求指定binlog、指定位置之后的日志内容。当从库执行START SLAVE命令开启主从复制后，从库会创建IO thread，用来连接主节点，请求指定binlog、指定位置之后的日志内容，并将获得的内容存到relay log中。
SQL thread是MySQL从库上的一个线程，负责检测relay log新增的内容，并将relay log的内容解析成具体的SQL，在从节点按照位置顺序执行，从而保证主从节点数据一致。
五、binlog
| 变量名称 | 作用 |
|---|---|
| log_bin | 是否开启binlog |
| log_bin_basename | binlog的基路径与名称 |
| log_bin_index | binlog索引文件的路径与名称 |
| log_bin_trust_function_creators | 是否可以信任存储函数创建者，不会创建写入二进制日志引起不安全事件的存储函数。如果设置为0(默认值)，用户不得创建或修改存储函数，除非它们具有除CREATE ROUTINE或ALTER ROUTINE特权之外的SUPER权限。设置为0还强制使用DETERMINISTIC特性或READS SQL DATA或NO SQL特性声明函数的限制。如果变量设置为1，MySQL不会对创建存储函数实施这些限制。此变量也适用于触发器的创建。 |
| log_bin_use_v1_row_events | 使用早期版本(MySQL5.5或更早)的记录方式，而非新版(MySQL5.6或更高)方式。此配置已被废弃。 |
| sql_log_bin | 用于精准控制当前会话是否要记录binlog |
| binlog_format | 用来指定binlog的格式： 1.Statement:基于SQL语句，每条修改数据的SQL都会存储到binlog文件里 2.Row:基于行，每行数据的变化都记录到binlog文件里，但是并不记录原始SQL 3.Mixed:混合模式，默认情况下会采用Statement记录，如果SQL中包含与时间、用户相关的函数时，会自动切换到Row 不同MySQL版本默认值不同！ |
常用命令：
- 查看所有binlog文件信息：
mysql> show master logs;
- 查看指定binlog文件中的事件：
-- 查看指定binlog文件中的所有事件
show binlog events in 'binlog.000001';
- 将当前会话的binlog格式设为STATEMENT:
set binlog_format = 'STATEMENT';
六、Mysql复制
MySQL复制是指将一个MySQL数据库服务器上的数据复制到另一个MySQL数据库服务器的过程。根据复制的方式不同，可以分为以下三种：
-
异步复制：在主库上执行的事务提交后，从库会在一定时间后自动进行数据同步。这种复制方式不需要等待从库确认数据已经同步完成，因此可以快速地将数据复制到多个从库中。但是，由于从库可能会有一定的延迟，因此在主库发生故障时，可能会导致数据丢失。
-
同步复制：在主库上执行的事务提交后，需要等待从库确认数据已经同步完成后才会返回结果。这种复制方式可以保证数据的一致性和完整性，但是在高并发的情况下，可能会导致主库的性能下降。
-
半同步复制：是介于异步复制和同步复制之间的一种复制方式。在主库上执行的事务提交后，需要等待至少一个从库确认数据已经同步完成后才会返回结果。这种复制方式可以在保证数据一致性的同时，减少主库的性能损失。
七、GTID复制
MySQL GTID复制是一种基于全局事务标识符（Global Transaction Identifier，简称GTID）的复制方法。这种方法在MySQL 5.6及更高版本中得到支持，旨在简化复制过程和故障恢复操作。GTID为每个在主库上提交的事务生成一个唯一的ID，确保在整个复制集群中这个ID的一致性和唯一性。
GTID复制解决了传统基于二进制日志位置的复制方法中的许多弊端。在早期版本的MySQL中，从服务器需要明确告知主服务器从哪个二进制日志文件的哪个偏移量开始复制数据。这种基于位置的信息很容易因人为错误或记录丢失而导致主从数据不一致的问题。而基于GTID的复制方式，则通过自动记录和追踪已经执行的事务，简化了这一过程，并减少了人为错误的发生。
GTID复制的优点在于它能够保证数据的一致性和简化故障恢复。因为GTID是唯一的，所以同一个事务在每个节点上只会被执行一次，这避免了重复执行可能导致的数据不一致性问题。另外，在使用GTID时，如果主库发生故障，可以轻松确定哪个从库的数据是最新的，进而快速提升为新的主库，无需手动计算和指定二进制日志的位置。
然而，GTID复制也存在一些限制。例如，在设置GTID之前，需要在主库上进行一系列兼容性检查，确保所有的操作都与GTID兼容。此外，GTID复制的配置和维护比传统方法更为复杂，可能需要更深入的理解和操作经验。
总的来说，MySQL GTID复制提供了一种更加自动化和可靠的数据复制方法，极大地简化了复制拓扑的管理和维护工作。尽管配置过程可能相对复杂，但考虑到其带来的数据一致性保障和方便的故障恢复能力，这些努力都是值得的。
搭建
在每台机器上执行如下命令，并让机器运行一段时间，观察错误日志，如果发现有任何warning，需要调整应用，以确保应用只是用了兼容GTID的特性。
SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY=WAHIN;
错误日志的路径可用如下命令获取：
show variables like 'log_error';
在每台机器上执行如下命令，确保所有事务都不允许违反GTID一致性。
SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY= ON;
在每台机器上执行如下命令(即：GTID从OFF-> OFF_PERMISSIVE)，让新事务是非GTID的，从节点既接受不带GTID的事务也接受带GTID的事务。
SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;
在每台机器上执行如下命令(即：OFF_PERMISSIVE-> ON_PERMISSIVE)，新事务是GTID，从节点既接受不带GTID的事务也接受带GTID的事务。
SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;
在每台机器使用如下命令，检测ONGOING_ANONYMOUS_TRANSACTION_COUNT的值是否为0，变成0后才可执行下一步。
SHOW STATUS LIKE 'ONGOING_ANONYMOUS_TRANSACTION_COUNT';
在每台机器上执行如下命令，开启GTID。
SET @@GLOBAL.GTID_MODE =ON;
在从节点执行如下命令，启用GTID复制。
STOP SLAVE;
CHANGE MASTER TO MASTER_AUTO POSITION = 1;
START SLAVE;
在每台机器的my.cnf添加如下内容，这样即使重启，配置也会生效。
[mysqld]
gtid_mode=ON
enforce_gtid_consistency=ON
八、多源复制
MySQL多源复制是从一个或多个主数据库向单个从数据库同步数据的过程。多源复制自MySQL 5.7版本开始引入，与传统的一主一从或一主多从的复制模式不同，它可以将多个主数据库的数据集中到一个从数据库，从而优化资源使用和数据处理效率。多源复制的配置与应用不仅能够提高数据备份的效率，还能在数据分析等多个场景中发挥重要作用：
- 配置方法：
- 需要确保所有的主服务器和从服务器都运行在MySQL 5.7或更高版本上，因为早期版本不支持多源复制特性。
- 在从服务器上设置
master_info_repository和relay_log_info_repository参数为‘TABLE’，以确保同步信息通过表的形式安全存储于数据库中，避免因服务器意外关闭而导致的数据丢失风险。 - 对于每一个主服务器，需要在从服务器上创建一个单独的通道来维护同步信息。这通常通过
CHANGE MASTER TO命令实现，指定每个主服务器的连接详情包括host、port、用户和密码等信息。
- 使用场景：
- 数据备份：通过将所有主数据库的数据集中到一个从数据库进行备份，可以减少对硬件资源的需求，同时也简化了数据库管理员（DBA）的维护工作。
- 数据分析：当企业中有多个部门拥有独立的数据库，而需要对这些数据库中的数据进行统一分析时，多源复制能够将不同数据库的数据汇总到一个从数据库中，便于进行综合分析。
- 读写分离：多源复制架构支持在从服务器上执行查询操作，从而实现读写分离，提升数据库的整体性能。
- 优势显著：
- 节省成本：通过减少从服务器的数量，降低硬件投资和维护成本。
- 提高效率：允许在单一从库上进行数据汇总和分析，避免了在多个数据库之间切换的复杂性。
- 增强可靠性：多源复制架构支持使用GTID（全局事务标识符），使得数据同步更加准确和可靠，避免了传统复制架构中因主从数据不一致导致的问题。
总的来说，MySQL多源复制不仅优化了数据的管理与分析过程，还为数据库管理员提供了节省成本与增强数据安全的新方法。但在实施过程中，仍需注意精确配置与版本兼容性，以确保数据同步的准确性和稳定性。
搭建
在从节点上添加如下配置：
[mysqld]
master_info_repository=TABLE
relay_log_info_repository=TABLE
注意：MySQL 8.0.22之前建议配置这两个参数，8.0.23开始，这两个配置已废弃，并且默认就是TABLE，可以缺省。
接下来，搭建一个新的主节点，例如master2，并用类似如下的命令，在从节点执行以配置主从：
-- channel m1指向master1
mysql> CHANGE MASTER TO master_host='master1', master_user='user1', master_password='xx', master_auto_position=1 FOR CHANNEL 'm1';
-- channel m2指向master2
mysql> CHANGE MASTER TO master_host='master2', master_user='user2', master_password='yy', master_auto_position=1 FOR CHANNEL 'm2';
启动时，在从节点执行如下命令：
mysql> START SLAVE FOR CHANNEL 'm1';
mysql> START SLAVE FOR CHANNEL 'm2';
查看状态：
mysql> SHOW SLAVE STATUS FOR CHANNEL 'm1'\G
九、总结
MySQL主从复制是实现数据高可用性和负载均衡的重要手段。通过了解其原理和配置步骤，可以有效地搭建和管理MySQL主从复制环境。同时，需要关注常见的问题并采取相应的解决方案，以确保数据的安全和一致性。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
