本文详细介绍了MySQL主从复制的异步特性，包括传统复制、mydump实践、主从同步原理及监控。探讨了故障分类、半同步复制、过滤复制、延时从库以及GTID复制等高级话题，并特别提及5.7+版本的多源复制在OLAP场景的应用。
主从复制（异步）
复制2台以上节点，通过binlog实现最终“同步”关系。
传统复制
## 复制前提（搭建过程）
1. 准备两个以上数据库实例(主\从).
创建两台虚拟机。
2. 主库打开二进制日志
mysql> select @@log_bin; ## 查看主库是否启动了二进制日志
mysql> select @@log_bin_basename;
3. 不同节点的server_id 和 server_uuid不同.
mysql> select @@server_id; ## 查看虚拟机的id ,也是通过id来区分谁是主谁是从
mysql> select @@server_uuid; ## 从库的id，可以直接在配置文件中进行修改。
[root@db02 ~]# rm -rf /data/3306/data/auto.cnf （删除这个文件，在重启就会重新生成一个uuid）
[root@db02 ~]# /etc/init.d/mysqld restart
mysql> reset master; ` 如果觉得日志文件过多，可以进行删除的操作，然后就清空了
4. 主库中需要创建专用复制用户 replication slave
mysql> create user repl@'10.0.0.%' identified with mysql_native_password by '123';
## 创建一个专用的复制用户，用来主从连接的数据同步使用
mysql> grant replication slave on *.* to repl@'10.0.0.%';
## 给它授权一个专有的权限，用来主从复制
5. 备份主库已有数据到从库恢复(mdp pxb ## clone plugin 克隆
========================================================
4.2 远程clone
4.2.0 各个节点加载插件
INSTALL PLUGIN clone SONAME 'mysql_clone.so'; ## 两个节点都需要加载插件
或
[mysqld]
plugin-load-add=mysql_clone.so
clone=FORCE_PLUS_PERMANENT
SELECT PLUGIN_NAME, PLUGIN_STATUS ## 查看节点插件加载状态
FROM INFORMATION_SCHEMA.PLUGINS
WHERE PLUGIN_NAME LIKE 'clone';
4.2.1 创建远程clone用户
# 捐赠者(source)授权（在root用户下）
create user test_s@'%' identified by '123';
grant backup_admin on *.* to test_s@'%';
# 接受者(target)授权（在root用户下）
create user test_t@'%' identified by '123';
grant clone_admin on *.* to test_t@'%';
4.2.2 远程clone(目标端)
# 开始克隆
SET GLOBAL clone_valid_donor_list='10.0.0.51:3306'; ## 在root用户下，设置一个白名单。
mysql -utest_t -p123 -h10.0.0.52 -P3306 ## 退出root，进入到创建的用户
CLONE INSTANCE FROM test_s@'10.0.0.51':3306 IDENTIFIED BY '123'; ## 进行克隆的操作。
==========================================================
5. 告诉从库复制的起点信息: change master to
## 在从库的root用户下，
CHANGE MASTER TO
MASTER_HOST='10.0.0.51',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql_bin.000001', ## 通过下边的命令可以知道，然后修改一下。
MASTER_LOG_POS=1205,
MASTER_CONNECT_RETRY=10;
mysql> select * from performance_schema.clone_status\G
*************************** 1. row ***************************
ID: 1
PID: 0
STATE: Completed
BEGIN_TIME: 2020-12-29 00:51:57.623
END_TIME: 2020-12-29 00:52:17.381
SOURCE: 10.0.0.51:3306
DESTINATION: LOCAL INSTANCE
ERROR_NO: 0
ERROR_MESSAGE:
BINLOG_FILE: mysql-bin.000001
BINLOG_POSITION: 1205
GTID_EXECUTED: 00bf718b-491c-11eb-81a2-000c2905f029:1-4
1 row in set (0.00 sec)
6. 启动专用复制线程. start slave;
mysql> start slave ; ## 启动专有线程
mysql> show slave status \G ## 查看线程状态
`Slave_IO_Running: Yes
`Slave_SQL_Running: Yes
## 为成功。
7. 测试主从
在主库root用户下，mysql> create database xiaolei;
在从库root用户下，mysql> show databases;
mydump的实现主从
1. 主库开启binlog
[root@db01 ~]# mysql -uroot -p123 -e "select @@log_bin;"
[root@db01 ~]# mysql -uroot -p123 -e "select @@log_bin_basename;"
2. 检查server_id 和 server_uuid
[root@db01 ~]# mysql -uroot -p123 -e "select @@server_id;"
[root@db01 ~]# mysql -uroot -p123 -e "select @@server_uuid;"
如果一样:
[root@db03 data]# vim /etc/my.cnf
server_id=53
[root@db03 data]# rm -rf /data/3306/data/auto.cnf
[root@db03 data]# /etc/init.d/mysqld restart
[root@db03 data]# mysql -uroot -p123 -e "reset master;"
3. 主库创建复制用户
略 .
4. 备份恢复至从库
[root@db03 data]# mysqldump -uremote -p123 -h 10.0.0.51 -A --master-data=2 --single-transaction -R -E --triggers --max_allowed_packet=64M >/tmp/full.sql
5. change master to
[root@db03 ~]# grep '\-- \CHANGE' /tmp/full.sql (通过文件查看)
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=196;
mysql -uroot -p123
source /tmp/full.sql;
CHANGE MASTER TO
MASTER_HOST='10.0.0.152',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3306,
MASTER_LOG_FILE='binlog.000001',
MASTER_LOG_POS=674,
MASTER_CONNECT_RETRY=10;
6. 启动专用复制线程. start slave;
mysql> start slave ;
mysql> show slave status \G;
主从复制名词
1 涉及到的线程
1.1 主库
Binlog_Dump_Thread(DUMP): 接收从库请求和返回结果(binlog)
mysql> show processlist;
1.2 从库
IO: 连接主库和主库进行通信.
SQL: 回放binlog
[root@db02 ~]# mysql -uroot -p123 -e "show slave status \G"|grep Running:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
2 设计到的文件或者表.
2.2 主库
binlog
2.3 从库
relay-log 中继日志
master-info : 记录主库信息(ip port user passwd binlog_file binlog_pos)
文件方式: master.info
表 : mysql.slave_master_info
relay-info : 中继日志回放信息
文件方式: relay.info
表 : mysql.slave_relay_log_info
主从同步原理
2.2.3 复制原理文字说明
1. 从库: change master to ....,主库相关信息记录到master_info(MI)中.
2. 从库: start slave , 启动 IO SQL线程
3. 从库: 根据MI的信息,连接主库.
4. 主库: 生成一个DUMP线程和IO通信
5. 从库: 根据MI的信息(binlog pos),向主库请求新的binlog.
6. 主库: DUMP截取新的binlog,发送给从库IO
7. 从库: IO接收binlog,更新MI信息,存储binlog到relaylog中
8. 从库: SQL 读取Relay-info(RI)信息,获取上次会放到的位置点,回放最新的relaylog.
9. 从库: 从库SQL回放完成后,更新RI信息.
补充:
主库DUMP实时监控binlog的变化,通知给从库IO.
从库relaylog,会自动被清理
'注意：
'1. dump实时监控binlog的变化，一旦发生变化，就会发起一个信号给io ，然后io先去取master_info 的信息，然后去获取binlog的信息，然后更新master_info的日志，并存一份给relay log.'
'2. SQL实时监控着real_log,然后先获取relaylog_info的信息，然后去得real_log，最后跟新relaylog_info信息'
'3. 中继日志（记录的是sql语句）在中间其实做了一个转换，将主库中的binlog日志接收，然后转换到relaylog_info中。
` 然后通过binlog的pos值与relaylog_info中的pos值相减，来查看主从之间的延迟状态。
监控主从复制
1 主库 :
mysql> show master status ;
mysql> show processlist;
mysql> show slave hosts;
## 注: 如果需要show slave hosts看到从库的地址信息,需要在从库做以下配置.
vim /etc/my.cnf
[mysqld]
report_host=10.0.0.52
report_port=3306
2 从库:
mysql> show slave status\G
2.1 主库相关信息(MI) ---> mysql.slave_master_info
Master_Host: 10.0.0.51
Master_User: repl
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.000002 show master status ## 获取到主库的文件名 （主库执行到的）
Read_Master_Log_Pos: 196 ## 主库执行到的位置点
2.2 从库SQL线程回放信息(RI)----> mysql.slave_relay_log_info
`主库对应的是binlog，从库对应的realy log 。他们的文件是不对等的。但是我又想强硬的将他们对上关系。
Relay_Log_File: db02-relay-bin.000006 ## 接收到主库信息之后从库所对应到文件名
Relay_Log_Pos: 324 ## 接收到主库信息之后在从库的哪个位置点
'下边获取到的是你接收主库信息之后执行到的位置点，然后与从库直接的位置进行关联
Relay_Master_Log_File: mysql-bin.000002 ## 从库执行到的获取主库文件信息
Exec_Master_Log_Pos: 196 ## 从库执行到的获取主库的位置点
面试题: 如何计算,主从日志差异的日志量(字节)?
(主库show master status--> Position) - (从库 show slave status---> Exec_Master_Log_Pos) = 日志量差异
可以准确预估主从的延时.
2.3 线程状态监控 ## 如果出现报错，都是在这里进行查看的
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
2.4 过滤复制相关监控 （可进行筛选，监控哪些，忽略哪些）
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
2.5主从延时的时间.（因为日志在记录的时候，都是有一个时间戳，但是这个不一定准）
Seconds_Behind_Master: 0
2.6 延时从库状态
` 物理方面删除，并不会影响从库的数据，但是逻辑方面，会影响从库数据，会随着一起删除（因为从库记录的是日志信息，SQL记录的也是能执行的SQL语句）。
` 所以在逻辑方面，就可以添加这种物理延迟，然后可以在时间内进行通过从库进行恢复。
SQL_Delay: 0
SQL_Remaining_Delay: NULL
2.7 gtid 复制相关信息
Retrieved_Gtid_Set:
Executed_Gtid_Set: 00bf718b-491c-11eb-81a2-000c2905f029:1-4
故障分类:IO
`关于 IO `
干的活: 连接主库,请求 接收 存储日志
## Connecting（状态码） : 连接故障
1. 外部因素
网络: 网络不通,防火墙等.
2. 内部因素
数据库异常
用户 密码 IP PORT 加密插件
server_id server_uuid ## 出现的报错是NO
连接数上限
` 连接故障重现.
1. 宕掉主库
## 当主库的数据库停止了服务。
Slave_IO_Running: Connecting
Last_IO_Errno: 2003
Last_IO_Error: error connecting to master 'repl@10.0.0.51:3306' - retry-time: 10 retries: 1 message: Can't connect to MySQL server on '10.0.0.51' (111)
2. 用户 密码 IP PORT 加密插件
Slave_IO_Running: Connecting
Last_IO_Errno: 1045
Last_IO_Error: error connecting to master 'repl@10.0.0.51:3306' - retry-time: 10 retries: 1 message: Access denied for user 'repl'@'10.0.0.52' (using password: YES)
3. 最大连接数上限
mysql> set global max_connections=2;
Slave_IO_Running: Connecting
Last_IO_Errno: 1040
Last_IO_Error: error connecting to master 'repl@10.0.0.51:3306' - retry-time: 10 retries: 1 message: Too many connections
## 通用的排查方法:
使用复制相关信息,mysql 命令进行手工连接测试.
## No（状态码）：状态码
`问题: 如果在主从开始之后，又想要改变密码：
1.停止掉从库
stop slave;
2.清理掉slave的全部信息
reset slave all;
3.重新加载新信息
change master to ...
4.然后重新启动线程，重新连接
start slave;
` 故障案例：
'1. Server_id 或者Server_uuid重复
Slave_IO_Running: No
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
'2. binlog 位置点写错了.
Slave_IO_Running: No
Last_IO_Errno: 13114
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file
'3. 日志损坏
Last_IO_Errno: 13114
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'could not find next log; the first event 'mysql-bin.000003' at 774, the last event read from '/data/3306/binlog/mysql-bin.000003' at 953, the last byte read from '/data/3306/binlog/mysql-bin.000003' at 953.'
故障案例：SQL
` SQL : No
主要工作:回放relay log 中的 SQL语句
原因:
1. 需要创建的对象已经存在
2. 删除和修改的对象不存在
3. 约束冲突
'从库被误写入了.
'双主设计问题
'高可用脑裂
'主从数据不一致.
` 故障重现:
1. 从库误写入
Slave_SQL_Running: No
Last_Errno: 1007
Last_Error: Error 'Can't create database 'new'; database exists' on query. Default database: 'new'. Query: 'create database new'
## 以主库为准的处理方法:
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
mysql> drop database new;
Query OK, 0 rows affected (0.04 sec)
mysql> set sql_log_bin=1;
## 以从库为准:
` 没开GTID的模式:
mysql> stop slave;
mysql> set global sql_slave_skip_counter=1;
mysql> start slave;
` 开了GTID:
stop slave;
set gtid_next='00bf718b-491c-11eb-81a2-000c2905f029:6';
begin;commit; ## 写入一个空值
set gtid_next='AUTOMATIC';
` 从库被写入,源头解决问题:
设置从库为只读库.
mysql> set global read_only=1;
mysql> set global super_read_only=1;
` 主从数据不一致:
1. 增强半同步复制 (较强)
2. 组复制(很强)
3. PXC(很强)
主从延时
主库做了操作，但是从库并没有及时跟上，而是需要等一段时间才可以。
` 监控主从：
show slave status;
通过日志的时间戳间隔:
Seconds_Behind_Master: 0 ##但这不准确，因为是0不一定没问题，但是不是0，一定有问题
` 通过日志量判断?
非GTID :
'(主show master status--> Position) - (从 show slave status--> Exec_Master_Log_Pos) = 日志量差异
GTID:
可以用GTID号码判定.
` 延迟原因
1.外部：
1）网络慢
2）主从硬件配置差异大
3）参数配置的也不相同
4）版本不同， ## 版本从库必须高于主库，然后升级的时候，从库先升级，主库在升级
2.主库：
1）主库并发的数量高，而我们的dump线程只有一个，是串行工作的。
2）大事务（超5000行+）,在传输的时候，就是慢。
' 解决方法：
5.6+版本，加入了group commit(组提交)（GC）技术，
## 所谓组提交；其实就是我们将小事务，先打成一个组包，然后一起发送。
` 有两个指标：
1. binlog_group_commit_sync_delay=1 时间延时
2. binlog_group_commit_sync_no_delay_count=1000 个数控制
## 但是依旧会怕大事务的发生。
3.从库：
1.IO：'因为它只需要日志落盘就可以了，所以只需要提高硬盘的性能，比如将出库的binlog日志与从库的relay log 都放到固态盘中。在硬件方面，就能得到解决
2.SQL 也是一个sql线程。（所以也是串行回放的）
## 使用串行，是因为我们主库在做操作的时候，是有逻辑性的，但是打包发给从库的时候，可能逻辑性会乱，那么在回放的时候，如果并行，肯定就会执行不成功。
`但如果是串行的话，就会先分析一下打包过来的日志的逻辑，然后按照逻辑回放
5.6之后，出现一个叫parallel的机制。（SQL线程并发回放）
slave_parallel_type | DATABASE ## 默认是库为级别
slave_parallel_workers | 0 ## 0是不开启并发操作，最大值是1024
`默认情况下，是以database 级别进行并发回放，只要主库中的事务来自于不同库的操作，那么在做并发的时候。就不会产生冲突，也就可以进行并发回放。
'但是现在都是从一个库中发起的数据，那么不同主库的并发就没有用了。
'logical_clock逻辑时钟：记录的是 事务提交的时间点'，
5.7之后，加入了logical_clock(逻辑时钟) 模式，它的作用就是：
## 在主库中，它会将同一时刻正常提交的(group commit)事务，都会打上同样的标签（能够在同一时刻提交的事务，是证明他们之间是不会发生冲突的），并且也会给到他一个顺序，然后当打包发给从库之后，从库接收，查看到的是同一个时刻的，并且都打上了逻辑的标签的，就可以进行并发操作。
在binlog 日志中，通过 mysqlbinlog /data/3306/binlog/mysql-bin.00001 (GC)
日志文件中，last_commited=8 (同样的都是同一时间)
sequence_number=9 （这个就是同一时间之后的顺序编号）
而group commit的配置，也还是个数和时间这两个参数。
8.0之后，writesets 写集合方式. MGR.
总结：
1. 历史遗留的延时问题,在版本升级过程中基本解决了.
2. 所以主从延时,我们面临的问题就是优化业务. 所以减少大事务,锁问题,性能较差SQL才是优化主从延时的重点.
半同步复制
结果是保证最终主从数据的一致性
` 半同步原理：
'首先在主库和从库，都会有一个ACK的校验机制，当主库进行了一个begin~commit的操作，但当binlog接收到日志之后，就会传输到从库的relaylog，然后relaylog落到磁盘之后，会返回一个ACK 的确认码给主库，然后主库才会进行真正的commit的操作。这样就能保证主从的一致性。
'但同样的，在接收ACK校验码的时候，会出现等待的过程，就会造成并发量降低。
`并且在半同步中，ACK的等待位置，是在pc2的commit的redo commit 与最终commit之间。那么就会一个事情，只要写入binlog commit，在逻辑上就已经认为是数据落盘了，从库也会接收并且执行。但是当这个时候执行redo commit，发生了宕机。那么主库就没有做redo commit的操作，也就没有真正意义上的得到commit标签，那么这个时候即使修复好数据库，redo prepare并没有那条语句的标签，自然就不会再处理，那么主库就会出现没有数据，而从库有数据的现象。
## 注意：这个只是有可能会发生的事。在大几率上是不会发生的。在redo prepare查看的状态，一般都是准备提交，未提交，和已经提交。但是没有执行这条redo commit的。其实可以直接理解为。没有标签。就可以直接忽略。
'在增强半同步中，只是修改了等待ACk的位置，将这个位置放在了binlog commit 与redo commit之间。那么这个时候，binlog commit 接收到日志，发送给从库接收，从库接接收之后，返回ACK校验码，如果在这个时候宕机了，主库中binlog commit 就不会再继续进行执行，即使被数据库被修复好，也只会进行回滚的操作，因为根本就没有进行提交的操作。。而从库根本也就没有relay log中接收到日志，自然也不会执行。那么就会最终得数据的一致性
5.5版本，出现了半同步，但是因为没有GC机制，性能极差
5.6之后，出现了GC机制，才开始使用半同步复制
`半同步使用的是after_commit 机制
`增强半同步使用的是after_sync机制
## 注意：
不管哪种方式,还会出现,如果ACK超时,会被切换为异步复制的模式.还是有数据不一致的风险.
如果公司能容忍,可以使用这种架构,建议使用增强半同步+GTID模式.
如果不能容忍,可以使用MGR PXC .
-----------------------------------------------------------------------
## 增强半同步的配置
2.2 增强半同步复制配置
2.2.1 加载插件
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
2.2.2 查看是否加载成功:
show plugins;
启动:
SET GLOBAL rpl_semi_sync_master_enabled = 1;
SET GLOBAL rpl_semi_sync_slave_enabled = 1;
2.2.3 重启从库上的IO线程
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;
2.2.4 查看是否在运行
show status like 'Rpl_semi_sync_master_status';
show status like 'Rpl_semi_sync_slave_status';
2.2.5 其他的优化参数：
show variables like '%semi%';
rpl_semi_sync_master_enabled =ON
rpl_semi_sync_master_timeout =1000
rpl_semi_sync_master_trace_level =32
rpl_semi_sync_master_wait_for_slave_count =1
rpl_semi_sync_master_wait_no_slave =ON
rpl_semi_sync_master_wait_point =AFTER_SYNC
rpl_semi_sync_slave_enabled =ON
rpl_semi_sync_slave_trace_level =32
mysql> set global binlog_group_commit_sync_delay =1;
mysql> set global binlog_group_commit_sync_no_delay_count =1000;
主从复制演变-过滤复制
3.1 什么是过滤复制?
选择性复制.
3.2 应用场景
业务的分离.
部分数据同步.
3.3 如何实现
主库 : 是否记录binlog来控制
binlog_do_db : 白名单
binlog_ignore_db : 黑名单
从库 : SQL线程是否回放来控制
replicate_do_db=world
replicate_ignore_db=test
replicate_do_table=world.city
replicate_ignore_table:test.t100w
replicate_wild_do_table=oldboy.t*
replicate_wild_ignore_table=oldguo.t*
3.4 配置演练:
mysql> stop slave sql_thread;
mysql> change replication filter replicate_do_db = (oldguo, test);
mysql> start slave sql_thread;
延时从库
4.1介绍
是我们认为配置的一种特殊从库.人为配置从库和主库延时N小时.
4.2 为什么要有延时从
什么是数据库损坏？
物理损坏
主从复制非常擅长解决物理损坏.
逻辑损坏
普通主从复制没办法解决逻辑损坏
4.3 配置延时从库
SQL线程延时:数据已经写入relaylog中了,SQL线程"慢点"运行
一般企业建议3-6小时,具体看公司运维人员对于故障的反应时间。
stop slave;
CHANGE MASTER TO MASTER_DELAY = 300; ## 延迟时间是300s
start slave;
4.4 延时从库应用 *****
4.4.1 故障恢复思路
a. 发现问题
b. 停掉从库线程
c. 控制SQL回放日志的截止位置点.
d. 找回数据,快速恢复业务
4.4.2 故障模拟及恢复演练
stop slave;
CHANGE MASTER TO MASTER_DELAY = 300;
start slave;
create database relaydb ;
use relaydb;
create table t1(id int);
insert into t1 values(1),(2),(3);
commit;
insert into t1 values(11),(12),(13);
commit;
insert into t1 values(111),(112),(113);
commit;
drop database relaydb;
处理过程
show relaylog events in 'db02-relay-bin.000002' 查看从库relaylog的日志
| db02-relay-bin.000002 | 1732 | Xid | 51 | 3385 | COMMIT /* xid=2212 */ |
| db02-relay-bin.000002 | 1763 | Gtid | 51 | 3462 | SET @@SESSION.GTID_NEXT= '00bf718b-491c-11eb-81a2-000c2905f029:24' |
| db02-relay-bin.000002 | 1840 | Query | 51 | 3575 | drop database relaydb /* xid=2214 */
stop slave sql_thread;
CHANGE MASTER TO MASTER_DELAY = 0; ## 手动关闭延时从，要不然自己也的等那么久才能执行
START SLAVE SQL_THREAD UNTIL RELAY_LOG_FILE = 'db02-relay-bin.000002', RELAY_LOG_POS = 1763 ;
如果开启GTID，可以按照GTID方式UNTIL
START SLAVE UNTIL SQL_BEFORE_GTIDS = "188be1ed-c84c-11ea-98e7-000c29ea9d83:4";
GTID复制
5.2 GTID介绍
GTID(Global Transaction ID)是对于一个已提交事务的唯一编号，并且是一个全局(主从复制)唯一的编号。
它的官方定义如下：
GTID =server_uuid : transaction_id
7E11FA47-31CA-19E1-9E56-C43AA21293967:29
什么是sever_uuid，和Server-id 区别？
核心特性: 全局唯一,具备幂等性
5.3 GTID核心参数
重要参数：
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
gtid-mode=on --启用gtid类型，否则就是普通的复制架构
enforce-gtid-consistency=true --强制GTID的一致性
log-slave-updates=1 --slave更新是否记入日志
5.4 GTID复制配置过程：
5.4.1 清理环境
pkill mysqld
\rm -rf /data/3306/data/*
\rm -rf /data/3306/binlog/*
\mv /etc/my.cnf /tmp
mkdir -p /data/3306/data /data/3306/binlog/
chown -R mysql.mysql /data/*
5.4.2 准备配置文件
主库db01：
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql/
datadir=/data/3306/data
socket=/tmp/mysql.sock
server_id=51
port=3306
secure-file-priv=/tmp
log_bin=/data/3306/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db01 [\\d]>
EOF
slave1(db02)：
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock
server_id=52
port=3306
secure-file-priv=/tmp
log_bin=/data/3306/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db02 [\\d]>
EOF
slave2(db03)：
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock
server_id=53
port=3306
secure-file-priv=/tmp
log_bin=/data/3306/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db03 [\\d]>
EOF
5.4.3 初始化数据
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3306/data
5.4.4 启动数据库
/etc/init.d/mysqld start
5.4.5 构建主从：
master:51
slave:52,53
# 51:
create user repl@'10.0.0.%' identified with mysql_native_password by '123';
grant replication slave on *.* to repl@'10.0.0.%' ;
# 52\53:
change master to
master_host='10.0.0.51',
master_user='repl',
master_password='123' ,
MASTER_AUTO_POSITION=1;
start slave;
注意：
如果是已经运行很久的数据库，需要构建从库，都是需要备份恢复主库数据后再开启主从的。
mysqldump来讲，不要加--set-gtid-purged=OFF功能。如果加上这个参数，就需要从头开始备份了，如果不加，就会从你全备之后的当前GTID之后，进行开始从库，因为它知道前面的你主库已经有了，那我就不在做主从了。
5.7+版本 多源复制(MSR Multi Source Replication): OLAP (在线分析处理)
## 6.1 架构
其实就是多频道，多主一从
## 6.2 主机角色
| 主机角色 | 地址 | 端口 |
| -------- | --------- | ---- |
| Master1 | 10.0.0.51 | 3306 |
| Master2 | 10.0.0.52 | 3306 |
| Slave | 10.0.0.53 | 3306 |
## 6.3 配置过程
### a. GTID环境准备
```
(1) 清理环境
pkill mysqld
rm -rf /data/3306/*
\mv /etc/my.cnf /tmp
(2) 创建需要的目录
mkdir -p /data/3306/data /data/3306/binlog
chown -R mysql.mysql /data
(3) 准备配置文件
# db01
cat > /etc/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/3306/data
server_id=6
socket=/tmp/mysql.sock
log_bin=/data/3306/binlog/mysql-bin
gtid_mode=ON
enforce_gtid_consistency
log-slave-updates=1
secure-file-priv=/tmp
innodb_flush_method=O_DIRECT
slow_query_log=ON
slow_query_log_file=/data/3306/data/db01-slow.log
long_query_time=0.1
log_queries_not_using_indexes
master_info_repository=TABLE
relay_log_info_repository=TABLE
[client]
socket=/tmp/mysql.sock
[mysql]
prompt=db01 [\\d]>
socket=/tmp/mysql.sock
EOF
# db02
cat > /etc/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/3306/data
server_id=7
socket=/tmp/mysql.sock
log_bin=/data/3306/binlog/mysql-bin
gtid_mode=ON
enforce_gtid_consistency
log-slave-updates=1
secure-file-priv=/tmp
innodb_flush_method=O_DIRECT
slow_query_log=ON
slow_query_log_file=/data/3306/data/db01-slow.log
long_query_time=0.1
log_queries_not_using_indexes
master_info_repository=TABLE
relay_log_info_repository=TABLE
[client]
socket=/tmp/mysql.sock
[mysql]
prompt=db02 [\\d]>
socket=/tmp/mysql.sock
EOF
# db03
cat > /etc/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/3306/data
server_id=8
socket=/tmp/mysql.sock
log_bin=/data/3306/binlog/mysql-bin
gtid_mode=ON
enforce_gtid_consistency
log-slave-updates=1
secure-file-priv=/tmp
innodb_flush_method=O_DIRECT
slow_query_log=ON
slow_query_log_file=/data/3306/data/db01-slow.log
long_query_time=0.1
log_queries_not_using_indexes
master_info_repository=TABLE
relay_log_info_repository=TABLE
[client]
socket=/tmp/mysql.sock
[mysql]
prompt=db03 [\\d]>
socket=/tmp/mysql.sock
EOF
(4) 初始化数据
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3306/data
(5) 启动数据库
/etc/init.d/mysqld start
(6) 构建主从
# 1. 创建复制用户(主节点)
set sql_log_bin=0;
create user repl@'10.0.0.%' identified with mysql_native_password by '123';
grant replication slave on *.* to repl@'10.0.0.%' ;
set sql_log_bin=1;
```
### b. 配置多源复制
```
CHANGE MASTER TO MASTER_HOST='10.0.0.51',MASTER_USER='repl', MASTER_PASSWORD='123', MASTER_AUTO_POSITION=1 FOR CHANNEL 'Master_1';
CHANGE MASTER TO MASTER_HOST='10.0.0.52',MASTER_USER='repl', MASTER_PASSWORD='123', MASTER_AUTO_POSITION=1 FOR CHANNEL 'Master_2';
start slave for CHANNEL 'Master_1';
start slave for CHANNEL 'Master_2';
```
### c. 多源复制监控
```
db03 [(none)]>SHOW SLAVE STATUS FOR CHANNEL 'Master_1'\G
db03 [(none)]>SHOW SLAVE STATUS FOR CHANNEL 'Master_2'\G
select * from performance_schema.replication_connection_configuration\G
SELECT * FROM performance_schema.replication_connection_status WHERE CHANNEL_NAME='master_1'\G
select * from performance_schema.replication_applier_status_by_worker;
```
### d.多源复制配置过滤(黑白名单)
```
mysql> CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('db1.%') FOR CHANNEL "master_1";
mysql> CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('db2.%') FOR CHANNEL "master_2";
MASTER_USER=‘repl’, MASTER_PASSWORD=‘123’, MASTER_AUTO_POSITION=1 FOR CHANNEL ‘Master_1’;
CHANGE MASTER TO MASTER_HOST=‘10.0.0.52’,MASTER_USER=‘repl’, MASTER_PASSWORD=‘123’, MASTER_AUTO_POSITION=1 FOR CHANNEL ‘Master_2’;
start slave for CHANNEL ‘Master_1’;
start slave for CHANNEL ‘Master_2’;
### c. 多源复制监控
db03 [(none)]>SHOW SLAVE STATUS FOR CHANNEL ‘Master_1’\G
db03 [(none)]>SHOW SLAVE STATUS FOR CHANNEL ‘Master_2’\G
select * from performance_schema.replication_connection_configuration\G
SELECT * FROM performance_schema.replication_connection_status WHERE CHANNEL_NAME=‘master_1’\G
select * from performance_schema.replication_applier_status_by_worker;
### d.多源复制配置过滤(黑白名单)
mysql> CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = (‘db1.%’) FOR CHANNEL “master_1”;
mysql> CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = (‘db2.%’) FOR CHANNEL “master_2”;
扫一扫
644
到【灌水乐园】发言
