OurSQL Episode 165: Top Hat Cluster

This week we talk about how to install and set up Galera Cluster. Ear Candy talks about the new MySQL repos from Oracle and what to know about using them; At the Movies is Michael Stonebreaker talking about how to process today's big data transactional processing needs.

Galera Cluster
Codership documentation of Galera Cluster
Installing Percona XtraDB Cluster on Ubuntu documentation
MariaDB's Galera Cluster documentation

Galera overview at Codership

Getting Galera:
Galera Cluster for MySQL from Codership
MariaDB Galera Cluster
Percona XtraDB Cluster

Installing the MariaDB Repository:

sudo apt-get install python-software-properties
sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db
sudo add-apt-repository 'deb http://ftp.osuosl.org/pub/mariadb/repo/5.5/ubuntu precise main'

Update the repos:

sudo apt-get update

Install the MariaDB server patched with the wsrep api calls, otherwise known as the MariaDB Galera Server:

sudo apt-get install mariadb-galera-server

Installing Percona XtraDB cluster, including where to find repositories
Downloading Galera Cluster directly involves 2 packages

Setting variables in the configuration file for the first Galera node

[mysqld]
wsrep_provider="/usr/lib/galera/libgalera_smm.so"
wsrep_sst_auth=user:password
wesrep_cluster_address="gcomm://"

Verify the variables are properly set up:
my_print_defaults mysqld | grep wsrep

See status variables:

SHOW GLOBAL STATUS LIKE 'wsrep%';
+----------------------------+-------------------------------------------------------+
| Variable_name              | Value                                                 |
+----------------------------+-------------------------------------------------------+
| wsrep_local_state_uuid     | e4d01214-5647-11e3-9111-234454536736                  |
| wsrep_protocol_version     | 4                                                     |
| wsrep_last_committed       | 4                                                     |
| wsrep_replicated           | 1                                                     |
| wsrep_replicated_bytes     | 154                                                   |
| wsrep_received             | 12                                                    |
| wsrep_received_bytes       | 1556                                                  |
| wsrep_local_commits        | 0                                                     |
| 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   | 1.500000                                              |
| wsrep_apply_oooe           | 0.000000                                              |
| wsrep_apply_oool           | 0.000000                                              |
| wsrep_apply_window         | 1.000000                                              |
| wsrep_commit_oooe          | 0.000000                                              |
| wsrep_commit_oool          | 0.000000                                              |
| wsrep_commit_window        | 1.000000                                              |
| wsrep_local_state          | 4                                                     |
| wsrep_local_state_comment  | Synced                                                |
| wsrep_cert_index_size      | 5                                                     |
| wsrep_causal_reads         | 0                                                     |
| wsrep_incoming_addresses   | 192.168.1.35:3306,192.168.1.36:3306,192.168.1.29:3306 |
| wsrep_cluster_conf_id      | 5                                                     |
| wsrep_cluster_size         | 3                                                     |
| wsrep_cluster_state_uuid   | e4d01214-5647-11e3-9111-234454536736                  |
| wsrep_cluster_status       | Primary                                               |
| wsrep_connected            | ON                                                    |
| wsrep_local_index          | 2                                                     |
| wsrep_provider_name        | Galera                                                |
| wsrep_provider_vendor      | Codership Oy <info@codership.com>                     |
| wsrep_provider_version     | 23.2.7-wheezy(r)                                      |
| wsrep_ready                | ON                                                    |
+----------------------------+-------------------------------------------------------+
40 rows in set (0.01 sec)

State Snapshot Transfer, or SST in Galera's clustering solution for MySQL.

Variables in the config file for using mysqldump as the State Snaphot Transfer solution:

wsrep_sst_method = mysqldump
wsrep_sst_auth = user:password

rsync man page
Variable in the config file for using rsync:
wsrep_sst_method = rsync

Variables in the config file for using xtrabackup

wsrep_sst_method = xtrabackup
wsrep_sst_auth = user:password

Example: 1st node is 192.168.1.29, 2nd node is 192.168.1.35, 3rd node is 192.168.1.41
2nd node wsrep_cluster_address variable uses the IP from node 1:
wsrep_cluster_address="gcomm://192.168.1.29"

3rd node wsrep_cluster_address variable uses the IP from nodes 1 and 2:
wsrep_cluster_address="gcomm://192.168.1.29,192.168.1.35"

Once all three are in service, you can use this in the config for all three nodes:
wsrep_cluster_address="gcomm://192.168.1.29,192.168.1.35,192.168.1.41"

Wikipedia page on "split brain"

Virtual Machines with Galera
vagrant, a tool that uses VirtualBox and configuration management software to produce preconfigured virtual machines.

Using 2 virtual machines that are already set up with the Percona repositories, to install Percona XtraDB Cluster for testing

Several Nines Galera Configurator.

Galera Cluster known limitations from the MariaDB website

Ear Candy
New MySQL yum repositories from Oracle

Morgan Tocker's blog post revealing some possible issues when changing from using the regular RPMs from the dev.mysql.com downloads page to using the new repositories.

Common pitfalls using the repos from Ronald Bradford

At the Movies
This week in At the Movies we present Michael Stonebreaker at last year's Strangeloop conference comparing how traditional relational databases, NoSQL and NewSQL support today's big data transaction processing needs in Not Your Father's Transactional Processing.

Feedback
Facebook group
Google+ page
e-mail: podcast at technocation.org
voicemail using phone/Skype: +1-617-674-2369
twitter: @oursqlcast
or Tweet about @oursqlcast