Teguh Triharto Learning Center: .::: How to Create HA (High Availability) MariaDB/MySQL uting Galera Cluster on Linux (Include trouble shoot Crash case :::.

A. Installation Galera Cluster (example on 3 node = node1, node2, node3)
1. Disable selinux
[root@teguhth01 ~]# sestatus
SELinux status: disabled
[root@teguhth01 ~]# cat /etc/selinux/config
# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
# enforcing - SELinux security policy is enforced.
# permissive - SELinux prints warnings instead of enforcing.
# disabled - No SELinux policy is loaded.
SELINUX=disabled
# SELINUXTYPE= can take one of three values:
# targeted - Targeted processes are protected,
# minimum - Modification of targeted policy. Only selected processes are protected.
# mls - Multi Level Security protection.
SELINUXTYPE=targeted
[root@teguhth01 ~]#

2. alow/disable firewall
# systemctl stop firewalld
# systemctl disable firewalld
# systemctl status firewalld
[root@teguhth01 ~]# systemctl status firewalld
● firewalld.service - firewalld - dynamic firewall daemon
Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)
Active: inactive (dead)
Docs: man:firewalld(1)
[root@teguhth01 ~]#

3. additional node to /etc/hosts
10.10.10.10 node1
10.10.10.20 node2
10.10.10.30 node3

[root@teguhth01 ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
10.10.10.10 node01 teguhth01
10.10.10.20 node02 teguhth02
10.10.10.30 node03 teguhth03
[root@teguhth01 ~]#

4. remove mariadb-libs
[root@teguhth01 ~]# rpm -qa | grep maria
mariadb-libs-5.5.68-1.el7.x86_64
[root@teguhth01 ~]# yum remove mariadb-libs -y
[root@teguhth01 ~]# rpm -qa | grep maria
[root@teguhth01 ~]#

5. create cluster repo mariadb
[root@teguhth01 ~]# cat /etc/yum.repos.d/cluster.repo
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.0/centos7-amd64
gpgkey = https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
[root@teguhth01 ~]#
[root@teguhth01 ~]# yum repolist
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
* base: centos.mirror.angkasa.id
* extras: mirror.dewabiz.com
* updates: centos.mirror.angkasa.id
repo id repo name status
base/7/x86_64 CentOS-7 - Base 10,072
extras/7/x86_64 CentOS-7 - Extras 498
mariadb MariaDB 43
updates/7/x86_64 CentOS-7 - Updates 2,458
repolist: 13,071
[root@teguhth01 ~]#

6. install socat
[root@teguhth01 ~]# yum -y install socat

7. install MariaDB galera cluster package on node 1 2 3
[root@teguhth01 ~]# yum install MariaDB-Galera-server MariaDB-client rsync galera -y
[root@teguhth01 ~]# rpm -qa | grep MariaDB*
MariaDB-common-10.0.38-1.el7.centos.x86_64
MariaDB-Galera-server-10.0.38-1.el7.centos.x86_64
MariaDB-client-10.0.38-1.el7.centos.x86_64
[root@teguhth01 ~]#

8. start & enable mariadb
[root@teguhth01 ~]# systemctl start mysql
[root@teguhth01 ~]# systemctl enable mysql
mysql.service is not a native service, redirecting to /sbin/chkconfig.
Executing /sbin/chkconfig mysql on
[root@teguhth01 ~]# /sbin/chkconfig mysql on
[root@teguhth01 ~]# systemctl status mysql
● mysql.service - LSB: start and stop MariaDB
Loaded: loaded (/etc/rc.d/init.d/mysql; bad; vendor preset: disabled)
Active: active (running) since Tue 2021-07-06 10:54:46 WIB; 28s ago
Docs: man:systemd-sysv-generator(8)
CGroup: /system.slice/mysql.service
├─1578 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/v...
└─1701 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-d...
Jul 06 10:54:41 teguhth01 systemd[1]: Starting LSB: start and stop MariaDB...
Jul 06 10:54:41 teguhth01 mysql[1573]: Starting MariaDB.210706 10:54:41 mysqld_safe...'.
Jul 06 10:54:41 teguhth01 mysql[1573]: 210706 10:54:41 mysqld_safe Starting mysqld ...ql
Jul 06 10:54:46 teguhth01 mysql[1573]: . SUCCESS!
Jul 06 10:54:46 teguhth01 systemd[1]: Started LSB: start and stop MariaDB.
Hint: Some lines were ellipsized, use -l to show in full.
[root@teguhth01 ~]#

9. secure mysql
# mysql_secure_installation

[root@teguhth01 ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.0.38-MariaDB-wsrep MariaDB Server, wsrep_25.25.rc3fc46e
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>

10. enable user for cluster acsess
# mysql -u root -p

grant usage on *.* to teguh@'%' identified by 'triharto';
grant all privileges on *.* to teguh@'%' identified by 'triharto';
flush privileges;

[root@teguhth01 ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.0.38-MariaDB-wsrep MariaDB Server, wsrep_25.25.rc3fc46e
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> grant usage on *.* to teguh@'%' identified by 'triharto';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> grant all privileges on *.* to teguh@'%' identified by 'triharto';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]>

B. Configuration MariaDB / Galera Cluster
1. stop mysql
# systemctl stop mysql
[root@teguhth01 ~]# systemctl stop mysql
[root@teguhth01 ~]# systemctl status mysql
[root@teguhth01 ~]#

2. Configuration to node1 /etc/my.cnf.d/server.cnf
[mariadb-10.0]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog=1
query_cache_size=0
query_cache_type=0
bind-address=0.0.0.0
datadir=/var/lib/mysql
innodb_log_file_size=100M
innodb_file_per_table
innodb_flush_log_at_trx_commit=2
# ip node
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://10.10.10.10, 10.10.10.20, 10.10.10.30"
wsrep_cluster_name='galera_cluster'
wsrep_node_address='10.10.10.10'
wsrep_node_name='node1'
wsrep_sst_method=rsync
wsrep_sst_auth=teguh:triharto

[root@teguhth01 ~]# cat /etc/my.cnf.d/server.cnf
[galera]
# Mandatory settings
#wsrep_provider=
#wsrep_cluster_address=
#binlog_format=row
#default_storage_engine=InnoDB
#innodb_autoinc_lock_mode=2
#bind-address=0.0.0.0
#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0
[mariadb-10.0]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog=1
query_cache_size=0
query_cache_type=0
bind-address=0.0.0.0
datadir=/var/lib/mysql
innodb_log_file_size=100M
innodb_file_per_table
innodb_flush_log_at_trx_commit=2
# ip node
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://10.10.10.10, 10.10.10.20, 10.10.10.30"
wsrep_cluster_name='galera_cluster'
wsrep_node_address='10.10.10.10'
wsrep_node_name='node1'
wsrep_sst_method=rsync
wsrep_sst_auth=teguh:triharto
[root@teguhth01 ~]#

3. Configuration to node2 /etc/my.cnf.d/server.cnf
[mariadb-10.0]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog=1
query_cache_size=0
query_cache_type=0
bind-address=0.0.0.0
datadir=/var/lib/mysql
innodb_log_file_size=100M
innodb_file_per_table
innodb_flush_log_at_trx_commit=2
# ip node
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://10.10.10.10, 10.10.10.20, 10.10.10.30"
wsrep_cluster_name='galera_cluster'
wsrep_node_address='10.10.10.20'
wsrep_node_name='node2'
wsrep_sst_method=rsync
wsrep_sst_auth=teguh:triharto

4. Configuration to node3 /etc/my.cnf.d/server.cnf
[mariadb-10.0]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog=1
query_cache_size=0
query_cache_type=0
bind-address=0.0.0.0
datadir=/var/lib/mysql
innodb_log_file_size=100M
innodb_file_per_table
innodb_flush_log_at_trx_commit=2
# ip node
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://10.10.10.10, 10.10.10.20, 10.10.10.30"
wsrep_cluster_name='galera_cluster'
wsrep_node_address='10.10.10.30'
wsrep_node_name='node3'
wsrep_sst_method=rsync
wsrep_sst_auth=teguh:triharto

5. node 1 start mysql
[root@teguhth01 ~]# /etc/init.d/mysql start --wsrep-new-cluster
Starting MariaDB.210706 11:03:49 mysqld_safe Logging to '/var/lib/mysql/teguhth01.err'.
210706 11:03:50 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
.. SUCCESS!
[root@teguhth01 ~]#
[root@teguhth01 ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.0.38-MariaDB-wsrep MariaDB Server, wsrep_25.25.rc3fc46e
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show status like 'wsrep%';
+------------------------------+-------------------------------------------------+
| Variable_name | Value |
+------------------------------+-------------------------------------------------+
| wsrep_local_state_uuid | 2e11121c-de0f-11eb-b2f2-4eb8fba62ec1 |
| wsrep_protocol_version | 9 |
| wsrep_last_committed | 4 |
| wsrep_replicated | 0 |
| wsrep_replicated_bytes | 0 |
| wsrep_repl_keys | 0 |
| wsrep_repl_keys_bytes | 0 |
| wsrep_repl_data_bytes | 0 |
| wsrep_repl_other_bytes | 0 |
| wsrep_received | 3 |
| wsrep_received_bytes | 343 |
| wsrep_local_commits | 0 |
| wsrep_local_cert_failures | 0 |
| wsrep_local_replays | 0 |
| wsrep_local_send_queue | 0 |
| wsrep_local_send_queue_max | 1 |
| wsrep_local_send_queue_min | 0 |
| wsrep_local_send_queue_avg | 0.000000 |
| wsrep_local_recv_queue | 0 |
| wsrep_local_recv_queue_max | 2 |
| wsrep_local_recv_queue_min | 0 |
| wsrep_local_recv_queue_avg | 0.333333 |
| wsrep_local_cached_downto | 18446744073709551615 |
| wsrep_flow_control_paused_ns | 0 |
| wsrep_flow_control_paused | 0.000000 |
| wsrep_flow_control_sent | 0 |
| wsrep_flow_control_recv | 0 |
| wsrep_cert_deps_distance | 0.000000 |
| wsrep_apply_oooe | 0.000000 |
| wsrep_apply_oool | 0.000000 |
| wsrep_apply_window | 0.000000 |
| wsrep_commit_oooe | 0.000000 |
| wsrep_commit_oool | 0.000000 |
| wsrep_commit_window | 0.000000 |
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced |
| wsrep_cert_index_size | 0 |
| wsrep_causal_reads | 0 |
| wsrep_cert_interval | 0.000000 |
| wsrep_open_transactions | 0 |
| wsrep_open_connections | 0 |
| wsrep_incoming_addresses | 10.10.10.20:3306,10.10.10.10:3306 |
| wsrep_cluster_weight | 2 |
| wsrep_desync_count | 0 |
| wsrep_evs_delayed | |
| wsrep_evs_evict_list | |
| wsrep_evs_repl_latency | 0.000745203/0.00098208/0.00121896/0.000236877/2 |
| wsrep_evs_state | OPERATIONAL |
| wsrep_gcomm_uuid | f4f8872f-de23-11eb-89fb-b3e16875830f |
| wsrep_cluster_conf_id | 2 |
| wsrep_cluster_size | 2 |
| wsrep_cluster_state_uuid | 2e11121c-de0f-11eb-b2f2-4eb8fba62ec1 |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
| wsrep_local_bf_aborts | 0 |
| wsrep_local_index | 1 |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy <info@codership.com> |
| wsrep_provider_version | 25.3.25(r3836) |
| wsrep_ready | ON |
| wsrep_thread_count | 2 |
+------------------------------+-------------------------------------------------+
61 rows in set (0.00 sec)
MariaDB [(none)]>

6. node 2, 3 start mysql/mariadb
/etc/init.d/mysql start

[root@teguhth02 ~]# systemctl start mysql
[root@teguhth02 ~]#
[root@teguhth02 ~]# systemctl status mysql
● mysql.service - LSB: start and stop MariaDB
Loaded: loaded (/etc/rc.d/init.d/mysql; bad; vendor preset: disabled)
Active: active (running) since Tue 2021-07-06 12:16:09 WIB; 30s ago
Docs: man:systemd-sysv-generator(8)
Process: 1494 ExecStart=/etc/rc.d/init.d/mysql start (code=exited, status=0/SUCCESS)
CGroup: /system.slice/mysql.service
├─1556 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/teguhth02.pid
└─1739 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --wsrep_provider=/usr/lib64/galera/libgalera_smm.so --log-error...
Jul 06 12:16:04 teguhth02 rsyncd[1821]: rsync to rsync_sst/./mysql from node1 (10.10.10.10)
Jul 06 12:16:04 teguhth02 rsyncd[1821]: receiving file list
Jul 06 12:16:05 teguhth02 rsyncd[1827]: connect from node1 (10.10.10.10)
Jul 06 12:16:05 teguhth02 rsyncd[1827]: rsync to rsync_sst/./performance_schema from node1 (10.10.10.10)
Jul 06 12:16:05 teguhth02 rsyncd[1827]: receiving file list
Jul 06 12:16:05 teguhth02 rsyncd[1829]: connect from node1 (10.10.10.10)
Jul 06 12:16:05 teguhth02 rsyncd[1829]: rsync to rsync_sst/ from node1 (10.10.10.10)
Jul 06 12:16:05 teguhth02 rsyncd[1829]: receiving file list
Jul 06 12:16:09 teguhth02 mysql[1494]: .SST in progress, setting sleep higher. SUCCESS!
Jul 06 12:16:09 teguhth02 systemd[1]: Started LSB: start and stop MariaDB.
[root@teguhth02 ~]#
[root@teguhth02 ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.0.38-MariaDB-wsrep MariaDB Server, wsrep_25.25.rc3fc46e
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show status like 'wsrep%';
+------------------------------+--------------------------------------+
| Variable_name | Value |
+------------------------------+--------------------------------------+
| wsrep_local_state_uuid | 2e11121c-de0f-11eb-b2f2-4eb8fba62ec1 |
| wsrep_protocol_version | 9 |
| wsrep_last_committed | 4 |
| wsrep_replicated | 0 |
| wsrep_replicated_bytes | 0 |
| wsrep_repl_keys | 0 |
| wsrep_repl_keys_bytes | 0 |
| wsrep_repl_data_bytes | 0 |
| wsrep_repl_other_bytes | 0 |
| wsrep_received | 2 |
| wsrep_received_bytes | 210 |
| wsrep_local_commits | 0 |
| wsrep_local_cert_failures | 0 |
| wsrep_local_replays | 0 |
| wsrep_local_send_queue | 0 |
| wsrep_local_send_queue_max | 1 |
| wsrep_local_send_queue_min | 0 |
| wsrep_local_send_queue_avg | 0.000000 |
| wsrep_local_recv_queue | 0 |
| wsrep_local_recv_queue_max | 1 |
| wsrep_local_recv_queue_min | 0 |
| wsrep_local_recv_queue_avg | 0.000000 |
| wsrep_local_cached_downto | 18446744073709551615 |
| wsrep_flow_control_paused_ns | 0 |
| wsrep_flow_control_paused | 0.000000 |
| wsrep_flow_control_sent | 0 |
| wsrep_flow_control_recv | 0 |
| wsrep_cert_deps_distance | 0.000000 |
| wsrep_apply_oooe | 0.000000 |
| wsrep_apply_oool | 0.000000 |
| wsrep_apply_window | 0.000000 |
| wsrep_commit_oooe | 0.000000 |
| wsrep_commit_oool | 0.000000 |
| wsrep_commit_window | 0.000000 |
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced |
| wsrep_cert_index_size | 0 |
| wsrep_causal_reads | 0 |
| wsrep_cert_interval | 0.000000 |
| wsrep_open_transactions | 0 |
| wsrep_open_connections | 0 |
| wsrep_incoming_addresses | 10.10.10.20:3306,10.10.10.10:3306 |
| wsrep_cluster_weight | 2 |
| wsrep_desync_count | 0 |
| wsrep_evs_delayed | |
| wsrep_evs_evict_list | |
| wsrep_evs_repl_latency | 0/0/0/0/0 |
| wsrep_evs_state | OPERATIONAL |
| wsrep_gcomm_uuid | 1d90064e-de24-11eb-8b5f-ae537bb3b1c0 |
| wsrep_cluster_conf_id | 2 |
| wsrep_cluster_size | 2 |
| wsrep_cluster_state_uuid | 2e11121c-de0f-11eb-b2f2-4eb8fba62ec1 |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
| wsrep_local_bf_aborts | 0 |
| wsrep_local_index | 0 |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy <info@codership.com> |
| wsrep_provider_version | 25.3.25(r3836) |
| wsrep_ready | ON |
| wsrep_thread_count | 2 |
+------------------------------+--------------------------------------+
61 rows in set (0.00 sec)
MariaDB [(none)]>

C. Testing database clustering
1. query to node 1
mysql -u root -p
create database database1 ;
use database1;
create table os(categories char(25));
insert into os values ('Linux');
select * from os;
MariaDB [(none)]> create database database1 ;
Query OK, 1 row affected (0.37 sec)
MariaDB [(none)]> use database1;
Database changed
MariaDB [database1]> create table os(categories char(25));
Query OK, 0 rows affected (0.15 sec)
MariaDB [database1]> insert into os values ('Linux');
Query OK, 1 row affected (0.02 sec)
MariaDB [database1]> select * from os;
+------------+
| categories |
+------------+
| Linux |
+------------+
1 row in set (0.01 sec)
MariaDB [database1]>

2. check node 2, 3
mysql -u root -p
use database1
show databases;
select * from os;
MariaDB [database1]> show databases;
+--------------------+
| Database |
+--------------------+
| database1 |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
MariaDB [database1]> select * from os;
+------------+
| categories |
+------------+
| Linux |
+------------+
1 row in set (0.00 sec)
MariaDB [database1]>

3. query to node 2
insert into os values ('Windows');
select * from os;
MariaDB [database1]> insert into os values ('Windows');
Query OK, 1 row affected (0.01 sec)
MariaDB [database1]> select * from os;
+------------+
| categories |
+------------+
| Linux |
| Windows |
+------------+
2 rows in set (0.00 sec)
MariaDB [database1]>

5. query to node 3
insert into os values ('Solaris');
select * from os;

6. check node 1, 2
mysql -u root -p
show databases;
select * from os;

D. Testing database again
1. query to node 1
mysql -u root -p
create database mobile_legend ;
use mobile_legend;
create table tbl_hero(hero char(25));
insert into tbl_hero values ('Miya');
select * from tbl_hero;
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| database1 |
| information_schema |
| mobile_legend |
| mysql |
| performance_schema |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]>
MariaDB [mobile_legend]> create table tbl_hero(hero char(25));
Query OK, 0 rows affected (0.10 sec)
MariaDB [mobile_legend]> insert into tbl_hero values ('Miya');
Query OK, 1 row affected (0.00 sec)
MariaDB [mobile_legend]> select * from tbl_hero;
+------+
| hero |
+------+
| Miya |
+------+
1 row in set (0.01 sec)
MariaDB [mobile_legend]>

Teguh Triharto Learning Center

Blog List

Tuesday, July 6, 2021

.::: How to Create HA (High Availability) MariaDB/MySQL uting Galera Cluster on Linux (Include trouble shoot Crash case :::.

No comments:

Post a Comment

Popular Posts