setup mysql on all 3 vm
Prepare 3 vm
First_node : 192.168.115.128
Second_node : 192.168.115.129
Third_node : 192.168.115.130
Install mysql on all 3 vm
sudo apt-key adv — keyserver keyserver.ubuntu.com — recv BC19DDBA
sudo nano /etc/apt/sources.list.d/galera.list
deb http://releases.galeracluster.com/mysql-wsrep-5.7/ubuntu bionic main
deb http://releases.galeracluster.com/galera-3/ubuntu bionic main
sudo nano /etc/apt/preferences.d/galera.pref
# Prefer Codership repository
Package: *
Pin: origin releases.galeracluster.com
Pin-Priority: 1001
sudo apt update
sudo apt install galera-3 mysql-wsrep-5.7
Disable apparmor and remove mysql definition loaded in kernel
sudo ln -s /etc/apparmor.d/usr.sbin.mysqld /etc/apparmor.d/disable/
sudo apparmor_parser -R /etc/apparmor.d/usr.sbin.mysqld
prepare configuration on all 3 node
sudo nano /etc/mysql/conf.d/galera.cnf
[mysqld]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
# Galera Cluster Configuration
wsrep_cluster_name=”test_cluster”
wsrep_cluster_address=”gcomm://{first_ip},{second_ip},{third_ip}”
# Galera Synchronization Configuration
wsrep_sst_method=rsync
# Galera Node Configuration
wsrep_node_address=”{ip_address}”
wsrep_node_name=”{node name}”
Replace value wrapped with ‘{}’ with your node values
Ufw config on 3 node
sudo ufw status
If status inactive then is fine
If active , add below ports in ufw
sudo ufw allow 3306,4567,4568,4444/tcp
sudo ufw allow 4567/udp
Start first node — 192.168.115.128
sudo mysqld_bootstrap
Check cluster size
mysql -u root -p -e “SHOW STATUS LIKE ‘wsrep_cluster_size’”
Join second node 192.168.115.129 into cluster
sudo systemctl start mysql
Check cluster size again
mysql -u root -p -e “SHOW STATUS LIKE ‘wsrep_cluster_size’”
Join third node 192.168.115.130 into cluster
sudo systemctl start mysql
Check cluster size again
mysql -u root -p -e “SHOW STATUS LIKE ‘wsrep_cluster_size’”
Configure Start on boot on each node
sudo systemctl enable mysql
Test
Login to first_node 192.168.115.128
Run
mysql -u root -p -e ‘CREATE DATABASE test;
CREATE TABLE test.person ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(50) ,PRIMARY KEY(id));
INSERT INTO test.person (name) VALUES (“jeo”);’
Login on second_node 192.168.115.129
mysql -u root -p -e ‘SELECT * FROM test.person;’
should see the data inserted from first node
Now Insert row from second node (192.168.115.129)
mysql -u root -p -e ‘INSERT INTO test.person (name) VALUES (“mark”);’
Login on third node 192.168.115.130
mysql -u root -p -e ‘SELECT * FROM test.person;’
should see the data inserted from first and second node
Now insert row from third node (192.168.115.130)
mysql -u root -p -e ‘INSERT INTO test.person (name) VALUES (“peter”);’
Login on first node (192.168.115.128) again
mysql -u root -p -e ‘SELECT * FROM test.person;’
should see the newly inserted data from 3rd node
Data is synchronizing correctly between 3 nodes
Done!
