About SRGArticlesRecruitment Information
About SRGArticlesRecruitment Information
HOME/Articles/Achieving redundant configuration of MySQL using Orchestrator

Achieving redundant configuration of MySQL using Orchestrator

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.
 

Introduction

When using MySQL in a replication configuration, you need to consider redundancy in case the source (master) DB goes down due to some kind of failure.
We need to implement a mechanism to detect downtime and automatically promote the replica (slave),
MySQL MHA has been widely used for a long time.
Although MHA is still in active use, no new releases have been made since MySQL MHA 0.58 (released in 2018).
Therefore, we would like to introduce Orchestrator, which can provide similar functionality to MySQL MHA and is released regularly.
 

What is Orchestrator?

This is a tool for managing MySQL high availability and replication configuration, and has features such as replication visualization, slave replacement, and automatic failover.
This product was developed by Shlomi Noach and is also used by Github and Booking.com.
 
Compared to MySQL MHA, it has the following advantages:
  • You can visually check the replication status and execute failover using the GUI.
  • No need for an agent such as MHA node on the DB side
  • Simply register the source (master) and the management target will be automatically added.
  • In addition to GUI, there is CLI and HTTP API
  • No need to reconfigure after failover. MHA had to restart the process.
 

Setting up Orchestrator

Testing environment

Hostnamerole
DB1DB Source
DB2DB replica
DB3DB replica
Orc-admOrchestrator Management Server

Work with Orc-adm (administration server)

Installing Orchestrator
Run the installation script
 
Or you can enter it manually like this:
 
Installing MySQL for Management
Orchestrator requires a MySQL or SQLite backend.
This backend can also be made redundant, but for now we will keep it single-configured.
For more information about redundancy,official docPlease refer to.
 
Create a schema and user for Orchestrator in the administrative MySQL
 
Create the Orchestrator configuration file
Edit the following items:
 
PostMasterFailoverProcesses is a script that is kicked off when a failover occurs. This is where you write the process to switch the master IP.
The following is posted as a sample.
This is just a script that connects and disconnects IP addresses.
 

Work on managed DB servers

We will work on each of the managed DB1, DB2, and DB3.
Set up replication with DB1 as the source and DB2 and DB3 as replicas.
log-slave-updates
GTID can be either enabled or disabled.
 
Next, create a MySQL user that can be monitored from Orchestrator.
Run the query on the source (DB1).
 

Work with Orc-adm (administration server)

Start Orchestrator.
 

Registering a cluster using the management GUI

We could also add a cluster using the CLI, but since we have the opportunity, we will try using the GUI.
Once the startup is complete, open the following URL in your browser:
http://【Orc-adm IP】:3000
Once you see this screen, you will need to register the cluster you want to manage.
 
Select Clusters > Discover.
 
Enter the IP address of the source DB (DB1) and the MySQL port number, then click "Submit".
 
Once you have registered, return to the dashboard by going to "Clusters" → "Dashboard."
 
The DB1 I had just registered and its associated replicas DB2 and DB3 were then displayed.
 
Orchestrator setup is now complete.
Next, we will verify whether the failover can actually be performed successfully.
 

Conclusion

When using MySQL, it is essential to consider a configuration that ensures redundancy.
Orchestrator requires MySQL on the backend and has many complex functions, so it may be a little difficult to implement, but for simple use it doesn't require much more effort than MHA, so I would like to use it actively.
In addition to the MHA and Orchestrator introduced here, there is also a system called InnoDB Cluster that is officially available from MySQL, so I hope to introduce it to you someday.
 
SRG is looking for people to work with us. If you are interested, please contact us here.