Percona XtraDB Cluster Install the first node of the cluster Install the first node of the cluster Install subsequent nodes to the cluster Install subsequent nodes to the cluster Install HAProxy on the application server Install HAProxy on the application server Testing with a real-world application Testing with a real-world application
Full data SST – New node New node – Node long time disconnected Node long time disconnected Incremental IST Incremental IST – Node disconnected short time Node disconnected short time
Mysqldump – Small databases Small databases Rsync Rsync – Donor disconnected for copy time Donor disconnected for copy time – faster faster XtraBackup XtraBackup – Donor disconnected for short time Donor disconnected for short time – slower slower
Configuration version Configuration version wsrep_conf_id wsrep_conf_id – Number of active nodes Number of active nodes wsrep_cluster_status wsrep_cluster_status – Should be “Primary” Should be “Primary”
Should be “On” Should be “On” wsrep_local_state_comment wsrep_local_state_comment – Status message Status message wsep_local_send_q_avg wsep_local_send_q_avg – Possible network bottleneck Possible network bottleneck wsrep_flow_control_paused wsrep_flow_control_paused – Replication lag Replication lag
commits Before source return commits – Certify trx on all other nodes Certify trx on all other nodes Nodes reject on locking conflicts Nodes reject on locking conflicts Commit successfully if no conflicts on Commit successfully if no conflicts on any node any node
Writing to only one node Writing to only one node – All pessimistic locking happens on one node All pessimistic locking happens on one node Different nodes can handle writes for Different nodes can handle writes for different datasets different datasets – Different database, tables, rows etc. Different database, tables, rows etc.
writes For writes – Best practice: single node Best practice: single node For reads For reads – All nodes load balanced All nodes load balanced glbd – Galera Load Balancer glbd – Galera Load Balancer Haproxy Haproxy
*:3307 mode tcp mode tcp default_backend pxc-back default_backend pxc-back backend pxc-back backend pxc-back mode tcp mode tcp balance leastconn balance leastconn option httpchk option httpchk server db1 192.168.1.100:3306 check port 9200 inter server db1 192.168.1.100:3306 check port 9200 inter 12000 rise 3 fall 3 12000 rise 3 fall 3 server db2 192.168.1.101:3306 check port 9200 inter server db2 192.168.1.101:3306 check port 9200 inter 12000 rise 3 fall 3 12000 rise 3 fall 3 server db3 192.168.1.102:3306 check port 9200 inter server db3 192.168.1.102:3306 check port 9200 inter 12000 rise 3 fall 3 12000 rise 3 fall 3
*:3308 mode tcp mode tcp default_backend pxc-onenode-back default_backend pxc-onenode-back backend pxc-onenode-back backend pxc-onenode-back mode tcp mode tcp balance leastconn balance leastconn option httpchk option httpchk server db1 192.168.1.100:3306 check port 9200 inter server db1 192.168.1.100:3306 check port 9200 inter 12000 rise 3 fall 3 12000 rise 3 fall 3 server db2 192.168.1.101:3306 check port 9200 inter server db2 192.168.1.101:3306 check port 9200 inter 12000 rise 3 fall 3 12000 rise 3 fall 3 backup backup server db3 192.168.1.102:3306 check port 9200 inter server db3 192.168.1.102:3306 check port 9200 inter 12000 rise 3 fall 3 12000 rise 3 fall 3 backup backup
appleboy
sugamasao
shigashiyama
nssv
kakehashi
%20(1).jpg){kind=avatar}
giftojabu1
nikkei_engineer_recruiting
ryder472
kidooonn
wa6sn
shonansurvivors
neuecc
.jpeg){kind=avatar}
arthur1
jonyablonski
shpigford
malarkey
marktimemedia
bryan
eitanlees
geeforr
skipperchong
sachag
hannesfritz
palkan
jlugia
![44 44 Configuring subsequent nodes Configuring subsequent nodes [mysqld]](https://files.speakerdeck.com/presentations/65f93150a46b0131fd0712b6182a0314/slide_43.jpg){kind=link}
