MySql教程

aws-rds for mysql 5.7.34搭建备库

本文主要是介绍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)

 

这篇关于aws-rds for mysql 5.7.34搭建备库的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!