How to setup Galera 3 node cluster on Ubuntu 12.04

Galera is a multi-master replication solution for MySQL, which provides an interesting alternative to the standard master-master MySQL replication we are all so used with. One main advantage of Galera is the ability of doing sync replication, thus reducing the risk of data inconsistency between masters.

Setup on RackSpace Cloud

3x 512MB RAM instances, with 20GB storage space
1x Load Balancer for MySQL, RoundRobin algorithm, Health check enabled
1x 512MB RAM instance for testing
OS: Ubuntu 12.04 LTS 64bit

Goal:

Quickly setup a Galera cluster and run some benchmarks using sysbench.

Note: For the sake of simplicity I will refer to the Galera instances as node01, node02 and node03. The test instance will be referred as test01.

Common settings on all nodes

On every node execute:

  1. An apt-get update and upgrade to bring the instances up to date.
  2. Install required packages
    apt-get install libaio1 libssl0.9.8 mysql-client libdbd-mysql-perl libdbi-perl
  3. Download Galera wsrep provider
    wget https://launchpad.net/galera/2.x/23.2.4/+download/galera-23.2.4-amd64.deb
    dpkg -i galera-23.2.4-amd64.deb
  4. Download MySQL server with wsrep patch
    wget https://launchpad.net/codership-mysql/5.5/5.5.28-23.7/+download/mysql-server-wsrep-5.5.28-23.7-amd64.deb
    dpkg -i mysql-server-wsrep-5.5.28-23.7-amd64.deb
  5. I had some issues and I had to create /var/log/mysql
    mkdir -pv /var/log/mysql
    chown mysql:mysql -R /var/log/mysql
  6. Secure the mysql installation and assign a good password to root user:
    service mysql restart
    mysql_secure_installation
  7. Create an user for galera nodes to use for connect/replication
    mysql -p
    mysql> grant all privileges on *.* to galera@'%' identified by 'password';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> flush privileges;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> set global max_connect_errors = 10000;
    Query OK, 0 rows affected (0.01 sec)
  8. Edit /etc/hosts and make sure you add all the nodes and their corresponding IPs

Galera setup for each node

Edit the /etc/mysql/conf.d/wsrep.cnf and change the values for the following variables:

Configuration for node01:

wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_name="galera"
wsrep_cluster_address="gcomm://"
wsrep_sst_method=mysqldump
wsrep_sst_auth=galera:password

Configuration for node02:

wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_name="galera"
wsrep_cluster_address="gcomm://node01:4567"
wsrep_sst_method=mysqldump
wsrep_sst_auth=galera:password

Configuration for node03:

wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_name="galera"
wsrep_cluster_address="gcomm://node02:4567"
wsrep_sst_method=mysqldump
wsrep_sst_auth=galera:password

Testing the setup

Now restart mysql on all the nodes and check out if cluster is working:

service mysql restart
mysql -p
mysql> show status like 'wsrep%';
+----------------------------+-------------------------------------------------------------+
| Variable_name | Value |
+----------------------------+-------------------------------------------------------------+
| wsrep_cluster_size | 3 |
| wsrep_ready | ON |
+----------------------------+-------------------------------------------------------------+

One more thing before you are done:
Edit node01 wsrep_cluster_address=”gcomm://node3:4567″ and restart mysql server.

Benchmarks were performed from test01 instance using sysbench 0.5 OLTP read-only complex test:

sysbench OLTP (ro) Galera cluster transactions vs threads
Threads Transactions/s
1 15
2 25
4 49
8 103
16 205
32 390
64 506
128 653

galera-transactions-threads

sysbench OLTP (ro) Galera cluster avg response time
Threads Avg response time Min response time Aprox 95%
1 66 42 131
2 79 53 135
4 80 42 153
8 77 42 136
16 77 43 143
32 81 42 142
64 125 48 322
128 194 45 427

galera-response-times

Yuriy

So if I want use one ip for all my clients for connecting to DB with balancing I should use tool like haproxy? Galera cant provide such thing herself?

Thanks!

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