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

1. View Slide

2. View Slide

3. View Slide

4. Scalability
   

   View Slide

5. More
   ✓RAM
   ✓CPU
   ✓Diskspace
   ✓Disk I/O
   ✓Bandwidth
   Vertical
   scalabiblity
   

   View Slide

6. High availability
   

   View Slide

7. Horizontal
   scalability
   

   View Slide

8. Replication
   

   View Slide

9. Master Slave
   Bin logs
   SELECT
   INSERT
   UPDATE
   DELETE
   

   View Slide

10. Read/write splitting
    

    View Slide

11. Passive
    Active
    

    View Slide

12. Prone to failure
    

    View Slide

13. Measures
    

    View Slide

14. View Slide

15. View Slide

16. Hi, I'm Thijs
    

    View Slide

17. I'm an
    Evangelist
    at
    

    View Slide

18. I'm @thijsferyn
    

    View Slide

19. View Slide

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
    

    View Slide

21. CREATE USER 'replication'@'%' IDENTIFIED BY 'password';
    GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';
    On all servers
    

    View Slide

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
    

    View Slide

23. In case of failure
    

    View Slide

24. View Slide

25. A MySQL high availability
    and replication
    management tool
    

    View Slide

26. ✓Discovery
    ✓Refactoring
    ✓Recovery
    ✓Interface
    Orchestrator
    

    View Slide

27. View Slide

28. Only
    register
    master
    

    View Slide

29. View Slide

30. View Slide

31. View Slide

32. Orchestrator 1
    Orchestrator 2
    Orchestrator 3
    Orchestrator
    DB
    Master
    Slave 1
    Slave 2
    

    View Slide

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
    

    View Slide

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
    

    View Slide

35. ...
    "MySQLOrchestratorHost": "127.0.0.1",
    "MySQLOrchestratorPort": 3306,
    "MySQLOrchestratorDatabase": "orchestrator",
    "MySQLOrchestratorUser": "orchestrator",
    "MySQLOrchestratorPassword": "orch_backend_password",
    ...
    orchestrator.conf.json
    

    View Slide

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
    

    View Slide

37. ...
    "MySQLTopologyUser": "orchestrator",
    "MySQLTopologyPassword": "orch_topology_password",
    ...
    orchestrator.conf.json
    

    View Slide

38. Master
    

    View Slide

39. Slave
    

    View Slide

40. Slave is
    down
    

    View Slide

41. Why does this matter?
    

    View Slide

42. http://185.115.218.72:3000/web/cluster/alias/orchestrator
    http://185.115.218.72:3000/api/cluster/alias/orchestrator
    

    View Slide

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}'
    

    View Slide

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"
    }
    

    View Slide

45. https://github.com/mcrauwel/go-check-orchestrator
    Nagios
    check
    Checks
    clusters
    Checks
    Orchestrator
    

    View Slide

46. View Slide

47. View Slide

48. View Slide

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"
    }
    

    View Slide

50. View Slide

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
    

    View Slide

52. View Slide

53. ...
    "RecoverMasterClusterFilters": [
    "*"
    ],
    "RecoverIntermediateMasterClusterFilters": [
    "*"
    ],
    "ApplyMySQLPromotionAfterMasterFailover": true,
    "DetectClusterAliasQuery":"select
    substring_index(@@hostname,'.',1) as cluster_alias",
    ...
    orchestrator.conf.json
    

    View Slide

54. Manual master
    recovery
    

    View Slide

55. Or re-attach as
    slave
    

    View Slide

56. View Slide

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
    

    View Slide

58. View Slide

59. View Slide

60. ...
    "KVClusterMasterPrefix": "mysql/master",
    "ConsulAddress": "localhost:8500",
    ...
    orchestrator.conf.json
    

    View Slide

61. https://github.com/hashicorp/consul-template
    Perform
    post-template
    action
    Template
    language
    Populate
    values from Consul
    Requires
    Consul agent
    

    View Slide

62. Consul template
    Orchestrator
    Orchestrator DB
    Master
    Slave 1
    Slave 2
    Consul
    Loadbalancer
    

    View Slide

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
    

    View Slide

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
    

    View Slide

65. Your reads and
    writes are still split
    

    View Slide

66. View Slide

67. https://github.com/sysown/proxysql
    

    View Slide

68. ✓MySQL aware
    ✓Query caching
    ✓Query routing
    ✓Statistics
    ✓Read write splitting
    ✓Failover
    ProxySQL
    

    View Slide

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
    

    View Slide

70. cat <[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
    

    View Slide

71. Config file
    Active
    config
    DB persisted DB runtime
    In case of
    restart
    When
    nothing is
    stored in DB
    

    View Slide

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
    

    View Slide

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=
    (
    )
    

    View Slide

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
    

    View Slide

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)
    

    View Slide

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
    

    View Slide

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
    

    View Slide

78. INSERT INTO mysql_replication_hostgroups VALUES (0,1,'null');
    Set hostgroups
    Writer
    group
    Reader
    group
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

82. $ mysql -u my_user -h 127.0.0.1 -P6033 -p my_db
    Accessing ProxySQL
    

    View Slide

83. How do you synchronise
    ProxySQL & Orchestrator?
    

    View Slide

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
    

    View Slide

85. Use the failover hooks
    

    View Slide

86. I wrote a PHP script
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

92. ...
    {
    "PostFailoverProcesses": [
    "curl -s http://proxy-sync-host:8080/"
    ],
    }
    ...
    orchestrator.conf.json
    Force
    ProxySQL sync
    on failover
    

    View Slide

93. Orchestrator
    Orchestrator DB
    Master
    Slave 1
    Slave 2
    ProxySQL
    Proxy-sync
    

    View Slide

94. ✓Single point of entry
    ✓Read write splitting
    ✓Automatic failover
    ✓Automatic master promotion
    End result
    

    View Slide

95. https://github.com/ThijsFeryn/proxysql-orchestrator-sync
    

    View Slide

96. View Slide

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

    View Slide