Hands on Maxscale and Galera Cluster Centos7

MariaDB MaxScale is a database proxy that extends the high availability, scalability, and security of MariaDB Server while at the same time simplifying application development by decoupling it from underlying database infrastructure.

First Create 4 Nodes of Centminmod Installation

Node1:192.168.1.1 (CMM Installed) (MariaDB Cluster 1)
Node2:192.168.1.2 (CMM Installed) (MariaDB Cluster 2)
Node3:192.168.1.3 (CMM Installed) (MariaDB Cluster 3)
Node4:192.168.1.4 (CMM Installed) (Maxscale)

Open These ports 4567,4568,4444,3306 under TCP in your firewall (CSF or IPTABLES) on All MariaDB Cluster nodes.


On 192.168.1.1 (CMM Installed) (MariaDB Cluster 1)

Edit my.cnf file and at the end add lines below

Must change
Replace wsrep_node_address=”192.168.1.1″ with your node1 IP address.
Replace wsrep_cluster_address=gcomm://192.168.1.1,192.168.1.2,192.168.1.3 with your node1,node2,node3 IP respectively.


Alternatively you can also

Replace wsrep_cluster_name=”bullten” as you want but do remember it should be same on all nodes.
Replace wsrep_node_name=”node1″ to any name you want.

vi /etc/my.cnf
 [galera]
 # Mandatory settings
 wsrep_on=ON
 wsrep_cluster_name="bullten"
 wsrep_node_name="node1"
 wsrep_node_address="192.168.1.1"
 #wsrep_provider_options='pc.recovery=ON'
 wsrep_provider=/usr/lib64/galera/libgalera_smm.so
 wsrep_cluster_address=gcomm://192.168.1.1,192.168.1.2,192.168.1.3
 binlog_format=row
 innodb_autoinc_lock_mode=2

Stop MariaDB

systemctl stop mariadb

Start Galera Cluster

galera_new_cluster

Change the permission

chown -R mysql:mysql /var/lib/mysql

On 192.168.1.2 (CMM Installed) (MariaDB Cluster 2)

Edit my.cnf file and at the end add lines below

Must change
Replace wsrep_node_address=”192.168.1.2″ with your node2 IP address.
Replace wsrep_cluster_address=gcomm://192.168.1.1,192.168.1.2,192.168.1.3 with your node1,node2,node3 IP respectively.


Alternatively you can also

Replace wsrep_node_name=”node2″ to any name you want.

vi /etc/my.cnf
[galera]
# Mandatory settings
wsrep_on=ON
wsrep_cluster_name="bullten"
wsrep_node_name="node2"
wsrep_node_address="192.168.1.2"
#wsrep_provider_options='pc.recovery=ON'
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.1.1,192.168.1.2,192.168.1.3
binlog_format=row
innodb_autoinc_lock_mode=2 

Change the permission

chown -R mysql:mysql /var/lib/mysql

Stop MariaDB

systemctl stop mariadb

Start MariaDB

systemctl start mariadb

On 192.168.1.3 (CMM Installed) (MariaDB Cluster 3)

Edit my.cnf file and at the end add lines below

Must change
Replace wsrep_node_address=”192.168.1.3″ with your node3 IP address.
Replace wsrep_cluster_address=gcomm://192.168.1.1,192.168.1.2,192.168.1.3 with your node1,node2,node3 IP respectively.


Alternatively you can also

Replace wsrep_node_name=”node3″ to any name you want.

vi /etc/my.cnf
[galera]
 # Mandatory settings
 wsrep_on=ON
 wsrep_cluster_name="bullten"
 wsrep_node_name="node3"
 wsrep_node_address="192.168.1.3"
 #wsrep_provider_options='pc.recovery=ON'
 wsrep_provider=/usr/lib64/galera/libgalera_smm.so
 wsrep_cluster_address=gcomm://192.168.1.1,192.168.1.2,192.168.1.3
 binlog_format=row
 innodb_autoinc_lock_mode=2

Change the permission

chown -R mysql:mysql /var/lib/mysql

Stop MariaDB

systemctl stop mariadb

Start MariaDB

systemctl start mariadb

Now your Node is connected to check cluster size run the below command on any MariaDB node
Code:

SHOW GLOBAL STATUS LIKE 'wsrep_cluster_size';

wsrep_cluster_size = 3 shows three nodes are inter connected.

On 192.168.1.4 (CMM Installed) Install (Maxscale)

Install Maxscale

curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash
yum -y install maxscale

On one of the MariaDB node run the below commands. Replace 192.168.1.4 with your maxscale node IP.

create user 'maxscale_user'@'192.168.1.4' identified by 'maxscale_password';

grant select on mysql.user to 'maxscale_user'@'192.168.1.4';

grant select on mysql.roles_mapping to 'maxscale_user'@'192.168.1.4';

grant select on mysql.db to 'maxscale_user'@'192.168.1.4';

grant select on mysql.tables_priv to 'maxscale_user'@'192.168.1.4';

grant show databases on *.* to 'maxscale_user'@'192.168.1.4';

Generate and save the encrypted password

maxkeys
maxpasswd maxscale_password

Save Original configuration file of Maxscale

mv /etc/maxscale.cnf /etc/maxscale.cnf.old

Create new maxscale.cnf file and add below entries.
password=9D797E4DAB257039C167186BC2527463DB5878F85E543A3A418EDA8ED447344B is the encrypted password we created above i.e maxscale_password.
module=galeramon describes galera cluster monitoring
port=5492 Port at which remotely we will connect.

vi /etc/maxscale.cnf
#Globals
[maxscale]
threads=1

#Servers
[server1]
type=server
address=192.168.1.1
port=3306
protocol=MariaDBBackend
priority=0

[server2]
type=server
address=192.168.1.2
port=3306
protocol=MariaDBBackend
priority=1

[server3]
type=server
address=192.168.1.3
port=3306
protocol=MariaDBBackend
priority=2

#Monitoring for the servers
[Galera-Monitor]
type=monitor
module=galeramon
servers=server1,server2,server3
user=maxscale_user
password=9D797E4DAB257039C167186BC2527463DB5878F85E543A3A418EDA8ED447344B
monitor_interval=1000

#Galera router service
[Galera-Service]
type=service
router=readwritesplit
servers=server1,server2,server3
user=maxscale_user
password=9D797E4DAB257039C167186BC2527463DB5878F85E543A3A418EDA8ED447344B

#Galera cluster listener
[Galera-Listener]
type=listener
service=Galera-Service
protocol=MariaDBClient
address=0.0.0.0
port=5492

verify configuration file

maxscale -c -U maxscale
2019-12-20 02:37:01   notice : Configuration file: /etc/maxscale.cnf
2019-12-20 02:37:01   notice : Log directory: /var/log/maxscale
2019-12-20 02:37:01   notice : Data directory: /var/lib/maxscale
2019-12-20 02:37:01   notice : Module directory: /usr/lib64/maxscale
2019-12-20 02:37:01   notice : Service cache: /var/cache/maxscale
2019-12-20 02:37:01   notice : Worker message queue size: 1.00MiB
2019-12-20 02:37:01   notice : No query classifier specified, using default 'qc_sqlite'.
2019-12-20 02:37:01   notice : Loaded module qc_sqlite: V1.0.0 from /usr/lib64/maxscale/libqc_sqlite.so
2019-12-20 02:37:01   notice : Query classification results are cached and reused. Memory used per thread: 275.67MiB
2019-12-20 02:37:01   notice : Loading /etc/maxscale.cnf.
2019-12-20 02:37:01   notice : /etc/maxscale.cnf.d does not exist, not reading.
2019-12-20 02:37:01   notice : Loaded module MariaDBClient: V1.1.0 from /usr/lib64/maxscale/libmariadbclient.so
2019-12-20 02:37:01   notice : [readwritesplit] Initializing statement-based read/write split router module.
2019-12-20 02:37:01   notice : Loaded module readwritesplit: V1.1.0 from /usr/lib64/maxscale/libreadwritesplit.so
2019-12-20 02:37:01   notice : [galeramon] Initialise the MySQL Galera Monitor module.
2019-12-20 02:37:01   notice : Loaded module galeramon: V2.0.0 from /usr/lib64/maxscale/libgaleramon.so
2019-12-20 02:37:01   notice : Loaded module MariaDBBackend: V2.0.0 from /usr/lib64/maxscale/libmariadbbackend.so
2019-12-20 02:37:01   notice : Loaded module mariadbbackendauth: V1.0.0 from /usr/lib64/maxscale/libmariadbbackendauth.so
2019-12-20 02:37:01   notice : Loaded module mariadbauth: V1.1.0 from /usr/lib64/maxscale/libmariadbauth.so
2019-12-20 02:37:01   notice : Configuration was successfully verified.

Start and enable Maxscale service

systemctl start maxscale.service
systemctl enable maxscale.service

On Maxscale node enter into mysql and check cluster. After running the below command stop any node of MariaDB and check if hostname values changes then. Change 192.168.1.4 with your maxscal node IP.

mysql -h 192.68.1.4 -P 5492 -u maxscale_user -pmaxscale_password
show variables like 'hostname';

Now lets try some Maxscale commands using maxctrl

maxctrl list servers
maxctrl show servers
maxctrl list modules
maxctrl list services
maxctrl list threads

Take Server Out for Maintenance.

maxctrl set server server1 maintenance

Set server back online after maintenance

maxctrl clear server server1 maintenance
maxctrl show threads --tsv
Id      0
Accepts 3
Reads   61
Writes  54
Hangups 2
Errors  0
Avg event queue length  1
Max event queue length  2
Max exec time   0
Max queue time  0
Current FDs     3
Total FDs       14
Load (1s)       0
Load (1m)       0
Load (1h)       0
QC cache size   53
QC cache inserts        2
QC cache hits   8
QC cache misses 2
QC cache evictions      0

Related posts