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）/

赞赏

微信赞赏支付宝赞赏

发表评论