作者 韩杰 · 沃趣科技高级数据库技术专家
出品 沃趣科技
前 言
上篇文章讲了orchestrator复制拓扑的发现方式。本篇文章我们继续探索orchestrator的旅程，讲一讲orchestrator的探测机制。
故障检测
orch使用了一种整体性的方法去探测主库和中间主库是否正常。一种比较天真的方法，比如，监控工具探测到主库无法连接或者查询，就发出报警。这种方法容易受到网络故障而造成误报。为了降低误报，会通过运行n次每次间隔t时间的方式。在某些情况下，这减少了误报的机会，但是增加了真正故障的响应时间。orchestrator会利用复制拓扑。orch不仅会监测主库，也会检测从库。比如，要诊断出主库挂了的情况，orch必须满足以下两个条件：
-
联系不到主库。
-
可以联系到主库对应的从库，并且这些从库也连不上主库。
orch没有将错误按时间来进行分类，而是按复制拓扑服务器（也就是所谓的multiple observers）本身进行分类。实际上，当所有的从库都连不上主库的时候，说明复制拓扑实际上就被破坏了，有理由需要进行故障转移。orch的这种整体故障检测方式在生产环境是非常可靠的。
探测机制
orch会每隔InstancePollSeconds（默认5s）时间去被监控的实例上拉取实例状态，并将这些状态信息存入orch的元数据库的orchestrator.database_instance表中，然后orch会每隔InstancePollSeconds秒从元数据库中获取每个instance的状态，展示在web界面上。拉取实例状态的语句如下：
show variables like 'maxscale%'
show global status like 'Uptime'
select @@global.hostname, ifnull(@@global.report_host, ''), @@global.server_id, @@global.version, @@global.version_comment, @@global.read_only, @@global.binlog_format, @@global.log_bin, @@global.log_slave_updates
show master status
show global status like 'rpl_semi_sync_%_status'
select @@global.gtid_mode, @@global.server_uuid, @@global.gtid_executed, @@global.gtid_purged, @@global.master_info_repository = 'TABLE', @@global.binlog_row_image
show slave status
select count(*) > 0 and MAX(User_name) != '' from mysql.slave_master_info
show slave hosts
select substring_index(host, ':', 1) as slave_hostname from information_schema.processlist where command IN ('Binlog Dump', 'Binlog Dump GTID')
SELECT SUBSTRING_INDEX(@@hostname, '.', 1)
拉取得到实例状态之后，通过下面语句将状态值存入到orch的元数据库中：
注：values后面的值就是上面拉取到的实例状态值。
INSERT INTO database_instance
(hostname, port, last_checked, last_attempted_check, last_check_partial_success, uptime, server_id, server_uuid, version, major_version, version_comment, binlog_server, read_only, binlog_format, binlog_row_image, log_bin, log_slave_updates, binary_log_file, binary_log_pos, master_host, master_port, slave_sql_running, slave_io_running, replication_sql_thread_state, replication_io_thread_state, has_replication_filters, supports_oracle_gtid, oracle_gtid, master_uuid, ancestry_uuid, executed_gtid_set, gtid_mode, gtid_purged, gtid_errant, mariadb_gtid, pseudo_gtid, master_log_file, read_master_log_pos, relay_master_log_file, exec_master_log_pos, relay_log_file, relay_log_pos, last_sql_error, last_io_error, seconds_behind_master, slave_lag_seconds, sql_delay, num_slave_hosts, slave_hosts, cluster_name, suggested_cluster_alias, data_center, region, physical_environment, replication_depth, is_co_master, replication_credentials_available, has_replication_credentials, allow_tls, semi_sync_enforced, semi_sync_master_enabled, semi_sync_replica_enabled, instance_alias, last_discovery_latency, last_seen)
VALUES
('10.10.30.5', 3306, NOW(), NOW(), 1, 322504, 1521, 'e2685a0f-d8f8-11e9-a2c9-002590e95c3c', '5.7.22-log', '5.7', 'MySQL Community Server (GPL)', 0, 1, 'ROW', 'FULL', 1, 1, 'mysql-bin.000016', 129186924, '10.10.30.6', 3306, 1, 1, 1, 1, 0, 1, 1, '6bf30525-d8f8-11e9-808c-0cc47a74fca8', '6bf30525-d8f8-11e9-808c-0cc47a74fca8,e2685a0f-d8f8-11e9-a2c9-002590e95c3c', '6bf30525-d8f8-11e9-808c-0cc47a74fca8:1-1554568,ne2685a0f-d8f8-11e9-a2c9-002590e95c3c:1-632541', 'ON', '', '', 0, 0, 'mysql-bin.000017', 150703414, 'mysql-bin.000017', 150703414, 'mysql-relay-bin.000052', 137056344, '', '', 0, 0, 0, 1, '[{"Hostname":"10.10.30.6","Port":3306}]', '10.10.30.6:3306', 'qhp-6', '', '', '', 1, 1, 1, 1, 0, 0, 0, 0, '', 8083748, NOW())
ON DUPLICATE KEY UPDATE
hostname=VALUES(hostname), port=VALUES(port), last_checked=VALUES(last_checked), last_attempted_check=VALUES(last_attempted_check), last_check_partial_success=VALUES(last_check_partial_success), uptime=VALUES(uptime), server_id=VALUES(server_id), server_uuid=VALUES(server_uuid), version=VALUES(version), major_version=VALUES(major_version), version_comment=VALUES(version_comment), binlog_server=VALUES(binlog_server), read_only=VALUES(read_only), binlog_format=VALUES(binlog_format), binlog_row_image=VALUES(binlog_row_image), log_bin=VALUES(log_bin), log_slave_updates=VALUES(log_slave_updates), binary_log_file=VALUES(binary_log_file), binary_log_pos=VALUES(binary_log_pos), master_host=VALUES(master_host), master_port=VALUES(master_port), slave_sql_running=VALUES(slave_sql_running), slave_io_running=VALUES(slave_io_running), replication_sql_thread_state=VALUES(replication_sql_thread_state), replication_io_thread_state=VALUES(replication_io_thread_state), has_replication_filters=VALUES(has_replication_filters), supports_oracle_gtid=VALUES(supports_oracle_gtid), oracle_gtid=VALUES(oracle_gtid), master_uuid=VALUES(master_uuid), ancestry_uuid=VALUES(ancestry_uuid), executed_gtid_set=VALUES(executed_gtid_set), gtid_mode=VALUES(gtid_mode), gtid_purged=VALUES(gtid_purged), gtid_errant=VALUES(gtid_errant), mariadb_gtid=VALUES(mariadb_gtid), pseudo_gtid=VALUES(pseudo_gtid), master_log_file=VALUES(master_log_file), read_master_log_pos=VALUES(read_master_log_pos), relay_master_log_file=VALUES(relay_master_log_file), exec_master_log_pos=VALUES(exec_master_log_pos), relay_log_file=VALUES(relay_log_file), relay_log_pos=VALUES(relay_log_pos), last_sql_error=VALUES(last_sql_error), last_io_error=VALUES(last_io_error), seconds_behind_master=VALUES(seconds_behind_master), slave_lag_seconds=VALUES(slave_lag_seconds), sql_delay=VALUES(sql_delay), num_slave_hosts=VALUES(num_slave_hosts), slave_hosts=VALUES(slave_hosts), cluster_name=VALUES(cluster_name), suggested_cluster_alias=VALUES(suggested_cluster_alias), data_center=VALUES(data_center), region=VALUES(region), physical_environment=VALUES(physical_environment), replication_depth=VALUES(replication_depth), is_co_master=VALUES(is_co_master), replication_credentials_available=VALUES(replication_credentials_available), has_replication_credentials=VALUES(has_replication_credentials), allow_tls=VALUES(allow_tls), semi_sync_enforced=VALUES(semi_sync_enforced), semi_sync_master_enabled=VALUES(semi_sync_master_enabled), semi_sync_replica_enabled=VALUES(semi_sync_replica_enabled), instance_alias=VALUES(instance_alias), last_discovery_latency=VALUES(last_discovery_latency), last_seen=VALUES(last_seen)
然后orch会每隔InstancePollSeconds秒从元数据库中获取每个被监控实例的状态，通过web端展示到页面上。
探测实例失败
如果某个instance挂了，orch每隔InstancePollSeconds时间拉取实例状态失败，无法获取到最新的实例状态，也就无法用上面这条insert将实例状态存入到元数据库中，那么orch会按下面的方式更新元数据库：
// 每隔InstancePollSeconds时间更新database_instance表的last_checked和last_check_partial_success字段
update database_instance set last_checked = NOW(), last_check_partial_success = 0 where hostname = '10.10.30.170' and port = 3306
// 每隔InstancePollSeconds+1s时间更新database_instance表的last_attempted_check字段
update database_instance set last_attempted_check = NOW() where hostname = '10.10.30.170' and port = 3306
这里为什么要引入last_attempted_check，摘两处源码中的注释。
// UpdateInstanceLastAttemptedCheck updates the last_attempted_check timestamp in the orchestrator backed database
// for a given instance.
// This is used as a failsafe mechanism in case access to the instance gets hung (it happens), in which case
// the entire ReadTopology gets stuck (and no, connection timeout nor driver timeouts don't help. Don't look at me,
// the world is a harsh place to live in).
// And so we make sure to note down *before* we even attempt to access the instance; and this raises a red flag when we
// wish to access the instance again: if last_attempted_check is *newer* than last_checked, that's bad news and means
// we have a "hanging" issue.
func UpdateInstanceLastAttemptedCheck(instanceKey *InstanceKey) error {
writeFunc := func() error {
_, err := db.ExecOrchestrator(`
update
database_instance
set
last_attempted_check = NOW()
where
hostname = ?
and port = ?`,
instanceKey.Hostname,
instanceKey.Port,
)
return log.Errore(err)
}
return ExecDBWriteFunc(writeFunc)
}
// ValidSecondsFromSeenToLastAttemptedCheck returns the maximum allowed elapsed time
// between last_attempted_check to last_checked before we consider the instance as invalid.
func ValidSecondsFromSeenToLastAttemptedCheck() uint {
return config.Config.InstancePollSeconds + 1
}
判断实例是否存活
被orch监控的实例是否正常，通过如下方式进行判断：
// instance每隔InstancePollSeconds时间通过如下方式判断某个instance是否正常
select ifnull(last_checked
