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=2Stop 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=2Change 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 maxscaleOn 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.oldCreate 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=5492verify configuration file
maxscale -c -U maxscale2019-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.serviceOn 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 --tsvId 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