About SRGArticlesRecruitment Information
About SRGArticlesRecruitment Information
HOME/Articles/Using MHA to make MySQL running on Amazon EC2 redundant

Using MHA to make MySQL running on Amazon EC2 redundant

This is Oniumi (@fat47) from the Service Reliability Group (SRG) of the Technology Headquarters.
#SRG(Service Reliability Group) mainly provides cross-sectional support for the infrastructure of our media services, improving existing services, launching new ones, contributing to OSS, etc.
This article is located in SRGDBWGWe will publish materials regarding the database that the (DB Working Group) provides to the entire company.
I hope this helps in some way.
 

overview

When running MySQL on AWS, you would normally use Amazon RDS/Aurora.
But what ifThe profound reasonIf you need to run MySQL on EC2,
The configuration must ensure database redundancy.
In this article, we will introduce how to achieve failover in an EC2 environment using MySQL-MHA.
 

Server Information

The following four EC2 instances will be featured:
HostnameroleVersion Information
DB1DB MasterMySQL5.7 , MHA-node 0.58
DB2DB SlaveMySQL5.7 , MHA-node 0.58
DB3DB SlaveMySQL5.7 , MHA-node 0.58
MHA-ADMINMHA ManagerMHA-manager 0.58

Installing MHA

MHA Node

Installation target: All servers (DB1, DB2, DB3, MHA-ADMIN)
Get the rpm package:https://github.com/yoshinorim/mha4mysql-node/releases
  • install
 
  • Create necessary directories
 
  • Public Key Exchange
 
  • Enter the host name (the same as the Name of the EC2 tag) and the IP address of the EC2 in the hosts file of all servers.
 

MHA Manager

Installation target: MHA-ADMIN
Get the rpm package:https://github.com/yoshinorim/mha4mysql-manager/releases/tag/v0.58
  • install
 
  • MHA Manager systemd configuration
 
  • MHA Manager conf
 
  • Prepare the failover script
 
  • Route table rewrite script
This is the script called in the failover script above.
 
  • Grant execution permission
 

Operations on the AWS Management Console

  • Specifying the Virtual IP
A virtual VIP in AWS must be specified outside the VPC's CIDR range.
So first, check the CIDR of the VPC in which the EC2 instance you created is located.
In this case, we will assume it is "10.91.16.0/23".
The IP range is 10.91.16.0 - 10.91.17.255, so
The following IPs are outside the range and will be used as virtual IPs in this case.
【10.100.0.1/32】
 
  • Editing a Route Table
Edit the route table used by the subnet in which the EC2 instance is located.
Set the destination for the virtual IP 10.100.0.1/32 to the specified EC2 instance ID.
When failing over, set this target to the ID of the EC2 instance to which you want to fail over.
*Even if you specify an instance ID, the ENI will automatically be linked to that instance, but this is okay.
ルートテーブル
Route Table
 
  • Disable ENI source/destination check
Change the ENI settings for all target EC2 instances.
Right-click on the target ENI, click "Change Source/Destination Check" and uncheck Enable.
ENIのチェック無効
ENI check disabled
 

Operations on Master DB DB1

  • Assign a virtual IP address only to the master DB of the target EC2 instance.
  • Create a MySQL user for MHA

Operation check

Run the check script on the MHA-ADMIN server and if there are no problems, you're done.
 
If there are no problems, start the MHA manager process.
 

summary

Even if RDS/Aurora is not available, it is possible to configure MySQL with minimum redundancy.
It's really great to have a managed service that you can use without having to worry about these things.
 

Conclusion

SRG is looking for people to work with us. If you are interested, please contact us here.