MariaDB galera cluster Ubuntu 14.04

Today its about mariaDB cluster, you need this to serve high intense request of your apps-query.

On my lab, i use 2 nodes and ubuntu as host OS. Lets try :

Let say :

Node 1 : 192.168.43.203
Node 4 : 192.168.43.204

DO THIS ON BOTH NODE

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

OR YOU CAN USE THIS REPO
deb http://mariadb.biz.net.id//repo/10.0/ubuntu trusty main


sudo apt-get update
sudo apt-get install mariadb-galera-server galera

sudo vim /etc/mysql/my.cnf     (comment binding)
#bind-address           = 127.0.0.1

CREATE THIS FILE ON BOTH

sudo vim /etc/mysql/conf.d/cluster.cnf

ON NODE 1:

[mysqld]
query_cache_size=0
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
query_cache_type=0
bind-address=0.0.0.0

# Galera Provider Configuration
wsrep_provider=/usr/lib/galera/libgalera_smm.so
#wsrep_provider_options="gcache.size=32G"

# Galera Cluster Configuration
wsrep_cluster_name="m_cluster"
wsrep_cluster_address="gcomm://192.168.43.203,192.168.43.204"

# Galera Synchronization Congifuration
wsrep_sst_method=rsync
#wsrep_sst_auth=user:pass

# Galera Node Configuration
wsrep_node_address="192.168.43.203"
wsrep_node_name="m1"

ON NODE 2:
sudo vim /etc/mysql/conf.d/cluster.cnf

[mysqld]
query_cache_size=0
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
query_cache_type=0
bind-address=0.0.0.0

# Galera Provider Configuration
wsrep_provider=/usr/lib/galera/libgalera_smm.so
#wsrep_provider_options="gcache.size=32G"

# Galera Cluster Configuration
wsrep_cluster_name="m_cluster"
wsrep_cluster_address="gcomm://192.168.43.203,192.168.43.204"

# Galera Synchronization Congifuration
wsrep_sst_method=rsync
#wsrep_sst_auth=user:pass

# Galera Node Configuration
wsrep_node_address="192.168.43.204"
wsrep_node_name="m2"

COPY FROM NODE 1 to NODE 2

sudo vim /etc/mysql/debian.cnf

[client]
host     = localhost
user     = debian-sys-maint
password = 03P8rdlknkXr1upa
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host     = localhost
user     = debian-sys-maint
password = 03P8rdlknkXr1upa
socket   = /var/run/mysqld/mysqld.sock
basedir  = /usr

RUNNING ON MASTER FIRST

sudo service mysql stop
sudo service mysql start --wsrep-new-cluster

RUN IT ON SLAVE(s) if there is some servers, you need to stop first if there was runed
sudo service mysql start

TESTING CLUSTER

ON NODE 1
mysql -u root -pMyPassword -e 'CREATE DATABASE testing;'
mysql -u root -pMyPassword -e 'CREATE TABLE testing.equipment ( id INT NOT NULL AUTO_INCREMENT, type VARCHAR(50), quant INT, color VARCHAR(25), PRIMARY KEY(id));'
mysql -u root -pMyPassword -e 'INSERT INTO testing.equipment (type, quant, color) VALUES ("slide", 2, "blue")'
ON NODE 2

mysql -u root -pMyPassword -e 'SELECT * FROM testing.equipment;'
mysql -u root -pMyPassword -e 'INSERT INTO testing.equipment (type, quant, color) VALUES ("swing", 10, "yellow");'
ON NODE 1
mysql -u root -pMyPassword -e 'SELECT * FROM testing.equipment;'
+----+-------+-------+--------+
| id | type  | quant | color  |
+----+-------+-------+--------+
|  1 | slide |     2 | blue   |
|  2 | swing |    10 | yellow |
+----+-------+-------+--------+

then you can see that all of update are synced on both.

..show all:.

.

Today its about mariaDB cluster, you need this to serve high intense request of your apps-query.

On my lab, i use 2 nodes and ubuntu as host OS. Lets try :

Let say :

Node 1 : 192.168.43.203
Node 4 : 192.168.43.204

DO THIS ON BOTH NODE

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

OR YOU CAN USE THIS REPO
deb http://mariadb.biz.net.id//repo/10.0/ubuntu trusty main


sudo apt-get update
sudo apt-get install mariadb-galera-server galera

sudo vim /etc/mysql/my.cnf     (comment binding)
#bind-address           = 127.0.0.1

CREATE THIS FILE ON BOTH

sudo vim /etc/mysql/conf.d/cluster.cnf

ON NODE 1:

[mysqld]
query_cache_size=0
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
query_cache_type=0
bind-address=0.0.0.0

# Galera Provider Configuration
wsrep_provider=/usr/lib/galera/libgalera_smm.so
#wsrep_provider_options="gcache.size=32G"

# Galera Cluster Configuration
wsrep_cluster_name="m_cluster"
wsrep_cluster_address="gcomm://192.168.43.203,192.168.43.204"

# Galera Synchronization Congifuration
wsrep_sst_method=rsync
#wsrep_sst_auth=user:pass

# Galera Node Configuration
wsrep_node_address="192.168.43.203"
wsrep_node_name="m1"

ON NODE 2:
sudo vim /etc/mysql/conf.d/cluster.cnf

[mysqld]
query_cache_size=0
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
query_cache_type=0
bind-address=0.0.0.0

# Galera Provider Configuration
wsrep_provider=/usr/lib/galera/libgalera_smm.so
#wsrep_provider_options="gcache.size=32G"

# Galera Cluster Configuration
wsrep_cluster_name="m_cluster"
wsrep_cluster_address="gcomm://192.168.43.203,192.168.43.204"

# Galera Synchronization Congifuration
wsrep_sst_method=rsync
#wsrep_sst_auth=user:pass

# Galera Node Configuration
wsrep_node_address="192.168.43.204"
wsrep_node_name="m2"

COPY FROM NODE 1 to NODE 2

sudo vim /etc/mysql/debian.cnf

[client]
host     = localhost
user     = debian-sys-maint
password = 03P8rdlknkXr1upa
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host     = localhost
user     = debian-sys-maint
password = 03P8rdlknkXr1upa
socket   = /var/run/mysqld/mysqld.sock
basedir  = /usr

RUNNING ON MASTER FIRST

sudo service mysql stop
sudo service mysql start --wsrep-new-cluster

RUN IT ON SLAVE(s) if there is some servers, you need to stop first if there was runed
sudo service mysql start

TESTING CLUSTER

ON NODE 1
mysql -u root -pMyPassword -e 'CREATE DATABASE testing;'
mysql -u root -pMyPassword -e 'CREATE TABLE testing.equipment ( id INT NOT NULL AUTO_INCREMENT, type VARCHAR(50), quant INT, color VARCHAR(25), PRIMARY KEY(id));'
mysql -u root -pMyPassword -e 'INSERT INTO testing.equipment (type, quant, color) VALUES ("slide", 2, "blue")'
ON NODE 2

mysql -u root -pMyPassword -e 'SELECT * FROM testing.equipment;'
mysql -u root -pMyPassword -e 'INSERT INTO testing.equipment (type, quant, color) VALUES ("swing", 10, "yellow");'
ON NODE 1
mysql -u root -pMyPassword -e 'SELECT * FROM testing.equipment;'
+----+-------+-------+--------+
| id | type  | quant | color  |
+----+-------+-------+--------+
|  1 | slide |     2 | blue   |
|  2 | swing |    10 | yellow |
+----+-------+-------+--------+

then you can see that all of update are synced on both.