Practical Orchestrator � Shlomi Noach GitHub Percona Live Europe 2017 � How people build so fu ware 1
Agenda • Se tu ing up orchestrator • Backend • Discovery � • Refactoring • Detection & recovery • Scripting • HA • Ra fu cluster • Deployment • Roadmap � How people build so fu ware 2
About me • Infrastructure engineer at GitHub • Member of the database-infrastructure team • MySQL community member � • Author of orchestrator, gh-ost, common_schema, freno, ccql and other open source tools. • Blog at openark.org github.com/shlomi-noach @ShlomiNoach � How people build so fu ware 3
GitHub � • The world’s largest Octocat T-shirt and stickers store • And water bo tu les • And hoodies • We also do stu ff related to things � How people build so fu ware 4
MySQL at GitHub • GitHub stores repositories in git , and uses MySQL as the backend database for all related metadata: • Repository metadata, users, issues, pull � requests, comments etc. • Website/API/Auth/more all use MySQL. • We run a few (growing number of) clusters, totaling around 100 MySQL servers. • The setup isn’t very large but very busy. • Our MySQL service must be highly available. � How people build so fu ware 5
Orchestrator, meta • Born, open sourced at Outbrain • Further development at Booking.com, main focus on failure detection & recovery � • Adopted, maintained & supported by GitHub, github.com/github/orchestrator • Orchestrator is free and open source, released under the Apache 2.0 license github.com/github/orchestrator/releases � How people build so fu ware 6
Orchestrator • Discovery Probe, read instances, build topology graph, a tu ributes, queries • Refactoring � Relocate replicas, manipulate, detach, reorganize • Recovery Analyze, detect crash scenarios, structure warnings, failovers, promotions, acknowledgements, flap control, downtime, hooks � How people build so fu ware 7
Deployment in a nutshell � � � orchestrator � � � � � � � � � � backend DB � � � � How people build so fu ware 8
Deployment in a nutshell • orchestrator runs as a service • It is mostly stateless (except for pending operations) � • State is stored in backend DB (MySQL/SQLite) • orchestrator continuously discovers/probes MySQL topology servers • Connects as client over MySQL protocol • Agent-less (though an agent design exists) � How people build so fu ware 9
Agenda • Se tu ing up orchestrator • Backend • Discovery � • Refactoring • Detection & recovery • Scripting • HA • Ra fu cluster • Deployment • Roadmap � How people build so fu ware 10
Basic & backend setup • Let orchestrator know where to find backend database • Backend can be MySQL or SQLite � • MySQL configuration sample • Serve HTTP on :3000 { "Debug": false, "ListenAddress": ":3000", "MySQLOrchestratorHost": "orchestrator.backend.master.com", "MySQLOrchestratorPort": 3306, "MySQLOrchestratorDatabase": "orchestrator", "MySQLOrchestratorCredentialsConfigFile": "/etc/mysql/orchestrator-backend.cnf", } � How people build so fu ware 11
Grants on MySQL backend � CREATE USER 'orchestrator_srv'@'orc_host' IDENTIFIED BY 'orc_server_password'; GRANT ALL ON orchestrator.* TO 'orchestrator_srv'@'orc_host'; � How people build so fu ware 12
SQLite backend • Only applicable for: • standalone setups (dev, testing) • Ra fu setup (discussed later) � • Embedded with orchestrator. • No need for MySQL backend. No backend credentials. { "BackendDB": "sqlite", "SQLite3DataFile": “/var/lib/orchestrator/orchestrator.db”, } � How people build so fu ware 13
Agenda • Se tu ing up orchestrator • Backend • Discovery � • Refactoring • Detection & recovery • Scripting • HA • Ra fu cluster • Deployment • Roadmap � How people build so fu ware 14
Discovery: polling servers • Provide credentials • Orchestrator will crawl its way and figure out the topology • SHOW SLAVE HOSTS requires report_host and report_port � on servers { "MySQLTopologyCredentialsConfigFile": "/etc/mysql/orchestrator-topology.cnf", "InstancePollSeconds": 5, "DiscoverByShowSlaveHosts": false, } � How people build so fu ware 15
Discovery: polling servers • Or, plaintext credentials � { "MySQLTopologyUser": "wallace", "MySQLTopologyPassword": "grom1t", } � How people build so fu ware 16
Grants on topologies • meta schema to be used shortly � CREATE USER 'orchestrator'@'orc_host' IDENTIFIED BY 'orc_topology_password'; GRANT SUPER, PROCESS, REPLICATION SLAVE, REPLICATION CLIENT, RELOAD ON *.* TO 'orchestrator'@'orc_host'; GRANT SELECT ON meta.* TO 'orchestrator'@'orc_host'; � How people build so fu ware 17
Discovery: name resolve • Resolve & normalize hostnames • via DNS • via MySQL � { "HostnameResolveMethod": "default", "MySQLHostnameResolveMethod": "@@hostname" } � How people build so fu ware 18
Discovery: classifying servers • Which cluster? • Which data center? • By hostname regexp or by query � • Custom replication lag query { "ReplicationLagQuery": "select absolute_lag from meta.heartbeat_view", "DetectClusterAliasQuery": "select ifnull(max(cluster_name), '') as cluster_alias from meta.cluster where anchor=1", "DetectClusterDomainQuery": "select ifnull(max(cluster_domain), '') as cluster_domain from meta.cluster where anchor=1", "DataCenterPattern": "", "DetectDataCenterQuery": "select substring_index(substring_index(@@hostname, '-', 3), '-', -1) as dc", "PhysicalEnvironmentPattern": "", } � How people build so fu ware 19
Discovery: populating cluster info • Use meta schema • Populate via puppet � 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; mysql meta -e "INSERT INTO cluster (anchor, cluster_name, cluster_domain) \ VALUES (1, '${cluster_name}', '${cluster_domain}') \ ON DUPLICATE KEY UPDATE \ cluster_name=VALUES(cluster_name), cluster_domain=VALUES(cluster_domain)" � How people build so fu ware 20
Pseudo-GTID • Injecting Pseudo-GTID by issuing no-op DROP VIEW statements, detected both in SBR and RBR • This isn’t visible in table data � • Possibly updating a meta table to learn about Pseudo-GTID updates. set @pseudo_gtid_hint := concat_ws(':', lpad( hex (unix_timestamp(@now)), 8, '0'), lpad( hex (@connection_id), 16, '0'), lpad( hex (@rand), 8, '0')); set @_pgtid_statement := concat('drop ', 'view if exists `meta`.`_pseudo_gtid_', 'hint__asc:', @pseudo_gtid_hint, '`'); prepare st FROM @_pgtid_statement; execute st; deallocate prepare st; insert into meta.pseudo_gtid_status ( anchor, ..., pseudo_gtid_hint ) values (1, ..., @pseudo_gtid_hint) on duplicate key update ... pseudo_gtid_hint = values (pseudo_gtid_hint) � How people build so fu ware 21
Pseudo-GTID • Identifying Pseudo-GTID events in binary/relay logs • Heuristics for optimized search • Meta table lookup to heuristically identify Pseudo-GTID is � available { "PseudoGTIDPattern": "drop view if exists `meta`.`_pseudo_gtid_hint__asc:", "PseudoGTIDPatternIsFixedSubstring": true, "PseudoGTIDMonotonicHint": "asc:", "DetectPseudoGTIDQuery": "select count(*) as pseudo_gtid_exists from meta.pseudo_gtid_status where anchor = 1 and time_generated > now() - interval 2 hour", } � How people build so fu ware 22
Pseudo GTID binary logs relay logs binary logs insert insert insert > PGTID 17 > PGTID 17 > PGTID 17 update update update delete delete delete create create create > PGTID 56 > PGTID 56 > PGTID 56 � � delete delete delete delete delete delete > PGTID 82 > PGTID 82 > PGTID 82 master replica insert insert insert insert insert insert update update drop drop update � How people build so fu ware 23
Running from command line • Scripts, cron jobs, automation and manual labor all benefit from executing orchestrator from the command line. � • Depending on our deployment, we may choose orchestrator-client or the orchestrator binary • Discussed in depth later on • Spoiler: orchestrator CLI binary only supported on shared backend. orchestrator/ra fu requires orchestrator-client . • The two have similar interface. � How people build so fu ware 24
Deployment, CLI � � � orchestrator � � � � � � � � orchestrator, cli � Shared backend DB � How people build so fu ware 25
Recommend
More recommend
