By Ankit Kapoor, Database Senior Solution Architect, Alibaba Cloud
In this MySQL based article we are going to discuss about:
In order for you to fully understand the concepts presented in this blog, you should already be well aware about:
External based replication is one of the architecture of replication's topology between an [ApsaraDB RDS] and on-premises Database server. In this architecture we can have RDS as master and on-prem DB as slave. We can also construct RDS as slave with on-prem DB as its master. In this article we will consider this architecture for MySQL and how can we deploy it in our production and staging environment. In this article, I am going to have Master on RDS MySQL 5.7 and slave on MySQL 8.0 version & reason behind this is to know what are the errors we can face while setting up this architecture and how can we resolve it. Having plain setup is simple and will not produce any errors.
Please note that currently we don't support external based replication of architecture in which RDS is slave with on-prem DB server as Master.
Main purpose of having this architecture may vary for different business requirement. One of the business requirements I have seen is where we want one of our database servers to act as warehouse or OLAP and we want it to self-managed. In some cases COST can also be counted as a factor to adopt such architecture.
Main purpose of having RDS as a slave is because we want to keep our data safe and thus we opt for managed services. In times of crash recovery or backup recovery or to generate BI -report we can use this RDS and doesn't worry about the monitoring or managing it.
I have replicated below scenario where I wanted to have below architecture:
1. Login to your RDS console https://rdsnext.console.aliyun.com/
2. Go to Products and locate Relational Database Services.
3. Click on create instance.
4. Make ensure that you setup below configuration:
5. Click next.
6. You will land to Instance configuration page.
7. Make ensure that you have your VPC and vswitch must be setup already.
8. Click on next and you will land to confirm order page.
9. Tick Terms and Services and confirm your order.
10. Wait for few minutes until your RDS instance gets ready.
11. Once ready click on the instance and you will land to below page:
12. Click on Configure Whitelist.
13. Click on Create Whitelist and add the IP of your on-prem server.
14. For testing purpose, I have made this RDS as public and put 0.0.0.0 in whitelisting.
15. Make ensure that public endpoint of this RDS must exist so that you can connect from outside VPC. To apply for the public end point, you can follow steps mentioned in below link: https://www.alibabacloud.com/help/doc-detail/26128.htm
16. Create Privileged account for RDS MySQL . For this please follow below link: https://www.alibabacloud.com/help/doc-detail/87038.htm
We will need this in creating replication user.
17. Connect to the RDS via DMS or MySQL client on ECS or on your local machine. For DMS, you can login via console only.
18. Once RDS is setup, please install MySQL on your local machine. You can follow Oracle guide on this. It is solely depend on you how to install MySQL . Either via RPM, binary files or DMZ package.
19. Once Database has been started, please enable gtid mode and put below configuration in your cnf file. I am pasting a sample config file for this:
[mysqld]
user=_mysql
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
plugin-dir=/usr/local/mysql/lib/plugin
log-error=/usr/local/mysql/data/mysqld.local.err
pid-file=/usr/local/mysql/data/mysqld.local.pid
keyring-file-data=/usr/local/mysql/keyring/keyring
early-plugin-load=keyring_file=keyring_file.so
log-bin=mybinlog
server-id=33
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON20. Login to your RDS DB server.
21. Take the backup of RDS database using mysqldump. This has
22. been done to make ensure that all transactions are in GTID mode.
23. Restore it on your on-prem DB server.
24. Once done, please configure the master at on-prem DB server :
mysql> change master to
-> master_host='IP',
-> master_user='username',
-> master_password='password',
-> master_auto_position=1;
mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: rm-6gj5q0vyqonif43jwyo.mysql.ap-south-1.rds.aliyuncs.com
Master_User: replica
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000037
Read_Master_Log_Pos: 83548
Relay_Log_File: Ankits-MacBook-Pro-relay-bin.000002
Relay_Log_Pos: 720
Relay_Master_Log_File: mysql-bin.000037
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 13146
Last_Error: Column 1 of table 'mysql.ha_health_check' cannot be converted from type 'char(3(bytes))' to type 'char(4(bytes) utf8mb4)'
Skip_Counter: 0
Exec_Master_Log_Pos: 76809
Relay_Log_Space: 7680
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 13146
Last_SQL_Error: Column 1 of table 'mysql.ha_health_check' cannot be converted from type 'char(3(bytes))' to type 'char(4(bytes) utf8mb4)'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3299953582
Master_UUID: 1094aa27-56e4-11ea-9428-00163e01327b
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 200303 17:23:57
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 1094aa27-56e4-11ea-9428-00163e01327b:91015-91038
Executed_Gtid_Set: 1094aa27-56e4-11ea-9428-00163e01327b:1-91015
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)25. This error has been introduced because of the different versions between master and slave. Let's see how can we resolve it
As per error, table structure seems to be different. We can fix this error by having similar structure at both end and then run start slave . I have pasted my output . Make ensure that you should take data from source too.
mysql> CREATE TABLE `ha_health_check` (
-> `id` bigint(20) DEFAULT '0',
-> `type` char(1) NOT NULL DEFAULT '0',
-> PRIMARY KEY (`type`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: rm-6gj5q0vyqonif43jwyo.mysql.ap-south-1.rds.aliyuncs.com
Master_User: replica
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000037
Read_Master_Log_Pos: 360025
Relay_Log_File: Ankits-MacBook-Pro-relay-bin.000002
Relay_Log_Pos: 283936
Relay_Master_Log_File: mysql-bin.000037
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 360025
Relay_Log_Space: 284157
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3299953582
Master_UUID: 1094aa27-56e4-11ea-9428-00163e01327b
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 1094aa27-56e4-11ea-9428-00163e01327b:91015-91980
Executed_Gtid_Set: 1094aa27-56e4-11ea-9428-00163e01327b:1-91980,
9f4302de-56ef-11ea-ab0e-8372b01c7d1e:1-4
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)26. Hence this will start the external replication.
https://www.alibabacloud.com/help/product/26590.htm?spm=a3c0i.126076.1204981.3.6295155aFo7R9z
Alibaba Cloud ApsaraDB RDS for MySQL is a stable, reliable, and scalable online database service. Based on Alibaba Cloud distributed file system and high-performance SSD storage, ApsaraDB RDS for MySQL features disaster tolerance, backup, recovery, monitoring, and migration capabilities to facilitate database operations and maintenance. To learn more, visit the official product page
Data Encryption, Semi Masking and Recover Deleted Data Using DMS
4 posts | 0 followers
FollowAlibaba Clouder - February 13, 2019
Alibaba Clouder - July 1, 2019
Alibaba Clouder - September 10, 2018
Alibaba Clouder - October 22, 2018
Alibaba Clouder - March 16, 2017
ApsaraDB - October 26, 2023
4 posts | 0 followers
Follow
Database Overview
ApsaraDB: Faster, Stronger, More Secure
Learn More
Database Backup
A reliable, cost-efficient backup service for continuous data protection.
Learn More
PolarDB for MySQL
Alibaba Cloud PolarDB for MySQL is a cloud-native relational database service 100% compatible with MySQL.
Learn More
Tair
Tair is a Redis-compatible in-memory database service that provides a variety of data structures and enterprise-level capabilities.
Learn MoreMore Posts by Your Friend in a need