About SRGTechnical ArticlesRecruitment Information
About SRGTechnical ArticlesRecruitment Information
HOME/Technical 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) is a group that mainly provides cross-sectional support for the infrastructure of our media services, improving existing services, launching new ones, and contributing to OSS.
This article is located in SRGDBWGWe will publish materials regarding the database provided by the DB Working Group for 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 ifA profound reasonIf you need to install and operate MySQL on EC2,
The configuration must ensure DB 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 used:
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 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
 
  • Preparing 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
The virtual VIP in AWS must be outside the CIDR range of the VPC.
So first, check the CIDR of the VPC where the EC2 instance you created is located.
In this example, 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 where 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, it will automatically become an ENI 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 the target ENI, click "Change Source/Destination Check", and uncheck "Enabled".
ENIのチェック無効
ENI check disabled
 

Operations on the master DB DB1

  • Assign a virtual IP address only to the master DB of the target EC2 instance.
  • Creating 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, you can configure MySQL with minimum redundancy.
Managed services that can be used without having to worry about these things are truly great.
 

Conclusion

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