MySQL high availability & autofailover with ProxySQL & Orchestrator - Codemotion Milan 2018

This presentation showcases GitHub's Orchestrator tool that manages MySQL replicasets and also ProxySQL, a MySQL-aware proxy server.

See https://feryn.eu/speaking/mysql-high-availability-autofailover-proxysql-orchestrator-codemotion-milan-2018/ for more information about this presentation.

Transcript

4. Scalability
5. More ✓RAM ✓CPU ✓Diskspace ✓Disk I/O ✓Bandwidth Vertical scalabiblity
6. High availability
7. Horizontal scalability
8. Replication
9. Master Slave Bin logs SELECT INSERT UPDATE DELETE
10. Read/write splitting
11. Passive Active
12. Prone to failure
13. Measures
16. Hi, I'm Thijs
17. I'm an Evangelist at
18. I'm @thijsferyn
20. Replication [mysqld] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql log-error = /var/log/mysql/error.log bind-address = 0.0.0.0 log-bin = mysql-bin log-slave-updates = 1 server-id = 1 [mysqld] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql log-error = /var/log/mysql/error.log bind-address = 0.0.0.0 log-bin = mysql-bin log-slave-updates = 1 server-id = 2 Master Slave
21. CREATE USER 'replication'@'%' IDENTIFIED BY 'password'; GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%'; On all servers
22. STOP SLAVE; RESET SLAVE; CHANGE MASTER TO MASTER_HOST='master_host', MASTER_USER='replication', MASTER_PASSWORD='replication_password', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000016', MASTER_LOG_POS=1794, MASTER_CONNECT_RETRY=10; START SLAVE; On the slaves
23. In case of failure
25. A MySQL high availability and replication management tool
26. ✓Discovery ✓Refactoring ✓Recovery ✓Interface Orchestrator
28. Only register master
32. Orchestrator 1 Orchestrator 2 Orchestrator 3 Orchestrator DB Master Slave 1 Slave 2
33. curl -s https://packagecloud.io/install/repositories/ github/orchestrator/script.deb.sh | sudo bash curl -s https://packagecloud.io/install/repositories/ github/orchestrator/script.rpm.sh | sudo bash Install Orchestrator
34. CREATE DATABASE IF NOT EXISTS orchestrator; CREATE USER 'orchestrator'@'127.0.0.1' IDENTIFIED BY 'orch_backend_password'; GRANT ALL PRIVILEGES ON `orchestrator`.* TO 'orchestrator'@'127.0.0.1'; Orchestrator DB
35. ... "MySQLOrchestratorHost": "127.0.0.1", "MySQLOrchestratorPort": 3306, "MySQLOrchestratorDatabase": "orchestrator", "MySQLOrchestratorUser": "orchestrator", "MySQLOrchestratorPassword": "orch_backend_password", ... orchestrator.conf.json
36. CREATE USER 'orchestrator'@'orch_host' IDENTIFIED BY 'orch_topology_password'; GRANT SUPER, PROCESS, REPLICATION SLAVE, RELOAD ON *.* TO 'orchestrator'@'orch_host'; GRANT SELECT ON mysql.slave_master_info TO 'orchestrator'@'orch_host'; On all DB nodes
37. ... "MySQLTopologyUser": "orchestrator", "MySQLTopologyPassword": "orch_topology_password", ... orchestrator.conf.json
38. Master
39. Slave
40. Slave is down
41. Why does this matter?
42. http://185.115.218.72:3000/web/cluster/alias/orchestrator http://185.115.218.72:3000/api/cluster/alias/orchestrator
43. curl http://185.115.218.72:3000/api/cluster/alias/orchestrator | jq '.[] | {"Hostname":.Key.Hostname, "IsDowntimed":.IsDowntimed, "IsLastCheckValid":.IsLastCheckValid, "Slave_SQL_Running":.Slave_SQL_Running, "Slave_IO_Running":.Slave_IO_Running, "Master":.MasterKey.Hostname}'
44. { "Hostname": "orchestrator.master.192.168.0.13.xip.io", "IsDowntimed": false, "IsLastCheckValid": true, "Slave_SQL_Running": false, "Slave_IO_Running": false, "Master": "" } { "Hostname": "orchestrator.slave1.192.168.0.14.xip.io", "IsDowntimed": false, "IsLastCheckValid": true, "Slave_SQL_Running": true, "Slave_IO_Running": true, "Master": "orchestrator.master.192.168.0.13.xip.io" } { "Hostname": "orchestrator.slave2.192.168.0.17.xip.io", "IsDowntimed": false, "IsLastCheckValid": false, "Slave_SQL_Running": true, "Slave_IO_Running": true, "Master": "orchestrator.master.192.168.0.13.xip.io" }
45. https://github.com/mcrauwel/go-check-orchestrator Nagios check Checks clusters Checks Orchestrator
49. { "Hostname": "orchestrator.slave1.192.168.0.14.xip.io", "IsDowntimed": false, "IsLastCheckValid": true, "Slave_SQL_Running": false, "Slave_IO_Running": false, "Master": "" } { "Hostname": "orchestrator.slave2.192.168.0.17.xip.io", "IsDowntimed": false, "IsLastCheckValid": true, "Slave_SQL_Running": true, "Slave_IO_Running": true, "Master": "orchestrator.slave1.192.168.0.14.xip.io" }
51. ... "RaftEnabled": true, "RaftDataDir": "/var/lib/orchestrator", "RaftBind": "192.168.0.9", "DefaultRaftPort": 10008, "RaftNodes": [ "192.168.0.8", "192.168.0.9", "192.168.0.19" ], ... orchestrator.conf.json
53. ... "RecoverMasterClusterFilters": [ "*" ], "RecoverIntermediateMasterClusterFilters": [ "*" ], "ApplyMySQLPromotionAfterMasterFailover": true, "DetectClusterAliasQuery":"select substring_index(@@hostname,'.',1) as cluster_alias", ... orchestrator.conf.json
54. Manual master recovery
55. Or re-attach as slave
57. ... { "OnFailureDetectionProcesses": [ "echo 'Detected {failureType} on {failureCluster}. Affected replicas: {countReplicas}' >> /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": [], } ... orchestrator.conf.json
60. ... "KVClusterMasterPrefix": "mysql/master", "ConsulAddress": "localhost:8500", ... orchestrator.conf.json
61. https://github.com/hashicorp/consul-template Perform post-template action Template language Populate values from Consul Requires Consul agent
62. Consul template Orchestrator Orchestrator DB Master Slave 1 Slave 2 Consul Loadbalancer
63. listen mysql-master bind 192.168.0.22:3306 mode tcp option mysql-check user haproxy_check balance roundrobin server master {{ key "mysql/master/orchestrator/ipv4" }}:{{ key "mysql/master/orchestrator/port" }} check listen mysql-slaves bind 192.168.0.20:3306 mode tcp option mysql-check user haproxy_check balance roundrobin server slave1 192.168.0.5:3306 check server slave2 192.168.0.7:3306 check server slave3 192.168.0.15:3306 check Consul template HAProxy template
64. consul-template -template '/var/consul/haproxy.cfg:/etc/haproxy/ haproxy.cfg:/etc/init.d/haproxy reload' -consul-addr=127.0.0.1:8501 Consul template command reload HAProxy local Consul agent
65. Your reads and writes are still split
67. https://github.com/sysown/proxysql
68. ✓MySQL aware ✓Query caching ✓Query routing ✓Statistics ✓Read write splitting ✓Failover ProxySQL
69. apt-get install -y lsb-release wget -O - 'http://repo.proxysql.com/ProxySQL/repo_pub_key' | apt-key add - echo deb http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/$(lsb_release - sc)/ ./ \ | tee /etc/apt/sources.list.d/proxysql.list apt-get update apt-get install proxysql Install ProxySQL DEB
70. cat <<EOF | tee /etc/yum.repos.d/proxysql.repo [proxysql_repo] name= ProxySQL YUM repository baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/\ $releasever gpgcheck=1 gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key EOF yum install proxysql Install ProxySQL RPM
71. Config file Active config DB persisted DB runtime In case of restart When nothing is stored in DB
72. datadir="/var/lib/proxysql" admin_variables= { admin_credentials="admin:password" mysql_ifaces="0.0.0.0:6032" } mysql_variables= { threads=4 max_connections=2048 default_query_delay=0 default_query_timeout=36000000 have_compress=true poll_timeout=2000 interfaces="0.0.0.0:6033" default_schema="information_schema" stacksize=1048576 server_version="5.5.30" connect_timeout_server=3000 monitor_username="monitor" monitor_password="monitor" monitor_history=600000 monitor_connect_interval=60000 monitor_ping_interval=10000 monitor_read_only_interval=1500 monitor_read_only_timeout=500 ping_interval_server_msec=120000 ping_timeout_server=500 commands_stats=true sessions_sort=true connect_retries_on_failure=10
73. monitor_ping_interval=10000 monitor_read_only_interval=1500 monitor_read_only_timeout=500 ping_interval_server_msec=120000 ping_timeout_server=500 commands_stats=true sessions_sort=true connect_retries_on_failure=10 } mysql_servers = ( ) mysql_users: ( ) mysql_query_rules: ( ) scheduler= ( ) mysql_replication_hostgroups= ( )
74. $ mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> ' Admin> Admin> SHOW DATABASES; +-----+---------+-------------------------------+ | seq | name | file | +-----+---------+-------------------------------+ | 0 | main | | | 2 | disk | /var/lib/proxysql/proxysql.db | | 3 | stats | | | 4 | monitor | | +-----+---------+-------------------------------+ 4 rows in set (0.00 sec) Managing ProxySQL
75. Admin> show tables; +--------------------------------------------+ | tables | +--------------------------------------------+ | global_variables | | mysql_collations | | mysql_group_replication_hostgroups | | mysql_query_rules | | mysql_query_rules_fast_routing | | mysql_replication_hostgroups | | mysql_servers | | mysql_users | | proxysql_servers | | runtime_checksums_values | | runtime_global_variables | | runtime_mysql_group_replication_hostgroups | | runtime_mysql_query_rules | | runtime_mysql_query_rules_fast_routing | | runtime_mysql_replication_hostgroups | | runtime_mysql_servers | | runtime_mysql_users | | runtime_proxysql_servers | | runtime_scheduler | | scheduler | +--------------------------------------------+ 20 rows in set (0.01 sec)
76. UPDATE global_variables SET variable_value = 'proxysql_check' WHERE variable_name='mysql-monitor_username'; UPDATE global_variables SET variable_value = 'password' WHERE variable_name='mysql-monitor_password'; UPDATE global_variables SET variable_value = 'admin:password;remote:password' WHERE variable_name='admin-admin_credentials'; UPDATE global_variables SET variable_value = 'haproxy_check:' WHERE variable_name='admin-stats_credentials'; Set variables
77. INSERT INTO mysql_servers (hostname, hostgroup_id, weight) VALUES ('orchestrator.master.192.168.0.13.xip.io', 0, 1); INSERT INTO mysql_servers (hostname, hostgroup_id, weight) VALUES ('orchestrator.slave1.192.168.0.14.xip.io', 1, 1); INSERT INTO mysql_servers (hostname, hostgroup_id, weight) VALUES ('orchestrator.slave2.192.168.0.17.xip.io', 1, 1); Provision servers
78. INSERT INTO mysql_replication_hostgroups VALUES (0,1,'null'); Set hostgroups Writer group Reader group
79. INSERT INTO mysql_users (username, password, default_schema) VALUES ('my_user','password','my_db'); INSERT INTO mysql_users (username, password, default_schema) VALUES ('haproxy_check','',''); Provision users
80. INSERT INTO mysql_query_rules (active, match_pattern, destination_hostgroup, cache_ttl,apply) VALUES (1, '^SELECT .* FOR UPDATE', 0, NULL,1); INSERT INTO mysql_query_rules (active, match_pattern, destination_hostgroup, cache_ttl,apply) VALUES (1, '^SELECT .*', 1, NULL,1); Router queries
81. LOAD ADMIN VARIABLES TO RUNTIME; SAVE ADMIN VARIABLES TO DISK; LOAD MYSQL VARIABLES TO RUNTIME; SAVE MYSQL VARIABLES TO DISK; LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK; LOAD MYSQL USERS TO RUNTIME; SAVE MYSQL USERS TO DISK; LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK; Load config
82. $ mysql -u my_user -h 127.0.0.1 -P6033 -p my_db Accessing ProxySQL
83. How do you synchronise ProxySQL & Orchestrator?
84. ✓Fetch master from Consul agent ✓Use Consul template ✓Parse master IP into ProxySQL config file ✓Remove ProxySQL DB file ✓Reload ProxySQL ProxySQL & Orchestrator sync
85. Use the failover hooks
86. I wrote a PHP script
87. $response = $client->request( 'GET', 'cluster/alias/'.$config['orchestrator']['clusterAlias'], [ 'auth' => [ $config['orchestrator']['servers'][$index]['username'], $config['orchestrator']['servers'][$index]['password'] ] ] ); $body = json_decode($response->getBody()); Fetch cluster status from API
88. foreach ($body as $server) { if($server->IsDowntimed) { $logger->debug('Server "'.$server->Key->Hostname.'" is in scheduled downtime until '. $server->DowntimeEndTimestamp.' by '.$server->DowntimeOwner.': ' . $server->DowntimeReason); } elseif($server->IsLastCheckValid){ if($server->MasterKey->Hostname == ""){ $servers->masters[] = $server->Key->Hostname; $logger->debug('Master: '.$server->Key->Hostname); } elseif($server->Slave_SQL_Running == false || $server->Slave_IO_Running == false) { $logger->debug('Slave not replicating: '.$server->Key->Hostname); } else { $servers->slaves[] = $server->Key->Hostname; $logger->debug('Slave: '.$server->Key->Hostname); } } } return $servers; Discover masters & slaves
89. foreach ($proxySqlServers as $proxySqlHostname => $proxySqlServer){ try { $proxySqlServer->beginTransaction(); $proxySqlServer->query('DELETE FROM `mysql_servers`'); foreach ($servers->masters as $master) { $proxySqlServer->query("INSERT INTO `mysql_servers` (`hostgroup_id`,`hostname`,`port`) VALUES ('0','".$master."','3306')"); } foreach ($servers->slaves as $slave) { $proxySqlServer->query("INSERT INTO `mysql_servers` (`hostgroup_id`,`hostname`,`port`) VALUES ('1','".$slave."','3306')"); } $proxySqlServer->query('LOAD MYSQL SERVERS FROM MEMORY'); $proxySqlServer->query('SAVE MYSQL SERVERS TO DISK'); } catch (Exception $e ){ $proxySqlServer->rollback(); } } Re-populate ProxySQL
90. $loop = React\EventLoop\Factory::create(); //Force ProxySql update based on HTTP call $server = new HttpServer(function (ServerRequestInterface $request) use ($callOrchestratorApi, $updateProxySql, $logger) { $logger->debug('Web request triggered from '.$request->getServerParams() ['REMOTE_ADDR']); $updateProxySql($callOrchestratorApi(),true); return new Response( 200, array( 'Content-Type' => 'text/plain' ), "OK\n" ); }); $socket = new SocketServer($config['proxy-sync']['binding'], $loop); $server->listen($socket); //Periodic ProxySql update $loop->addPeriodicTimer($pollInterval, function () use ($callOrchestratorApi, $updateProxySql) { $updateProxySql($callOrchestratorApi()); }); $loop->run(); Periodic & forced checks
91. proxy-sync: binding: 8080 orchestrator: servers: - url: http://orchestrator:3000/api/ username: orchestrator-username password: orchestrator-password pollInterval: 10 clusterAlias: yourCluster proxysql: servers: - hostname: proxysql-hostname username: proxysql-username password: proxysql-password port: 6032 Config
92. ... { "PostFailoverProcesses": [ "curl -s http://proxy-sync-host:8080/" ], } ... orchestrator.conf.json Force ProxySQL sync on failover
93. Orchestrator Orchestrator DB Master Slave 1 Slave 2 ProxySQL Proxy-sync
94. ✓Single point of entry ✓Read write splitting ✓Automatic failover ✓Automatic master promotion End result
95. https://github.com/ThijsFeryn/proxysql-orchestrator-sync
97. https://feryn.eu https://twitter.com/ThijsFeryn https://instagram.com/ThijsFeryn