Tuesday, May 26, 2015

Ubuntu 14.04 - Mysql Galera Cluster for Wordpress

This cluster has 2 nodes run in multi master mode

Installing cluster
sudo apt-key adv --recv-keys --keyserver keyserver.ubuntu.com 0xcbcb082a1bb943db
sudo add-apt-repository 'deb http://mirror3.layerjet.com/mariadb/repo/5.5/ubuntu trusty main'
sudo apt-get update -y ; sudo apt-get install -y galera mariadb-galera-server rsync

Configuring cluster
On each of the host in the cluster add this configuration
vi /etc/mysql/conf.d/galera.cnf
[mysqld]
#mysql settings
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
query_cache_size=0
query_cache_type=0
bind-address=0.0.0.0
#galera settings
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_name="my_wsrep_cluster"
wsrep_cluster_address="gcomm://192.20.3.31,192.20.3.32"
wsrep_sst_method=rsync

Stop MariaDB instance in all of the Galera hosts
sudo service mysql stop

Init the Galera cluster on my 1st node by start MariaDB
sudo service mysql start --wsrep-new-cluster

Just start MariaDB on my 2nd node
sudo service mysql start

To prevent startup error on 2nd node we need to copy /etc/mysql/debian.cnf content from node1 to node2 and then restart MariaDB on 2nd node

Confirm cluster status has started
mysql -u root -p -e 'SELECT VARIABLE_VALUE as "cluster size" FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME="wsrep_cluster_size"'
Enter password: 
+--------------+
| cluster size |
+--------------+
| 2            |
+--------------+
From now on the data will sync between both hosts. To test it try create one database from 1st node then go to 2nd node and see it is there.
On 1st node
mysql -u root -p
create database HelloWorld;

On 2nd node
mysql -u root -p
show databases;
if Helloworld database was there. That's kool. We're ready to go.

Grant Remote privilege to remote user on DBcluster
On one of the DB host (node1 or node2)
mysql -u root -p
create user 'handsome'@'%' identified by handsome_password;
grant all privileges on Database_name . * to  'handsome'@'%';
flush privileges;
show grants for 'handsome'@'%';

Note: 
Replace handsome with your username or root
Replace Database_name with your DB name or * to grant privileges on all Database

DNS setting
On my premise DNS server I add a Round Robin DNS record point to 2 Galera host
dbcluster -> 192.20.3.31
dbcluster -> 192.20.3.32

On my PHP app server I can connect to the dbcluster with this command
mysql -u root -p -h dbcluster 

If it not work, you could have a look on MariaDB log

Reference link:
https://www.linode.com/docs/databases/mariadb/clustering-with-mariadb-and-galera

0 comments:

Post a Comment