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 |
+--------------+
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
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.
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