Percona XtraDB Cluster Webinar

(1)

Percona XtraDB Cluster Webinar

(2)

Tutorial Setup

Single master node; Two slave nodes node1 will also run sysbench

Sample application, simulating activity

2 / 38

(3)

Run Sample Application

Start the application and verify

Use another window for this or use screen

node1# run_sysbench_oltp.sh

Is replication owing to all the nodes?

(4)

XtraDB Cluster Tutorial

MIGRATING FROM MASTER/SLAVE

4 / 38

(5)

What is Our Plan?

We have an application working on node1.

We want to migrate our master/slave to a PXC cluster with minimal downtime.

We will build a cluster from one slave and then add the other servers to it one at a time.

(6)

Upgrade node3

node3# systemctl stop mysql

node3# yum swap -- remove Percona-Server-shared-57 Percona-Server-server-57 \ -- install Percona-XtraDB-Cluster-shared-57 Percona-XtraDB-Cluster-server-57 node3# systemctl start mysql

node3# mysql_upgrade

node3# mysql -e "show slave status\G"

node3# systemctl stop mysql -- For the next steps

The Percona XtraDB Cluster Server and Client packages are drop-in replacements for Percona Server and even community MySQL.

6 / 38

(7)

Con gure Node3 for PXC

[mysqld]

# Leave existing settings and add these binlog_format = ROW log-slave-updates

# galera settings

wsrep_provider = /usr/lib64/libgalera_smm.so wsrep_cluster_name = mycluster

wsrep_cluster_address = gcomm://node1,node2,node3 wsrep_node_name = node3

wsrep_node_address = 192.168.70.4 wsrep_sst_auth = sst:secret innodb_autoinc_lock_mode = 2

Start node3 with:

systemctl start mysql@bootstrap

(8)

Checking Cluster State

Use 'myq_status' to check the state of Galera on node3:

node3# /usr/local/bin/myq_status wsrep

Replication:

node3# mysql -e "show slave status\G"

8 / 38

(9)

Upgrade node2

We only need a single node replicating from our production master.

node2> STOP SLAVE; RESET SLAVE ALL;

Upgrade/Swap packages as before

node2# yum swap -- remove Percona-Server-shared-57 Percona-Server-server-57 \ -- install Percona-XtraDB-Cluster-shared-57 Percona-XtraDB-Cluster-server-57

Edit node2's my.cnf as you did for node3

What needs to be different from node3's con g?

(10)

node2's Con g

[mysqld]

# galera settings

10 / 38

(11)

What's going to happen?

When node2 is started, what's going to happen?

How does node2 get a copy of the dataset?

Can it use the existing data it already has?

Do we have to bootstrap node2?

(12)

State Snapshot Transfer (SST)

Transfer a full backup of an existing cluster member (donor) to a new node entering the cluster (joiner).

We con gured our SST method to use ‘xtrabackup-v2’.

12 / 38

(13)

Additional Xtrabackup SST Setup

What about that sst user in your my.cnf?

[mysqld]

...

wsrep_sst_auth = sst:secret ...

User/Grants need to be added for Xtrabackup

node3> CREATE USER 'sst'@'localhost' IDENTIFIED BY 'secret';

node3> GRANT PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sst'@'localhost';

The good news is that once you get things gured out the rst time, it's typically very easy to get an SST the rst

time on subsequent nodes.

(14)

Up and Running node2

Now you can start node2

node2# systemctl start mysql

Watch node2's error log for progress

node2# tail -f /var/lib/mysql/error.log

14 / 38

(15)

Taking Stock of our Achievements

We have a two node cluster:

Our production master replicates, asynchronously, into the cluster via node3

What might we do now in a real migration before proceeding?

(16)

Finishing the Migration

How should we nish the migration?

How do we minimize downtime?

What do we need to do to ensure there are no data inconsistencies?

How might we rollback?

16 / 38

(17)

Our Migration Steps

Shutdown the application pointing to node1

Shutdown (and RESET) replication on node3 from node1

node3> STOP SLAVE; RESET SLAVE ALL;

Startup the application pointing to node3 Rebuild node1 as we did for node2

node1# yum swap -- remove Percona-Server-shared-57 Percona-Server-server-57 \ -- install Percona-XtraDB-Cluster-shared-57 Percona-XtraDB-Cluster-server-57

(18)

node1's Con g

[mysqld]

# galera settings

node1# systemctl start mysql

18 / 38

(19)

Where are We Now?

(20)

XtraDB Cluster Tutorial

APPLICATION HIGH AVAILABILITY

20 / 38

(21)

ProxySQL Architecture

(22)

Setting up ProxySQL

Install and Start ProxySQL - Just on node1

node1# yum install proxysql node1# systemctl start proxysql

Create monitor user

node1 mysql> CREATE USER 'monitor'@'%' IDENTIFIED BY 'monit0r';

22 / 38

(23)

Con guring of ProxySQL

Con guration is done via SQL

ProxySQL "speaks" MySQL protocol Backended by SQLite

Changes are made to "staging" area Must be loaded to "runtime"

Must be saved to disk to persist restart

node1# mysql -P 6032 -uadmin -padmin -h 127.0.0.1 proxysql> SHOW TABLES;

proxysql> UPDATE .. SET .. ;

proxysql> LOAD MYSQL [SERVERS|QUERY RULES|USERS|VARIABLES] TO RUNTIME;

proxysql> SAVE MYSQL [SERVERS|QUERY RULES|USERS|VARIABLES] TO DISK;

(24)

New Ports and Monitoring

Listen on different ports; default is 6033

proxysql> UPDATE global_variables

SET variable_value = '0.0.0.0:4306;0.0.0.0:5306;/tmp/proxysql.sock' WHERE variable_name = 'mysql-interfaces';

Fix password for the monitor user we created earlier

proxysql> UPDATE global_variables SET variable_value = 'monit0r' WHERE variable_name = 'mysql-monitor_password';

Save and Restart ProxySQL

proxysql> SAVE MYSQL VARIABLES TO DISK;

proxysql> PROXYSQL RESTART;

24 / 38

(25)

Add Some Servers

De ne our "master" / "slave" pool

Add hosts to create hostgroups with weights

-- Hostgroup 10 - WRITE

proxysql> INSERT INTO mysql_servers (hostgroup_id, hostname, weight, comment) VALUES (10, '192.168.70.2', 1000000, 'WRITE');

-- Hostgroup 20 - READS

proxysql> INSERT INTO mysql_servers (hostgroup_id, hostname, weight, comment) VALUES (20, '192.168.70.3', 1000, 'READ'),

(20, '192.168.70.4', 1000, 'READ');

(26)

Add a User

Add the user with which to connect to backend MySQL hosts.

This user will be used both to authenticate against

ProxySQL and be used as the account for connections to backend MySQL servers.

You can con gure separate users for frontend and backend authentication.

proxysql> INSERT INTO mysql_users (username, password, default_hostgroup) VALUES ('sbuser', 'sbpass', 10);

MySQL user:

node1> CREATE USER 'sbuser'@'%' IDENTIFIED BY 'sbpass';

node1> GRANT ALL ON *.* TO 'sbuser'@'%';

26 / 38

(27)

Some Rules to Follow

Add the rules fo Read/Write Splitting.

proxysql> insert into mysql_query_rules

(rule_id,destination_hostgroup,active,retries,match_digest,apply) values(1,10,1,3,'^SELECT.*FOR UPDATE',1);

proxysql> insert into mysql_query_rules

(rule_id,destination_hostgroup,active,retries,match_digest,apply) values(2,20,1,3,'^SELECT.*$',1);

(28)

Load Everything to Runtime

This far, everything has been written to the staging area within ProxySQL.

Needs to be loaded to the "runtime"

Needs to be saved to disk to persist ProxySQL restart.

proxysql>

LOAD MYSQL SERVERS TO RUNTIME;

LOAD MYSQL QUERY RULES TO RUNTIME;

LOAD MYSQL USERS TO RUNTIME;

SAVE MYSQL SERVERS TO DISK;

SAVE MYSQL QUERY RULES TO DISK;

SAVE MYSQL USERS TO DISK;

28 / 38

(29)

Test Splitting Connection

node3# i=0; while [ $i -lt 500 ]; do

mysql -BNe "SELECT @@hostname" -usbuser -psbpass -h node1 -P 4306 2>/dev/null;

((i++));

done | sort | uniq -c 245 node2

255 node3

Check ProxySQL Stats

node1# mysql -P6032 -ustats -pstats -h 127.0.0.1 proxysql> SELECT * FROM stats_mysql_query_rules;

+---+---+

| rule_id | hits | +---+---+

| 1 | 0 |

| 2 | 500 | +---+---+

2 rows in set (0.00 sec)

(30)

Using the Scheduler

ProxySQL has an event scheduler that can run scripts Uses 'Comment' eld in failover logic

proxysql> INSERT INTO scheduler VALUES

(1, 1, 3000, '/usr/bin/proxysql_galera_checker', '10', '20', '1', '1',

'/var/lib/proxysql/proxysql_galera_check.log', 'Checker');

proxysql> LOAD SCHEDULER TO RUNTIME; SAVE SCHEDULER TO DISK;

30 / 38

(31)

Updating Application

sysbench --db-driver=mysql

--test=/usr/share/doc/sysbench/tests/db/oltp.lua --mysql-user=sbuser --mysql-password=sbpass --mysql-db=test --mysql-host=127.0.0.1

--mysql-port=4306 --mysql-ignore-errors=all --oltp-tables-count=1 --oltp-table-size=250000

--oltp-auto-inc=off --num-threads=1 --report-interval=1 --max-requests=0 --tx-rate=10 run | grep tps

ProxySQL:

proxysql> select * from stats_mysql_connection_pool;

(32)

Testing Out the Scheduler

Shutdown node3-1 and observe changes to runtime_mysql_servers

proxysql# select * from stats_mysql_connection_pool;

32 / 38

(33)

XtraDB Cluster Tutorial

MONITORING GALERA

(34)

Percona Monitoring and Management

(*) https://pmmdemo.percona.com/

34 / 38

(35)

Start PMM container

Start Docker

Create PMM Container

node1# systemctl restart docker

node1# docker pull percona/pmm-server:latest node1# docker create \

-v /opt/prometheus/data \ -v /opt/consul-data \ -v /var/lib/mysql \ -v /var/lib/grafana \ --name pmm-data \

percona/pmm-server:latest /bin/true

(36)

Start the container

node1# docker run -d \ -p 80:80 \

--volumes-from pmm-data \ --name pmm-server \

--restart always \

percona/pmm-server:latest

Check the Dashboard

open http://192.168.70.2/graph/

36 / 38

(37)

Con gure PMM Clients on All nodes

node1-3# pmm-admin config --server 192.168.70.2 node1-3# pmm-admin add mysql

Check the Dashboard again open http://192.168.70.2/graph/

(38)

Questions?

38 / 38