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

MySQL high availability & autofailover with Pro...

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.

Thijs Feryn

November 29, 2018
Tweet

More Decks by Thijs Feryn

Other Decks in Technology

Transcript

  1. 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
  2. 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
  3. 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
  4. 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
  5. { "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" }
  6. { "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" }
  7. ... "RecoverMasterClusterFilters": [ "*" ], "RecoverIntermediateMasterClusterFilters": [ "*" ], "ApplyMySQLPromotionAfterMasterFailover":

    true, "DetectClusterAliasQuery":"select substring_index(@@hostname,'.',1) as cluster_alias", ... orchestrator.conf.json
  8. ... { "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
  9. 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
  10. 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
  11. 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
  12. Config file Active config DB persisted DB runtime In case

    of restart When nothing is stored in DB
  13. 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
  14. $ 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
  15. 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)
  16. 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
  17. 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
  18. 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
  19. 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
  20. 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
  21. ✓Fetch master from Consul agent ✓Use Consul template ✓Parse master

    IP into ProxySQL config file ✓Remove ProxySQL DB file ✓Reload ProxySQL ProxySQL & Orchestrator sync
  22. $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
  23. 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
  24. 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
  25. $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
  26. 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
  27. ... { "PostFailoverProcesses": [ "curl -s http://proxy-sync-host:8080/" ], } ...

    orchestrator.conf.json Force ProxySQL sync on failover