MySQL 主从复制

二进制日志管理

MySQL的主从复制是借助二进制日志来实现的，二进制日志（Binary Log）是一种特殊类型的日志文件，它记录了所有修改数据库内容的操作，并且二进制日志对性能的影响通常较小。

二进制日志配置

  • log_bin ：用于开启二进制日志，如果不指定日志名前缀，默认会以当前主机名为前缀，存放在MySQL的数据目录中。

  • skip-log-bin：当配置这个选项后，会覆盖 log_bin 的设置，导致二进制日志不会被启用。

  • expire_logs_days：设置二进制日志文件的自动过期天数。过了这个期限，旧的日志文件会被自动删除。

  • max_binlog_size：指定单个二进制日志文件的最大大小。达到这个大小后，MySQL 会关闭当前日志文件，并创建一个新的日志文件。

  • binlog_do_db：指定只有哪些数据库的更改会被记录在二进制日志中。

  • binlog_ignore_db：指定哪些数据库的更改不应该被记录在二进制日志中。

[mysqld]
log_bin              = /var/log/mysql/mysql-bin.log
expire_logs_days     = 14
max_binlog_size      = 256M
binlog-do-db         = position_ehcommon
binlog-do-db         = iot
binlog-do-db         = map

二进制日志格式

二进制日志一般有三种格式，基于语句、基于行和混合模式。

  • 基于语句复制（SBR）：记录执行的SQL语句，日志文件较小，但是某些情况下可能导致数据不一致。例如使用NOW()、RAND()）或用户自定义函数的时候

  • 基于行级复制（RBR）：记录对每一行数据进行的更改，日志文件较大，能确保数据的一致性，不受SQL语句的具体形式影响

  • 混合模式复制（MBR）：MySQL自动选择使用哪种格式。通常对于简单的操作使用SBR，而对于可能导致数据不一致的操作使用RBR。默认就是这种模式

指定默认二进制日志格式：

[mysqld]
binlog_format=STATEMENT|ROW|MIXED

二进制日志管理

  • RESET MASTER ：用于重置主服务器上的二进制日志。执行 RESET MASTER 会删除所有的二进制日志文件，并重新创建一个新的二进制日志文件。

  • FLUSH LOGS：关闭并重新打开所有日志文件（包括二进制日志、错误日志等），不会删除现有的二进制日志，会创建新的二进制日志文件

  • SHOW MASTER STATUS：显示的是当前主服务器的二进制日志信息，包括当前正在使用的二进制日志文件名和位置（Position）。

  • SHOW BINARY LOGS：列出了MySQL服务器上所有的二进制日志文件。提供了每个日志文件的名称和大小。

  • SHOW MASTER LOGS：SHOW BINARY LOGS的一个别名，两者提供相同的功能和输出

临时禁用二进制日志

可以通过 sql_log_bin 变量来启用或禁用当前会话的二进制日志记录，

  • SET sql_log_bin = 0;（禁用）

  • SET sql_log_bin = 1;（启用）

说明：

  • 设置用户变量时，使用一个@符号标识这是一个用户变量，所以不能省略这个@符号。如：SET @myVar = 'value';

  • 设置系统变量时，使用两个@符号表示这是一个系统变量，但是这两个@符号可以省略。

系统变量又分为全局变量和会话变量：

  • 对于全局变量：使用SET GLOBAL关键字设置变量值，如：SET GLOBAL max_connections = 200;

  • 对于会话变量：使用 SET SESSION 关键字设置变量值，但是SESSION关键字可以省略。如：SET sql_mode = 'STRICT_TRANS_TABLES';

MySQL 主从复制介绍

MySQL的主从复制是一种数据库复制技术，用于将一个数据库服务器（主服务器）复制数据到一个或多个数据库服务器（从服务器）。

使用场景

  • 数据备份：在从服务器上复制主服务器的数据，可以在主服务器发生故障时快速恢复数据，减少数据丢失的风险。

  • 负载均衡：将读取操作分散到一个或多个从服务器上，从而减轻主服务器的负载。

说明：

如果使用主从架构实现负载均衡，让master服务器只写数据，客户端读数据在slave服务器上完成，需要给slave服务器加上 read_only=ON 选项，让从服务器处于只读模式，不允许写入数据。

实现原理

1、主服务器上的所有数据修改操作都会被记录到二进制日志中，当从服务器连接到主服务器并请求复制数据时，主服务器会创建一个日志转储线程。这个线程负责读取本机二进制日志中的数据更改信息，并发送给从服务器。

2、从服务器会开启一个I/O线程用于接收主机发送过来的二进制日志，并将接收到的数据写入到自己的中继日志（Relay Log）

3、从服务器上还会开启一个SQL线程，负责读取中继日志中的事件，并在从服务器上执行这些事件，从而将更改应用到从服务器的数据库中

主从复制容错性介绍

MySQL复制架构设计的目的之一就是提供高可用性和容错性。

在MySQL的主从复制中，如果从服务器因为某些原因（比如断电）暂时离线，然后重新启动，它通常会尝试自动重新连接到主服务器并继续复制过程。具体过程如下：

1、从服务器的MySQL重新启动后，会尝试根据之前的配置重新连接到主服务器。

2、从服务器存储了复制的状态信息，包括它上次同步到的二进制日志文件和位置。因此，当它重新连接到主服务器后，它会从上次停止的位置继续复制。

3、从服务器离线期间发生的数据更改都是记录在二进制中的，这些更改将会被从服务器自动同步，以恢复数据一致性。

MySQL server-id介绍

mysqld的server-id参数用于唯一标识复制环境中的每个服务器，这个参数对于复制过程至关重要。具体作用如下：

1、用于区分不同的服务器，server-id为每个MySQL服务器实例提供了一个唯一标识符。通过这个参数可以让主服务器和从服务器可以区分彼此。

2、在复制过程中，主服务器的二进制日志（binlog）需要被正确地发送到从服务器。server-id帮助MySQL确定哪些日志属于哪个服务器，从而确保日志正确地路由到相应的从服务器。

3、，在使用binlog-do-db或replicate-do-db等过滤规则时，借助server-id可以过滤特定服务器的事件

4、在双主架构或者链式复制架构中，通过server-id来追踪更改是从哪个服务器发起的，从而防止将这些更改再次复制回原服务器，否则可能形成一个闭环。

例如：A和B两个服务器配置为双主架构，如果没有server-id来标识一个服务器。

A发生数据更改时：当在服务器A上进行数据更改时，这些更改被记录到A的二进制日志中。通过复制过程，A上的更改被发送到B，并在B上应用。

B记录更改到中继日志：当这些更改在B上应用时，它们也被记录到B的二进制日志中。因为没有server-id来标识这些更改的来源，B会将这些刚应用的更改（实际上是A上的原始更改）再次发送给A。

A接收自己的更改：A接收来自B的更改，并将其再次应用，这些更改又被记录到A的二进制日志中。然后A再次将这些更改发送给B，形成一个无限的复制循环。

MySQL 主从架构配置

1、master 开启二进制日志

[mysqld]
server-id               = 145
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 14
max_binlog_size         = 256M

2、master 创建复制用户

mysql> create user replica@'%' identified by 'eHIGH2014';
Query OK, 0 rows affected (0.01 sec)

mysql> grant all privileges ON *.* to replica@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

说明：用于复制的用户需要有 REPLICATION SLAVE 这两个权限

3、slave 开启中继日志

从服务器会自动创建并使用中继日志，不需要手动开启它。因为当从服务器配置好并连接到主服务器后，它会自动处理中继日志的创建和管理。

所以一个与主服务器不同的唯一的server-id就行了

[mysqld]
server-id               = 146

4、master 记录当前二进制位置

使用 SHOW MASTER STATUS 查看当前使用的二进制日志文件和位置。如果以前的二进制日志无用，可以直接执行 RESET MASTER 进行清除，或者执行 FLUSH LOG生成一个新的日志。

SHOW MASTER STATUS\G

5、slave 配置master 信息

使用CHANGE MASTER TO命令在从服务器上配置主服务器的详细信息，包括主服务器的IP地址、复制用户的用户名和密码，以及之前记录的二进制日志文件名和位置。

只需在mysql客户端输入HELP 'CHANGE MASTER TO' 就可以查看到相关参数了。

change master to
	master_host="192.168.12.145",
	master_port=3306,
	master_user="replica",
	master_password="eHIGH2014",
	master_log_file='mysql-bin.000001',
	master_log_pos=157;
Query OK, 0 rows affected, 8 warnings (0.03 sec)

6、slave 启动复制进程

从机上的复制进程有两个，io_thread 和 sql_thread。

START SLAVE IO_THREAD；

START SLAVE SQL_THREAD;

说明：如果像同时将两个进程启动，使用 START SLAVE 即可。

7、slave 验证复制状态

从机通过 show slave status\G 命令即可查看从机两个进程是否正常，以及复制的相关状态信息。

SHOW SLAVE STATUS\G

MySQL 一主多从架构配置

一主多从就是一个Master服务器，多个Slave服务器，每个从机的配置步骤和上面一样的。

1、master 配置

2、slave1 配置

3、slave2 配置

1、修改配置文件，指定server-id，用于唯一表示本服务器

2、根据主服务器的二进制日志信息配置master信息

3、启动io线程和sql线程

4、配置检查

MySQL 双主架构配置

将两个MySQL服务器实例都作为主节点运行，它们互相复制彼此的数据，只有当数据发生变化时（如插入、更新或删除操作），这些变化才会被同步到另一个服务器。

同步过程不会无端覆盖另一个服务器上的原有数据。只有在复制的命令在另一服务器上执行时，数据才会被改变。

例如：在一个服务器上执行了一个UPDATE命令，只有这个特定的UPDATE操作会在另一服务器上执行。

在双主架构下，两个服务器都可以进行写入操作，可能会出现数据不一致的情况。数据不一致主要体现在这几个方面：

1、操作同一条数据：因为MySQL的主从复制是异步的，如果两个服务器分别对同一数据做了不同的更改，将导致一个服务器上的更改覆盖另一个服务器上的更改。

2、主键冲突：如果同时尝试插入具有相同主键的新记录，就会发生主键冲突。

解决方法：

1、避免同一时间都对同一条数据就行修改

2、使用不同的自增主键策略

例如：

# mysql1
[mysqld]
# 主键初始值为1  增长幅度是2
auto_increment_offset=1
auto_increment_increment=2

# mysql2
[mysqld]
# 主键初始值为2 增长幅度是2
auto_increment_offset=2
auto_increment_increment=2

1、node1 配置

2、node2 配置

MySQL 实现复制过滤

有些时候我们只想要同步某些数据库的数据，可以通过配置主服务器和从服务器来选择性地复制数据。这个时候有两种方式可以实现；

修改master节点二进制日志实现

主服务器只记录特定数据库的数据更改到二进制日志，这样dump线程将二进制日志发送给从机后，从机就只会同步特定的更改事件。

通过二进制日志配置中的这两个参数可以实现：

  • binlog_do_db：指定只有哪些数据库的更改会被记录在二进制日志中。

  • binlog_ignore_db：指定哪些数据库的更改不应该被记录在二进制日志中。

例如：master只记录这些数据库的变更到二进制日志

[mysqld]
binlog-do-db = position_ehcommon
binlog-do-db = iot
binlog-do-db = iot_equipment_manager
binlog-do-db = lct_iap_upgrade
binlog-do-db = lct_net_manager

修改slave节点配置实现

也可以在从服务器上配置，即接收到主服务器的二进制日志后，只选择特定的数据库更改同步到本服务器。通过以下参数可以实现：

  • replicate_do_db：指定从服务器应该复制哪些数据库的更改

  • replicate_ignore_db：指定从服务器应该忽略哪些数据库的更改

允许更细粒度的控制，可以指定哪些表的更改应该被复制或忽略

  • replicate_do_table：指定从服务器应该复制哪些数据表的更改

  • replicate_ignore_table：指定从服务器应该忽略哪些数据表的更改

例如：只复制map、iot、db1这三个数据库的数据更改

[mysqld]
replicate_do_db=map
replicate_do_db=iot
replicate_do_db=db1

说明：

直接在主服务器上配置binlog_do_db或binlog_ignore_db来指定哪些数据库的更改需要记录到二进制日志是一个相对简单直接的方法，通过配置从服务器来只记录或者跳过某些特定的库和表，这种方法有些时候不生效。

1、从库的配置依赖当前数据库上下文，在使用replicate_do_db和replicate_ignore_db时，MySQL的复制过滤依赖于当前的数据库上下文。也就是说，如果更改不是在显式选定的数据库上下文中进行的（例如，通过USE database_name;语句），这些更改可能不会被正确地过滤。

2、对于涉及多个数据库的复杂查询或交叉数据库更新，从服务器的复制过滤规则可能不会按预期工作。

MySQL 处理主从同步错误

在复制过程中，如果从服务器遇到任何错误（如数据不一致、唯一键冲突等），通常这会导致复制进程停止。从服务器设置slave-skip-errors=all可以使从服务器忽略这些错误，继续复制操作。

[mysqld]
slave-skip-errors=all

当开启了这个设置后，可能导致主从服务器之间的数据不一致，因为错误的数据更改被跳过而没有应用到从服务器。

查看同步错误的方法

如果想要查看发生了哪些复制错误，可以通过以下几种方式查看：

1、查看MySQL的错误日志，通过错误日志判断发生了哪些错误。

2、查看复制状态，执行SHOW SLAVE STATUS\G;命令后，Last_SQL_Errno和Last_SQL_Error字段会显示最后一个复制错误的错误码和错误信息。如果之后发生了新的错误（或者复制成功地继续进行），之前的错误信息将会被覆盖，就需要看错误日志确定了。

Slave节点复制过程管理

  • STOP SLAVE：用于停止从服务器上的复制进程。执行这个命令时会停止从服务器上的I/O线程和SQL线程。这些线程分别负责从主服务器读取二进制日志事件（I/O线程）和执行这些事件（SQL线程）。

  • START SLAVE：用于启动复制进程，不指定进程名，两个复制进程都会被启动。

  • RESET SLAVE：需要限制性STOP SLAVE，会清除从服务器上的中继日志（Relay Log）和复制位置信息，但不会删除复制配置，如主服务器的连接信息。

  • RESET SLAVE ALL：（在MySQL 5.5.16及更高版本中可用），不仅重置从服务器的复制状态和中继日志，还会清除所有复制配置信息，包括主服务器的连接信息。

master节点复制信息管理

在MySQL从服务器配置主机复制信息时，包括两个主要部分：主服务器的连接信息和复制位置信息。

主服务器连接信息： 包括用于连接主服务器的详细信息，如用户名、密码、端口号和主机地址等。

如果只是填错了这部分信息，您可以简单地使用 STOP SLAVE; 命令停止复制进程，然后使用 CHANGE MASTER TO 命令重新指定正确的连接信息。

CHANGE MASTER TO
	MASTER_HOST='HOST',
	MASTER_PORT='PORT',
	MASTER_USER='replication_user',
	MASTER_PASSWORD='new_correct_password';

主服务器复制位置信息： 包括从哪个二进制日志文件和位置开始复制数据。

如果复制位置信息有误或需要重置，可以执行 RESET SLAVE; 命令。这将清除从服务器上的中继日志和复制位置信息，但不会影响连接配置。

更换主服务器： 如果需要彻底更换主服务器，或者完全重新配置复制关系，执行RESET SLAVE ALL;命令会清除所有复制相关的配置和状态，包括连接信息和复制位置信息。

在执行RESET SLAVE ALL;之后，需要使用 CHANGE MASTER TO 命令重新配置所有复制信息，就像从头开始配置复制一样。

MySQL 复制进程管理

MySQL进行主从复制时，一共会开启三个进程。

dump线程

dump线程用于发送二进制日志（binlog）的内容给从服务器（slave）。当从服务器连接到主服务器并请求复制数据时，主服务器上会为每个这样的从服务器连接创建一个Binlog Dump线程。

主机通过 show processlist\G 命令可以查看到对应的DUMP线程信息

show processlist\G

例如：

image

状态信息说明

User: replica 表明这个线程是为复制用户replica创建的。

Host: 192.168.14.8:58280 显示了从服务器的IP地址和端口。

Command: Binlog Dump 指出这个线程正在执行的命令是二进制日志的传输。

State: Source has sent all binlog to replica; waiting for more updates 表明目前所有的二进制日志都已经发送给从服务器，现在主服务器正在等待更多的更新来继续发送。

IO线程和SQL线程

从机的IO进程负责接收主机dump进程发送过来的数据信息存放到自己的中继日志。

使用 show processlist\G 命令时，如果复制线程当前没有活动（例如：I/O线程已经读取了所有可用的二进制日志，但主服务器上没有新的更改），此时这些线程可能不会显示在列表中。

此时可以通过 SHOW SLAVE STATUS\G; 查看从机两个复制线程的状态，如果 Slave_IO_Running和Slave_SQL_Running都是"Yes"，那么复制应该是正常运行

SHOW SLAVE STATUS\G
	Slave_IO_Running: Yes
	Slave_SQL_Running: Yes
slave节点状态信息说明：
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
#  显示了I/O线程的当前状态。此时从服务器正在等待主服务器发送更多的二进制日志事件。
               Slave_IO_State: Waiting for source to send event
#  # 主服务器的相关信息
                  Master_Host: 192.168.0.107
                  Master_User: replica
                  Master_Port: 3306
                Connect_Retry: 60
# 当前正在读取的主服务器上的二进制日志文件和位置。
              Master_Log_File: mysql-bin.000015
          Read_Master_Log_Pos: 22689659
               Relay_Log_File: node2-relay-bin.000038
                Relay_Log_Pos: 22635898
# 显示了从服务器上的中继日志文件和读取到的位置。
        Relay_Master_Log_File: mysql-bin.000015
# 表明I/O线程和SQL线程是否在运行
             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: 22689113
              Relay_Log_Space: 22671268
              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:
# 显示从服务器落后于主服务器的秒数 0 表示正常
        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: 107
                  Master_UUID: aa971894-690c-11eb-8715-000c29c73113
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica 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:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:

posted on 2023-12-13 23:58  背对背依靠  阅读(57)  评论(0)  编辑  收藏  举报