1. Foreword
Let’s talk about why orchestrator is used as the high-availability component of the database instead of MHA. Because the mha component has a single point problem, while the orchestrator component can form a cluster and there will be no single point problem.
2. Construction
Build mysql gtid master-slave
Reference: mysql master-slave setup (gtid)-CSDN blog
The above master-slave configuration is for the use of orchestrator database high-availability components. There are several configurations that cannot go wrong.
CHANGE MASTER TO MASTER_HOST=’rabbitmq02′ in the master-slave configuration must use the host name, not the IP address, otherwise problems will occur when using the orchestrator component for mysql failover
There are also several additional permission configurations for master-slave synchronization users, instead of simply configuring REPLICATION SLAVE permissions.
Build an orchestrator cluster
Download the orchestrator installation package on github
Address: Releases · openark/orchestrator · GitHub
Here we use three hosts to build an orchestrator cluster, use the raft protocol to use high availability, and allow one host to go down.
| ip | port |
| 10.1.60.118 | 3306, 3001 |
| 10.1.60.120 | 3306, 3001 |
| 10.1.60.125 | 3306, 3001 |
You need to install the mysql service for each host first
Reference: yum installs mysql 5.7_yum installs mysql5.7_Apex Predator’s blog-CSDN blog
Create orchestrator database and user
mysql -u root -p
create database orchestrator;
create user ‘orchestrator’@’%’ identified by ‘11111111’;
grant all on orchestrator.* to ‘orchestrator’@’%’;
flush privileges;
Place the installation packages on three hosts respectively and unzip them
mkdir /opt/orchestrator & amp; & amp; cd /opt/orchestrator
tar -zxvf orchestrator-3.2.5-linux-amd64.tar.gz & amp; & ls
Copy the configuration file (required for all three hosts)
cd usr/local/orchestrator/
cp orchestrator-sample.conf.json orchestrator.conf.json
Edit the configuration file (required for all three hosts)
vi orchestrator.conf.json
{
"Debug": true,
"EnableSyslog": true,
"ListenAddress": ":3001", #The default is to use port 3000, but I have a service here that is used, so I changed it to port 3001.
"MySQLTopologyUser": "orchestrator", #Configure the user of the monitored master-slave database
"MySQLTopologyPassword": "11111111", #Configure the user password of the monitored master-slave database
"MySQLTopologyCredentialsConfigFile": "",
"MySQLTopologySSLPrivateKeyFile": "",
"MySQLTopologySSLCertFile": "",
"MySQLTopologySSLCAFile": "",
"MySQLTopologySSLSkipVerify": true,
"MySQLTopologyUseMutualTLS": false,
"MySQLOrchestratorHost": "10.1.60.125", #Configure the orchestrator component service local address
"MySQLOrchestratorPort": 3306, #Configure orchestrator component service database port
"MySQLOrchestratorDatabase": "orchestrator", #Configure the orchestrator component database
"MySQLOrchestratorUser": "orchestrator", #Configure orchestrator database user
"MySQLOrchestratorPassword": "11111111", ##Configure orchestrator database user password
"MySQLOrchestratorCredentialsConfigFile": "",
"MySQLOrchestratorSSLPrivateKeyFile": "",
"MySQLOrchestratorSSLCertFile": "",
"MySQLOrchestratorSSLCAFile": "",
"MySQLOrchestratorSSLSkipVerify": true,
"MySQLOrchestratorUseMutualTLS": false,
"MySQLConnectTimeoutSeconds": 1,
"RaftEnabled": true, #Enable raft configuration. The following configurations are not available by default and need to be added manually.
"RaftDataDir": "/opt/orchestrator", #Configure the orchestrator installation package address
"RaftBind": "10.1.60.125", #Configure the orchestrator component service local address
"DefaultRaftPort": 10008, #Configure raft service port
"RaftNodes": [ #Configure orchestrator component cluster address
"10.1.60.118",
"10.1.60.120",
"10.1.60.125"
],
"DefaultInstancePort": 3306,
"DiscoverByShowSlaveHosts": true,
"InstancePollSeconds": 5,
"DiscoveryIgnoreReplicaHostnameFilters": [
"a_host_i_want_to_ignore[.]example[.]com",
".*[.]ignore_all_hosts_from_this_domain[.]example[.]com",
"a_host_with_extra_port_i_want_to_ignore[.]example[.]com:3307"
],
"UnseenInstanceForgetHours": 240,
"SnapshotTopologiesIntervalHours": 0,
"InstanceBulkOperationsWaitTimeoutSeconds": 10,
"HostnameResolveMethod": "None", #This needs to be changed to None. The default is default. When using the default and adding the mysql master and slave through the host name, it will not be found.
"MySQLHostnameResolveMethod": "SELECT concat(@@report_host,':',@@port)", #This also needs to be changed to report_host mode
"SkipBinlogServerUnresolveCheck": true,
"ExpiryHostnameResolvesMinutes": 60,
"RejectHostnameResolvePattern": "",
"ReasonableReplicationLagSeconds": 10,
"ProblemIgnoreHostnameFilters": [],
"VerifyReplicationFilters": false,
"ReasonableMaintenanceReplicationLagSeconds": 20,
"CandidateInstanceExpireMinutes": 60,
"AuditLogFile": "",
"AuditToSyslog": false,
"RemoveTextFromHostnameDisplay": ":3306",
"ReadOnly": false,
"AuthenticationMethod": "",
"HTTPAuthUser": "",
"HTTPAuthPassword": "",
"AuthUserHeader": "",
"PowerAuthUsers": [
"*"
],
"ClusterNameToAlias": {
"127.0.0.1": "test suite"
},
"ReplicationLagQuery": "",
"DetectClusterAliasQuery": "SELECT SUBSTRING_INDEX(@@hostname, '.', 1)",
"DetectClusterDomainQuery": "",
"DetectInstanceAliasQuery": "",
"DetectPromotionRuleQuery": "",
"DataCenterPattern": "[.]([^.] + )[.][^.] + [.]mydomain[.]com",
"PhysicalEnvironmentPattern": "[.]([^.] + [.][^.] + )[.]mydomain[.]com",
"PromotionIgnoreHostnameFilters": [],
"DetectSemiSyncEnforcedQuery": "",
"ServeAgentsHttp": false,
"AgentsServerPort": ":3001", #You also need to change the default port number of the orchestrator component here.
"AgentsUseSSL": false,
"AgentsUseMutualTLS": false,
"AgentSSLSkipVerify": false,
"AgentSSLPrivateKeyFile": "",
"AgentSSLCertFile": "",
"AgentSSLCAFile": "",
"AgentSSLValidOUs": [],
"UseSSL": false,
"UseMutualTLS": false,
"SSLSkipVerify": false,
"SSLPrivateKeyFile": "",
"SSLCertFile": "",
"SSLCAFile": "",
"SSLValidOUs": [],
"URLPrefix": "",
"StatusEndpoint": "/api/status",
"StatusSimpleHealth": true,
"StatusOUVerify": false,
"AgentPollMinutes": 60,
"UnseenAgentForgetHours": 6,
"StaleSeedFailMinutes": 60,
"SeedAcceptableBytesDiff": 8192,
"PseudoGTIDPattern": "",
"PseudoGTIDPatternIsFixedSubstring": false,
"PseudoGTIDMonotonicHint": "asc:",
"DetectPseudoGTIDQuery": "",
"BinlogEventsChunkSize": 10000,
"SkipBinlogEventsContaining": [],
"ReduceReplicationAnalysisCount": true,
"FailureDetectionPeriodBlockMinutes": 60,
"FailMasterPromotionOnLagMinutes": 0,
"RecoveryPeriodBlockSeconds": 3600,
"RecoveryIgnoreHostnameFilters": [],
"RecoverMasterClusterFilters": ["*"], #The default configuration needs to be changed to *, otherwise the automatic master-slave failover will not take effect
"RecoverIntermediateMasterClusterFilters": ["*"], #The default configuration needs to be changed to *, otherwise the automatic master-slave failover will not take effect
"OnFailureDetectionProcesses": [
"echo 'Detected {failureType} on {failureCluster}. Affected replicas: {countSlaves}' >> /tmp/recovery.log"
],
"PreGracefulTakeoverProcesses": [
"echo 'Planned takeover about to take place on {failureCluster}. Master will switch to read_only' >> /tmp/recovery.log"
],
"PreFailoverProcesses": [
"echo 'Will recover from {failureType} on {failureCluster}' >> /tmp/recovery.log"
],
"PostFailoverProcesses": [
"echo '(for all types) Recovered from {failureType} on {failureCluster}. Failed: {failedHost}:{failedPort}; Successor: {successorHost}:{successorPort}' >> /tmp/recovery.log\ "
],
"PostUnsuccessfulFailoverProcesses": [],
"PostMasterFailoverProcesses": [
"echo 'Recovered from {failureType} on {failureCluster}. Failed: {failedHost}:{failedPort}; Promoted: {successorHost}:{successorPort}' >> /tmp/recovery.log"
],
"PostIntermediateMasterFailoverProcesses": [
"echo 'Recovered from {failureType} on {failureCluster}. Failed: {failedHost}:{failedPort}; Successor: {successorHost}:{successorPort}' >> /tmp/recovery.log"
],
"PostGracefulTakeoverProcesses": [
"echo 'Planned takeover complete' >> /tmp/recovery.log"
],
"CoMasterRecoveryMustPromoteOtherCoMaster": true,
"DetachLostSlavesAfterMasterFailover": true,
"ApplyMySQLPromotionAfterMasterFailover": true,
"PreventCrossDataCenterMasterFailover": false,
"PreventCrossRegionMasterFailover": false,
"MasterFailoverDetachReplicaMasterHost": false,
"MasterFailoverLostInstancesDowntimeMinutes": 0,
"PostponeReplicaRecoveryOnLagMinutes": 0,
"OSCIgnoreHostnameFilters": [],
"GraphiteAddr": "",
"GraphitePath": "",
"GraphiteConvertHostnameDotsToUnderscores": true,
"ConsulAddress": "",
"ConsulAclToken": "",
"ConsulKVStoreProvider": "consul"
}
Start the orchestrator component service
nohup ./orchestrator http & amp;>./orchestrator.log & amp;
Check whether the orchestrator component service is normal
netstat -tlpn |grep 3001
Visit the orchestrator component web
Next, add the mysql master and slave. Just add any master and slave and the entire master-slave architecture will be automatically recognized.
The test about master-slave failure will no longer be demonstrated. You can operate it yourself. However, after the original failed master database is restarted, you need to manually configure the slave service to rejoin the master-slave architecture.
The knowledge points of the article match the official knowledge files, and you can further learn relevant knowledge. MySQL entry-level skills treeDatabase compositionTable 76808 people are learning the system