Ubuntu 12.04 / LTS : 3 Node Percona XtraDB Cluster + XtraBackup + GLB Load Balancer ( aka MySQL + Galera )

Assumptions:

Completed Server Preparation: KVM / VMware / Standalone Ubuntu 12.04 / 12.10 Server Preparation or Proxmox / OpenVZ Container / Ubuntu 12.04 /12.10 Server Preparation

Percona XtraDB CLluster 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 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

#Hostnames
dbnode1 11.11.11.11
dbnode2 22.22.22.22
dbnode3 33.33.33.33

#Ports
TCP 22 : SSH

#MariaDB 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

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 repository **all nodes**

Import the GP key **all nodes**

Apt-Pinning the packages **all nodes**

Install XtraDB Cluster **all nodes**

**SET A PERCONA XTRADB CLUSTER ROOT PASSWORD, SAME ON ALL NODES**

Initial XtraDB Cluster COnfiguration

Our default my.cnf config

Shutdown all instances of XtraDB Cluster **all nodes**

Node1

Replace dbclusterroot with a random username
Replace securepassword with a random password

Configure XtraDB Cluster

Add missing my.cnf options **all nodes**

Create the cluster root sql user **all nodes**

Replace securepassword with a random password
*avoid all special characters*

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**

**optional: Enable slow query logging **all nodes**

Node1

Node2

Node3

Bugfix: Access denied for user ‘debian-sys-maint’@’localhost’

[Warning] Access denied for user ‘debian-sys-maint’@’localhost’ (using password: YES)
Node1

Optional: Install the latest xtrabackup ( wsrep_sst_method=xtrabackup ) to avoid read-only Donor during SST

The main advantage of using xtrabackup to synchronize the nodes, is that the Donor is writeable during the synchronization process.
Install Xtrabackup and Percona-toolkit **all nodes**
Note: install netcat-openbsd fixes the error: nc: invalid option — ‘d’

Set the wsrep_sst_method to xtrabackup **all nodes**

Bootstrap the cluster

Stop mysql** all nodes **

Bootstrap node1** node 1 **
Set the wsrep_cluster_address to gcom:// to enable the cluster to be bootstrapped

Start MySQL Server

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

Restore the wsrep_cluster_address to the cluster, to enable node1 to connect to the cluster

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

Optional: allow a node to handle the traffic in a splitbrain situation

** primary node only**

Test

Monitor the XtraDB 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 2.6(r152)
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

Optional: CSF Firewall

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

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

Optional: UFW Firewall

Install UFW

Secure SSH for galera/wsrep
Secure MariaDB

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 MariaDB (mysql server)

Confirm MariaDB is running

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

———–

6 replies
  1. Andreas Krüger
    Andreas Krüger says:

    The results of sysbench before and after the use of Pen (Load balance) seems almost identical? I’ve tried testing the setup and get the same results. Seems to me, the load is not distributed across the servers?

    Reply
    • admin
      admin says:

      In production we use xtrabackup and xtrabackup-wan for when the cluster spans multiple datacenters.

      I chose to use rsync-wan for this guide to help users with simplicity.

      Reply
      • seqizz
        seqizz says:

        There is absolutely no example of “xtrabackup-wan” usage on the internet (except this blog). They’re suggesting xbstream’s compact and compressed methods. But I’ll try.

        Reply

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 *