Load Balancing MariaDB Galera Cluster with ProxySQL

Load Balancing MariaDB Galera Cluster dengan ProxySQL 2.4

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

MariaDB Galera Cluster

  • db01 : 192.168.88.11
  • db02 : 192.168.88.12
  • db03 : 192.168.88.13
  • proxysql: 192.168.88.14
  • web : 192.168.88.15

Install ProxySQL 2.4

  • Pastikan mariadb galera cluster sudah di settinga dan berjalan tanpa error
  • Update repository index server proxysql
apt update && apt upgrade -y
wget https://repo.proxysql.com/ProxySQL/proxysql-2.4.x/focal/proxysql_2.4.2-ubuntu20_amd64.deb
dpkg -i proxysql_*

List package :

### for Ubuntu 20.04
https://repo.proxysql.com/ProxySQL/proxysql-2.4.x/focal/proxysql_2.4.2-ubuntu20_amd64.deb

### for Ubuntu 22.04
https://repo.proxysql.com/ProxySQL/proxysql-2.4.x/jammy/proxysql_2.4.2-ubuntu22_amd64.deb

### for CentOS 7
https://repo.proxysql.com/ProxySQL/proxysql-2.4.x/centos/7/proxysql-2.4.2-1-centos7.x86_64.rpm

### for AlmaLinux 8
https://repo.proxysql.com/ProxySQL/proxysql-2.4.x/almalinux/8/proxysql-2.4.2-1-almalinux8.x86_64.rpm
  • Jalankan service proxysql
systemctl enable --now proxysql
netstat -tulpn | grep proxysql
  tcp        0      0 0.0.0.0:6032            0.0.0.0:*               LISTEN      3764/proxysql
  tcp        0      0 0.0.0.0:6033            0.0.0.0:*               LISTEN      3764/proxysql
  tcp        0      0 0.0.0.0:6033            0.0.0.0:*               LISTEN      3764/proxysql
  tcp        0      0 0.0.0.0:6033            0.0.0.0:*               LISTEN      3764/proxysql
  tcp        0      0 0.0.0.0:6033            0.0.0.0:*               LISTEN      3764/proxysql

Konfigurasi ProxySQL

  • Secara default user dan password proxysql menggunakan admin/admin dengan port 6032
  • Akses ke dalam proxysql
root@proxySQL:~# mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Admin> 
  • Ganti password admin proxysql
Admin> UPDATE global_variables SET variable_value='admin:P@ssw0rd!' WHERE variable_name='admin-admin_credentials';
  • Simpan konfigurasi
Admin> LOAD ADMIN VARIABLES TO RUNTIME;
Admin> SAVE ADMIN VARIABLES TO DISK;

  • Masuk ke server db01 lalu buat user dengan nama proxysql untuk komunikasi antara Galera Cluster dengan ProxySQL
MariaDB [(none)]> CREATE USER 'proxysql'@'%' IDENTIFIED BY 'P@ssw0rd!';
MariaDB [(none)]> flush privileges;
  • Konfigurasi monitoring galera cluster pada proxysql
Admin> UPDATE global_variables SET variable_value='proxysql' WHERE variable_name='mysql-monitor_username';
Admin> UPDATE global_variables SET variable_value='P@ssw0rd!' WHERE variable_name='mysql-monitor_password';
Admin> UPDATE global_variables SET variable_value='2000' WHERE variable_name IN ('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval');
  • Verifikasi konfigurasi monitoring galera cluster pada proxysql
Admin> SELECT * FROM global_variables WHERE variable_name LIKE 'mysql-monitor_%';
+----------------------------------------------------------------------+----------------+
| variable_name                                                        | variable_value |
+----------------------------------------------------------------------+----------------+
| mysql-monitor_enabled                                                | true           |
| mysql-monitor_connect_timeout                                        | 600            |
| mysql-monitor_ping_max_failures                                      | 3              |
| mysql-monitor_ping_timeout                                           | 1000           |
| mysql-monitor_read_only_max_timeout_count                            | 3              |
| mysql-monitor_replication_lag_group_by_host                          | false          |
| mysql-monitor_replication_lag_interval                               | 10000          |
| mysql-monitor_replication_lag_timeout                                | 1000           |
| mysql-monitor_replication_lag_count                                  | 1              |
| mysql-monitor_groupreplication_healthcheck_interval                  | 5000           |
| mysql-monitor_groupreplication_healthcheck_timeout                   | 800            |
| mysql-monitor_groupreplication_healthcheck_max_timeout_count         | 3              |
| mysql-monitor_groupreplication_max_transactions_behind_count         | 3              |
| mysql-monitor_groupreplication_max_transactions_behind_for_read_only | 1              |
| mysql-monitor_galera_healthcheck_interval                            | 5000           |
| mysql-monitor_galera_healthcheck_timeout                             | 800            |
| mysql-monitor_galera_healthcheck_max_timeout_count                   | 3              |
| mysql-monitor_replication_lag_use_percona_heartbeat                  |                |
| mysql-monitor_query_interval                                         | 60000          |
| mysql-monitor_query_timeout                                          | 100            |
| mysql-monitor_slave_lag_when_null                                    | 60             |
| mysql-monitor_threads_min                                            | 8              |
| mysql-monitor_threads_max                                            | 128            |
| mysql-monitor_threads_queue_maxsize                                  | 128            |
| mysql-monitor_wait_timeout                                           | true           |
| mysql-monitor_writer_is_also_reader                                  | true           |
| mysql-monitor_username                                               | proxysql       |
| mysql-monitor_password                                               | P@ssw0rd!      |
| mysql-monitor_history                                                | 600000         |
| mysql-monitor_connect_interval                                       | 2000           |
| mysql-monitor_ping_interval                                          | 2000           |
| mysql-monitor_read_only_interval                                     | 2000           |
| mysql-monitor_read_only_timeout                                      | 500            |
+----------------------------------------------------------------------+----------------+
  • Simpan konfigurasi
Admin> LOAD MYSQL VARIABLES TO RUNTIME;
Admin> SAVE MYSQL VARIABLES TO DISK;

  • Konfigurasi table mysql_replication_hostgroup untuk reader dan writer pada proxysql
Admin> INSERT INTO main.mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,comment) VALUES (1,2,'galera_cluster');
  • Tambahkan node-node galera cluster pada proxysql
Admin> INSERT INTO main.mysql_servers(hostgroup_id,hostname,port) VALUES (1,'192.168.88.11',3306);
Admin> INSERT INTO main.mysql_servers(hostgroup_id,hostname,port) VALUES (1,'192.168.88.12',3306);
Admin> INSERT INTO main.mysql_servers(hostgroup_id,hostname,port) VALUES (1,'192.168.88.13',3306);
  • Simpan konfigurasi
Admin> LOAD MYSQL VARIABLES TO RUNTIME;
Admin> SAVE MYSQL VARIABLES TO DISK;
  • Loads MySQL servers dan replikasi hostgroups dari dalam memory database ke runtime data structures
Admin> LOAD MYSQL SERVERS TO RUNTIME;
  • Verifikasi node galera cluster pada proxysql pastikan sudah terhubung
Admin> SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 3;
+---------------+------+------------------+-------------------------+---------------+
| hostname      | port | time_start_us    | connect_success_time_us | connect_error |
+---------------+------+------------------+-------------------------+---------------+
| 192.168.88.12 | 3306 | 1657606218118632 | 4984                    | NULL          |
| 192.168.88.11 | 3306 | 1657606218093520 | 4957                    | NULL          |
| 192.168.88.13 | 3306 | 1657606218071099 | 4377                    | NULL          |
+---------------+------+------------------+-------------------------+---------------+

Admin> SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 3;
+---------------+------+------------------+----------------------+------------+
| hostname      | port | time_start_us    | ping_success_time_us | ping_error |
+---------------+------+------------------+----------------------+------------+
| 192.168.88.11 | 3306 | 1657606244346263 | 1948                 | NULL       |
| 192.168.88.12 | 3306 | 1657606244330004 | 1598                 | NULL       |
| 192.168.88.13 | 3306 | 1657606244314059 | 1629                 | NULL       |
+---------------+------+------------------+----------------------+------------+

Remote User Database

  • Masuk ke server db01 lalu buat user dengan nama sysops untuk pengujian remote load balancing
MariaDB [(none)]> create user 'sysops'@'%' identified by 'P@ssw0rd!';
MariaDB [(none)]> grant all privileges on testdb.* to 'sysops'@'%' with grant option;
MariaDB [(none)]> flush privileges;
  • Tambahkan remote user pada proxysql yang barusan sudah dibuat di server db01
Admin> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('sysops','P@ssw0rd!',1);
  • Verifiaksi remote user
Admin> SELECT * FROM mysql_users;
+----------+-----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+
| username | password  | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | attributes | comment |
+----------+-----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+
| sysops   | P@ssw0rd! | 1      | 0       | 1                 | NULL           | 0             | 1                      | 0            | 1       | 1        | 10000           |            |         |
+----------+-----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+
  • Simpan konfigurasi
Admin> LOAD MYSQL USERS TO RUNTIME;
Admin> SAVE MYSQL USERS TO DISK;

Pengujian

  • Masuk ke proxysql client pada server proxysql dengan user sysops lalu cek hostname
root@proxySQL:~# mysql -u sysops -h 127.0.0.1 -P6033 -pP@ssw0rd!
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.5.30 (ProxySQL)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> show variables like 'hostname';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| hostname      | db03  |
+---------------+-------+

MySQL [(none)]> show variables like 'hostname';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| hostname      | db02  |
+---------------+-------+

MySQL [(none)]> show variables like 'hostname';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| hostname      | db01  |
+---------------+-------+
  • Cek status node galera cluster, pastikan status semua node ONLINE
root@proxySQL:~# mysql -u admin -pP@ssw0rd! -h 127.0.0.1 -P6032 --prompt='Admin> '
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Admin> SELECT hostgroup,srv_host,status FROM stats.stats_mysql_connection_pool;
+-----------+---------------+--------+
| hostgroup | srv_host      | status |
+-----------+---------------+--------+
| 1         | 192.168.88.11 | ONLINE |
| 1         | 192.168.88.12 | ONLINE |
| 1         | 192.168.88.13 | ONLINE |
+-----------+---------------+--------+

Admin> SELECT * FROM mysql_servers;
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname      | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1            | 192.168.88.11 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 1            | 192.168.88.12 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 1            | 192.168.88.13 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
  • Matikan/stop service mariadb pada server db03 lalu cek status node galera cluster
  • Status server db03 akan berubah dari ONLINE menjadi SHUNNED
Admin> SELECT hostgroup,srv_host,status FROM stats.stats_mysql_connection_pool;
+-----------+---------------+---------+
| hostgroup | srv_host      | status  |
+-----------+---------------+---------+
| 1         | 192.168.88.11 | ONLINE  |
| 1         | 192.168.88.12 | ONLINE  |
| 1         | 192.168.88.13 | SHUNNED |
+-----------+---------------+---------+
  • Monitoring ProxySQL secara realtime
root@proxySQL:~# watch -n 1 'mysql -h 127.0.0.1 -P 6032 -uadmin -pP@ssw0rd! -t -e "select * from stats_mysql_connection_pool where hostgroup in (1,2) order by hostgroup,srv_host ;" -e " select hostgroup_id,hostname,status,weight,comment from mysql_servers where hostgroup_id in (1,2) order by hostgroup_id,hostname ;" -e "SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 3;"'

  • Tambahkan database dengan nama sys_ops_id pada server db01
MariaDB [(none)]> CREATE DATABASE sys_ops_id;
MariaDB [(none)]> CREATE USER 'user_sys_ops'@'localhost' IDENTIFIED BY 'pass_sys_ops';
MariaDB [(none)]> GRANT ALL PRIVILEGES ON sys_ops_id.* TO 'user_sys_ops'@'192.168.88.15' IDENTIFIED BY 'pass_sys_ops';
MariaDB [(none)]> FLUSH PRIVILEGES;
  • Tambahkan remote user (user_sys_ops) pada server proxysql yang barusan sudah dibuat di server db01
Admin> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('user_sys_ops','pass_sys_ops',2);

Admin> SELECT * FROM mysql_users;
+--------------+--------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+
| username     | password     | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | attributes | comment |
+--------------+--------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+
| sysops       | P@ssw0rd!    | 1      | 0       | 1                 | NULL           | 0             | 1                      | 0            | 1       | 1        | 10000           |            |         |
| user_sys_ops | pass_sys_ops | 1      | 0       | 2                 | NULL           | 0             | 1                      | 0            | 1       | 1        | 10000           |            |         |
+--------------+--------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+

Admin> LOAD MYSQL USERS TO RUNTIME;
Admin> SAVE MYSQL USERS TO DISK;
  • Install apache2 dan php 8.0 pada server app
apt update && apt install apache2 -y
apt install software-properties-common -y && add-apt-repository ppa:ondrej/php && apt update
apt install php8.0 -y
apt install -y php8.0-{common,cli,gd,curl,mysql,xml,mbstring,zip,ldap,xmlrpc,curl,intl,fpm,imagick,dev,imap,opcache,soap,memcached,redis}
systemctl restart apache2
  • Buat file koneksi.php di directory /var/www/html
<?php
// Koneksi Database
$konek = mysqli_connect("192.168.88.14:6033", "user_sys_ops", "pass_sys_ops", "sys_ops_id");

if (!$konek) {
    echo "Error: Tidak dapat terhubung ke database." . PHP_EOL;
    echo "Debugging errno: " . mysqli_connect_errno() . PHP_EOL;
    echo "Debugging error: " . mysqli_connect_error() . PHP_EOL;
    exit;
}
echo "Sukses: Koneksi ke database berhasil terjalin." . PHP_EOL;
echo "Informasi Host: " . mysqli_get_host_info($link) . PHP_EOL;
mysqli_close($link);
?>
  • Akses file koneksi.php lewat browser dengan url: http://ip_server_app/koneksi.php (http://192.168.88.15/koneksi.php) atau bisa juga menggunakan terminal dengan curl
root@web:~# curl http://192.168.88.15/koneksi.php
Sukses: Koneksi ke database berhasil terjalin.

herdiana3389

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