CentOS 6 / RHCE 6 / Redhat : 3 Node Percona XtraDB Cluster + XtraBackup + GLB Load Balancer ( aka MySQL + Galera )

Assumptions:

Minimal CentOS 6 installation Completed Proxmox / OpenVZ Container / CentOS 6 / RHCE 6 / Redhat Server Preparation , Optimised and Secured

Note: xtrabackup ( wsrep_sst_method=xtrabackup ) is the default SST method for my guide to avoid read-only Donor during SST, this has replaced the default rsync/mysql dump sst methods.
The main advantage of using xtrabackup to synchronize the nodes, is that the Donor is writeable during the synchronization process.

Percona XtraDB Cluster provides the following features
Synchronous replication
Active/active multi-master topology
Read and write to any cluster node
Automatic membership control, failed nodes drop from the cluster
Automatic node joining
True parallel row level replication
Direct client connections
Drop-in replacement for native MySQL
All the Percona XtraDB Server benefits

Benefits using Galera Replication
High Availability
No slave lag
No lost transactions
No more data inconsistency
Smaller client latencies
Read scalability and write throughput improvement (3 times and more, depending on your workload

#mysql startup timeout is 900seconds

#Hostnames
dbnode1 11.11.11.11
dbnode2 22.22.22.22
dbnode3 33.33.33.33

#Ports
TCP 22 : SSH

#XtraDB Cluster Cluster Ports
TCP 3306 : Regular MySQL
TCP 4567 : Group Communication (wsrep_provider gmcast.listen_addr)
TCP 4568 : Incremental State Transfer (wsrep_provider ist.recv_addr)
TCP 4444 : State Transfer (wsrep_sst_receive_address)

Set the timezone

Set the system timezone to UTC

Optional: CSF Firewall

Allow/add the dbnode IPs to csf** all nodes **

Remove conflicting solutions and configs

**all nodes**

Setup Hostnames and SSH Passwordless logins between the nodes

Generate the rsa key **all nodes**

Enter file in which to save the key (/root/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:

Optional: Add hosts to /etc/hosts.allow **all nodes**

Node1

Node2

Node3

Install the latest PErcona XtraDB Cluster

Install the latest Percona XtraDB Cluster ( mysql )

**all nodes**
Add the Percona repository to replace MySQL **all nodes**
rpm -Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm

Set the repo priority to 100 **all nodes**

Install XtraDB Cluster **all nodes**

Install a default MySQL config (my.cnf) **all nodes**

# 1G-2G of memory of the system runs mainly MySQL
/usr/share/mysql/my-huge.cnf

# 4GB of memory running mostly MySQL using InnoDB only tables and performing complex queries with few connections.
/usr/share/mysql/my-innodb-heavy-4G.cnf

# 512M where the system runs mainly MySQL.
/usr/share/mysql/my-large.cnf

# 32M – 64M where MySQL plays an important part, or systems up to 128M where MySQL is used together with other programs (such as a web server)
/usr/share/mysql/my-medium.cnf

# For a system with little memory (<= 64M) where MySQL is only used from time to time and it's important that the mysqld daemon doesn't use much resources.
/usr/share/mysql/my-small.cnf

Merge the default WSREP config (wsrep.cnf) into the my.cnf **all nodes**

Initial XtraDB Cluster Configuration

Shutdown all instances of mysql **all nodes**

OpenVZ AIO Bugfix

***All OpenVZ Nodes***
Only run this on your openvz nodes

Insert missing values for sanity and to ease management

Insert missing values to my.cnf***all Nodes***

Create the log and pid dir***all Nodes***

Optimise for InnoDB Tables **all nodes**

Force Innodb logfile to 64mb **all nodes**

Remove all old innodb logfiles **all nodes**

Configure for clustering

**all nodes**
64bit Nodes Only

32bit Nodes Only

All Nodes

Replace yoursecurepassword with the root mysql password

Optional: Enable xbstream** all nodes **

Node 1

Node 2

Node 3

Default is 4GB ram
**optional: gcache for 512MB ram **all nodes**

**optional: gcache for 1024MB ram **all nodes**

**optional: gcache for 2GB ram **all nodes**

Start Percona XtraDB Server **all nodes**

Bootstrap the cluster

Stop mysql** all nodes **

Node 2

Node 3

Bootstrap node1** node 1 **

ONLY after your mysql starts without errors on node1, continue

Node 1

Secure Percona Server **all nodes**

**SET A MYSQL ROOT PASSWORD, SAME ON ALL NODES**

Enter current password for root (enter for none):
Set root password? Y
Remove anonymous users? Y
Disallow root login remotely? Y
Remove test database and access to it? Y
Reload privilege tables now? Y

Check that mysql is online, functioning and to monitor real-time node status

Variable_name Value
wsrep_local_state_uuid 2e1a97ef-d284-11e2-0800-122d8af6b436
wsrep_local_state_comment Synced
wsrep_incoming_addresses 33.33.33.33:3306
wsrep_cluster_size 1
wsrep_cluster_state_uuid 2e1a97ef-d284-11e2-0800-122d8af6b436
wsrep_cluster_status Primary
wsrep_connected ON
wsrep_ready ON

Bootstrap node2**node 2 **

Check that mysql is online, functioning and to monitor real-time node status

Variable_name Value
wsrep_local_state_uuid 2e1a97ef-d284-11e2-0800-122d8af6b436
wsrep_local_state_comment Synced
wsrep_incoming_addresses 33.33.33.33:3306,11.11.11.11:3306
wsrep_cluster_size 2
wsrep_cluster_state_uuid 2e1a97ef-d284-11e2-0800-122d8af6b436
wsrep_cluster_status Primary
wsrep_connected ON
wsrep_ready ON

Bootstrap node2**node 3 **

Check that mysql is online, functioning and to monitor real-time node status

Variable_name Value
wsrep_local_state_uuid 2e1a97ef-d284-11e2-0800-122d8af6b436
wsrep_local_state_comment Synced
wsrep_incoming_addresses 33.33.33.33:3306,22.22.22.22:3306,11.11.11.11:3306
wsrep_cluster_size 3
wsrep_cluster_state_uuid 2e1a97ef-d284-11e2-0800-122d8af6b436
wsrep_cluster_status Primary
wsrep_connected ON
wsrep_ready ON

Notes / Bug Fixes: * Starting XtraDB Cluster database server mysqld [fail]

I have had issues with large databases and mysql failing to start. This is caused by the start script timing out before rsync is able to complete.
A simple solution to this is to start the failed node manually with the following:
Make sure mysql is not running and force kill any processes

Start mysql manually

Test

Monitor the XtraDB Cluster Cluster Status

Variable_name Value
wsrep_local_state_uuid 2e1a97ef-d284-11e2-0800-122d8af6b436
wsrep_protocol_version 4
wsrep_last_committed 44150
wsrep_replicated 20000
wsrep_replicated_bytes 22623578
wsrep_received 4163
wsrep_received_bytes 4863775
wsrep_local_commits 20000
wsrep_local_cert_failures 0
wsrep_local_bf_aborts 0
wsrep_local_replays 0
wsrep_local_send_queue 0
wsrep_local_send_queue_avg 0.000000
wsrep_local_recv_queue 0
wsrep_local_recv_queue_avg 0.000000
wsrep_flow_control_paused 0.000000
wsrep_flow_control_sent 0
wsrep_flow_control_recv 0
wsrep_cert_deps_distance 0.000000
wsrep_apply_oooe 0.000000
wsrep_apply_oool 0.000000
wsrep_apply_window 0.000000
wsrep_commit_oooe 0.000000
wsrep_commit_oool 0.000000
wsrep_commit_window 0.000000
wsrep_local_state 4
wsrep_local_state_comment Synced
wsrep_cert_index_size 0
wsrep_causal_reads 0
wsrep_incoming_addresses 33.33.33.33:3306,22.22.22.22:3306,11.11.11.11:3306
wsrep_cluster_conf_id 22
wsrep_cluster_size 3
wsrep_cluster_state_uuid 2e1a97ef-d284-11e2-0800-122d8af6b436
wsrep_cluster_status Primary
wsrep_connected ON
wsrep_local_index 0
wsrep_provider_name Galera
wsrep_provider_vendor Codership Oy
wsrep_provider_version 23.2.4(r147)
wsrep_ready ON

Variable_name Value
wsrep_local_state_uuid 2e1a97ef-d284-11e2-0800-122d8af6b436
wsrep_local_state_comment Synced
wsrep_incoming_addresses 33.33.33.33:3306,22.22.22.22:3306,11.11.11.11:3306
wsrep_cluster_size 3
wsrep_cluster_state_uuid 2e1a97ef-d284-11e2-0800-122d8af6b436
wsrep_cluster_status Primary
wsrep_connected ON
wsrep_ready ON

Performance Testing

Create the sbtest database with 1 000 000 tables** Node1 **

Replace securepassword with a random password

Run the test on all nodes** All Nodes **

Replace securepassword with a random password

Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
Using “BEGIN” for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 500000
Threads started!
Time limit exceeded, exiting…
(last message repeated 15 times)
Done.

OLTP test statistics:
queries performed:
read: 856198
write: 305785
other: 122314
total: 1284297
transactions: 61157 (203.81 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 1161983 (3872.47 per sec.)
other operations: 122314 (407.63 per sec.)

Test execution summary:
total time: 300.0627s
total number of events: 61157
total time taken by event execution: 4799.9976
per-request statistics:
min: 55.43ms
avg: 78.49ms
max: 689.76ms
approx. 95 percentile: 98.52ms

Threads fairness:
events (avg/stddev): 3822.3125/6.61
execution time (avg/stddev): 299.9999/0.02

Test Cluster load with Pen Load Balancer

This is just to test load balance performance, I suggest you use either GLB or HAProxy in a production environment
Install Pen** Node 1 **

Run Pen and listen to port 4406** Node 1 **

Execute the Sysbench, using the Pen Load Balancer

Number of threads: 16

Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
Using “BEGIN” for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 500000
Threads started!
Time limit exceeded, exiting…
(last message repeated 15 times)
Done.

OLTP test statistics:
queries performed:
read: 855974
write: 305705
other: 122282
total: 1283961
transactions: 61141 (203.76 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 1161679 (3871.43 per sec.)
other operations: 122282 (407.52 per sec.)

Test execution summary:
total time: 300.0643s
total number of events: 61141
total time taken by event execution: 4799.9052
per-request statistics:
min: 55.38ms
avg: 78.51ms
max: 605.87ms
approx. 95 percentile: 94.22ms

Threads fairness:
events (avg/stddev): 3821.3125/11.27
execution time (avg/stddev): 299.9941/0.02

Terminate Pen

Optional: Galera Load Balancer ( GLB )

Note: I will only write a guide once the 1.0 version is released.
http://www.codership.com/downloads/glb
http://www.codership.com/files/glb/README-1.0.0rc1

==== THE ITEMS BELOW ARE IN PROGRESS :: DO NOT USE =====

Secure SSH for galera/wsrep
Secure XtraDB Cluster

Enter current password for root (enter for none): yoursecurepassword
Change the root password? N
Remove anonymous users? Y
Disallow root login remotely? Y
Remove test database and access to it? Y
Reload privilege tables now? Y

Restart XtraDB Cluster (mysql server)

Confirm XtraDB Cluster is running

tcp 0 0 localhost:mysql *:* LISTEN 11878/mysqld

———–

3 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *