Thursday, April 4, 2013

Setting up MariaDB Galera Cluster from Scratch

Okay, this one is a little fun for the techie crowd out there. Nobody can say this blog isn't eclectic at least.

MariaDB is the new database program from the folks who created MySQL, arguably the most popular database on the planet. It was created when Oracle gained control of MySQL because, well, Oracle. Galera is a multi-master "live" database that sits on top of MariaDB. The instructions on the web for installing Galera that I've been able to find are woefully inadequate, and presume a certain level of "already installed and configured".

This instruction set is designed to walk through the install procedure from start to end. Text written in courier is meant to be typed in the command line.

You're going to want to set up three servers to get this going (this is where VMware is very handy).

1. Install CentOS. I used this image:

http://mirror.its.sfu.ca/mirror/CentOS/6/isos/x86_64/CentOS-6.4-x86_64-netinstall.iso

But you should go to www.centos.org and pick the proper mirror.
During installation, since this is a net install, use an appropriate location for the url:
http://mirror.its.sfu.ca/mirror/CentOS/6/os/x86_64/

When prompted for the type of installation, select "Basic Server". Do NOT select "Database Server" or anything else that might install the MySQL database...that will cause problems. Trust me on this.

2. Create repo files for MariaDB
(See: https://downloads.mariadb.org/mariadb/repositories/)

As root: nano /etc/yum.repos.d/MariaDB.repo
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/5.5/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

rpm --import https://yum.mariadb.org/RPM-GPG-KEY-MariaDB

3. Create repo files for Percona
nano /etc/yum.repos.d/Percona.repo

[percona]
name = CentOS $releasever - Percona
baseurl=http://repo.percona.com/centos/$releasever/os/$basearch/
enabled = 1
gpgkey = file:///etc/pki/rpm-gpg/RPM-GPG-KEY-percona
gpgcheck = 1


wget -O /etc/pki/rpm-gpg/RPM-GPG-KEY-percona http://www.percona.com/downloads/RPM-GPG-KEY-percona

4. Install Packages
yum install MariaDB-Galera-server galera MariaDB-client nc xtrabackup 

5. Create my.cnf
Below are the defaults that are required to successfully start the cluster.
(See: http://www.codership.com/wiki/doku.php?id=mysql_galera_configuration)
nano /etc/my.cnf

[mysqld]
query_cache_size=0
binlog_format=ROW
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
# this has to be specified for xtrabackup to work
datadir=/var/lib/mysql
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_provider_options="gcache.size=32G; gcache.page_siz=1G"
# Address of first server started in cluster;
# comment this line out in my.cnf of first server
wsrep_cluster_address=gcomm://10.0.1.190
# This is just an informational string, just make sure
# it matches on all clustered machines
wsrep_cluster_name='gamaria1'
# This is the address of the local machine
wsrep_node_address='10.0.1.192'
# This is just an informational string
# to describe the local machine
wsrep_node_name='node3'
wsrep_sst_method=xtrabackup
wsrep_sst_auth=<user>:<password>

Replace <user> and <password> with the appropriate username and password. When configuring the first node, be sure to set up the appropriate user profile and access permissions. After you've set up the first node, the other nodes get the security information automatically. How cool is that? (Be warned: this bit grants access to the database from any machine on the network...replace % with the IP address of an appropriate machine if you don't want that!)
Sign into the local MariaDB:
mysql -u rootCREATE USER 'monty'@'localhost' IDENTIFIED BY 'some_pass';
GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%' WITH GRANT OPTION;

6. Disable the Firewall
Not the best solution, but the cluster seems to use more than just port 4567. Haven't quite worked this one out yet. Suggestions?
service iptables stop chkconfig iptables off
7. Edit script on all nodes to prevent cluster from erroring out on startup

(See http://www.perconaforum.com/index.php?t=msg&goto=10012&)

nano /usr/bin/wsrep_sst_commonReplace
echo "WSREP_SST: $* ($tst)" >>/dev/stderrWith
echo "WSREP_SST: $* ($tst)" >&2
8. Start the Server
(See: https://kb.askmonty.org/en/getting-started-with-mariadb-galera-cluster/)

For all nodes, start the server as the mysql user: su mysql - 
Don't forget the - at the end! That forces the proper environment. 
First server: mysqld --wsrep_cluster_address=gcomm://
As long as my.cnf is correct (wsrep_cluster_address variable),
you can start other machines with: mysqld


A couple of notes:


Set up the first server following these instructions, get it working, then set up each server in turn, following 1 through 8 again, but making the appropriate mods for each.

Start the first server, let it get to "waiting for connections", then start the other servers.
If you shut down the first server for any reason, when you start it up you must use this:
mysqld --wsrep_cluster_address=gcomm://address_of_another_server

Or you're pooched and will have to shut them all down and start the boot sequence from scratch. Yeah, I made that mistake a few times.

That's it! Kind of. I may have glossed over a couple of points. As I discover problems with these instructions I'll update the post. 

Last, but certainly not least, I can't take credit for these instructions beyond typing them here. Credit goes to John Flaskay, my resident expert-in-everything-linux/security/tcp/etc!

9 comments:

  1. I nad some question about installing MariaDB-galera on CentOS

    I have 2 server to install MariaDB-galera to test multi-master database server but I'm not sure I had right configuretions on my servers.

    on Node A (I provide node A is clusters)

    server.cnf (On centos with yum install, Configuretion file is in /etc/my.cnf.d/)
    --------------------------------------------------
    [mysqld]
    query_cache_size = 0
    binlog_format = ROW
    default_storage_engine = innodb
    innodb_autoinc_lock_mode = 2
    wsrep_provider = /usr/lib64/galera/libgalera_smm.so
    wsrep_provider_options = "gcache.size=4G; gcache.page_size=1G"
    wsrep_cluster_name = 'test-Node-A-cluster'
    wsrep_node_address = '192.168.0.1'
    wsrep_node_name = 'server-A'
    wsrep_sst_method = xtrabackup
    wsrep_sst_auth = root:[password]
    --------------------------------------------------

    Node B (I provide node B is a node to connect with node A)
    --------------------------------------------------
    query_cache_size = 0
    binlog_format = ROW
    default_storage_engine = innodb
    innodb_autoinc_lock_mode = 2
    wsrep_provider = /usr/lib64/galera/libgalera_smm.so
    wsrep_provider_options = "gcache.size=4G; gcache.page_size=1G"
    wsrep_cluster_address = gcomm://192.168.0.1
    wsrep_cluster_name = 'test-Node-A-cluster'
    wsrep_node_address = '192.168.0.2'
    wsrep_node_name = 'Server-B'
    wsrep_sst_method = xtrabackup
    wsrep_sst_auth = root:[password]
    --------------------------------------------------

    I have started server A but it not have any massage like "waiting for connections" , after I start mysql on server B, nothing any happen too. (server B I use command : mysqld --wsrep_cluster_address=gcomm://192.168.0.2 on user mysql)

    How do I know is working ?

    Thank you for anyhelp.

    ReplyDelete
  2. You're probably having a "split brain" condition. Galera requires three nodes, not two.

    ReplyDelete
  3. So if I add 3 of these...
    Say DB-1, DB-2 and DB-3.
    DB-1 starts up on 192.168.0.1, DB-2 is on 192.168.0.2 but uses g-comm://192.168.0.1 and DB-3 is on 192.168.0.3 but uses g-comm://192.168.0.2.

    Now..

    Say DB-2 was down when DB-3 booted up. Is there anyway to have it try another machine in the cluster if it can't connect to one of them?

    ReplyDelete
  4. Absolutely. When you're starting it up, just do:

    mysqld --wsrep_cluster_address=gcomm://192.168.0.2

    And change the IP to be one of the running nodes.

    ReplyDelete
  5. What about possibly using haproxy or something as the gcomm address to ensure a server can always hit a live machine as it starts up? What port does gcomm use to connect? Is such a thing possible just for automation sake?

    ReplyDelete
  6. haproxy will definitely do the trick from an external client (ie. web server) but I don't know if it would work for another member of the cluster.

    As for the port, that might take some Googling but I suspect it's the default MySQL port of 3306.

    ReplyDelete
  7. Informative post! I was getting some problem in setting up but you solved it. I followed all steps mentioned by you in sequence. Thanks!
    android digital signage player

    ReplyDelete
  8. What about possibly using haproxy or something as the gcomm address to ensure a server can always hit a live machine as it starts up? What port does gcomm use to connect? Is such a thing possible just for automation sake?

    chromebox digital signage

    ReplyDelete