Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

Ca901ddcea38854b9783781c91fc87c9?s=47 Thijs Feryn
November 29, 2018

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.

Ca901ddcea38854b9783781c91fc87c9?s=128

Thijs Feryn

November 29, 2018
Tweet

Transcript

  1. None
  2. None
  3. None
  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

  14. None
  15. None
  16. Hi, I'm Thijs

  17. I'm an Evangelist at

  18. I'm @thijsferyn

  19. None
  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

  24. None
  25. A MySQL high availability and replication management tool

  26. ✓Discovery ✓Refactoring ✓Recovery ✓Interface Orchestrator

  27. None
  28. Only register master

  29. None
  30. None
  31. None
  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

  46. None
  47. None
  48. None
  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" }
  50. None
  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
  52. None
  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

  56. None
  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
  58. None
  59. None
  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

  66. None
  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

  96. None
  97. https://feryn.eu https://twitter.com/ThijsFeryn https://instagram.com/ThijsFeryn