MySQL高可用探索之orchestrator

MySQL


摘要：使用orchestrator实现mysql主从自动切换、故障转移、拓扑管理。

部署MySQL环境(一主两从)

IP:端口 角色 版本
192.168.30.113:4406 master Percona 5.7.24-27-log CentOS Linux release 7.4.1708 (Core) 3.10.0-693.el7.x86_64
192.168.30.118:4406 slave Percona 5.7.24-27-log CentOS Linux release 7.4.1708 (Core) 3.10.0-693.el7.x86_64
192.168.30.119:4406 slave Percona 5.7.24-27-log CentOS Linux release 7.4.1708 (Core) 3.10.0-693.el7.x86_64

安装MySQL(略)

创建复制用户(三台mysql上面都创建)

GRANT REPLICATION SLAVE ON . TO repluser@’192.168.30.%’ IDENTIFIED BY ‘rep_2019’;
set global slave_net_timeout=8; 缩短从库感知主库宕机的等待时间

创建复制关系

在118和119上执行：

change master to master_host='192.168.30.113',master_port=4406,master_user='repluser',master_password='rep_2019',master_auto_position=1,MASTER_HEARTBEAT_PERIOD=2,MASTER_CONNECT_RETRY=1, MASTER_RETRY_COUNT=86400;
start slave;

以119为例，实操：

mysql> change master to master_host='192.168.30.113',master_port=4406,master_user='repluser',master_password='rep_2019',master_auto_position=1,MASTER_HEARTBEAT_PERIOD=2,MASTER_CONNECT_RETRY=1, MASTER_RETRY_COUNT=86400;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> show warnings \G;
*************************** 1. row ***************************
  Level: Note
   Code: 1759
Message: Sending passwords in plain text without SSL/TLS is extremely insecure.
*************************** 2. row ***************************
  Level: Note
   Code: 1760
Message: Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2 rows in set (0.00 sec)
ERROR: 
No query specified
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.30.113
                  Master_User: repluser
                  Master_Port: 4406
                Connect_Retry: 1
              Master_Log_File: mysql-bin.000024
          Read_Master_Log_Pos: 588
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 665
        Relay_Master_Log_File: mysql-bin.000024
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 588
              Relay_Log_Space: 873
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1134406
                  Master_UUID: 9b8d01ee-081d-11e9-94a1-005056a99b7f
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 9b8d01ee-081d-11e9-94a1-005056a99b7f:1
            Executed_Gtid_Set: 3db33b36-0e51-409f-a61d-c99756e90155:1-31:1000007,
7b8d01ee-081d-11e9-94a1-005056a99b7f:1-3,
8b8d01ee-081d-11e9-94a1-005056a99b7f:1-2,
9b8d01ee-081d-11e9-94a1-005056a99b7f:1
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)
ERROR: 
No query specified

在113上查看slave信息:

mysql> show slave hosts;
+-----------+----------------+------+-----------+--------------------------------------+
| Server_id | Host           | Port | Master_id | Slave_UUID                           |
+-----------+----------------+------+-----------+--------------------------------------+
|   1194406 | 192.168.30.119 | 4406 |   1134406 | 8b8d01ee-081d-11e9-94a1-005056a99b7f |
|   1184406 | 192.168.30.118 | 4406 |   1134406 | 7b8d01ee-081d-11e9-94a1-005056a99b7f |
+-----------+----------------+------+-----------+--------------------------------------+
2 rows in set (0.00 sec)

一主两从搭建完毕。

安装orchestrator

下载并解压

下载地址：https://github.com/github/orchestrator/releases/download/v3.0.14/orchestrator-3.0.14-linux-amd64.tar.gz
将解压出来的orchestrator目录拷贝到/usr/local/，将/usr/local/orchestrator加入环境变量(3台都装)
[root@mysql2 src]# tar xvf orchestrator-3.0.14-linux-amd64.tar.gz
[root@mysql2 src]# cp -r ./usr/local/orchestrator/ /usr/local/

配置

首先在orchestrator管理数据库的实例上创建账号：orch自用

root@(none) 04:11:09>create database orchestrator;
Query OK, 1 row affected (0.02 sec)
root@(none) 04:13:49>CREATE USER 'orchestrator'@'192.168.1.3' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)
root@(none) 04:13:58>GRANT ALL ON orchestrator.* TO 'orchestrator'@'192.168.1.3'; 
Query OK, 0 rows affected (0.00 sec)

然后再在被管理的MySQL实例上创建账号（只在主库113执行即可，从库会复制过去）：

mysql> GRANT SELECT, RELOAD, PROCESS, SUPER, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'orchestrator'@'192.168.30.%' identified by 'abc123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                            |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | Using GRANT statement to modify existing user's properties other than privileges is deprecated and will be removed in future release. Use ALTER USER statement for this operation. |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

编辑配置文件
https://github.com/github/orchestrator/blob/master/docs/configuration-sample.md
使用官网的配置文件模板，修改一下数据源即可：

[root@mysql-113 ~]# cat /usr/local/orchestrator/orchestrator.conf.json 
{
  "Debug": true,
  "EnableSyslog": false,
  "ListenAddress": ":3000",
  "MySQLTopologyUser": "orchestrator",
  "MySQLTopologyPassword": "abc123",
  "MySQLTopologyCredentialsConfigFile": "",
  "MySQLTopologySSLPrivateKeyFile": "",
  "MySQLTopologySSLCertFile": "",
  "MySQLTopologySSLCAFile": "",
  "MySQLTopologySSLSkipVerify": true,
  "MySQLTopologyUseMutualTLS": false,
  "MySQLOrchestratorHost": "192.168.1.3",
  "MySQLOrchestratorPort": 3306,
  "MySQLOrchestratorDatabase": "orchestrator",
  "MySQLOrchestratorUser": "orchestrator",
  "MySQLOrchestratorPassword": "123456",
  ... ...
  "RaftEnabled": true,
  "BackendDB": "mysql",
  "RaftBind": "192.168.30.113",
  "RaftDataDir": "/var/lib/orchestrator",
  "DefaultRaftPort": 10008,
  "RaftNodes": [
    "192.168.30.113",
    "192.168.30.118",
    "192.168.30.119"
    ],
 "ConsulAddress": "",
 "ConsulAclToken": ""
}

启动

root@:mysql-113:/usr/local/orchestrator$./orchestrator --config=./orchestrator.conf.json http
2019-02-26 16:21:50 INFO starting orchestrator
2019-02-26 16:21:50 INFO Read config: ./orchestrator.conf.json
2019-02-26 16:21:50 DEBUG Initializing orchestrator
2019-02-26 16:21:50 DEBUG Migrating database schema
2019-02-26 16:22:25 DEBUG Migrated database schema to version [1.5.7]
2019-02-26 16:22:25 DEBUG Connected to orchestrator backend: orchestrator:?@tcp(127.0.0.1:3306)/orchestrator?timeout=1s
2019-02-26 16:22:25 DEBUG Orchestrator pool SetMaxOpenConns: 128
2019-02-26 16:22:25 INFO Starting Discovery
2019-02-26 16:22:25 INFO Registering endpoints
2019-02-26 16:22:25 INFO Starting continuous discovery
2019-02-26 16:22:25 INFO Starting HTTP listener on :3000
2019-02-26 16:22:26 DEBUG outdated keys: []
2019-02-26 16:22:27 DEBUG outdated keys: []
2019-02-26 16:22:28 DEBUG outdated keys: []
2019-02-26 16:22:29 DEBUG outdated keys: []
2019-02-26 16:22:30 DEBUG outdated keys: []
2019-02-26 16:22:31 DEBUG outdated keys: []

访问WEB

http://192.168.30.113:3000
web1
查看主从拓扑：
web2
查看orchestrator状态：
web3

命令行操作

将/usr/local/orchestrator/resources/bin目录加入path环境变量

列出集群（含别名）

[root@mysql-119 ~]# orchestrator-client -c clusters-alias
mysql-113:4406,cluster_dev
mysql2:3306,trotdb_test

发现实例

[root@mysql-119]# orchestrator -c discover -i mysql1:3306  
2019-03-11 07:12:45 FATAL Orchestrator configured to run raft ("RaftEnabled": true). All access must go through the web API of the active raft node. You may use the orchestrator-client script which has a similar interface to the command line invocation. You may override this with --ignore-raft-setup
报错说在启用raft模式上禁止使用orchestrator客户端，建议使用orchestrator-client，orchestrator-client可以安装在没有orchestrator上的服务器。
要使用orchestrator-client需要先安装jq，否则会报错：orchestrator-client[18537]: cannot find jq
[root@mysql-119]# yum install jq -y
[root@mysql-119]# orchestrator-client -c discover -i mysql1:3306
mysql1:3306

查看拓扑

[root@mysql-119 ~]# orchestrator-client -c topology -i mysql-113:4406
mysql-113:4406     [0s,ok,5.7.24-27-log,rw,ROW,>>,GTID,P-GTID]
+ mysql-119:4406   [0s,ok,5.7.24-27-log,rw,ROW,>>,GTID,P-GTID]
  + mysql-118:4406 [0s,ok,5.7.24-27-log,rw,ROW,>>,GTID]
[root@mysql-119 ~]# 
返回列表格式：
[root@mysql-119 ~]# orchestrator-client -c topology-tabulated -i mysql-113:4406
mysql-113:4406    |0s|ok|5.7.24-27-log|rw|ROW|>>,GTID,P-GTID
+ mysql-119:4406  |0s|ok|5.7.24-27-log|rw|ROW|>>,GTID,P-GTID
  + mysql-118:4406|0s|ok|5.7.24-27-log|rw|ROW|>>,GTID

忘记实例

[root@mysql-119 ~]# orchestrator-client -c forget -i mysql-118:4406
查看拓扑可以发现没有了mysql-118:
[root@mysql-119 ~]# orchestrator-client -c topology-tabulated -i mysql-113:4406
mysql-113:4406  |0s|ok|5.7.24-27-log|rw|ROW|>>,GTID,P-GTID
+ mysql-119:4406|0s|ok|5.7.24-27-log|rw|ROW|>>,GTID,P-GTID
重新发现mysql-118：
[root@mysql-119 ~]# orchestrator-client -c discover -i mysql-118:4406                  
mysql-118:4406
[root@mysql-119 ~]# orchestrator-client -c topology-tabulated -i mysql-113:4406
mysql-113:4406    |0s|ok|5.7.24-27-log|rw|ROW|>>,GTID,P-GTID
+ mysql-119:4406  |0s|ok|5.7.24-27-log|rw|ROW|>>,GTID,P-GTID
  + mysql-118:4406|0s|ok|5.7.24-27-log|rw|ROW|>>,GTID

查看API接口

[root@mysql-119 ~]# orchestrator-client -c which-api
http://localhost:3000/api

调用API

[root@mysql-119 ~]# orchestrator-client -c api -path clusters
[ "mysql-113:4406", "mysql2:3306" ]
[root@mysql-119 ~]# orchestrator-client -c api -path leader-check
"Not leader"
[root@mysql-119 ~]# orchestrator-client -c api -path status
{ "Code": "OK", "Message": "Application node is healthy", "Details": { "Healthy": true, "Hostname": "mysql-119", "Token": "3bfe4deae671a528477bb3caa281b4d50b7aae33b052dd421d2b8655c8220864", "IsActiveNode": false, "ActiveNode": { "Hostname": "192.168.30.118:10008", "Token": "", "AppVersion": "", "FirstSeenActive": "", "LastSeenActive": "", "ExtraInfo": "", "Command": "", "DBBackend": "", "LastReported": "0001-01-01T00:00:00Z" }, "Error": null, "AvailableNodes": [ { "Hostname": "mysql-113", "Token": "30a4dc2ab18b3fce1f66b518a3c87f5f8c9351bcf43e09e10718390392bb64aa", "AppVersion": "3.0.14", "FirstSeenActive": "2019-03-05 14:04:02", "LastSeenActive": "2019-03-11 19:36:14", "ExtraInfo": "", "Command": "", "DBBackend": "192.168.1.3:3306", "LastReported": "0001-01-01T00:00:00Z" }, { "Hostname": "mysql-118", "Token": "b8e7dea2b6bf2a7117f0ba6258633f7ad677675530d499462d51473bffd6e54b", "AppVersion": "3.0.14", "FirstSeenActive": "2019-03-05 13:58:53", "LastSeenActive": "2019-03-11 19:36:13", "ExtraInfo": "", "Command": "", "DBBackend": "192.168.1.3:3306", "LastReported": "0001-01-01T00:00:00Z" }, { "Hostname": "mysql-119", "Token": "3bfe4deae671a528477bb3caa281b4d50b7aae33b052dd421d2b8655c8220864", "AppVersion": "3.0.14", "FirstSeenActive": "2019-03-05 14:01:55", "LastSeenActive": "2019-03-11 19:36:14", "ExtraInfo": "", "Command": "", "DBBackend": "192.168.1.3:3306", "LastReported": "0001-01-01T00:00:00Z" } ], "RaftLeader": "192.168.30.118:10008", "IsRaftLeader": false, "RaftLeaderURI": "http://192.168.30.118:3000", "RaftAdvertise": "192.168.30.119", "RaftHealthyMembers": null } }
[root@mysql-118 ~]# orchestrator-client -c api -path leader-check
"OK"
[root@mysql-113 ~]# orchestrator-client -c api -path leader-check
"Not leader"

搜索实例

[root@mysql-119 ~]# orchestrator-client -c search -i mysql-118
mysql-118:4406
[root@mysql-119 ~]# orchestrator-client -c search -i mysql3
mysql3:3307

查看指定实例的主库

[root@mysql-119 ~]# orchestrator-client -c which-master -i mysql-119:4406
mysql-113:4406

查看指定实例的从库

[root@mysql-119 ~]# orchestrator-client -c which-replicas -i mysql-113:4406
mysql-119:4406
[root@mysql-119 ~]# orchestrator-client -c which-replicas -i mysql2
mysql1:3306
mysql3:3307

查看指定实例名

[root@mysql-119 ~]# orchestrator-client -c instance -i mysql2                  
mysql2:3306

打印指定主实例从库异常的列表：which-broken-replicas，模拟test3的复制异常：

# orchestrator-client -c which-broken-replicas -i test2:3307
test3:3307

给定一个实例，列出所在集群下所有实例

[root@mysql-119 ~]#  orchestrator-client -c which-cluster-instances -i mysql-119:4406        
mysql-113:4406
mysql-118:4406
mysql-119:4406
[root@mysql-119 ~]#  orchestrator-client -c which-cluster-instances -i mysql1
mysql1:3306
mysql2:3306
mysql3:3307

查看所有实例

[root@mysql-119 ~]#  orchestrator-client -c all-clusters-masters  
mysql-113:4406
mysql2:3306
[root@mysql-119 ~]#  orchestrator-client -c all-instances         
mysql2:3306
mysql-113:4406
mysql-119:4406
mysql1:3306
mysql3:3307
mysql-118:4406

查看可作为pt-online-schema-change操作的副本列表

[root@mysql-119 ~]#  orchestrator-client -c which-cluster-osc-replicas -i mysql2
mysql1:3306
mysql3:3307
[root@mysql-119 ~]#  orchestrator-client -c which-cluster-osc-running-replicas -i mysql2
mysql1:3306
mysql3:3307

查看集群中主实例的数据中心

[root@mysql-119 ~]#  orchestrator-client -c dominant-dc
mysql2

将集群的主提交到KV存储

[root@mysql-119 ~]#  orchestrator-client -c submit-masters-to-kv-stores
mysql/master/cluster_dev:mysql-113:4406
mysql/master/cluster_dev/hostname:mysql-113
mysql/master/cluster_dev/port:4406
mysql/master/cluster_dev/ipv4:192.168.30.113
mysql/master/cluster_dev/ipv6:
mysql/master/trotdb_test:mysql2:3306
mysql/master/trotdb_test/hostname:mysql2
mysql/master/trotdb_test/port:3306
mysql/master/trotdb_test/ipv4:192.168.1.173
mysql/master/trotdb_test/ipv6:

修改拓扑（迁移从库到另一个实例上）

[root@mysql-119 ~]#  orchestrator-client -c topology -i mysql-118:4406                  
mysql-113:4406     [0s,ok,5.7.24-27-log,rw,ROW,>>,GTID,P-GTID]
+ mysql-119:4406   [0s,ok,5.7.24-27-log,rw,ROW,>>,GTID,P-GTID]
  + mysql-118:4406 [0s,ok,5.7.24-27-log,rw,ROW,>>,GTID]
将mysql-118:4406改为mysql-113:4406的从库
[root@mysql-119 ~]#  orchestrator-client -c relocate -i mysql-118:4406 -d mysql-113:4406
mysql-118:4406<mysql-113:4406
[root@mysql-119 ~]#  orchestrator-client -c topology -i mysql-118:4406                  
mysql-113:4406   [0s,ok,5.7.24-27-log,rw,ROW,>>,GTID,P-GTID]
+ mysql-118:4406 [0s,ok,5.7.24-27-log,rw,ROW,>>,GTID]
+ mysql-119:4406 [0s,ok,5.7.24-27-log,rw,ROW,>>,GTID,P-GTID]

如果发生过了failover，旧主修复后只能通过手动方式将其加入复制。
如果使用orchestrator加入会报错：

[root@mysql-113 ~]# orchestrator-client -c relocate -i mysql-118:4406 -d mysql-113:4406
2019-03-19 08:37:10 ERROR Relocating mysql-118:4406 below mysql-113:4406 turns to be too complex; please do it manually

故障切换

主库宕机，自动Failover

② Detected UnreachableMaster on mysql-113:4406. Affected replicas: 2
② Detected DeadMaster on mysql-113:4406. Affected replicas: 1
③ Will recover from DeadMaster on mysql-113:4406
④ Recovered from DeadMaster on mysql-113:4406. Failed: mysql-113:4406; Promoted: mysql-118:4406
⑤ (for all types) Recovered from DeadMaster on mysql-113:4406. Failed: mysql-113:4406; Successor: mysql-118:4406

遇到问题及解决

访问WEB报：html/template: “templates/layout” is undefined

访问http://192.168.30.113:3000时报错：html/template: “templates/layout” is undefined
由于启动orchstrator时没有cd到go的项目路径，而是直接使用绝对路径启动：
/usr/local/orchestrator/orchestrator –config=/usr/local/orchestrator/orchestrator.conf.json http &
正确启动方式：
cd /usr/local/orchestrator && ./orchestrator –config=./orchestrator.conf.json http &

glibc版本太低问题

如果系统版本是centos6，运行时可能遇到如下报错：

[root@mysql2 ~]# orchestrator 
orchestrator: /lib64/libc.so.6: version `GLIBC_2.14' not found (required by orchestrator)
[root@mysql2 ~]# strings /lib64/libc.so.6 |grep GLIBC
GLIBC_2.2.5
GLIBC_2.2.6
GLIBC_2.3
GLIBC_2.3.2
GLIBC_2.3.3
GLIBC_2.3.4
GLIBC_2.4
GLIBC_2.5
GLIBC_2.6
GLIBC_2.7
GLIBC_2.8
GLIBC_2.9
GLIBC_2.10
GLIBC_2.11
GLIBC_2.12
GLIBC_PRIVATE
[root@mysql2 ~]# 
升级glibc要非常小心，一般不建议升级。
root@:mysql_dev:~$strings /lib64/libc.so.6 |grep GLIBC
GLIBC_2.2.5
GLIBC_2.2.6
GLIBC_2.3
GLIBC_2.3.2
GLIBC_2.3.3
GLIBC_2.3.4
GLIBC_2.4
GLIBC_2.5
GLIBC_2.6
GLIBC_2.7
GLIBC_2.8
GLIBC_2.9
GLIBC_2.10
GLIBC_2.11
GLIBC_2.12
GLIBC_2.13
GLIBC_2.14
GLIBC_2.15
GLIBC_2.16
GLIBC_2.17
GLIBC_PRIVATE
root@:mysql_dev:~$orchestrator --help                 
Usage of orchestrator:
  -alias string
        cluster alias
  -binlog string
        Binary log file name
  -c string
        command, required. See full list of commands via 'orchestrator -c help'
  -config string
        config file name
  -d string
        destination instance, host_fqdn[:port] (synonym to -s)
  -databaseless
        EXPERIMENTAL! Work without backend database
  -debug
        debug mode (very verbose)
  -discovery
        auto discovery mode (default true)
  -duration string
        maintenance duration (format: 59s, 59m, 23h, 6d, 4w)
  -grab-election
        Grab leadership (only applies to continuous mode)
  -hostname string
        Hostname/fqdn/CNAME/VIP (applies for hostname/resolve related commands)
  -i string
        instance, host_fqdn[:port] (e.g. db.company.com:3306, db.company.com)
  -noop
        Dry run; do not perform destructing operations
  -owner string
        operation owner
  -pattern string
        regular expression pattern
  -pool string
        Pool logical name (applies for pool-related commands)
  -promotion-rule string
        Promotion rule for register-andidate (prefer|neutral|must_not) (default "prefer")
  -quiet
        quiet
  -reason string
        operation reason
  -s string
        sibling instance, host_fqdn[:port]
  -skip-unresolve
        Do not unresolve a host name
  -skip-unresolve-check
        Skip/ignore checking an unresolve mapping (via hostname_unresolve table) resolves back to same hostname
  -stack
        add stack trace upon error
  -statement string
        Statement/hint
  -strict
        strict mode (more checks, slower)
  -verbose
        verbose
  -version
        Print version and exit

虚GTID问题

报错：2019-03-05 10:31:52 ERROR ReadTopologyInstance(mysql-113:4406) DetectPseudoGTIDQuery: Error 1146: Table ‘meta.pseudo_gtid_status’ doesn’t exist
在被管理的MySQL实例上创建Pseudo-GTID的meta库（只在主库执行即可，从库会复制过去）

mysql> create database meta;
Query OK, 1 row affected (0.01 sec)
mysql> source /usr/local/orchestrator/resources/pseudo-gtid/pseudo-gtid.sql;
Query OK, 1 row affected, 1 warning (0.00 sec)
Database changed
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE IF NOT EXISTS cluster (
    ->    anchor TINYINT NOT NULL,
    ->    cluster_name VARCHAR(128) CHARSET ascii NOT NULL DEFAULT '',
    ->    cluster_domain VARCHAR(128) CHARSET ascii NOT NULL DEFAULT '',
    ->    PRIMARY KEY (anchor)
    ->  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+--------------------+
| Tables_in_meta     |
+--------------------+
| cluster            |
| pseudo_gtid_status |
+--------------------+
2 rows in set (0.00 sec)
mysql> INSERT INTO cluster (anchor, cluster_name, cluster_domain) 
    -> VALUES (1, CONCAT('cls_',@@hostname), @@hostname) 
    -> ON DUPLICATE KEY UPDATE cluster_name=VALUES(cluster_name), cluster_domain=VALUES(cluster_domain);
Query OK, 1 row affected (0.00 sec)
mysql> select * from cluster;
+--------+---------------+----------------+
| anchor | cluster_name  | cluster_domain |
+--------+---------------+----------------+
|      1 | cls_mysql-113 | mysql-113      |
+--------+---------------+----------------+
1 row in set (0.00 sec)

hosts没配置报错

2019-02-27 11:28:43 DEBUG outdated keys: []
2019-02-27 11:28:44 DEBUG outdated keys: [mysql1:3306 mysql2:3306 mysql3:3307]
2019-02-27 11:28:44 ERROR dial tcp: lookup mysql3 on [::1]:53: read udp [::1]:32462->[::1]:53: read: connection refused
2019-02-27 11:28:44 ERROR ReadTopologyInstance(mysql3:3307) show variables like 'maxscale%': dial tcp: lookup mysql3 on [::1]:53: read udp [::1]:32462->[::1]:53: read: connection refused
2019-02-27 11:28:44 ERROR ReadTopologyInstance(mysql3:3307) Cleanup: dial tcp: lookup mysql3 on [::1]:53: read udp [::1]:19871->[::1]:53: read: connection refused
2019-02-27 11:28:44 WARNING discoverInstance(mysql3:3307) instance is nil in 0.009s, error=Failed ReadTopologyInstance
2019-02-27 11:28:44 DEBUG Discovered host: mysql2:3306, master: mysql1:3306, version: 5.7.20-19-log in 0.018s
2019-02-27 11:28:44 DEBUG Discovered host: mysql1:3306, master: mysql2:3306, version: 5.7.20-19-log in 0.020s
2019-02-27 11:28:45 DEBUG outdated keys: []
报错：
2019-03-05 13:31:04 DEBUG raft leader is 192.168.30.113:10008; state: Follower
2019-03-05 13:31:07 ERROR ReadTopologyInstance(mysql1:3306) show global status like 'Uptime': dial tcp: lookup mysql1 on 127.0.0.1:53: no such host
2019-03-05 13:31:07 ERROR ReadTopologyInstance(mysql3:3307) show global status like 'Uptime': dial tcp: lookup mysql3 on 127.0.0.1:53: no such host
原因：域名解析问题，配置/etc/hosts解决
解决：在orchestrator运行的机器上添加mysql的ip及对应主机名
#orchestrator
192.168.1.172 mysql1
192.168.1.173 mysql2
192.168.1.123 mysql3

report-host没设置报错

2019-02-26 17:15:03 DEBUG outdated keys: [mysql1:3306 mysql2:3306]
2019-02-26 17:15:03 ERROR ReadTopologyInstance(mysql2:3306) show slave hosts: ReadTopologyInstance(mysql2:3306) 'show slave hosts' returned row with <host,port>: <,3306>
2019-02-26 17:15:03 ERROR ReadTopologyInstance(mysql1:3306) show slave hosts: ReadTopologyInstance(mysql1:3306) 'show slave hosts' returned row with <host,port>: <,3306>
2019-02-26 17:15:03 DEBUG Discovered host: mysql2:3306, master: mysql1:3306, version: 5.7.20-19-log in 0.018s
2019-02-26 17:15:03 DEBUG Discovered host: mysql1:3306, master: mysql2:3306, version: 5.7.20-19-log in 0.025s
2019-02-26 17:15:04 DEBUG outdated keys: []
2019-02-26 17:15:05 DEBUG outdated keys: []
解决：
Mysql主从复制，在master上查看从的信息：show slave hosts
http://dinglin.iteye.com/blog/1255160 
默认不记录从的host信息，只记录端口和Server_id信息，需要设置my.cnf 中的[mysqld] report-host=IP
正常输出：
2019-02-27 11:31:43 DEBUG outdated keys: []
2019-02-27 11:31:44 DEBUG outdated keys: [mysql1:3306 mysql2:3306 mysql3:3307]
2019-02-27 11:31:44 DEBUG Discovered host: mysql2:3306, master: mysql1:3306, version: 5.7.20-19-log in 0.019s
2019-02-27 11:31:44 DEBUG Discovered host: mysql1:3306, master: mysql2:3306, version: 5.7.20-19-log in 0.023s
2019-02-27 11:31:44 DEBUG Discovered host: mysql3:3307, master: mysql2:3306, version: 5.7.20-19-log in 0.042s
2019-02-27 11:31:45 DEBUG outdated keys: []

mysql用户权限问题

2019-03-05 13:58:55 ERROR ReadTopologyInstance(mysql2:3306) show global status like 'Uptime': Error 1045: Access denied for user 'orchestrator'@'192.168.30.118' (using password: YES)
2019-03-05 14:08:34 ERROR ReadTopologyInstance(mysql3:3307) show global status like 'Uptime': Error 1045: Access denied for user 'orchestrator'@'192.168.30.118' (using password: YES)
2019-03-05 14:08:34 WARNING  DiscoverInstance(mysql3:3307) instance is nil in 0.017s (Backend: 0.004s, Instance: 0.013s), error=Error 1045: Access denied for user 'orchestrator'@'192.168.30.118' (using password: YES)
2019-03-05 14:08:34 ERROR ReadTopologyInstance(mysql1:3306) show global status like 'Uptime': Error 1045: Access denied for user 'orchestrator'@'192.168.30.118' (using password: YES)
2019-03-05 14:08:34 WARNING  DiscoverInstance(mysql1:3306) instance is nil in 0.023s (Backend: 0.004s, Instance: 0.019s), error=Error 1045: Access denied for user 'orchestrator'@'192.168.30.118' (using password: YES)
2019-03-05 14:08:35 ERROR ReadTopologyInstance(mysql2:3306) show global status like 'Uptime': Error 1045: Access denied for user 'orchestrator'@'192.168.30.118' (using password: YES)
2019-03-05 14:08:35 WARNING  DiscoverInstance(mysql2:3306) instance is nil in 0.016s (Backend: 0.008s, Instance: 0.009s), error=Error 1045: Access denied for user 'orchestrator'@'192.168.30.118' (using password: YES)
解决：由于orchestrator部署到了新的网段，需重新赋权
mysql> GRANT SELECT, RELOAD, PROCESS, SUPER, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'orchestrator'@'192.168.30.%' identified by 'abc123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                            |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | Using GRANT statement to modify existing user's properties other than privileges is deprecated and will be removed in future release. Use ALTER USER statement for this operation. |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

GTID错误跳过

Problem:Errant GTID
replica has gtid entries not found on its master
可以在页面上处理GTID错误：
图1
图2
图3
然后刷新下页面就没有GTID的报错了。

本文标题:MySQL高可用探索之orchestrator

文章作者:一条大河

发布时间:2019-03-19, 15:43:11

最后更新:2019-03-19, 16:42:10

原始链接:http://riverdba.github.io/2019/03/19/orchestrator/

许可协议: "署名-非商用-相同方式共享 4.0" 转载请保留原文链接及作者。

文章目录
  1. 1. 部署MySQL环境(一主两从)
    1. 1.1. 安装MySQL(略)
    2. 1.2. 创建复制用户(三台mysql上面都创建)
    3. 1.3. 创建复制关系
  2. 2. 安装orchestrator
    1. 2.1. 下载并解压
    2. 2.2. 配置
    3. 2.3. 启动
    4. 2.4. 访问WEB
  3. 3. 命令行操作
    1. 3.1. 列出集群（含别名）
    2. 3.2. 发现实例
    3. 3.3. 查看拓扑
    4. 3.4. 忘记实例
    5. 3.5. 查看API接口
    6. 3.6. 调用API
    7. 3.7. 搜索实例
    8. 3.8. 查看指定实例的主库
    9. 3.9. 查看指定实例的从库
    10. 3.10. 查看指定实例名
    11. 3.11. 打印指定主实例从库异常的列表：which-broken-replicas，模拟test3的复制异常：
    12. 3.12. 给定一个实例，列出所在集群下所有实例
    13. 3.13. 查看所有实例
    14. 3.14. 查看可作为pt-online-schema-change操作的副本列表
    15. 3.15. 查看集群中主实例的数据中心
    16. 3.16. 将集群的主提交到KV存储
    17. 3.17. 修改拓扑（迁移从库到另一个实例上）
  4. 4. 故障切换
    1. 4.1. 主库宕机，自动Failover
  5. 5. 遇到问题及解决
    1. 5.1. 访问WEB报：html/template: “templates/layout” is undefined
    2. 5.2. glibc版本太低问题
    3. 5.3. 虚GTID问题
    4. 5.4. hosts没配置报错
    5. 5.5. report-host没设置报错
    6. 5.6. mysql用户权限问题
    7. 5.7. GTID错误跳过
|