02-MySQL主从同步

MySQL主从同步

MySQL主从同步

一、主从架构规划

主机名	IP地址	节点信息
db-01	10.0.0.51	主节点
db-02	10.0.0.52	从节点

二、搭建步骤

1.创建主节点和从节点实例

从节点无数据

2.进行主库数据全备份(可选)

直接在从库上远程备份

[root@db02 ~]# mysqldump -uroot -p1231 -h10.0.0.51 -P3306 -A --source-data --single-transaction >/tmp/all.sql

参数详解
- source-data：在备份文件中，可以记录备份数据位置点信息
  - 设置参数=1 表示利用命令信息记录位置点（等价于直接使用参数 --source-data）
  即：CHANGE MASTER TO MASTER_LOG_FILE='binlog.000020', MASTER_LOG_POS=157;导出文件的这行信息
  - 设置参数=2 表示利用注释方式记录位置点
- single-transaction：在备份时会对备份的数据信息，创建快照，从而避免备份过程的全局锁对主库业务影响

3.从库进行数据恢复

[root@db02 ~]# mysql -uroot </tmp/all.sql

4.在主库中创建数据同步用户

以创建repl用户为例

mysql> create user repl@'10.0.0.%' identified with mysql_native_password by '123456';

授予读取binlog信息的权限

mysql> grant Replication slave on *.* to repl@'10.0.0.%';
Replication slave   -- 此权限表示可以用于读取主节点中binlog日志信息

5.从库进行主从同步设置

-- 在<主库>查看当前使用的binlog日志以及起始的pos点
mysql> show master status ;
+---------------+----------+
| File          | Position |
+---------------+----------+
| binlog.000020 |      157 |
+---------------+----------+

-- 在<从库>设置主节点信息
CHANGE MASTER TO
MASTER_HOST='10.0.0.51',
MASTER_PORT=3306,
MASTER_USER='repl',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='binlog.000020',
MASTER_LOG_POS=157,
MASTER_CONNECT_RETRY=10;

6.修改主从节点server_id

主节点默认为1，修改从节点server_id 为2，此信息不一样即可
查看server_id

select @@server_id

在配置文件中修改

[root@db02 ~]# vim /etc/my.cnf
[mysql]
server_id=2

重新启动数据库

[root@db02 ~]# /etc/init.d/mysqld restart

7.启用主从同步功能

-- 在<从库>设置
start slave;

三、主从同步原理（高频面试题）

1.根据主从搭建流程

1. 从库配置
	在从库上配置主从信息，保存到 master.info 文件。
2. 启动主从同步
	从库启动主从功能，创建两个线程：
	IO 线程：连接主库建立会话，接收 Binlog 数据。
	SQL 线程：读取并回放主库同步信息。
3. IO 线程连接主库
	IO 线程加载 master.info 文件，向主库发起连接请求。
4. 主库响应
	主库验证从库连接请求后，创建 Dump 线程：
	Dump 线程负责读取主库的 Binlog 并传输给从库的 IO 线程。
5. 数据传输与保存
	IO 线程接收 Binlog 数据，保存到 Relay Log，并更新 master.info 文件中的位置点。
6. 数据回放
	SQL 线程读取 Relay Log，回放数据，并将回放位置点记录到 Relay Log Info 文件。
7. 完成
	至此，主从同步建立完成，数据持续同步。

2.根据主从需要的文件和线程

#主从建立原理主要会应用 3个线程 和 4个文件，最终实现主从同步
#3个线程： 
IO线程：  负责和主库建立连接会话/负责将主机信息保存到从库  
SQL线程： 负责读取主库同步信息/将主库同步信息进行回放
DUMP线程：负责将主库binlog信息传输给从库IO线程/负责读取或监控主库binlog文件内容
#4个文件： 
binlog：		主库中记录事务操作信息的，用于主从同步 
relaylog：      保存主库发送的事件信息，用于SQL线程读取并回放数据 
master info     保存连接主库信息，以及和主库同步位置点信息
relay log info  保存SQL线程回放过程事件位置点信息

四、主从状态查看

从库监控线程状态信息

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 10.0.0.51
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: binlog.000008
          Read_Master_Log_Pos: 1667
               Relay_Log_File: db02-relay-bin.000003-- 主从延迟同步时有用
                Relay_Log_Pos: 623
        Relay_Master_Log_File: binlog.000008
             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: 错误码-- 记录主从同步异常错误原因
                   Last_Error: 错误原因
                 Skip_Counter: 0-- 跳过错误信息     
          Exec_Master_Log_Pos: 1667
              Relay_Log_Space: 2208
              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: cc1aeb72-f574-11ef-803e-000c29141679 -- GTID同步可以关注 
             Master_Info_File: /data/3306/data/master.info-- 主从配置信息如何保存 master.info-file/mysql.slave_master_info-table
                    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: bcfe4c0a-f893-11ef-bd8c-000c29f7e34a:1-220
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set, 1 warning (0.02 sec)

1.确认主从同步功能正常

状态中，若以下两个状态信息结尾YES，表示主从同步功能正常

mysql> show slave status\G
…………
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

2.判断主从之间是否有延迟

#表示从库的 IO 线程当前正在读取的主库（Master）的二进制日志（Binlog）文件名
Master_Log_File: binlog.000008
#表示从库的 IO 线程在主库的 Binlog 文件中读取到的最后位置
Read_Master_Log_Pos: 1667  
#表示从库的 SQL 线程当前正在处理的 Binlog 文件名,若存在复制延迟会有所不同
Relay_Master_Log_File: binlog.000008
#表示从库的 SQL 线程已经成功回放（执行）到的 Binlog 文件的位置
Exec_Master_Log_Pos: 1667  
#表示从库的 SQL 线程在处理主库的 Binlog 数据时，与主库的实时数据之间的延迟时间（单位为秒）
Seconds_Behind_Master: 0              
-- 值为 0：表示从库与主库之间的数据完全同步，没有延迟。
-- 值大于 0：表示从库在应用 Binlog 数据时落后于主库，数值越大，延迟越严重

3.主库监控主从同步情况

mysql> show slave hosts\G
*************************** 1. row ***************************
 Server_id: 2
      Host: 
      Port: 3306
 Master_id: 1
Slave_UUID: bcfe4c0a-f893-11ef-bd8c-000c29f7e34a
-- 从库的 UUID（Universally Unique Identifier，全局唯一标识符）

五、主从异常原因和处理思路

1.IO线程异常

功能作用：
- 负责向主库发送连接请求，和主库dump线程建立连接会话
- 负责接收主库dump数据，并将数据进行保存

①Connecting状态

当Slave_IO_Running: Connecting时，表示正在连接状态，可能是以下几种原因：

网络通讯有异常：设备问题配置问题使用pingIP，telnet端口确认

创建的连接信息是否正确：地址；端口；用户；密码；

查看状态信息

  Slave_IO_Running: Connecting
  Last_IO_Errno: 2003
  Last_IO_Error: error reconnecting to master 'repl@10.0.0.51:3306' - retry-time: 10 retries: 7 message: Can't connect to MySQL server on '10.0.0.51:3306' (111)

解决方案

#查看slave状态，记录信息
Master_Log_File: binlog.000008
Read_Master_Log_Pos: 1667
#清理master info信息
stop slave;
reset slave all;
CHANGE MASTER TO
  MASTER_HOST='10.0.0.51',
  MASTER_PORT=3306,
  MASTER_USER='repl',
  MASTER_PASSWORD='123456',
  MASTER_LOG_FILE='binlog.000008',
  MASTER_LOG_POS=1667,
  MASTER_CONNECT_RETRY=10;
start slave;

具有防火墙安全服务功能：
- 硬件防火墙设置
- 软件iptables设置表链，firewalld-zone设置主库
主库在并发访问量较大时，主从出现异常重新建立后，会显示IO connecting状态
- 调整最大连接数 max_connections 默认为151

②no状态

当Slave_IO_Running: no时，为无法运行的状态，可能是以下几种原因：

主库中需要binlog文件或对应位置点事件不存在

缺陷：此方法修复主从异常，会造成主从之间部分数据不一致，需要进行主从之间数据库数据补全
状态信息

Last_IO_Errno: 13114
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not open log file'

手动更新从库的复制状态信息

方法1：
STOP SLAVE;

update mysql.slave_master_info set Master_log_name='binlog.000010',Master_log_pos=157 where Number_of_lines=33;
-- Master_log_name 当前主库的binlog文件
-- Master_log_pos  当前主库binlog读取位置
-- mysql.slave_master_info表中的一个字段

START SLAVE;

方法2：
STOP SLAVE;

   reset slave all;
   CHANGE MASTER TO
   MASTER_HOST='10.0.0.51',
   MASTER_PORT=3306,
   MASTER_USER='repl',
   MASTER_PASSWORD='123456',
   MASTER_LOG_FILE='binlog.000010',
   MASTER_LOG_POS=157,
   MASTER_CONNECT_RETRY=10

START SLAVE;

从库中需要relaylog文件不存在 IO=no SQL=no

缺陷：此方法修复主从异常，会造成主从之间部分数据不一致，需要进行主从之间数据库数据补全
解决：

STOP SLAVE;

   reset slave all;
   CHANGE MASTER TO
   MASTER_HOST='10.0.0.51',
   MASTER_PORT=3306,
   MASTER_USER='repl',
   MASTER_PASSWORD='123456',
   MASTER_LOG_FILE='binlog.000010',
   MASTER_LOG_POS=157,
   MASTER_CONNECT_RETRY=10

START SLAVE;

确保主从之间server_id和server_uuid配置信息不能一致

若server_id一致，状态信息报错：

  Last_IO_Errno: 13117
  Last_IO_Error: Fatal error: 
  The slave I/O thread stops because master and slave have equal MySQL server ids; 
  these ids must be different for replication to work 
  (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).

若server_uuid一致，状态信息报错；
- 通过SHOW VARIABLES LIKE 'server_uuid';查看server_uuid
- 主库通过show slave hosts;查看从库的server_uuid

  Last_IO_Errno: 13117
  Last_IO_Error: Fatal error: 
  The slave I/O thread stops because master and slave have equal MySQL server UUIDs; 
  these UUIDs must be different for replication to work.

2.SQL线程异常;出现故障：no

读取relay log文件信息失败（文件找不到或对应文件中位置点）
回放relay log文件信息失败
- 状态信息报错
```
   Last_SQL_Errno: 1007
   Last_SQL_Error: 
   Coordinator stopped because there were error(s) in the worker(s). 
   The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at master log binlog.000013, end_log_pos 721.
   See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
```
- 查看具体报错信息
```
方法1：
select * from performance_schema.replication_applier_status_by_worker\G

方法二：
查看数据目录中的`主机名.err`文件
```
- 解决SQL线程回放数据失败问题：
  - 方式一：删除冲突数据
  删除库删除表删除数据删除索引删除约束（索引-禁用索引）
  - 方式二：忽略冲突错误
    
    sql_slave_skip_counter=1; -- 忽略几个SQL线程回放事件信息 xiaoAA 跳过 xiaoBB 跳过
    slave_skip_errors=1007 -- 忽略指定错误码错误信息
- 避免以上SQL线程异常情况
  - super_read_only=ON -- 限制管理员对数据库服务进行写入操作
  - read_only=ON -- 限制普通用户对数据库服务进行写入操作

六、主从延迟原因和处理思路(主从数据不一致)

1.外部因素导致的延时问题

网络通讯不稳定，有带宽阻塞等情况，造成主从数据传输同步延时；
主从硬件差异大，从库磁盘性能较低，内存和CPU资源都不够充足；
主从配置区别大，从库配置没有优化，导致并发处理能力低于主库；

2.主库因素导致的延时问题

主要涉及Dump thread工作效率缓慢，可能是由于主库并发压力比较大；
- 调整内存资源利用率，减少磁盘和内存之间IO消耗
- 对业务数据进行拆分，可以实现分库分表操作
- 进行数据库分布式存储，可以实现并发压力分担
主要涉及Dump thread工作效率缓慢，可能是由于从库数量比较多导致；
- 通常设置1主3从-（备节点测试节点主从延迟）（1主5从）
主要涉及Dump thread工作效率缓慢，主要由于线程本身串型工作方式；
- 利用组提交缓解此类问题-5.6开始 group commit
- 主库查看是否开启组提交：
  
  binlog_group_commit_sync_delay
  -- 表示延迟多少微秒同步到磁盘
  binlog_group_commit_sync_no_delay_count
  -- 表示延迟提交的最大事务数量

3.从库因素导致的延时问题

从库产生延迟受SQL线程影响较大，由于线程本身串型工作方式导致；
- 利用不同数据库并行执行事务操作，但是一个库有多张表情况，产生大量并发事务操作，依旧是串型的（5.6开始多SQL线程回放）
- 利用logical_clock机制进行并发回放，由于组提交事务是没有冲突的，从库并行执行也不会产生冲突（5.7开始多SQL线程回放）
- 根据日志内容信息，获取logical_clock机制的组提交标记信息：（事务级别并发）
- slave_parallel_type=loglcal_clock
```
    -- 设置回放方式为loglcal_clock
```
- slave_parallel_workers=8
```
	-- 设置SQL回放数据线程数量
```

4.其他因素导致的延时问题

由于数据库大事务产生的数据同步延时问题；（更新100W数据/尽量切割事务）
由于数据库锁冲突机制的数据同步延时问题；（资源被锁无法同步/隔离级别配置RR-锁冲突严重，可调整RC降低延时索引主从一致）
由于数据库过度追求安全配置也会导致同步延时问题（从库关闭双一参数）；

七、GTID主从同步

1.基于GTID实现主从同步

基于GTID事务编号，实现主从数据信息同步，可以更好实现自动化同步
避免从库出现异常，导致恢复主从同步时，位置点异常
基于GTID的主从同步原理机制：
- 1）主库数据进行备份，在备份文件中会包含所有事务的 gtid编号信息（主库uuid:1-100）
- 2）从库进行数据恢复，进行数据恢复后，会将备份文件中事务信息记录到自己本地binlog文件（主库uuid:1-100）
- 3）在从库进行主从配置（不会配置pos位置点信息），但是会开启自动识别位置点功能
- 4）从库IO线程会读取从库binlog中 gtid信息，根据gtid信息向主库获取新的位置点binlog数据

基于GTID实现主从同步步骤

1）重置主从库实例

# 停服务、清空数据、重新初始化、启动
[root@db01 ~]# /etc/init.d/mysqld stop
[root@db01 ~]# rm -rf /data/3306/data/* 
[root@db01 ~]# mysqld --initialize-insecure --user=mysql --datadir=/data/3306/data/  --basedir=/usr/local/mysql 
[root@db01 ~]# /etc/init.d/mysqld start

#创建复制用户和主从同步用户 以及测试数据库
CREATE USER 'repl'@'10.0.0.%' IDENTIFIED BY '123123';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.0.%';
FLUSH PRIVILEGES;

2）主从数据库开启gtid功能

主库my.cnf配置文件添加：

[mysqld]
gtid-mode=on
enforce-gtid-consistency=true

从库my.cnf配置文件添加：

[mysqld]
gtid-mode=on
enforce-gtid-consistency=true 
log-slave-updates=1  
-- 可以让从库SQL线程回放的事件信息也记录到自己的binlog日志

3）将主库数据进行备份，并在从库中进行还原

#主库中查看Executed_Gtid_Set
mysql> show master status;

#从库远程备份、还原
gtid功能开启警告说明：
[root@db02 ~]# mysqldump -uroot -p123456 -h10.0.0.51 -P3307 -A --source-data >/tmp/all.sql
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, 
even those that changed suppressed parts of the database. 
If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. 
To make a complete dump, pass --all-databases --triggers --routines --events.

--set-gtid-purged=ON  （在备份文件中会记录GTID编号信息）
可以用于主从同步，在从数据库binlog中会生成对应gtid编号信息（和主节点一致）
--set-gtid-purged=OFF （在备份文件中不会记录GTID编号信息）
可以用于恢复数据，去除备份文件中gtid编号信息，避免幂等性对数据恢复的影响

[root@db02 ~]# mysql -uroot </tmp/all.sql
#从库查看Executed_Gtid_Set，与主库相同
mysql> show master status;

#在主库上添加测试数据
create database xiaoC;
create database xiaoD;

4）编写主从配置信息

change master to
master_host='10.0.0.51',
master_port=3306,
master_user='repl',
master_password='123123',
master_auto_position=1;

5）启用主从同步功能

start slave;
Retrieved_Gtid_Set: xxxxx
Executed_Gtid_Set: xxxxx

show databases;
xiaoC
xiaoD

2.GTID主从的管理

①GTID编号的异常情况

没了：影响GTID主从同步 SQL线程异常
编号小于主库：
- 可能出现的状况：从节点uuid：1-5 主节点 uuid 1-10
- 影响GTID主从同步可以从库同步数据会有丢失
gtid编号大于主从：
- 可能出现的状况：从节点uuid：1-20 主节点 uuid 1-10
- 影响GTID主从同步 IO线程异常
gtid编号UUID错误：
- 可能出现的状况：从节点uuid：AABB 主节点 uuid:BBAA
- 影响GTID主从同步 IO线程异常

②设置UUID:GTID

reset master;
set global GTID_PURGED='UUID:GTID';

③修复从节点的 GTID 事务编号信息

停止从节点同步：
```
STOP REPLICA;
```
设置 GTID_PURGED：
根据主节点的 GTID_EXECUTED 或备份文件中的 GTID_PURGED 值，设置从节点的 GTID_PURGED。
```
SET GLOBAL GTID_PURGED='UUID:1-n';
```
这里的 UUID:1-n 是主节点已执行的 GTID 范围。
重置从节点复制配置：
```
RESET REPLICA ALL;
```

重新配置主从关系：

CHANGE REPLICATION SOURCE TO 
SOURCE_HOST='主节点IP', 
SOURCE_USER='复制用户', 
SOURCE_PASSWORD='复制用户密码', 
SOURCE_AUTO_POSITION=1;

启动同步：
```
START REPLICA;
```
检查同步状态：
```
SHOW REPLICA STATUS\G;
```

④修复主节点的 GTID 事务编号信息

如果主节点的 GTID 信息出现问题（例如，主节点重新初始化或数据丢失），可以参考以下步骤：

停止从节点同步：
在所有从节点上执行：
```
STOP REPLICA;
```
在主节点上设置 GTID_PURGED：
根据从节点的 GTID_EXECUTED 或备份文件中的值，设置主节点的 GTID_PURGED。
```
SET GLOBAL GTID_PURGED='UUID:1-n';
```
重置主节点复制配置：
```
RESET MASTER;
```
重新配置主从关系：
在从节点上重新执行 CHANGE REPLICATION SOURCE TO 和 START REPLICA。
验证主从同步状态：
检查主从同步是否正常工作。

八、主从延迟同步

1.主从延迟同步的配置方法

应用作用：可以用于修复主库误操作数据信息

配置方法：

1）创建主从同步环境

正常创建即可
2）修改从库配置信息，实现延迟同步

stop slave;
change master to master_delay=15;		-- 根据需求设定具体时间(s)
start slave;
show slave status\G
-- 开启延迟功能，主要影响SQL线程，限制SQL线程读取回放relaylog日志的时间

2.利用延迟从库恢复数据

方式一：全备+binlog恢复
方式二：利用延迟从库恢复（恢复库或表数据更快捷灵活）
恢复方法：

步骤一：模拟创建测试数据（主库操作）
1. 创建测试数据库和表：
```
CREATE DATABASE xiaoQ01;
USE xiaoQ01;
CREATE TABLE t1 (id INT);
INSERT INTO t1 VALUES (1), (2);
```
  这些操作会在主库中创建一个数据库 xiaoQ01 和一个表 t1，并插入两条数据。
2. 模拟误操作（删除数据库）：
```
DROP DATABASE xiaoQ01;
```
  这是模拟的误操作，删除了整个数据库。
3. 配置从库延迟复制：
```
STOP SLAVE;
CHANGE MASTER TO MASTER_DELAY=300;
START SLAVE;
SHOW SLAVE STATUS\G
```
  - STOP SLAVE;：停止从库的复制。
  - CHANGE MASTER TO MASTER_DELAY=300;：配置从库延迟 300 秒（5 分钟）。
  - START SLAVE;：启动从库复制。
  - SHOW SLAVE STATUS\G：检查从库状态，确认延迟生效。
步骤二：在从库中获取恢复数据（从库操作）
1. 停止从库 SQL 线程：
```
STOP SLAVE SQL_THREAD;
```
  停止 SQL 线程，避免从库继续应用主库的误操作。
2. 取消延时功能：
```
CHANGE MASTER TO MASTER_DELAY=0;
```
  取消延迟，以便从库可以快速追上主库的事务。
3. 定位误操作的 GTID 和 Binlog 位置：
  - 获取误操作的 GTID：
    - 在主库上，查看当前的 GTID：
```
SHOW MASTER STATUS;
```
      输出示例：
```
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000001 | 912      |              |                  | 868db540-f96c-11ef-ba07-000c29141679:1-24 |
+------------------+----------+--------------+------------------+------------------------------------------+
```
      假设误操作的 GTID 是 868db540-f96c-11ef-ba07-000c29141679:24。
  - 在从库上，让从库复制到误操作之前：
```
START SLAVE UNTIL SQL_BEFORE_GTIDS="868db540-f96c-11ef-ba07-000c29141679:24";
```
    或者通过 Binlog 位置：
```
START SLAVE UNTIL RELAY_LOG_FILE='db02-relay-bin.000002', RELAY_LOG_POS=1358;
```
    - 这里假设误操作的 Binlog 文件是 db02-relay-bin.000002，位置是 1358。
4. 备份从库数据：
```
mysqldump -uroot -p -B xiaoQ01 --set-gtid-purged=OFF >/tmp/xiaoQ01.sql
scp -rp /tmp/xiaoQ01.sql 10.0.0.51:/tmp/
```
  - 使用 mysqldump 导出数据库 xiaoQ01。
  - 将备份文件传输到主库。
步骤三：在主库中恢复数据信息（主库操作）
1. 恢复数据：
```
SET SQL_LOG_BIN=0;
SOURCE /tmp/xiaoQ01.sql;
SET SQL_LOG_BIN=1;
```
  - SET SQL_LOG_BIN=0;：关闭二进制日志记录，避免恢复操作被复制到从库。
  - SOURCE /tmp/xiaoQ01.sql;：恢复备份数据。
  - SET SQL_LOG_BIN=1;：重新开启二进制日志记录。
2. 确认数据恢复成功：
```
USE xiaoQ01;
SELECT * FROM t1;
```
  确保数据恢复正确。
步骤四：恢复延迟从库（从库操作）
1. 跳过误操作的 GTID：
```
STOP SLAVE;
SET GTID_NEXT='868db540-f96c-11ef-ba07-000c29141679:24';
BEGIN; COMMIT;
SET GTID_NEXT='AUTOMATIC';
START SLAVE;
```
  - 停止从库复制。
  - 使用 SET GTID_NEXT 跳过误操作的 GTID。
  - 重新启动从库复制。
2. 重新配置延迟复制：
```
STOP SLAVE;
CHANGE MASTER TO MASTER_DELAY=300;
START SLAVE;
SHOW SLAVE STATUS\G
```
  - 停止从库复制。
  - 重新配置延迟为 300 秒。
  - 启动从库复制。
  - 检查从库状态。

九、克隆同步数据构建主从

应用场景： 解决物理主机与云主机之间的数据迁移问题。

环境信息：

物理主机（主库）：IP 地址为 10.0.0.51，数据库实例为 db01。
云主机（从库）：IP 地址为 10.0.0.52，数据库实例为 db02。

步骤一：重置从数据库环境（在云主机上操作）

停止 MySQL 服务：
```
/etc/init.d/mysqld stop
```
删除数据目录中的所有文件：
```
rm -rf /data/3306/data/*
```
注意： 确保 /data/3306/data/ 是从库的数据目录，并且在删除文件之前备份重要数据。
初始化 MySQL 数据目录：
```
mysqld --initialize-insecure --user=mysql --datadir=/data/3306/data/ --basedir=/usr/local/mysql
```
参数说明：
- --initialize-insecure：初始化数据目录，允许 root 用户无密码登录。
- --user=mysql：以 mysql 用户运行 MySQL。
- --datadir：指定数据目录路径。
- --basedir：指定 MySQL 安装路径。
启动 MySQL 服务：
```
/etc/init.d/mysqld start
```
检查 MySQL 是否正常启动：
```
systemctl status mysqld
```
或者直接登录 MySQL：
```
mysql -uroot
```

步骤二：安装克隆功能插件（主库和从库都需要操作）

登录 MySQL：
```
mysql -uroot -p
```

安装克隆插件：

INSTALL PLUGIN clone SONAME 'mysql_clone.so';

验证插件是否安装成功：
```
SHOW PLUGINS;
```
确保 clone 插件的状态为 ACTIVE。

步骤三：创建克隆应用用户并进行授权（在主库上操作）

关闭二进制日志记录（防止授权操作被复制到从库）：
```
SET SQL_LOG_BIN=0;
```

创建克隆用户并授权：

CREATE USER test@'%' IDENTIFIED BY '123456';
GRANT BACKUP_ADMIN, CLONE_ADMIN ON *.* TO 'test'@'%';
FLUSH PRIVILEGES;

重新开启二进制日志记录：
```
SET SQL_LOG_BIN=1;
```

权限说明：

BACKUP_ADMIN：允许用户在主库上执行备份操作。
CLONE_ADMIN：允许用户在从库上执行克隆操作。

在从库上设置主库的白名单：
```
SET GLOBAL clone_valid_donor_list='10.0.0.51:3306';
```
- 这里假设主库的端口是 3306，请根据实际情况调整。

步骤四：实现数据克隆功能（在从库上操作）

登录从库 MySQL：

mysql -utest -p123456 -h10.0.0.52 -P3306

执行克隆操作：
```
CLONE INSTANCE FROM test@'10.0.0.51':3306 IDENTIFIED BY '123456';
```
- 这里假设主库的 IP 是 10.0.0.51，端口是 3306，克隆用户是 test，密码是 123456。
（可选）删除 auto.cnf 文件：
```
rm -f /data/3306/data/auto.cnf
```
- 删除 auto.cnf 文件可以避免从库启动时的 UUID 冲突。
重启 MySQL 服务：
```
/etc/init.d/mysqld restart
```
验证克隆是否成功：
- 登录从库 MySQL，检查数据是否与主库一致：
```
USE your_database;
SHOW TABLES;
SELECT * FROM your_table;
```

步骤五：实现主从同步（在从库上操作）

配置主从复制：
```
CHANGE MASTER TO
MASTER_HOST='10.0.0.51',
MASTER_PORT=3306,
MASTER_USER='repl',
MASTER_PASSWORD='123456',
MASTER_AUTO_POSITION=1;
```
- 确保 repl 用户已经在主库上创建，并且具有复制权限。
- MASTER_AUTO_POSITION=1 表示启用 GTID 复制。
启动从库复制：
```
START SLAVE;
```
检查复制状态：
```
SHOW SLAVE STATUS\G
```
- 确保 Slave_IO_Running 和 Slave_SQL_Running 都为 Yes。
- 如果出现错误，请根据 Last_Error 提示进行排查。

十、主从数据同步方式

1. 异步复制

工作原理

主库在执行事务后，将 Binlog 日志发送给从库。
主库不会等待从库的确认，事务即视为完成。

优点

性能高效：主库无需等待从库确认，事务处理速度快。

缺点

数据一致性问题：如果主库在 Binlog 传输完成前发生故障，可能导致主从数据不一致。

2. 半同步复制

半同步复制在异步复制的基础上增加了确认机制，分为两种类型：

2.1 有损半同步（早期半同步）

工作原理：
- 主库在事务提交后（after_commit），将 Binlog 发送给从库。
- 主库等待从库的确认（ACK）后，才向客户端返回结果。
- 如果主库在发送 Binlog 后发生故障，可能会导致主从数据不一致。
特点：
- 配置简单：实现机制固定。
- 性能较好：相比全同步，事务处理效率更高。

2.2 无损半同步（增强半同步）

工作原理：
- 主库在事务提交前（after_sync），将 Binlog 发送给从库。
- 主库等待从库确认后，才进行事务提交。
- 这种方式减少了主从数据不一致的风险。
特点：
- 配置复杂：需要更多参数配置。
- 数据一致性更好：减少了主从数据不一致的可能性。

默认设置

从 MySQL 8.0 开始，默认采用无损半同步（after_sync）。

3. 全同步复制（如 MGR 或 Percona XtraDB Cluster）

工作原理：
- 主从之间，进行binlog日志信息传递时，主库会有确认机制，确认收到从库信息，才完成事务提交（确认阶段：SQL线程回放后确认）
优点：
- 数据一致性：主从数据始终保持一致。
缺点：
- 性能较低：事务处理效率低，因为主库需要等待从库完成所有操作。

4. 半同步复制配置步骤

步骤一：重置从数据库环境

（这部分内容与克隆同步相关，不适用于半同步配置，因此可以忽略。）

步骤二：安装半同步功能插件

主库安装插件：
```
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
```
- 作用：影响主库的 dump 线程工作机制，发送 Binlog 日志时会等待从库确认。
从库安装插件：
```
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
```
- 作用：影响从库的 IO_THREAD 工作机制，接收 Binlog 后发送确认消息。

步骤三：开启半同步复制功能

主库开启功能：

SET GLOBAL rpl_semi_sync_master_enabled = 1;

从库开启功能：

SET GLOBAL rpl_semi_sync_slave_enabled = 1;
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;

步骤四：配置半同步模式

有损半同步（早期半同步）：

SET GLOBAL rpl_semi_sync_master_wait_point = 'AFTER_COMMIT';

无损半同步（增强半同步）：

SET GLOBAL rpl_semi_sync_master_wait_point = 'AFTER_SYNC';

默认值：
- MySQL 8.0 默认采用无损半同步（AFTER_SYNC）。

步骤五：设置超时时间

如果主库在指定时间内未收到从库的确认，会自动切换为异步模式：
```
SET GLOBAL rpl_semi_sync_master_timeout = 40s;
```

十一、实现主从过滤

好的！我将按照逻辑顺序整理你的笔记，纠正错误，并详细解释主从复制过滤的两种方式：主库过滤和从库过滤。

主从复制过滤概述

MySQL 主从复制支持在主库或从库上进行数据过滤，以控制哪些数据需要同步，哪些数据不需要同步。过滤方式分为两种：

主库过滤（基于 Binlog 的过滤）：
- 在主库上配置，控制哪些数据写入 Binlog。
- 适用于减少 Binlog 的大小和同步带宽。
从库过滤（基于 Relay Log 的过滤）：
- 在从库上配置，控制哪些 Binlog 事件被回放。
- 适用于从库的个性化需求，不影响主库的 Binlog。

1. 主库过滤（基于 Binlog 的过滤）

主库过滤通过配置 binlog_do_db 和 binlog_ignore_db 参数来实现。这些参数控制哪些数据库的数据会被记录到 Binlog 中。

1.1 参数说明

binlog_do_db：
- 指定哪些数据库的数据会被记录到 Binlog 中。
- 例如：binlog_do_db=xiaoQQ01 表示只有 xiaoQQ01 数据库的操作会被记录到 Binlog。
binlog_ignore_db：
- 指定哪些数据库的数据不会被记录到 Binlog 中。
- 例如：binlog_ignore_db=xiaoXX01 表示 xiaoXX01 数据库的操作不会被记录到 Binlog。

1.2 配置方法

在主库的配置文件（通常是 my.cnf 或 my.ini）中添加以下配置：

[mysqld]
binlog_do_db=xiaoQQ01
binlog_ignore_db=xiaoXX01

注意：

如果同时配置了 binlog_do_db 和 binlog_ignore_db，binlog_do_db 的优先级更高。
主库过滤会影响 Binlog 的内容，因此所有从库都会受到这些过滤规则的影响。

1.3 应用场景

减少 Binlog 大小：通过忽略某些不重要的数据库，减少 Binlog 的写入量。
节省同步带宽：只同步必要的数据库，减少网络传输的数据量。

2. 从库过滤（基于 Relay Log 的过滤）

从库过滤通过配置 replicate_do_db、replicate_ignore_db、replicate_do_table、replicate_ignore_table 等参数来实现。这些参数控制哪些 Binlog 事件会被从库回放。

2.1 参数说明

replicate_do_db：
- 指定哪些数据库的数据会被从库回放。
- 例如：replicate_do_db=xiaoQQ01 表示只有 xiaoQQ01 数据库的操作会被回放。
replicate_ignore_db：
- 指定哪些数据库的数据不会被从库回放。
- 例如：replicate_ignore_db=xiaoXX01 表示 xiaoXX01 数据库的操作不会被回放。
replicate_do_table：
- 指定哪些表的数据会被从库回放。
- 例如：replicate_do_table=xiaoQQ01.t1 表示只有 xiaoQQ01 数据库中的 t1 表的操作会被回放。
replicate_ignore_table：
- 指定哪些表的数据不会被从库回放。
- 例如：replicate_ignore_table=xiaoXX01.t2 表示 xiaoXX01 数据库中的 t2 表的操作不会被回放。
replicate_wild_do_table：
- 使用通配符指定哪些表的数据会被从库回放。
- 例如：replicate_wild_do_table=xiaoQQ01.t% 表示 xiaoQQ01 数据库中以 t 开头的表的操作会被回放。
replicate_wild_ignore_table：
- 使用通配符指定哪些表的数据不会被从库回放。
- 例如：replicate_wild_ignore_table=xiaoXX01.t% 表示 xiaoXX01 数据库中以 t 开头的表的操作不会被回放。

2.2 配置方法

在从库的配置文件（通常是 my.cnf 或 my.ini）中添加以下配置：

[mysqld]
replicate_do_db=xiaoQQ01
replicate_ignore_db=xiaoXX01
replicate_do_table=xiaoQQ01.t1
replicate_ignore_table=xiaoXX01.t2
replicate_wild_do_table=xiaoQQ01.t%
replicate_wild_ignore_table=xiaoXX01.t%