aws-rds for mysql 5.7.34搭建备库

环境说明：aws rds开启gtid
自建mysql也开启gtid

aws主从复制相关命令：
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/mysql_rds_stop_replication.html

1、在自建数据库上备份数据库,只备份业务库

mysqldump -h127.0.0.1 -uroot -pc123456 -P3357 -B ceshi czg --master-data=2 > mysql_bak.sql

 

2、记录备份位点

head -n 30 mysql_bak.sql
保存一下，
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=3763;

 

3、恢复备份数据到AWS

mysql -hczg.ckmuhrbhkmox.ap-east-1.rds.amazonaws.com -uroot -p123456 -P3306 < mysql_bak.sql

 

4、在AWS上以位点形式搭主从同步

#先写一条原生的命令，但AWS不能用
change master to master_host='16.162.88.254',master_port=3357,master_user='root',master_password='c123456',master_log_file='mysql-bin.000006',master_log_pos=3763;

 

4.1停止之前的主从同步

mysql> CALL mysql.rds_stop_replication;
+-----------------------------------------------------------------------------+
| Message |
+-----------------------------------------------------------------------------+
| Slave is already stopped or may not be configured. Run SHOW SLAVE STATUS\G; |
+-----------------------------------------------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

 

4.2配置同步复制
参考文档：https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/mysql_rds_set_external_master.html

CALL mysql.rds_set_external_master ('16.162.88.254',3357,'root','c123456','mysql-bin.000006',3763,0);

 

 

4.3启用同步复制

CALL mysql.rds_start_replication;

 

4.4验证测试

自建数据库操作如下：
mysql> create database aws_aliyun;
Query OK, 1 row affected (0.00 sec)

mysql> use aws_aliyun;
Database changed
mysql> create table t1(id int primary key);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into aws_aliyun.t1 values(1);
Query OK, 1 row affected (0.01 sec)

 

结论：在AWS RDS可正常查询数据。

 

5.在AWS上可以以GTID模式主从同步

CALL mysql.rds_set_master_auto_position(1);

 

 

常用命令小结：

CALL mysql.rds_reset_external_master; 等价于 reset slave

 

 

测试过程中遇到的一个bug:
使用 mysql.rds_set_external_master 存储过程报错后，会将会话级 sql_log_bin 参数值修改为 off,导致当前会话的DML操作都不写binlog。

mysql> show variables like '%sql_log_bin%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin | ON |
+---------------+-------+
1 row in set (0.00 sec)

mysql> CALL mysql.rds_start_replication;
+-------------------------+
| Message |
+-------------------------+
| Slave running normally. |
+-------------------------+
1 row in set (1.01 sec)

Query OK, 0 rows affected (1.01 sec)

mysql> show variables like '%sql_log_bin%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin | ON |
+---------------+-------+
1 row in set (0.00 sec)

mysql> CALL mysql.rds_set_external_master ('16.162.88.254',3357,'root','c123456','mysql-bin.000007',1212,0);
ERROR 3081 (HY000): This operation cannot be performed with running replication threads; run STOP SLAVE FOR CHANNEL '' first
mysql> show variables like '%sql_log_bin%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin | OFF |
+---------------+-------+
1 row in set (0.01 sec)