架构
RDS版本:5.6.40
ECS自建库版本:5.7.26
前置工作
配置ECS防火墙
默认入规则配置设置为drop
iptables -P INPUT DROP
只开启ssh和80端口,此外再打开MySQL端口(80端口看自身需求)
配置ECS内网IP到RDS的白名单里
ECS搭建单机MySQL
RDS物理备份还原到ECS
主库配置工作
主库需要高权限账户,创建一个用于复制的账号
create user 'rpl'@'%' identified by 'password';
grant REPLICATION SLAVE,Replication client,reload on *.* to 'rpl'@'%' identified by 'password';
从库配置工作
根据需求设置从库过滤选项(执行全库同步的忽略此步骤)
在配置文件中加入#replication,该部分用来配置从库参数
#replication
read_only=1 #根据业务需求,此处设置从库为只读
replicate-wild-do-table=xxx1.% #需要传输的库1
replicate-wild-ignore-table=mysql.% #需要过滤的库1
replicate-wild-ignore-table=xxx2.% #需要过滤的库2
删除从库中的几张表,并重新创建
reset slave;
use mysql;
drop table slave_master_info;
drop table slave_relay_log_info;
drop table slave_worker_info;
drop table innodb_index_stats;
drop table innodb_table_stats;
重新创建(mysql_system_tables.sql)
source /usr/local/mysql/share/mysql_system_tables.sql
从库创建复制账号,与主库的一致
create user 'rpl'@'%' identified by 'password';
grant REPLICATION SLAVE,Replication client,reload on *.* to 'rpl'@'%' identified by 'password';
从库创建业务用查询权限的账号
create user 'dasj'@'%' identified by 'password';
grant select on *.* to 'dasj'@'%' identified by 'password';
重启ECS端MySQL使设置生效
配置主从
change master to master_host='rm-xxxxxxxxxxx.mysql.rds.aliyuncs.com',master_user='rpl',master_password='password',master_port=3306,master_auto_position=1;
切换到主库
确认当前Executed_Gtid_Set
show master status
复制当前的Executed_Gtid_Set值
切换回从库
设置GTID_PURGED
reset master;
set global gtid_purged='14cd06a7-847a-11e8-bd61-506b4b2babde:1-4257177, 333cccc5-808b-11e8-a3bc-7cd30abeadba:1-6734';
配置完毕,启动主从
start slave;
检查状态
show slave status \G
出现双yes则表示配置成功
在线上RDS可以看到复制线程一直在工作
至此初步配置完成
关于过滤参数
replicate-wild-do-table和replicate-wild-ignore-table
replicate-do-db和replicate-ignore-db
根据网上的说法replicate-do-db和replicate-ignore-db这组参数有可能造成binlog过滤不完整的情况,因此普遍采用replicate-wild-do-table和replicate-wild-ignore-table这组参数进行数据过滤
比如不同步主库的mysql库,可以把参数写成replicate-wild-ignore-table=mysql.%
此外如果需要过滤多个库,不可以写成replicate-wild-ignore-table=mysql.%,xxx.%,yyy.%,需要分开写出来
在线设置复制过滤
过滤不需要重启数据库,但需要停sql_thread
STOP SLAVE SQL_THREAD;
CHANGE REPLICATION FILTER replicate-wild-do-table=('xxx.acp','xxx1.%')
注意在线设置的时候,写法要写在一起,而不是之前配置参数时的分开写,如果分开写的话,会导致前面的都不生效,只有最后一条生效(比如下面的2个语句,就只有xxx1.%那条生效了)
CHANGE REPLICATION FILTER replicate-wild-do-table=('xxx.acp')
CHANGE REPLICATION FILTER replicate-wild-do-table=('xxx1.%')
再开启sql_thread通过show slave status \G查看配置是否生效
START SLAVE SQL_THREAD;
报错处理
阿里金融云RDS默认不能拿到最新的binlog文件(延迟6小时),因此采用物理备份还原的数据不是最新数据,不可避免的会产生1032报错
执行show slave status \G
找到下面这2行
Retrieved_Gtid_Set: 364da72f-333f-11e8-abb2-7cd30ad3abda:104186843-104394919
Executed_Gtid_Set: 186c8f74-9620-11e9-9929-00163e0009fe:1-9166,
364da72f-333f-11e8-abb2-7cd30ad3abda:1-104394919,
b0a77135-4f35-11e7-9cb4-7cd30abeaf0e:1-47265271
Re表示Relay已接收的,Ex表示已经执行复制的
通常来说,Re大于等于Ex
此处Re为104394919,Ex也为104394919,表明数据已经完全同步
如果Re为一个远大于104394919的值,且有1032报错,则表明Ex在104394919这里卡住了,需要跳过去,跳过的值为当前Ex的GTID值+1,即:104394920
操作如下
方法1
STOP SLAVE;
SET @@SESSION.GTID_NEXT='364da72f-333f-11e8-abb2-7cd30ad3abda:104394920';
#设置空事务
BEGIN; COMMIT;
#恢复事务号
SET SESSION GTID_NEXT = AUTOMATIC;
START SLAVE;
方法2
STOP SLAVE;
RESET MASTER;
SET @@GLOBAL.GTID_PURGED ='364da72f-333f-11e8-abb2-7cd30ad3abda:1-104394920,'
START SLAVE;
此外还可以用pt系列的工具:pt-slave-restart