Here We have below nodes :
Orchestrator Node : FlyWithEdu-Monitor : 172.31.46.134
Source/Master Node : FlyWithEduDB1 : 172.31.18.17
Replica/Slave Node : FlyWithEduDB2 : 172.31.7.71
Table of Contents
Pre-requisites
— Enabled below parameters on all database nodes of replication topology
root@FlyWithEduDB1:~# cat /etc/mysql/percona-server.conf.d/mysqld.cnf |grep -A 7 -i Orchestrator
##Orchestrator
report_host=172.31.18.17
report_port=3306
log-slave-updates
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository= TABLE
read_only=1
root@FlyWithEduDB2:~# cat /etc/mysql/percona-server.conf.d/mysqld.cnf |grep -A 7 -i Orchestrator
##Orchestrator
report_host=172.31.7.71
report_port=3306
log-slave-updates
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE
read_only=1
— Create MySQLTopologyUser (MySQLTopologyPassword) for orchestrator configuration on Source/Master database node.
GRANT RELOAD, PROCESS, SUPER, REPLICATION SLAVE ON *.* TO 'orchestrator'@'172.31.%' IDENTIFIED BY '****************';
GRANT DROP ON `_pseudo_gtid_`.* TO 'orchestrator'@'172.31.%';
GRANT SELECT ON `mysql`.`slave_master_info` TO 'orchestrator'@'172.31.%';
Installation
— On orchestrator node (FlyWithEdu-Monitor), Download and install below packages from Orchestrator GitHub release section. https://github.com/github/orchestrator/releases
- orchestrator
- orchestrator-cli
- orchestrator-client
root@FlyWithEdu-Monitor:~# wget https://github.com/openark/orchestrator/releases/download/v3.2.2/orchestrator_3.2.2_amd64.deb
root@FlyWithEdu-Monitor:~# wget https://github.com/openark/orchestrator/releases/download/v3.2.2/orchestrator-cli_3.2.2_amd64.deb
root@FlyWithEdu-Monitor:~# wget https://github.com/openark/orchestrator/releases/download/v3.2.2/orchestrator-client_3.2.2_amd64.deb
— On orchestrator node(FlyWithEdu-Monitor), Install MySQL database.
root@FlyWithEdu-Monitor:~# apt install percona-server-server-5.7
— On orchestrator node(FlyWithEdu-Monitor), create orchestrator database.
FlyWithEdu-Monitor > create database orchestrator;
— On orchestrator node(FlyWithEdu-Monitor), Create MySQLOrchestratorUser(MySQLOrchestratorPassword) user account.
FlyWithEdu-Monitor > GRANT ALL PRIVILEGES ON *.* TO 'orchestrator'@'172.31.%' IDENTIFIED BY '****************';
Configuration
— Create orchestrator config file from one of the sample template
root@FlyWithEdu-Monitor:~# cd /usr/local/orchestrator
root@FlyWithEdu-Monitor:/usr/local/orchestrator# cp orchestrator-sample.conf.json /etc/orchestrator.conf.json
— Add or update below parameters inside orchestrator config file (/etc/orchestrator.conf.json).
"MySQLTopologyUser": "orchestrator",
"MySQLTopologyPassword": "xxxxxxxxx",
"MySQLOrchestratorUser": "orchestrator",
"MySQLOrchestratorPassword": "xxxxxxxxx",
"ApplyMySQLPromotionAfterMasterFailover": true,
"MasterFailoverDetachSlaveMasterHost": true,
"AutoPseudoGTID": true,
"UseSuperReadOnly": true,
— Start orchestrator service.
root@FlyWithEdu-Monitor:~# systemctl start orchestrator
Discover
— To search replication/cluster topology, run below command with any node of replication setup.
root@FlyWithEdu-Monitor:~# orchestrator-client -c discover -i FlyWithEduDB1
FlyWithEduDB1:3306
root@FlyWithEdu-Monitor:~#
— Check list of available cluster aliases
root@FlyWithEdu-Monitor:~# orchestrator-client -c clusters-alias
FlyWithEduDB1:3306,FlyWithEduDB1
root@FlyWithEdu-Monitor:~#
— Check replication architecture of any cluster alias
root@FlyWithEdu-Monitor:~# orchestrator-client -c topology -a FlyWithEduDB1
FlyWithEduDB1:3306 [0s,ok,5.7.29-32-log,rw,ROW,>>,P-GTID]
+ FlyWithEduDB2:3306 [0s,ok,5.7.29-32-log,ro,ROW,>>,P-GTID]
root@FlyWithEdu-Monitor:~#
Graceful Failover To FlyWithEduDB2
— Performing graceful failover once, we have Source/Master node is ONLINE.
— Current replication topology
root@FlyWithEdu-Monitor:~# orchestrator-client -c topology -a FlyWithEduDB1
FlyWithEduDB1:3306 [0s,ok,5.7.29-32-log,rw,ROW,>>,P-GTID]
+ FlyWithEduDB2:3306 [0s,ok,5.7.29-32-log,ro,ROW,>>,P-GTID]
root@FlyWithEdu-Monitor:~#
— Graceful master takeover to FlyWithEduDB2
root@FlyWithEdu-Monitor:~# orchestrator-client -c graceful-master-takeover -a FlyWithEduDB1 -d FlyWithEduDB2
FlyWithEduDB2:3306
root@FlyWithEdu-Monitor:~#
— Check replication topology
root@FlyWithEdu-Monitor:~# orchestrator-client -c topology -a FlyWithEduDB1
FlyWithEduDB2:3306 [0s,ok,5.7.29-32-log,rw,ROW,>>,P-GTID]
- FlyWithEduDB1:3306 [null,nonreplicating,5.7.29-32-log,ro,ROW,>>,P-GTID,downtimed]
root@FlyWithEdu-Monitor:~#
— Start replication on old source/master node ie on FlyWithEduDB1
root@FlyWithEdu-Monitor:~# orchestrator-client -c start-replica -a FlyWithEduDB1 -i FlyWithEduDB1
FlyWithEduDB1:3306
root@FlyWithEdu-Monitor:~# orchestrator-client -c topology -a FlyWithEduDB1
FlyWithEduDB2:3306 [0s,ok,5.7.29-32-log,rw,ROW,>>,P-GTID]
+ FlyWithEduDB1:3306 [0s,ok,5.7.29-32-log,ro,ROW,>>,P-GTID]
root@FlyWithEdu-Monitor:~#
Forceful Failover To FlyWithEduDB2
— Forceful failover can be performed once the source/master node is dead/offline.
— Replication topology once source/master node is dead/offline.
root@FlyWithEdu-Monitor:~# orchestrator-client -c topology -a FlyWithEduDB1
FlyWithEduDB1:3306 [unknown,invalid,5.7.29-32-log,rw,ROW,>>,P-GTID]
- FlyWithEduDB2:3306 [null,nonreplicating,5.7.29-32-log,ro,ROW,>>,P-GTID]
root@FlyWithEdu-Monitor:~#
— Perform forceful failover to FlyWithEduDB2.
root@FlyWithEdu-Monitor:~# orchestrator-client -c force-master-failover -a FlyWithEduDB1 -d FlyWithEduDB2
— After performing the forceful failover, old source/master node(FlyWithEduDB1) will be out of replication which can be again rebuilt/add under the replication topology later.