Percona XtraDB 集群内部

Percona XtraDB Cluster in a Nutshell Frédéric Descamps Seppo Jaakola Percona Live UK (London) 2012

Who are we ?

FrĂŠdĂŠric Descamps

Seppo Jaakola

@lefred

@codership

Percona Consultant

Founder of Codership

http://about.me/lefred devops believer Managing MySQL since 3.23 (as far as I remember)

www.percona.com

Agenda ●

●

What is Percona XtraDB Cluster (in few words) Hands on !

www.percona.com

What is PXC ?

+ = www.percona.com

To remember Important urls http://www.codership.com/wiki/doku.php?id=galera_parameters http://www.codership.com/wiki/doku.php?id=mysql_options_0.8

www.percona.com

Present â—?

Currently we have one node (production) running Percona Server

www.percona.com

Future â—? â—?

We want to have a 3 nodes PXC We want to have a smooth migration with minimum downtime

www.percona.com

The Plan ● ● ● ● ●

Configure PXC on node2 and node3 Take a backup of node1 Restore the backup on node2 Play with the 2 nodes cluster Setup the current production server as 3rd node

www.percona.com

Connect to your servers â—?

Test the connection (ssh) to all your servers (node1, node2 and node3) login: root password: vagrant ssh -p 2221 root@127.0.0.1 (node1) ssh -p 2222 root@127.0.0.1 (node2) ssh -p 2223 root@127.0.0.1 (node3)

www.percona.com

The production â—?

We have a script that simulate our production load while true do pluk.py sleep 5 done

www.percona.com

The production â—?

We have a script that simulate our Run the script production load(pluk.py) once on node1 while true do pluk.py sleep 5 done

www.percona.com

Install PXC ●

● ●

On node2 and node3, install PerconaXtraDB-Cluster-Server You should use yum (or apt) We will use rpm as the files are already downloaded in /usr/local/rpms

www.percona.com

Configure PXC [mysql] prompt="node2 mysql> " [mysqld] datadir=/var/lib/mysql user=mysql log_error=node2-error.log binlog_format=ROW innodb_log_file_size=64M innodb_locks_unsafe_for_binlog=1 innodb_autoinc_lock_mode=2 www.percona.com

Configure PXC (2) wsrep_cluster_name=pluk2k12 wsrep_cluster_address=gcomm://192.

168.70.1,192.168.70.2,192.168.70.3 wsrep_node_name=node2 wsrep_node_address=192.168.70.2 wsrep_provider=/usr/lib64/libgalera_smm.so wsrep_sst_method=rsync wsrep_slave_threads=4

www.percona.com

To remember ● Disable selinux ● wsrep_cluster_address now supports multiple entries, wsrep_urls in [mysqld_safe] is deprecated ● SST method is defined in my.cnf ● when wsrep_node_address is used we can omit wsrep_sst_receive_address, wsrep_node_incoming_address and ist. recv_addr

www.percona.com

Let's have a look... ● ●

Check MySQL error log, what do we see?

Check variables and status related to PXC ○

○ ●

SHOW GLOBAL VARIABLES LIKE 'wsrep%'; SHOW GLOBAL STATUS LIKE 'wsrep%';

Play with the cluster (follow instructor)

www.percona.com

To remember ● wsrep = 'Write Set Replicator' ● Settings are available with SHOW GLOBAL VARIABLES LIKE 'wsrep%'; ● Status counters available with SHOW GLOBAL STATUS LIKE 'wsrep%'; ● Are important to check cluster status: ○ wsrep_local_state_comment ○ wsrep_cluster_size ○ wsrep_cluster_status ○ wsrep_connected ○ wsrep_ready www.percona.com

What about State Snapshot Transfer (SST) ●

● ●

SST = full copy of cluster data to a specific node (from DONOR to JOINER) wsrep_sst_donor Multiple SST methods: ○ ○ ○ ○

skip rsync mysqldump xtrabackup

www.percona.com

What about State Snapshot Transfer (SST) ●

● ●

SST = full copy of cluster data to a specific node (from DONOR to JOINER) wsrep_sst_donor Multiple SST methods: ○ ○ ○ ○

skip rsync mysqldump xtrabackup

Test all SST methods www.percona.com

What about State Snapshot Transfer (SST) ●

● ●

SST = full copy of cluster data to a specific node (from DONOR to JOINER) wsrep_sst_donor No problem Multiple SST methods: with ○ ○ ○ ○

skip rsync mysqldump xtrabackup

mysldump ?

www.percona.com

To remember ● SST methods are not all the same. ● You can specify a donor per node ● Xtrabackup doesn't freeze the donor for the complete SST period ● Xtrabackup requires authentication parameter

www.percona.com

Loadbalancer ●

Let's configure a loadbalancer to connect to our cluster ○ ○

HA proxy (needs clustercheck) glb

Install and configure glb on node2

www.percona.com

Loadbalancer - glb glbd --daemon --threads 6 \ --control 127.0.0.1:4444 \ 192.168.70.2:3307 \ 192.168.70.2:3306 \ 192.168.70.3:3306

www.percona.com

Quorum and split brain â—?

PXC checks for Quorum to avoid split brain situation

www.percona.com

Quorum and split brain â—?

PXC checks for Quorum to avoid split brain situation

stop the communication between node2 and node3

www.percona.com

Quorum and split brain ●

●

BAD solution :-( wsrep_provider_options = “pc. ignore_quorum = true” and the GOOD solution.... next slide !

www.percona.com

Quorum and split brain (2) â—?

Galera Arbitration Daemon (garbd)

www.percona.com

Quorum and split brain (2) Galera Arbitration Daemon (garbd)

●

run garbd on node1 Test the following : ● ● ● ●

Stop mysql on node3: what's happening ?

Stop garbd on node1: what's happening ?

Start garbd on node1 and mysql on node3, block communication between node2 and node3, what's happening this time ?

Block communication between node1 and node3: what's happening ?

www.percona.com

To remember ● 3 nodes is the minimum recommended ! ● odd numbers of nodes are always better ● you can use a "fake" node (garbd) even to replicate through it !

www.percona.com

Incremental State Transfer (IST) ● ●

●

Used to avoid full SST (using gcache) gcache.size can be specified using wsrep_provider_options Now works even after a crash if the state is consistent

www.percona.com

Incremental State Transfer (IST) ● ●

●

Used to avoid full SST (using gcache) gcache.size can be specified using wsrep_provider_options Now works even after a crash if the state is consistent stop mysql on node3, run pluk.py on node2, restart node3 www.percona.com

Production Migration â—?

â—?

Backup the production server and restore the production on node2 Run pluk.py on node1

www.percona.com

Production Migration (2) ● ● ●

● ●

Start node3 Run pluk.py on node1 Start the async replication of node1 to node2 What about node3 ?

Run pluk.py on node1

www.percona.com

Production Migration (2)

www.percona.com

Production Migration (3) ●

● ● ● ●

●

Configure pluk.py to connect to the loadbalancer Run pluk.py Scratch data on node1 and install PXC Configure PXC on node1 Start the cluster on node1 (SST should be done with node3) Run pluk.py and check data on all nodes www.percona.com

Production Migration (3)

www.percona.com

Production Migration (4)

www.percona.com

Online Schema Changes ● ●

●

Total Order Isolation (TOI) Rolling Schema Upgrade (RSU) wsrep_OSU_method pt-online-schema-change

www.percona.com

Online Schema Changes (2) â—?

Create a large table to modify CREATE database pluk; use pluk; CREATE TABLE `actor` ( `actor_id` int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY, `first_name` varchar(45) NOT NULL, `last_name` varchar(45) NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB;

INSERT INTO actor (first_name, last_name) SELECT REPEAT('a', 45), REPEAT('b', 45) FROM dual; INSERT INTO actor (first_name, last_name) SELECT REPEAT('a', 45), REPEAT('b', 45) FROM actor;

repeat this step until it takes 10 sec+ www.percona.com

Online Schema Changes (3) â—?

â—?

Use all three methods while running pluk.py against your new database and add each time a new column Check pluk.py output

www.percona.com

Thank you !

Visit: http://www.percona.com/live/london-2012/ www.percona.com

Annual Percona Live MySQL Conference and Expo The Hyatt Regency Hotel, Santa Clara, CA

April 22nd-25th, 2013 Registration and Call for Papers are Open!

Visit: http://www.percona.com/live/mysql-conference-2013/ www.percona.com