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