Slide 1

Slide 1 text

No content

Slide 2

Slide 2 text

No content

Slide 3

Slide 3 text

No content

Slide 4

Slide 4 text

Scalability

Slide 5

Slide 5 text

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

Slide 6

Slide 6 text

High availability

Slide 7

Slide 7 text

Horizontal scalability

Slide 8

Slide 8 text

Replication

Slide 9

Slide 9 text

Master Slave Bin logs SELECT INSERT UPDATE DELETE

Slide 10

Slide 10 text

Read/write splitting

Slide 11

Slide 11 text

Passive Active

Slide 12

Slide 12 text

Prone to failure

Slide 13

Slide 13 text

Measures

Slide 14

Slide 14 text

No content

Slide 15

Slide 15 text

No content

Slide 16

Slide 16 text

Hi, I'm Thijs

Slide 17

Slide 17 text

I'm an Evangelist at

Slide 18

Slide 18 text

I'm @thijsferyn

Slide 19

Slide 19 text

No content

Slide 20

Slide 20 text

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

Slide 21

Slide 21 text

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

Slide 22

Slide 22 text

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

Slide 23

Slide 23 text

In case of failure

Slide 24

Slide 24 text

No content

Slide 25

Slide 25 text

A MySQL high availability and replication management tool

Slide 26

Slide 26 text

✓Discovery ✓Refactoring ✓Recovery ✓Interface Orchestrator

Slide 27

Slide 27 text

No content

Slide 28

Slide 28 text

Only register master

Slide 29

Slide 29 text

No content

Slide 30

Slide 30 text

No content

Slide 31

Slide 31 text

No content

Slide 32

Slide 32 text

Orchestrator 1 Orchestrator 2 Orchestrator 3 Orchestrator DB Master Slave 1 Slave 2

Slide 33

Slide 33 text

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

Slide 34

Slide 34 text

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

Slide 35

Slide 35 text

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

Slide 36

Slide 36 text

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

Slide 37

Slide 37 text

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

Slide 38

Slide 38 text

Master

Slide 39

Slide 39 text

Slave

Slide 40

Slide 40 text

Slave is down

Slide 41

Slide 41 text

Why does this matter?

Slide 42

Slide 42 text

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

Slide 43

Slide 43 text

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

Slide 44

Slide 44 text

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

Slide 45

Slide 45 text

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

Slide 46

Slide 46 text

No content

Slide 47

Slide 47 text

No content

Slide 48

Slide 48 text

No content

Slide 49

Slide 49 text

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

Slide 50

Slide 50 text

No content

Slide 51

Slide 51 text

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

Slide 52

Slide 52 text

No content

Slide 53

Slide 53 text

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

Slide 54

Slide 54 text

Manual master recovery

Slide 55

Slide 55 text

Or re-attach as slave

Slide 56

Slide 56 text

No content

Slide 57

Slide 57 text

... { "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

Slide 58

Slide 58 text

No content

Slide 59

Slide 59 text

No content

Slide 60

Slide 60 text

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

Slide 61

Slide 61 text

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

Slide 62

Slide 62 text

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

Slide 63

Slide 63 text

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

Slide 64

Slide 64 text

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

Slide 65

Slide 65 text

Your reads and writes are still split

Slide 66

Slide 66 text

No content

Slide 67

Slide 67 text

https://github.com/sysown/proxysql

Slide 68

Slide 68 text

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

Slide 69

Slide 69 text

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

Slide 70

Slide 70 text

cat <

Slide 71

Slide 71 text

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

Slide 72

Slide 72 text

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

Slide 73

Slide 73 text

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

Slide 74

Slide 74 text

$ 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

Slide 75

Slide 75 text

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)

Slide 76

Slide 76 text

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

Slide 77

Slide 77 text

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

Slide 78

Slide 78 text

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

Slide 79

Slide 79 text

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

Slide 80

Slide 80 text

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

Slide 81

Slide 81 text

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

Slide 82

Slide 82 text

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

Slide 83

Slide 83 text

How do you synchronise ProxySQL & Orchestrator?

Slide 84

Slide 84 text

✓Fetch master from Consul agent ✓Use Consul template ✓Parse master IP into ProxySQL config file ✓Remove ProxySQL DB file ✓Reload ProxySQL ProxySQL & Orchestrator sync

Slide 85

Slide 85 text

Use the failover hooks

Slide 86

Slide 86 text

I wrote a PHP script

Slide 87

Slide 87 text

$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

Slide 88

Slide 88 text

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

Slide 89

Slide 89 text

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

Slide 90

Slide 90 text

$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

Slide 91

Slide 91 text

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

Slide 92

Slide 92 text

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

Slide 93

Slide 93 text

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

Slide 94

Slide 94 text

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

Slide 95

Slide 95 text

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

Slide 96

Slide 96 text

No content

Slide 97

Slide 97 text

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