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