MariaDB Semi-synchronous Replication Master-Slave with Galera Cluster on Ubuntu

MariaDB Semi-synchronous Replication Master-Slave dengan Galera Cluster on Ubuntu

  • Mode semi-sinkron (Semi-synchronous), node master akan menunggu konfirmasi dari node slave jika telah menerima pembaruan data, memastikan bahwa selalu ada setidaknya satu salinan lengkap dari database yang tersedia jika terjadi kegagalan node master.
  • Artikel ini melanjutkan dari artikel sebelumnya:

Load Balancing MariaDB Galera Cluster Using MariaDB MaxScale With Encrypt Replication Trafficon Ubuntu 20.04/22.04

MariaDB Asynchronous Replication Master-Slave with Galera Cluster on Ubuntu

MariaDB Galera Cluster

  • db01 : 192.168.88.11 – Galera Cluster
  • db02 : 192.168.88.12 – Galera Cluster
  • db03 : 192.168.88.13 – Galera Cluster
  • db04 : 192.168.88.15 – Backup Slave

Setting Binnary Log dan Replikasi pada Gelara Cluster (Master)

  • Pastikan mariadb galera cluster sudah di settinga dan berjalan tanpa error
  • Edit file konfigurasi gelera
nano /etc/mysql/mariadb.conf.d/60-galera.cnf
  • Tambahkan baris berikut pada semua node gelare cluster
  • server-id dan log-basename harus berbeda dengan node slave
# Master-Master Backup config
server-id=1
log-bin
log-basename=host-galera
plugin_load_add=semisync_master
rpl_semi_sync_master_enabled=ON
  • full script:
[mysqld]
log_error=/var/log/mariadb.log
slow_query_log = ON
slow_query_log_file = /var/log/mariadb-slow.log
long_query_time = 30

innodb-file-per-table = 1
innodb-buffer-pool-size = 1G
innodb-buffer-pool-chunk-size = 128M
innodb-buffer-pool-instances = 8
innodb-log-file-size = 64M
innodb-io-capacity = 5000
innodb-io-capacity-max = 50000
innodb-read-io-threads = 8
innodb-write-io-threads = 8
innodb-thread-concurrency = 32
innodb_large_prefix = ON

max_connections = 300
thread-cache-size = 300
interactive_timeout = 600
wait_timeout = 600

max-allowed-packet = 32M
key-buffer-size = 128M
query-cache-size = 256M
query-cache-limit = 64M
max-heap-table-size = 32M
tmp-memory-table-size = 32M
tmp-table-size = 32M

open-files-limit = 50000
table-definition-cache = 5000
table-open-cache = 15000
binlog-cache-size = 1M
thread_stack = 1M
join-buffer-size = 1M

myisam_sort_buffer_size = 64M
sort_buffer_size = 2M
read_buffer_size = 1M
read_rnd_buffer_size = 1M

ssl-ca=/etc/ssl/mysql/ca.pem
ssl-cert=/etc/ssl/mysql/server-cert.pem
ssl-key=/etc/ssl/mysql/server-key.pem

[client]
ssl-ca=/etc/ssl/mysql/ca.pem
ssl-cert=/etc/ssl/mysql/client-cert.pem
ssl-key=/etc/ssl/mysql/client-key.pem

[galera]
# Mandatory settings
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_name="galeracluster_sys-ops-id"
wsrep_cluster_address="gcomm://192.168.88.11,192.168.88.12,192.168.88.13"
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
wsrep_sst_method=rsync
wsrep_node_address="192.168.88.12"
wsrep_node_name="node_2"
bind-address=0.0.0.0
wsrep_provider_options="gcache.size = 512M; gcache.page_size = 256M; gcache.recover = yes; socket.ssl_key=/etc/ssl/mysql/server-key.pem;socket.ssl_cert=/etc/ssl/mysql/server-cert.pem;socket.ssl_ca=/etc/ssl/mysql/ca.pem"

# Master-Master Backup config
server-id=1
log-bin
log-basename=host-galera
plugin_load_add=semisync_master
rpl_semi_sync_master_enabled=ON
  • Stop semua service mariadb pada node galera cluster
systemctl stop mariadb
  • Jalankan service galera cluster pada node db01
galera_new_cluster
  • Jalankan service mariadb pada node db02 & db03
systemctl start mariadb
  • Masuk ke node db01, tambahakan user untuk replikasi slave
MariaDB [(none)]> create user 'replikasi_user'@'%' identified by 'P@ssw0rd!';
MariaDB [(none)]> grant replication slave on *.* to 'replikasi_user'@'%';
MariaDB [(none)]> flush privileges;

  • Masih di node db01, Tambahkan database dengan nama sys_ops_id_db
MariaDB [(none)]> CREATE DATABASE sys_ops_id_db;
MariaDB [(none)]> use sys_ops_id_db;
MariaDB [sys_ops_id_db]> CREATE TABLE `sys_ops_id_db`.`webdata` (`id` INT NOT NULL AUTO_INCREMENT, `data_base` VARCHAR(20) NOT NULL, `webserver` VARCHAR(20) NOT NULL, PRIMARY KEY (`id`)) ENGINE = InnoDB;
MariaDB [sys_ops_id_db]> show tables;
+-------------------------+
| Tables_in_sys_ops_id_db |
+-------------------------+
| webdata                 |
+-------------------------+

MariaDB [sys_ops_id_db]> describe webdata;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| data_base | varchar(20) | NO   |     | NULL    |                |
| webserver | varchar(20) | NO   |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+

MariaDB [sys_ops_id_db]> INSERT INTO `webdata` (`id`, `data_base`, `webserver`) VALUES ('1', 'MySQL', 'Apache');
MariaDB [sys_ops_id_db]> INSERT INTO `webdata` (`id`, `data_base`, `webserver`) VALUES ('2', 'MariaDB', 'Nginx');
MariaDB [sys_ops_id_db]> INSERT INTO `webdata` (`id`, `data_base`, `webserver`) VALUES ('3', 'MsSQL', 'IIS');
MariaDB [sys_ops_id_db]> INSERT INTO `webdata` (`id`, `data_base`, `webserver`) VALUES ('4', 'Oracle', 'LiteSpeed');

MariaDB [sys_ops_id_db]> select * from sys_ops_id_db.webdata;
+----+------------+-----------+
| id | data_base  | webserver |
+----+------------+-----------+
|  1 | MySQL      | Apache    |
|  2 | MariaDB    | Nginx     |
|  3 | MsSQL      | IIS       |
|  4 | Oracle     | LiteSpeed |
+----+------------+-----------+

Setting Relay Log dan Replikasi pada node db04 (Slave)

  • Edit dan setting mariadb pada node db04
nano /etc/mysql/mariadb.conf.d/50-server.cnf
  • Tambahkan baris berikut:
  • server-id dan log-basename harus berbeda dengan node slave
#Replication Master Backup to Galera Cluster
server-id=2
log-bin
log-basename=host-backup
plugin_load_add=semisync_slave
rpl_semi_sync_slave_enabled=ON
  • full script:
[server]

[mysqld]
log_error=/var/log/mariadb.log
innodb_large_prefix = ON
key-buffer-size = 64M
max-heap-table-size = 64M
max-allowed-packet = 48M
tmp-table-size = 128M
max_connections = 256
thread-cache-size = 50
thread_stack=2M
open-files-limit = 500000
table-definition-cache = 4096
table-open-cache = 8192
innodb-log-files-in-group = 2
innodb-log-file-size = 1G
innodb-file-per-table = 1
innodb-buffer-pool-size = 256M
innodb-buffer-pool-instances = 8
innodb-io-capacity = 5000
innodb-read-io-threads = 16
innodb-write-io-threads = 16
innodb_doublewrite = 1
innodb_adaptive_hash_index = False
transaction_isolation = READ-COMMITTED
innodb-thread-concurrency = 64
wait_timeout = 300
sort_buffer_size = 8M
read_buffer_size = 8M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
query_cache_size= 64M
skip-name-resolve
open_files_limit = 64000

pid-file = /run/mysqld/mysqld.pid
basedir = /usr
bind-address = 0.0.0.0
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci

ssl-ca=/etc/ssl/mysql/ca.pem
ssl-cert=/etc/ssl/mysql/server-cert.pem
ssl-key=/etc/ssl/mysql/server-key.pem

#Replication Master Backup to Galera Cluster
server-id=2
log-bin
log-basename=host-backup
plugin_load_add=semisync_slave
rpl_semi_sync_slave_enabled=ON
slave-skip-errors=1062, 1032

[client]
ssl-ca=/etc/ssl/mysql/ca.pem
ssl-cert=/etc/ssl/mysql/client-cert.pem
ssl-key=/etc/ssl/mysql/client-key.pem
  • Restart service mariadb
systemctl restart mariadb

Copy Database dari Node Galera Cluster (Master) ke node Slave

  • Masuk ke node db01
  • Export database sys_ops_id_db yang sebelumnya sudah dibuat dengan mysqldump. File backup disimpan di directory /root/
mysqldump -u root sys_ops_id_db --master-data=2 -p > sys_ops_id_db.sql
  • Copy file backup .sql ke node db04 dengan scp
scp -r /root/sys_ops_id_db.sql  [email protected]:/root/
[email protected]'s password:
sys_ops_id_db.sql                    100% 2307     1.0MB/s   00:00
  • Buat database dengan nama sys_ops_id_db pada node db04
MariaDB [(none)]> create database sys_ops_id_db;
  • Import file backup sys_ops_id_db.sql ke database sys_ops_id_db
mysql -u root sys_ops_id_db -p < sys_ops_id_db.sql

Koneksikan Node Slave ke Node Master (Galera Cluster)

  • Masuk ke node db04
  • Tambahakan koneksi profile dengan nama galera1
  • master_host menggunakan IP node db01 (192.168.88.11)
MariaDB [(none)]> change master 'galera1' to
    master_host='192.168.88.11',
    master_user='replikasi_user',
    master_password='P@ssw0rd!',
    master_port=3306,
    master_connect_retry=10,
    master_use_gtid=slave_pos,
    master_ssl=1;
  • Jalankan koneksi slave galera1
MariaDB [(none)]> start slave 'galera1';
  • Check the status koneksi slave galera1
  • Pastikan Slave_IO_Running: Yes dan Slave_SQL_Running: Yes
  • Pastikan pada Slave_SQL_Running_State tidak ada error
MariaDB [(none)]> show slave 'galera1' status\G;
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.88.11
                   Master_User: replikasi_user
                   Master_Port: 3306
                 Connect_Retry: 10
               Master_Log_File: host-galera-bin.000003
           Read_Master_Log_Pos: 666
                Relay_Log_File: host-backup-relay-bin-galera1.000002
                 Relay_Log_Pos: 971
         Relay_Master_Log_File: host-galera-bin.000003
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
               Replicate_Do_DB:
           Replicate_Ignore_DB:
            Replicate_Do_Table:
        Replicate_Ignore_Table:
       Replicate_Wild_Do_Table:
   Replicate_Wild_Ignore_Table:
                    Last_Errno: 0
                    Last_Error:
                  Skip_Counter: 0
           Exec_Master_Log_Pos: 666
               Relay_Log_Space: 1294
               Until_Condition: None
                Until_Log_File:
                 Until_Log_Pos: 0
            Master_SSL_Allowed: Yes
            Master_SSL_CA_File:
            Master_SSL_CA_Path:
               Master_SSL_Cert:
             Master_SSL_Cipher:
                Master_SSL_Key:
         Seconds_Behind_Master: 0
 Master_SSL_Verify_Server_Cert: No
                 Last_IO_Errno: 0
                 Last_IO_Error:
                Last_SQL_Errno: 0
                Last_SQL_Error:
   Replicate_Ignore_Server_Ids:
              Master_Server_Id: 1
                Master_SSL_Crl:
            Master_SSL_Crlpath:
                    Using_Gtid: Slave_Pos
                   Gtid_IO_Pos: 0-1-2
       Replicate_Do_Domain_Ids:
   Replicate_Ignore_Domain_Ids:
                 Parallel_Mode: optimistic
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
              Slave_DDL_Groups: 2
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0
  • Untuk stop replikasi slave
MariaDB [(none)]> stop slave 'galera1';
  • Untuk reset replikasi (clean state)
MariaDB [(none)]> reset slave 'galera1';
  • Untuk melanjutkan replikasi task yang terhenti bisa dengan restart service mariadb
systemctl restart mariadb

Pengujian

  • Verifikasi status semi-synchronization pada node master
  • rpl_semi_sync_master_enabled = ON
  • rpl_semi_sync_slave_enabled = OFF
MariaDB [(none)]> show global variables like '%semi%' ;
+---------------------------------------+--------------+
| Variable_name                         | Value        |
+---------------------------------------+--------------+
| rpl_semi_sync_master_enabled          | ON           |
| rpl_semi_sync_master_timeout          | 10000        |
| rpl_semi_sync_master_trace_level      | 32           |
| rpl_semi_sync_master_wait_no_slave    | ON           |
| rpl_semi_sync_master_wait_point       | AFTER_COMMIT |
| rpl_semi_sync_slave_delay_master      | OFF          |
| rpl_semi_sync_slave_enabled           | OFF          |
| rpl_semi_sync_slave_kill_conn_timeout | 5            |
| rpl_semi_sync_slave_trace_level       | 32           |
+---------------------------------------+--------------+
  • Verifikasi status semi-synchronization pada node slave
  • rpl_semi_sync_master_enabled = OFF
  • rpl_semi_sync_slave_enabled = ON
MariaDB [(none)]> show global variables like '%semi%' ;
+---------------------------------------+--------------+
| Variable_name                         | Value        |
+---------------------------------------+--------------+
| rpl_semi_sync_master_enabled          | OFF          |
| rpl_semi_sync_master_timeout          | 10000        |
| rpl_semi_sync_master_trace_level      | 32           |
| rpl_semi_sync_master_wait_no_slave    | ON           |
| rpl_semi_sync_master_wait_point       | AFTER_COMMIT |
| rpl_semi_sync_slave_delay_master      | OFF          |
| rpl_semi_sync_slave_enabled           | ON           |
| rpl_semi_sync_slave_kill_conn_timeout | 5            |
| rpl_semi_sync_slave_trace_level       | 32           |
+---------------------------------------+--------------+

  • Tambahkan data (record) pada database sys_ops_id_db di node galera cluster (bebas bisa dari db01, db02, db03). Buat database baru dengan nama mariadb_sysopsid
  • lalu cek gtid
MariaDB [(none)]> INSERT INTO `sys_ops_id_db`.`webdata` (`id`, `data_base`, `webserver`) VALUES ('5', 'PostgreSQL', 'Lighttpd');
MariaDB [(none)]> create database mariadb_sysopsid;
MariaDB [(none)]> show variables like 'gtid_binlog_pos';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| gtid_binlog_pos | 0-1-8 |
+-----------------+-------+
  • Verifikasi pada node slave (db04)
  • Pastikan data yang barusan di tambahakan sudah tersedia di node slave dan gtid sama dengan yang ada di node master.
MariaDB [(none)]> show slave 'galera1' status\G;
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.88.11
                   Master_User: replication
                   Master_Port: 3306
                 Connect_Retry: 10

         Relay_Master_Log_File: host-galera-bin.000003
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes

                    Using_Gtid: Slave_Pos
                   Gtid_IO_Pos: 0-1-8
       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mariadb_sysopsid   |
| mysql              |
| performance_schema |
| sys_ops_id_db      |
+--------------------+

MariaDB [(none)]> select * from sys_ops_id_db.webdata;
+----+------------+-----------+
| id | data_base  | webserver |
+----+------------+-----------+
|  1 | MySQL      | Apache    |
|  2 | MariaDB    | Nginx     |
|  3 | MsSQL      | IIS       |
|  4 | Oracle     | LiteSpeed |
|  5 | PostgreSQL | Lighttpd  |
+----+------------+-----------+

Ganti Master Node

  • Jika yang dijadikan master node mengalami kerusakan/error dalam artikel ini menggunakan node db01, bisa di ganti dengan node lain yang ada pada galera cluster caranya cukup ganti saja pada bagian master_host. Bisa menggunakan node db02 ataupun db03.
  • Stop terlebih dahulu koneksi slave galera01, lalu ganti master_host, lalu start kemabli koneksi galera01
MariaDB [(none)]> stop slave 'galera1';

MariaDB [(none)]> change master 'galera1' to
    master_host='192.168.88.12',
    master_user='replikasi_user',
    master_password='P@ssw0rd!',
    master_port=3306,
    master_connect_retry=10,
    master_use_gtid=slave_pos,
    master_ssl=1;

MariaDB [(none)]> start slave 'galera1';

herdiana3389

A system administrator with skills in system administration, virtualization, linux, windows, networking, cloud computing, container, etc.