MySQL External-based Replication in Alibaba Cloud

Image for post
Image for post

By Ankit Kapoor, Database Senior Solution Architect

In this MySQL based article we are going to discuss about:

  1. What is external-based replication?
  2. Why we need it?
  3. How can we achieve it with Alibaba Cloud ApsaraDB RDS for MySQL?
  4. Limitations.

Prerequisites:

In order for you to fully understand the concepts presented in this blog, you should already be well aware about:

  1. What is replication & its terminology in MySQL.
  2. What is classic file-based replication.
  3. What is GTID based replication.
  4. What is asynchronous and synchronous replication.
  5. How to install MySQL.

1. What Is External-based Replication ?

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.

2. Why Do We Need It?

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.

3. How Can We Achieve It with Alibaba Cloud RDS?

I have replicated below scenario where I wanted to have below architecture:

Image for post
Image for post

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=ON

20. 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.

4. Limitations:

We don’t support Classic file based replication currently. But if you have standalone RDS MySQL instance then you can set classic file based replication with RDS as master or slave.

RDS as slave is not supported currently via GTID based but you can achieve this by using DTS tool.For more information you can visit below documentation:

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

Original Source:

Written by

Follow me to keep abreast with the latest technology news, industry insights, and developer trends.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store