多源复制

        多主复制，从库接收多个主库的binlog 进行重放, 用于多个主库数据备份到单个从库, 以及分库分表场景中

slave_parallel_workers = N

# 多源复制中必开
master_info_repository = table
relay_log_info_repository = table

1 传统模式

master1&2 
	select * from performance_schema.global_varibales where variable_name regexp 'enforce_gtid_consistency|gtid_mode';
	set global slave_parallel_workers = 0
	show master status  #file & position

slave1 
	change master to master_host="master1_ip", master_user='repl', master_password="xxx", master_port=3306, master_log_file="file", master_log_pos="position" for channel "master1";

	change master to master_host="master2_ip", master_user='repl', master_password="xxx", master_port=3306, master_log_file="file", master_log_pos="position" for channel "master2";
	
	start slave for channel 'master1'
	start slave for channel 'master2'
	
	show slave status for channel 'master1';
	show slave status for channel 'master2';
	
	ll /data/mysqldata/relaylog/
	# mysql-relay-bin-master1@xxxx.000001
	# mysql-relay-bin-master2@xxxx.000001
	
	
	# 2个I/O  2个 SQL线程
	select name,processlist_id, command, processlist_state, processlist_info,thread_os_id from performance_schema.threads
	where name regexp 'thread/sql/slave_io|thread/sql/slave_sql|thread/sql/slave_worker'
	
	show slave status \G;  # 会有两个 channel_name;  master1; master2 
	
slave1:
		show variables like "%parallel%"
			#slave_parallel_type = logical_clock;
			#slave_parallel_workers 0;
		set global slave_parallel_workers =16;
		stop slave;
		start slave;
		
	# 2个I/O  32个 SQL线程
	select name,processlist_id, command, processlist_state, processlist_info,thread_os_id from performance_schema.threads
	where name regexp 'thread/sql/slave_io|thread/sql/slave_sql|thread/sql/slave_worker'

2  GTID模式

master:
	select * from performance_schema.global_varibales where variable_name regexp 'enforce_gtid_consistency|gtid_mode';
		# enforce_gtid_consistency = on
		# gtid_mode = on 
	
	slave: 
	  change master to master_host="master1_ip", master_user='repl', master_password="xxx", master_port=3306, master_auto_position=1  for channel "master1";

	  change master to master_host="master2_ip", master_user='repl', master_password="xxx", master_port=3306,master_auto_position=1  for channel "master2";
	  
	  start slave for channel 'master1'
	  start slave for channel 'master2'
	  show slave status for channel 'master1';
	  show slave status for channel 'master2';

3  多源复制相关语句补充

1 启动/停止指定通道内相关线程 I/O  SQL 
    start/STOP SLAVE thread_types for channel  channel_name;
		
2 清理复制配置信息
	RESET SLAVE;  #所有通道
	RESET SLAVE FOR CHANNEL channel_name;
		
3 查看多源复制中的复制线程状态
	SHOW SLAVE STATUS \G; #所有通道下
	SHOW SLAVE STATUS FOR CHANNEL  channel_name; # 指定通道下复制信息

4 通过performance_schema下查看
	select * from performance_schema.replication_connection_status \G;  #显示两个通道

5 #查看协调器线程的状态信息

select * from performance_schema.replication_applier_status_by_coordinator;
	
CHANNEL_NAME 
THREAD_ID The SQL/coordinator thread ID
SERVICE_STATE ON (thread exists and is active or idle) or OFF (thread no longer exists).

6 #查看worker表工作状态

select * from performance_schema.replication_applier_status_by_worker;

CHANNEL_NAME 
WORKER_ID 
THREAD_ID The SQL/coordinator thread ID
SERVICE_STATE ON (thread exists and is active or idle) or OFF (thread no longer exists).

7 通过mysql系统库下的复制状态表查看
	
# slave_master_info表记录I/O线程状态信息 可以指定通道 where channel_name = "xxx" 
select * from mysql.slave_master_info \G;
	
# slave_relay_log_info 记录SQL线程状态信息，可以指定通道 where channel_name = "xxx" 
select * from mysql.slave_relay_log_info \G

过滤复制

• 1 规划好复制过滤，通过my.cnf 配置选项实现复制过滤
• 2 过程中产生新需求, 利用新增语句 CHANGE REPLICATION FILTER 语句新增过滤复制, 重启SQL线程

1 通过配置文件实现(master1 &  slave1&2&3)

#slave1&2&3配置文件

replicate_wild_do_table=databaseA.%
replicate_wild_do_table=databaseB.%
replicate_wild_do_table=databaseC.%

change master to master_host="master1_ip", master_user= , master_host, master_password, master_port, master_auto_position=1;

start slave;
show slave status \G;
#replicate_wild_do_table: databasexxx.%; #不同过滤复制 过滤选项不同

2 通过CHANGE REPLICATION FILTER 语句新增过滤复制,

# 使用 change replication filter 修改复制过滤规则, 要指定原过滤规则,否则会覆盖原规则

stop slave sql_thread;
change replication filter replicate_wild_do_table = ('databaseA.%','database_new.%');
show slave status \G;  # 查看 replicate_wild_do_table 过滤规则
start slave sql_thread;
# 命令仅仅是临时修改，固化配置文件
[mysqld]
replicate_wild_do_table=databaseA.%
replicate_wild_do_table=database_new.%

mysql8.0 的performance_schema 提供两张表查看复制过滤规则
	replication_applier_filters # 查看指定复制通道配置的复制过滤规则
	replication_applier_global_filters  #未指定复制通道，全局生效
	
	replication_applier_filters
	#channel_name; filter_name(replicate_wild_do_table), filter_rule: databaseA.%, configured by(change_replication_filter)