mysql5.7数据库主从同步(阿里RDS到ECS)

mysql5.6版本是分界线,5.6前与5.6后的mysql主从同步存在一些差异。rds与ecs自建mysql主从同步与一般的mysql主从同步操作应该是差不多一样的。建议mysql全部使用 INNODB引擎

RDS 存储类型:强烈建议选择本地SSD(支持直接备份数据库到ssd) 不要选SSD云盘

1.1、同步前的准备RDS(主库MASTR)

数据外网/内外地址:rm-akj3b4j3k3j.mysql.rds.aliyuncs.com
mysql5.7版本(主从的版本必须一致)
RDS--数据安全性--增加白名单分组,将你的从库ip地址如114.114.114.114加入
需要同步的数据库名称: testdb

在rds数据库上新建一个账号用于同步(新建用dbuser,赋予只读权限,赋予远程登陆的权限)然后刷新系统权限表flush privileges;

登录rds数据库执行下列操作并做好记录:
1、show variables like '%server_id%';    //记录下service_id 假设是51312
2、flush tables with read lock; //锁表,防止数据变动
3、show master status; //在锁表期间读取,数据库的日志文件名File及偏移地址Position
// 假设是File=mysql-bin.000152   Position=128769
4、unlock tables; //读取并记录下第3步的内容后就可以解锁表了
5、退出rds数据库(建议不要对rds主库进行任何只读外的操作!!)

1.2、同步前的准备ECS(自建数据库从库slave)

从库ecs的ip地址:114.114.114.114
mysql5.7版本(主从的版本必须一致)
(可选)centos本地防火墙开放3307端口(后面需修改从库的默认端口为3307)
(可选)ECS服务器安全组开放3307端口

修改my.conf配置文件
[mysqld]下添加或修改(添加前先查看是否已存在同名配置,一定要先检查!检查!否则你可能会发现某些配置不会生效)
port = 3307
sevice-id = 2   //与主库id要不一样,rds的service_id默认是多位的自建mysql一般都是1
replicate-do-db         =  testdb   //需要同步的数据库名字
replicate-ignore-db     =  mysql                  //不需要同步的数据库
replicate-ignore-db     =  information_schema     //不需要同步的数据库
replicate-ignore-db     =  performance_schema     //不需要同步的数据库
#replicate-wild-ignore-table     = mysql.% //不需要同步的表(可选)
#replicate-wild-ignore-table     = test.% //不需要同步的表(可选)
log-bin=mysql-bin

log-bin-index   = mysql-bin.index
relay-log                = relay-log
relay_log_index          = relay-log.index
gtid_mode=on   //配置GTID同步模式
enforce_gtid_consistency=on
log-slave-updates = 1
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
binlog_format   = ROW //binlog格式为ROW,严格同步模式默认的是mixed


保存配置后重启mysql5.7,登陆数据库查询
show variables like '%binlog_format%'; //如果值是ROW 就成功了
退出mysql

2、备份rds上需要同步过来的数据库。可以使用工具备份后拷贝到ecs服务器上

也可以选择在ecs的终端上使用命令备份远程rds上的数据库到本地
mysqldump -h RDS数据库ip -P RDS数据库端口 -u RDS数据库账号 -p RDS数据库密码 数据库名 > 导出的文件名.sql 
#远程RDS地址:rm-akj3b4j3k3j.mysql.rds.aliyuncs.com
#需要同步的数据库:testdb
# rds上mysql端口:3306
# rds上的数据库同步用户:dbuser
#备份到本地的文件名:sqltestback.sql

mysqldump -h rm-akj3b4j3k3j.mysql.rds.aliyuncs.com -P 3306 -u dbuser -p testdb > sqltestback.sql
如果上面语句报错可以使用下面语句(新增了 --set-gtid-purged=off 参数)
mysqldump -h rm-akj3b4j3k3j.mysql.rds.aliyuncs.com -P 3306 -u dbuser --set-gtid-purged=off -p testdb > sqltestback.sql

3、在ecs自建数据库导入rds主数据库的数据

登录ecs本地数据库 mysql -uroot -p
create database testdb;  //创建需要同步的数据库
use testdb;  //切换到新建的数据库
source sqltestback.sql; //将备份的sql恢复到ecs上自建的testdb数据库
//source的使用方法 source /home/admin/数据库备份.sql

//在从库ecs数据上继续配置
// 见1.1操作中 假设是File=mysql-bin.000152   Position=128769
change master to master_host='rm-akj3b4j3k3j.mysql.rds.aliyuncs.com',master_port=3306,master_user='dbuser',master_password='新增dbuser时设置的登陆密码',master_log_file='mysql-bin.000152',master_log_pos=128769; //master_log_file与master_log_pos

start slave; //启动slave;  ps:stop slave;是停止
show slave status\G  //查看从库状态:如果Slave_IO_Running 与 Slave_SQL_Running 都为Yes则配置成功

从库禁止进行出读取外的其他操作,如果对同步的testdb进行了insert操作会导致Slave_SQL_Running=NO 造成主从数据不一致。如果发生这种情况,比较笨的办法就是stop slave;停止从库的同步操作。然后把主库设置为只读后 show master status;获取File与Positon信息并导出此时的数据库备份,取消主库德只读限制。 再还覆盖原到ecs从库上后再change master to ,后面再查看show slave status\G 即可恢复正常。

参考地址: https://blog.csdn.net/weixin_42669785/article/details/88737688

GTID简介: https://www.cnblogs.com/cenalulu/p/4309009.html

MYSQL5.6 GTID: https://www.cnblogs.com/zhoujinyi/p/4717951.html

xtrabakup 热备、增量备( 备份MyISAM数据表需锁库 ): https://www.cnblogs.com/linuxk/p/9372990.html

主从同步分析: https://blog.csdn.net/z50L2O08e2u4afToR9A/article/details/80971847

基于互联网精神,在注明出处的前提下本站文章可自由转载!

本文链接:https://ranjuan.cn/mysql5-7数据库主从同步(阿里rds到ecs)/

赞赏

微信赞赏支付宝赞赏

au3连接数据库源码
p20130705
mysql5-7数据库主从同步(阿里rds到ecs)