MySQL 过滤复制+复制映射

mysqldump  --single-transaction   --master-data=2 --set-gtid-purged=OFF  -B site  site_ethic site_asset  site_finance  site_common site_sae  iit_visit> /data/backup/20210506-1507-192.168.104.101-site_dw.sql 

sed -i 's/^CREATE DATABASE/-- CREATE DATABASE/g' 20210506-1507-192.168.104.101-site_dw.sql
sed -i 's/^USE `site`/-- USE `site`/g' 20210506-1507-192.168.104.101-site_dw.sql
sed -i 's/^USE `site_ethic`/-- USE `site_ethic`/g' 20210506-1507-192.168.104.101-site_dw.sql
sed -i 's/^USE `site_asset`/-- USE `site_asset`/g' 20210506-1507-192.168.104.101-site_dw.sql
sed -i 's/^USE `site_finance`/-- USE `site_finance`/g' 20210506-1507-192.168.104.101-site_dw.sql
sed -i 's/^USE `site_common`/-- USE `site_common`/g' 20210506-1507-192.168.104.101-site_dw.sql
sed -i 's/^USE `site_sae`/-- USE `site_sae`/g' 20210506-1507-192.168.104.101-site_dw.sql
sed -i 's/^USE `iit_visit`/-- USE `iit_visit`/g' 20210506-1507-192.168.104.101-site_dw.sql


主库创建复制账号：
create user 'slave'@'192.168.104.%' identified by 'slave@TM';
grant replication slave on *.* to 'slave'@'192.168.104.%';
flush privileges;


从库配置参数，并重启数据库
replicate_wild_do_table=site_dw.%
replicate-rewrite-db=site -> site_dw
replicate-rewrite-db=site_ethic -> site_dw
replicate-rewrite-db=site_asset -> site_dw
replicate-rewrite-db=site_finance -> site_dw
replicate-rewrite-db=site_common -> site_dw
replicate-rewrite-db=site_sae -> site_dw
replicate-rewrite-db=iit_visit -> site_dw

 
change master to master_host='192.168.104.101',master_user='slave',master_password='slave@TM',master_port=3306,MASTER_LOG_FILE='mysql-bin.044809', MASTER_LOG_POS=15029505;

start slave;



CHANGE REPLICATION FILTER Replicate_Rewrite_DB=( (site,site_dw),(site_ethic,site_dw),(site_asset,site_dw),(site_finance,site_dw),(site_common,site_dw),(site_sae,site_dw),(iit_visit,site_dw) )