Clustering Database with Percona XtraDB Cluster on Ubuntu

Konfigurasi Database Cluster dengan Percona XtraDB Cluster pada Ubuntu

MariaDB Galera Cluster

  • node-1: 10.107.176.27
  • node-2: 10.107.176.55
  • Pastikan port berikut tidak di blokir oleh firewall: 3306 , 4444 , 4567 , 4568

Install Percona XtraDB Cluster pada semua node

  • Update repository index ubuntu
apt update && apt upgrade -y
  • Install necessary package
apt install -y wget gnupg2 lsb-release curl
  • Download repository package percona
wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb
  • Install package repository
dpkg -i percona-release_latest.generic_all.deb
  • Enable repository Percona XtraDB Cluster
apt update && percona-release setup pxc80
  • Install Percona XtraDB Cluster, saat install akan disuruh untuk memasukan root password mysql
apt install -y percona-xtradb-cluster

  • Cek versi mysql
mysql -V
mysql  Ver 8.0.27-18.1 for Linux on x86_64 (Percona XtraDB Cluster (GPL), Release rel18, Revision ac35177, WSREP version 26.4.3)

Konfigurasi Percona XtraDB Cluster

  • Pastikan status mysql sudah stop pada semua node
systemctl stop mysql
systemctl stop [email protected]
  • Konfigurasi percona pada node-1
  • Masukan 1 pada bagian server-id
  • Pada bagian wsrep_cluster_address=gcomm:// biarkan kosong terlebih dahulu
  • Masukan IP address node-1 pada bagian wsrep_node_address
  • Masukan nama cluster pada bagian wsrep_cluster_name
  • Masukan nama node pada bagian wsrep_node_name
root@db01:~# nano /etc/mysql/mysql.conf.d/mysqld.cnf
# Template my.cnf for PXC
# Edit to your requirements.
[client]
socket=/var/run/mysqld/mysqld.sock

[mysqld]
server-id=1
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
log-error=/var/log/mysql/error.log
pid-file=/var/run/mysqld/mysqld.pid

# Binary log expiration period is 604800 seconds, which equals 7 days
binlog_expire_logs_seconds=604800

######## wsrep ###############
# Path to Galera library
wsrep_provider=/usr/lib/galera4/libgalera_smm.so

# Cluster connection URL contains IPs of nodes
#If no IP is found, this implies that a new cluster needs to be created,
#in order to do that you need to bootstrap this node
wsrep_cluster_address=gcomm://10.107.176.27,10.107.176.55

# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW

# Slave thread to use
wsrep_slave_threads=8

wsrep_log_conflicts

# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2

# Node IP address
wsrep_node_address=10.107.176.27

# Cluster name
wsrep_cluster_name=pxc-cluster

#If wsrep_node_name is not specified,  then system hostname will be used
wsrep_node_name=node-1

#pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER
pxc_strict_mode=ENFORCING

# SST method
wsrep_sst_method=xtrabackup-v2
  • Konfigurasi percona pada node-2
  • Masukan 2 pada bagian server-id
  • Pada bagian wsrep_cluster_address=gcomm:// biarkan kosong terlebih dahulu
  • Masukan IP address node-2 pada bagian wsrep_node_address
  • Masukan nama cluster pada bagian wsrep_cluster_name
  • Masukan nama node pada bagian wsrep_node_name
root@db02:~# nano /etc/mysql/mysql.conf.d/mysqld.cnf
# Template my.cnf for PXC
# Edit to your requirements.
[client]
socket=/var/run/mysqld/mysqld.sock

[mysqld]
server-id=2
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
log-error=/var/log/mysql/error.log
pid-file=/var/run/mysqld/mysqld.pid

# Binary log expiration period is 604800 seconds, which equals 7 days
binlog_expire_logs_seconds=604800

######## wsrep ###############
# Path to Galera library
wsrep_provider=/usr/lib/galera4/libgalera_smm.so

# Cluster connection URL contains IPs of nodes
#If no IP is found, this implies that a new cluster needs to be created,
#in order to do that you need to bootstrap this node
wsrep_cluster_address=gcomm://10.107.176.27,10.107.176.55

# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW

# Slave thread to use
wsrep_slave_threads=8

wsrep_log_conflicts

# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2

# Node IP address
wsrep_node_address=10.107.176.55

# Cluster name
wsrep_cluster_name=pxc-cluster

#If wsrep_node_name is not specified,  then system hostname will be used
wsrep_node_name=node-2

#pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER
pxc_strict_mode=ENFORCING

# SST method
wsrep_sst_method=xtrabackup-v2

Copy file .pem dari node-1 ke node-2

  • Buat directory ssl-mysql di dalam directory /root pada node-2
root@db02:~# mkdir -p /root/ssl-mysql
  • Copy file sertificate (.pem) yang ada di directory /var/lib/mysql dari node-1 ke node-2 di directory /root/ssl-mysql
root@db01:~# scp -r /var/lib/mysql/*.pem [email protected]:/root/ssl-mysql
[email protected]'s password:
ca-key.pem                                  100% 1680   739.8KB/s   00:00
ca.pem                                      100% 1120   704.9KB/s   00:00
client-cert.pem                             100% 1120   524.6KB/s   00:00
client-key.pem                              100% 1680   849.3KB/s   00:00
private_key.pem                             100% 1680   673.6KB/s   00:00
public_key.pem                              100%  452   392.5KB/s   00:00
server-cert.pem                             100% 1120   726.3KB/s   00:00
server-key.pem                              100% 1680     1.2MB/s   00:00
  • Copy directory /root/ssl-mysql di node-2 ke /var/lib/mysql
  • Move semua file .pem ke folder /var/lib/mysql dari folder /var/lib/mysql/ssl-mysql
  • Ubah semua permission file .pem
cp -r /root/ssl-mysql/ /var/lib/mysql
cd /var/lib/mysql && mv -f ssl-mysql/*.pem .
chown mysql:mysql *.pem
  • Jalankan service mysql pada node-1 dan node-2
  • Pastikan serive mysql berjalan pada semua node, jika sudah berjalan stop kembali service mysql pada semua node
root@db01:/home/admin# systemctl start mysql
root@db01:/home/admin# systemctl status mysql
root@db01:/home/admin# systemctl stop mysql

root@db02:/home/admin# systemctl start mysql
root@db02:/home/admin# systemctl status mysql
root@db02:/home/admin# systemctl stop mysql

  • Edit file /etc/mysql/mysql.conf.d/mysqld.cnf pada semua node
  • Tambahkan IP semua node pada bagian wsrep_cluster_address=gcomm://
  • Ketika pertama kali akan menjalan cluster, jalankan service [email protected] pada node-1, pastikan berjalan tanpa error
root@db01:~# systemctl start [email protected]
root@db01:~# systemctl status [email protected][email protected] - Percona XtraDB Cluster with config /etc/default/mysql.bootstrap
     Loaded: loaded (/lib/systemd/system/[email protected]; disabled; vendor preset: enabled)
     Active: active (running) since Wed 2022-06-29 12:47:39 UTC; 7min ago
    Process: 43218 ExecStartPre=/usr/bin/mysql-systemd start-pre (code=exited, status=0/SUCCESS)
    Process: 43270 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
    Process: 43272 ExecStartPre=/bin/sh -c VAR=`bash /usr/bin/mysql-systemd galera-recovery`; [ $? -eq 0 ] && systemctl set-environme>
    Process: 43376 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
    Process: 43378 ExecStartPost=/usr/bin/mysql-systemd start-post $MAINPID (code=exited, status=0/SUCCESS)
   Main PID: 43320 (mysqld)
     Status: "Server is operational"
      Tasks: 52 (limit: 2338)
     Memory: 408.9M
     CGroup: /system.slice/system-mysql.slice/[email protected]
             └─43320 /usr/sbin/mysqld --wsrep-new-cluster --wsrep_start_position=baed8648-f7a2-11ec-9f0b-ce0d4304523e:2

Jun 29 12:47:35 db01 systemd[1]: Starting Percona XtraDB Cluster with config /etc/default/mysql.bootstrap...
Jun 29 12:47:39 db01 mysql-systemd[43378]:  SUCCESS!
Jun 29 12:47:39 db01 systemd[1]: Started Percona XtraDB Cluster with config /etc/default/mysql.bootstrap.
  • Pada node-2 untuk join cluster, cukup jalankan service mysql, pastikan berjalan dan tidak ada error
root@db02:~# systemctl start mysql 
root@db02:~# systemctl status mysql

● mysql.service - Percona XtraDB Cluster
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: active (running) since Wed 2022-06-29 12:56:25 UTC; 29s ago
    Process: 44137 ExecStartPre=/usr/bin/mysql-systemd start-pre (code=exited, status=0/SUCCESS)
    Process: 44186 ExecStartPre=/usr/bin/mysql-systemd check-grastate (code=exited, status=0/SUCCESS)
    Process: 44215 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
    Process: 44217 ExecStartPre=/bin/sh -c VAR=`bash /usr/bin/mysql-systemd galera-recovery`; [ $? -eq 0 ] && systemctl set-environme>
    Process: 44873 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
    Process: 44875 ExecStartPost=/usr/bin/mysql-systemd start-post $MAINPID (code=exited, status=0/SUCCESS)
   Main PID: 44265 (mysqld)
     Status: "Server is operational"
      Tasks: 51 (limit: 2338)
     Memory: 404.8M
     CGroup: /system.slice/mysql.service
             └─44265 /usr/sbin/mysqld --wsrep_start_position=baed8648-f7a2-11ec-9f0b-ce0d4304523e:4

Jun 29 12:56:15 db02 systemd[1]: Starting Percona XtraDB Cluster...
Jun 29 12:56:25 db02 mysql-systemd[44875]:  SUCCESS!
Jun 29 12:56:25 db02 systemd[1]: Started Percona XtraDB Cluster.
root@db01:~# systemctl stop [email protected]
root@db01:~# systemctl start mysql 
root@db01:~# systemctl status mysql

● mysql.service - Percona XtraDB Cluster
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: active (running) since Wed 2022-06-29 12:56:25 UTC; 29s ago
    Process: 44137 ExecStartPre=/usr/bin/mysql-systemd start-pre (code=exited, status=0/SUCCESS)
    Process: 44186 ExecStartPre=/usr/bin/mysql-systemd check-grastate (code=exited, status=0/SUCCESS)
    Process: 44215 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
    Process: 44217 ExecStartPre=/bin/sh -c VAR=`bash /usr/bin/mysql-systemd galera-recovery`; [ $? -eq 0 ] && systemctl set-environme>
    Process: 44873 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
    Process: 44875 ExecStartPost=/usr/bin/mysql-systemd start-post $MAINPID (code=exited, status=0/SUCCESS)
   Main PID: 44265 (mysqld)
     Status: "Server is operational"
      Tasks: 51 (limit: 2338)
     Memory: 404.8M
     CGroup: /system.slice/mysql.service
             └─44265 /usr/sbin/mysqld --wsrep_start_position=baed8648-f7a2-11ec-9f0b-ce0d4304523e:4

Jun 29 12:56:15 db01 systemd[1]: Starting Percona XtraDB Cluster...
Jun 29 12:56:25 db01 mysql-systemd[44875]:  SUCCESS!
Jun 29 12:56:25 db01 systemd[1]: Started Percona XtraDB Cluster.
  • Percona XtraDB Cluster berhasil berjalan dengan baik pada semua node

Pengujian

  • Cek status cluster pada salah satu node, pastikan pada bagian wsrep_cluster_size bernilai 2 , karena hanya ada 2 node
root@db01:~# mysql -u root -p

mysql> show status like 'wsrep_cluster%';
+----------------------------+--------------------------------------+
| Variable_name              | Value                                |
+----------------------------+--------------------------------------+
| wsrep_cluster_weight       | 2                                    |
| wsrep_cluster_capabilities |                                      |
| wsrep_cluster_conf_id      | 5                                    |
| wsrep_cluster_size         | 2                                    |
| wsrep_cluster_state_uuid   | baed8648-f7a2-11ec-9f0b-ce0d4304523e |
| wsrep_cluster_status       | Primary                              |
+----------------------------+--------------------------------------+

mysql> show status like 'wsrep_con%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| wsrep_connected | ON    |
+-----------------+-------+

mysql> show status like 'wsrep_ready';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wsrep_ready   | ON    |
+---------------+-------+
  • Masuk ke node-1, lalu buat database dengan nama percona dan table dengan nama sysops, lalu masukan data pada table tersebut
root@db01:~# mysql -u root -p

mysql> CREATE DATABASE percona;
mysql> USE percona;
mysql> CREATE TABLE sysops (node_id INT PRIMARY KEY, node_name VARCHAR(30));
mysql> INSERT INTO percona.sysops VALUES (1, 'node-1');
mysql> INSERT INTO percona.sysops VALUES (2, 'node-2');
mysql> SELECT * FROM percona.sysops;
+---------+-----------+
| node_id | node_name |
+---------+-----------+
|       1 | node-1    |
|       2 | node-2    |
+---------+-----------+
  • Masuk ke node-2, cek database dan cek table
root@db02:~# mysql -u root -p

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| percona            |
| performance_schema |
| sys                |
+--------------------+

mysql> SELECT * FROM percona.sysops;
+---------+-----------+
| node_id | node_name |
+---------+-----------+
|       1 | node-1    |
|       2 | node-2    |
+---------+-----------+

herdiana3389

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