MySQL Multi-Master replica on EC2
A few days ago, I have been looking for a solution where I can use a single EC2 instance to replicate multiple RDS mysql masters.
I did my research and realize that there is not a single blog that defines it completely step by step.
Generally, this topic has been on the boom for the purpose of cost optimization, for the techies who do not want to use aws-managed RDS replicas and for them who want to explore the insides.
What is the requirement:
You can read this to differentiate RDS vs Mysql on EC2.
Mysql on RDS vs mysql on EC2
We are using EC2 just for the replicas. Basically, we need to manage replicas of multiple MySQL masters on a single instance.
The first thing that will come in your mind is, how we are gonna do this in a single instance?
The answer is “Channels”, Mysql Channels are used to perform parallel replica operations.
one channel for each replica, that’s how you manage the multi-master replication.
Let’s do this
- Launch an EC2 instance, Install MySQL on that.
2. Login to master DB and run the command “show master status” and note down the binlog file and position.
Note: Check the binlog retention and make it to 24 hours by using the below command: (Only for RDS Master)
[call mysql.rds_set_configuration(‘binlog retention hours’, 24);]
3. Take the dump of master using below command:
mysqldump -u [user] -p [Password] -h[host] — lock-tables=false — single-transaction — routines — triggers — all-databases | gzip -9 > [backupname].sql.gz
4. Restore dump using this command:
gunzip < [backup name] | mysql -u [uname] -p[pass]
5. After restoring the DBs you will need a backup of users from master:: In most cases, it will start replicating users after starting the replication, but it’s good to be safer side.
If you get some “ALTER” error while restoring the users after starting the replication it means your users are already replicated.
Take dump of users from master
i) pt-show-grants — host [host] — user [uname] — password [password] — ignore root@localhost,’mysql.session’@’localhost’,rdsadmin@localhost,’rdsrepladmin’@’%’ > grants.sql
Restore Users to EC2 replica
ii) mysql -h [host] -u [unane] -p [password] < grants.sql
6. Now, go to /etc/mysql/mysql.cnf and add below lines under [mysqld] section::
This is to enable GroupBy in SQL queries.
“sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”
Change bind-address to 0.0.0.0 to bind to all interfaces.
bind-address = 0.0.0.0
Set your replica as read-only so nobody can make write operations
read_only = 1
This is needed to start the replication
master-info-repository=table
relay-log-info-repository=table
7. Finally, configure the replication
“ change master to master_host=[host],master_user=[replicauser], master_password=[replicauserpassword], master_log_file=[filename you have noted down of master], master_log_pos=[position you have noted down of master] FOR CHANNEL [Any channel name];”
8. Start the Replication:
START SLAVE FOR CHANNEL “Channel Name”
9. Check the replication Status:
SHOW SLAVE STATUS FOR CHANNEL “ChannelName”
10. If you are getting any error while starting the replication, and an output of command “show slave status”,
then note down the error number, and add that in mysql.cnf with the below parameter and restart MySQL.
slave-skip-errors = ErrorNumber (You can add multiple error numbers separated with comma)
If you want to use Multiple Master RDSs and single ec2 Replica then follow the same steps with changing the channel names.
Monitoring
for the monitoring purpose, you can use mysqld_exporter with Prometheus and Grafana.
