About SRGTechnical articleRecruitment Information
About SRGTechnical articleRecruitment Information
HOME/Technical article/Making MySQL running on Amazon EC2 redundant using MHA

Making MySQL running on Amazon EC2 redundant using MHA

This is Onikai (@fat47) from the Service Reliability Group (SRG) of the Technology Division.
#SRGThe Service Reliability Group primarily provides comprehensive support for the infrastructure surrounding our media services, focusing on improving existing services, launching new ones, and contributing to open-source software (OSS).
This article is located within SRG.DBWGThe DB Working Group is releasing database-related materials that it provides to the entire company.
I hope this is of some help.
 

overview

When running MySQL on AWS, you would typically use Amazon RDS/Aurora.
But ifProfound reasonIf you need to install and operate MySQL on EC2,
The database must be configured to ensure redundancy.
This article describes how to implement failover in an EC2 environment using MySQL-MHA.
 

Server Information

The following four EC2 instances will appear.
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

MHA Installation

MHA Node

Installation target: All servers (DB1, DB2, DB3, MHA-ADMIN)
Obtaining the rpm package:https://github.com/yoshinorim/mha4mysql-node/releases
  • install
 
  • Create necessary directories
 
  • public key exchange
 
  • Add the hostname (which should be the same as the EC2 tag's Name) and the EC2 IP address to the hosts file of all servers.
 

MHA Manager

Installation target: MHA-ADMIN
Obtaining the rpm package:https://github.com/yoshinorim/mha4mysql-manager/releases/tag/v0.58
  • install
 
  • MHA Manager systemd settings
 
  • MHA Manager configuration
 
  • Preparing the override script
 
  • Route table rewriting script
This is the script that is called within the failover script mentioned above.
 
  • Grant execution permissions
 

Operations in the AWS Management Console

  • Specifying a virtual IP address
In AWS, the virtual VIP must be an IP address outside the VPC's CIDR range.
So, first, let's check the CIDR of the VPC where the EC2 instance we created is located.
For this example, we will assume the IP address is "10.91.16.0/23".
The IP range will be 10.91.16.0 - 10.91.17.255,
The following will be used as the virtual IP address for this case, as it is an IP address outside the range.
【10.100.0.1/32】
 
  • Editing the route table
Edit the route table used by the subnet where the EC2 instance is located.
The destination for the virtual IP 10.100.0.1/32 will be set to the specified EC2 instance ID.
When performing a failover, this target should be the ID of the EC2 instance that will be failovered to.
*Even if you specify an instance ID, it will automatically be associated with that instance as the ENI, but that's okay.
ルートテーブル
Route table
 
  • Disable ENI source/destination checks.
We will modify the ENI settings for all target EC2 instances.
Right-click the target ENI and select "Change Source/Destination Checks" to uncheck the enable option.
ENIのチェック無効
ENI check disabled
 

Operations on DB1 of the master DB

  • 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, the process is complete.
 
If there are no problems, let's start the MHA manager process.
 

summary

Even if RDS/Aurora is unavailable, you can still configure MySQL with a minimum level of redundancy.
Managed services that you can use without having to think about these things are truly great.
 

In conclusion

SRG is looking for new team members. If you are interested, please contact us here.