Instant MariaDB Galera Cluster Recipe

Ok – heres a few easy steps to setup a MariaDB Galera Cluster using RSYNC for SST on Debian boxes. The Debian boxes were installed using ISO 1 of the Wheezy Release (7.0.0) and do not include a desktop environment.

The hostnames I have used are deb-box-1, deb-box-2 & deb-box-3. Now to start off you will need to create urls to download MariaDB, browse to the following url and select your distribution:
# Create urls:
http://downloads.mariadb.org/mariadb/repositories/

Now you should have a set of commands such as the following to run:

sudo apt-get install python-software-properties
sudo apt-key adv --recv-keys --keyserver keyserver.ubuntu.com 0xcbcb082a1bb943db
sudo add-apt-repository 'deb http://ftp.cc.uoc.gr/mirrors/mariadb/repo/5.5/debian wheezy main'

sudo apt-get update

You are ready to install MariaDB – go ahead and run either of the following:

# sudo apt-get install mariadb-server # standalone installer (optional instead of mariadb-galera-server & galera)
sudo apt-get install mariadb-galera-server galera

Enter password for root db user when prompted.

After completing the setup edit “/etc/mysql/my.cnf” adding the following lines to the [mysqld] section:

#* Specify the relevant cluster address nodes and remember to leave the address blank on the first node to bootstrap:
wsrep_cluster_address = 'gcomm://'
# After bootstrapping change to something like:
# wsrep_cluster_address = 'gcomm://deb-box-2,deb-box-3'
wsrep_provider = /usr/lib/galera/libgalera_smm.so
# wsrep_retry_autocommit = 0
wsrep_sst_method = rsync
binlog_format=ROW
query_cache_size=0
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog=1

Copy the same /etc/mysql/debian.cnf & /etc/mysql/my.cnf to all nodes so as to avoid password issues after SST.

Since we will be using rsync for SST run the following on each node to install rsync:

sudo apt-get install rsync

Now restart each of the nodes one by one. Start with the first node using a blank gcomm:// address. Then specify the first nodes ip address as the cluster address for the second node. Same for the third. Now that all three nodes are up and connected edit the cluster addresses specifying 2 for each node e.g. for “deb-box-1” I have specified “deb-box-2″,”deb-box-3”.

Check that your nodes are up and connected:


root@deb-box-1:~# mysql -uroot -proot -e "SHOW STATUS LIKE 'wsrep_%';"
+----------------------------+----------------------------------------------------------+
| Variable_name              | Value                                                    |
+----------------------------+----------------------------------------------------------+
| wsrep_local_state_uuid     | 5cae25c3-5145-11e3-bf5a-a7c9e899cf01                     |
| wsrep_protocol_version     | 4                                                        |
| wsrep_last_committed       | 0                                                        |
| wsrep_replicated           | 0                                                        |
| wsrep_replicated_bytes     | 0                                                        |
| wsrep_received             | 16                                                       |
| wsrep_received_bytes       | 1836                                                     |
| 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   | 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   | 192.168.10.28:3306,192.168.10.29:3306,192.168.10.31:3306 |
| wsrep_cluster_conf_id      | 9                                                        |
| wsrep_cluster_size         | 3                                                        |
| wsrep_cluster_state_uuid   | 5cae25c3-5145-11e3-bf5a-a7c9e899cf01                     |
| wsrep_cluster_status       | Primary                                                  |
| wsrep_connected            | ON                                                       |
| wsrep_local_index          | 0                                                        |
| wsrep_provider_name        | Galera                                                   |
| wsrep_provider_vendor      | Codership Oy <info@codership.com>                        |
| wsrep_provider_version     | 23.2.7-wheezy(r)                                         |
| wsrep_ready                | ON                                                       |
+----------------------------+----------------------------------------------------------+

Now you can go ahead and setup a Galera Load balancer either on one of the nodes or a seperate node (recommended):

wget http://www.codership.com/files/glb/glb-1.0.0rc1.tar.gz
tar xzvf glb-1.0.0rc1.tar.gz
cd glb-1.0.0rc1
./configure; make; make install

And run by specifying the nodes you wish to read/write to (should match the wsrep_incoming_addresses in most cases i.e. all nodes):

glbd --daemon --threads 6 --control 0.0.0.0:4444 0.0.0.0:3306 192.168.10.28:3306:1 192.168.10.29:3306:1 192.168.10.31:3306:1

Now you have Galera Load Balancer listening on the local port 3306 (choose another if you are running on one of the db nodes) and balancing connections between a 3-node MariaDB HA Galera Cluster.

Have fun!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s