|
Folks,
I added configurations to a Maxscale 2.2.7 to form a pool of slaves which replicates from MariaDB Cluster nodes. This monitor has three slaves while the router_options=slave.
The goal here is to have a specific port to send application's reports that will be load balanced among those three database servers:
[Slaves-Read-Pool-Service]
|
type=service
|
router=readconnroute
|
router_options=slave
|
servers=async01,async02,async03
|
version_string=5.5.50-MariaDB
|
user=maxusr
|
password=2C894F278CEC5B974966EC969E006E37
|
|
[Slaves-Read-Pool-Monitor]
|
type=monitor
|
module=mariadbmon
|
servers=async01,async02,async03
|
user=maxusr
|
password=2C894F278CEC5B974966EC969E006E37
|
monitor_interval=2000
|
journal_max_age=28800
|
script_timeout=90
|
detect_stale_master=false
|
|
[Slaves-Pool-Read-Listener]
|
type=listener
|
service=Slaves-Read-Pool-Service
|
protocol=MariaDBClient
|
port=3308
|
|
[async01]
|
type=server
|
address=165.227.225.138
|
port=3306
|
protocol=MariaDBBackend
|
|
[async02]
|
type=server
|
address=138.68.158.166
|
port=3306
|
protocol=MariaDBBackend
|
|
[async03]
|
type=server
|
address=165.227.225.121
|
port=3306
|
protocol=MariaDBBackend
|
|
When I list servers out of maxctrl:
[root@mxs01 ~]# maxscale --version
|
MaxScale 2.2.7
|
[root@mxs01 ~]# maxctrl list servers
|
┌─────────┬─────────────────┬──────┬─────────────┬─────────────────────────┬──────┐
|
│ Server │ Address │ Port │ Connections │ State │ GTID │
|
├─────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────┤
|
│ n01 │ 10.136.85.157 │ 3306 │ 0 │ Slave, Synced, Running │ │
|
├─────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────┤
|
│ n02 │ 10.136.87.40 │ 3306 │ 0 │ Master, Synced, Running │ │
|
├─────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────┤
|
│ n03 │ 10.136.80.203 │ 3306 │ 0 │ Slave, Synced, Running │ │
|
├─────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────┤
|
│ n04 │ 10.136.83.198 │ 3306 │ 0 │ Slave, Synced, Running │ │
|
├─────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────┤
|
│ n05 │ 10.136.86.152 │ 3306 │ 0 │ Slave, Synced, Running │ │
|
├─────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────┤
|
│ async01 │ 165.227.225.138 │ 3306 │ 0 │ Slave, Running │ │
|
├─────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────┤
|
│ async02 │ 138.68.158.166 │ 3306 │ 0 │ Slave, Running │ │
|
├─────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────┤
|
│ async03 │ 165.227.225.121 │ 3306 │ 0 │ Slave, Running │ │
|
└─────────┴─────────────────┴──────┴─────────────┴─────────────────────────┴──────┘
|
Adding masters with different names and changing the configurations, it shows the GTIDs:
[Slaves-Read-Pool-Service]
|
type=service
|
router=readconnroute
|
router_options=slave
|
servers=m03,m04,m05,async01,async02,async03
|
version_string=5.5.50-MariaDB
|
user=maxusr
|
password=2C894F278CEC5B974966EC969E006E37
|
|
[Slaves-Read-Pool-Monitor]
|
type=monitor
|
module=mariadbmon
|
servers=m03,m04,m05,async01,async02,async03
|
user=maxusr
|
password=2C894F278CEC5B974966EC969E006E37
|
monitor_interval=2000
|
journal_max_age=28800
|
script_timeout=90
|
detect_stale_master=false
|
#detect_replication_lag=true
|
|
[Slaves-Pool-Read-Listener]
|
type=listener
|
service=Slaves-Read-Pool-Service
|
protocol=MariaDBClient
|
port=3308
|
|
[m03]
|
type=server
|
protocol=mariadbbackend
|
address=204.48.16.193
|
port=3306
|
authenticator=MySQLBackendAuth
|
|
[m04]
|
type=server
|
protocol=mariadbbackend
|
address=204.48.31.170
|
port=3306
|
authenticator=MySQLBackendAuth
|
|
[m05]
|
type=server
|
protocol=mariadbbackend
|
address=204.48.31.235
|
port=3306
|
authenticator=MySQLBackendAuth
|
|
[async01]
|
type=server
|
address=165.227.225.138
|
port=3306
|
protocol=MariaDBBackend
|
|
[async02]
|
type=server
|
address=138.68.158.166
|
port=3306
|
protocol=MariaDBBackend
|
|
[async03]
|
type=server
|
address=165.227.225.121
|
port=3306
|
protocol=MariaDBBackend
|
Show monitors'show servers:
[root@mxs01 ~]# maxadmin show monitors
|
Monitor: 0x15bc400
|
Name: cluster-monitor
|
State: Running
|
Sampling interval: 1000 milliseconds
|
Connect Timeout: 3 seconds
|
Read Timeout: 1 seconds
|
Write Timeout: 2 seconds
|
Connect attempts: 1
|
Monitored servers: [10.136.85.157]:3306, [10.136.87.40]:3306, [10.136.80.203]:3306, [10.136.83.198]:3306, [10.136.86.152]:3306
|
Master Failback: on
|
Available when Donor: off
|
Master Role Setting Disabled: off
|
Use Priorities: off
|
Set wsrep_sst_donor node list: off
|
Galera Cluster UUID: 1b71282b-59de-11e8-873f-5ef1a7ab8899
|
Galera Cluster size: 5
|
|
Monitor: 0x15b91c0
|
Name: Slaves-Read-Pool-Monitor
|
State: Running
|
Sampling interval: 2000 milliseconds
|
Connect Timeout: 3 seconds
|
Read Timeout: 1 seconds
|
Write Timeout: 2 seconds
|
Connect attempts: 1
|
Monitored servers: [204.48.16.193]:3306, [204.48.31.170]:3306, [204.48.31.235]:3306, [165.227.225.138]:3306, [138.68.158.166]:3306, [165.227.225.121]:3306
|
Automatic failover: Disabled
|
Failcount: 5
|
Failover timeout: 90
|
Switchover timeout: 90
|
Automatic rejoin: Disabled
|
Enforce read-only: Disabled
|
MaxScale monitor ID: 0
|
Detect replication lag: Disabled
|
Detect stale master: Disabled
|
|
Server information:
|
-------------------
|
|
Server: m03
|
Server ID: 3
|
Read only: NO
|
Slave configured: NO
|
Gtid current position: 100-3-403918
|
Gtid binlog position: 100-3-403918
|
|
Server: m04
|
Server ID: 4
|
Read only: NO
|
Slave configured: NO
|
Gtid binlog position:
|
|
Server: m05
|
Server ID: 5
|
Read only: NO
|
Slave configured: NO
|
Gtid binlog position:
|
|
Server: async01
|
Server ID: 100
|
Read only: NO
|
Slave configured: YES
|
Slave IO running: YES
|
Slave SQL running: YES
|
Master ID: 3
|
Master binlog file: mariadb-bin.000047
|
Master binlog position: 392035512
|
Gtid current position: 100-3-403918
|
Gtid binlog position: 100-3-403918
|
Gtid slave IO position: 100-3-403918
|
|
Server: async02
|
Server ID: 200
|
Read only: NO
|
Slave configured: YES
|
Slave IO running: YES
|
Slave SQL running: YES
|
Master ID: 4
|
Master binlog file: mariadb-bin.000051
|
Master binlog position: 392034173
|
Gtid current position: 100-3-403918
|
Gtid binlog position: 100-3-403918
|
Gtid slave IO position: 100-3-403918
|
|
Server: async03
|
Server ID: 300
|
Read only: NO
|
Slave configured: YES
|
Slave IO running: YES
|
Slave SQL running: YES
|
Master ID: 5
|
Master binlog file: mariadb-bin.000050
|
Master binlog position: 392035464
|
Gtid current position: 100-3-403918
|
Gtid binlog position: 100-3-403918
|
Gtid slave IO position: 100-3-403918
|
|
[root@mxs01 ~]# maxctrl list servers
|
┌─────────┬─────────────────┬──────┬─────────────┬─────────────────────────┬──────────────┐
|
│ Server │ Address │ Port │ Connections │ State │ GTID │
|
├─────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────────────┤
|
│ n01 │ 10.136.85.157 │ 3306 │ 0 │ Slave, Synced, Running │ │
|
├─────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────────────┤
|
│ n02 │ 10.136.87.40 │ 3306 │ 0 │ Master, Synced, Running │ │
|
├─────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────────────┤
|
│ n03 │ 10.136.80.203 │ 3306 │ 0 │ Slave, Synced, Running │ │
|
├─────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────────────┤
|
│ n04 │ 10.136.83.198 │ 3306 │ 0 │ Slave, Synced, Running │ │
|
├─────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────────────┤
|
│ n05 │ 10.136.86.152 │ 3306 │ 0 │ Slave, Synced, Running │ │
|
├─────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────────────┤
|
│ m03 │ 204.48.16.193 │ 3306 │ 0 │ Master, Running │ 100-3-403918 │
|
├─────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────────────┤
|
│ m04 │ 204.48.31.170 │ 3306 │ 0 │ Master, Running │ │
|
├─────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────────────┤
|
│ m05 │ 204.48.31.235 │ 3306 │ 0 │ Master, Running │ │
|
├─────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────────────┤
|
│ async01 │ 165.227.225.138 │ 3306 │ 0 │ Slave, Running │ 100-3-403918 │
|
├─────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────────────┤
|
│ async02 │ 138.68.158.166 │ 3306 │ 0 │ Slave, Running │ 100-3-403918 │
|
├─────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────────────┤
|
│ async03 │ 165.227.225.121 │ 3306 │ 0 │ Slave, Running │ 100-3-403918 │
|
└─────────┴─────────────────┴──────┴─────────────┴─────────────────────────┴──────────────┘
|
|
More context on this configuration: I have a MariaDB Cluster of 5 nodes (n01,n02,n03,n04,n05). I have three slaves, async01 replicating off n03, async02 replicating off n04, and async03 replicating off n05. I have one readconnroute for the cluster with one port for reads and another one for writes and I have as well a read pool of slaves, where I sent the application's reports and I would like to have report queries being executed over those three slaves forming a reading pool. The complete config is below; servers form n03-n05 and m03-m05 are the same, just changing the IP between private and public when adding the m0X to the reading pool of slaves.
Complete configuration:
[maxscale]
|
threads=auto
|
users_refresh_time=120
|
retain_last_statements=20
|
dump_last_statements=on_error
|
log_augmentation=1
|
ms_timestamp=1
|
|
[cluster-monitor]
|
type=monitor
|
module=galeramon
|
user=maxmon
|
password=2C894F278CEC5B974966EC969E006E37
|
monitor_interval=1000
|
backend_connect_timeout=3
|
backend_write_timeout=2
|
backend_read_timeout=1
|
backend_connect_attempts=1
|
journal_max_age=28800
|
script_timeout=90
|
servers=n01,n02,n03,n04,n05
|
disable_master_failback=false
|
available_when_donor=false
|
disable_master_role_setting=false
|
root_node_as_master=false
|
use_priority=false
|
events=master_down,master_up,slave_down,slave_up,server_down,server_up,synced_down,synced_up,donor_down,donor_up,ndb_down,ndb_up,lost_master,lost_slave,lost_synced,lost_donor,lost_ndb,new_master,new_slave,new_synced,new_donor,new_ndb
|
set_donor_nodes=false
|
|
[n01]
|
type=server
|
protocol=mariadbbackend
|
address=10.136.85.157
|
port=3306
|
authenticator=MySQLBackendAuth
|
|
[n02]
|
type=server
|
protocol=mariadbbackend
|
address=10.136.87.40
|
port=3306
|
authenticator=MySQLBackendAuth
|
|
[n03]
|
type=server
|
protocol=mariadbbackend
|
address=10.136.80.203
|
port=3306
|
authenticator=MySQLBackendAuth
|
|
[n04]
|
type=server
|
protocol=mariadbbackend
|
address=10.136.83.198
|
port=3306
|
authenticator=MySQLBackendAuth
|
|
[n05]
|
type=server
|
protocol=mariadbbackend
|
address=10.136.86.152
|
port=3306
|
authenticator=MySQLBackendAuth
|
|
##########################################################################
|
#: here is where you will point out all the application writes
|
##########################################################################
|
[cluster-write-service]
|
type=service
|
router=readconnroute
|
router_options=master
|
servers=n01,n02,n03,n04,n05
|
user=maxusr
|
password=2C894F278CEC5B974966EC969E006E37
|
|
[cluster-write-listener]
|
type=listener
|
service=cluster-write-service
|
protocol=MySQLClient
|
port=3310
|
|
##########################################################################
|
##: here is where you will point out all the application reads
|
###########################################################################
|
[cluster-read-service]
|
type=service
|
router=readconnroute
|
router_options=slave
|
servers=n01,n02,n03,n04,n05
|
user=maxusr
|
password=2C894F278CEC5B974966EC969E006E37
|
|
[cluster-read-listener]
|
type=listener
|
service=cluster-read-service
|
protocol=MySQLClient
|
port=3311
|
|
[CLI]
|
type=service
|
router=cli
|
|
[CLI-Unix-Listener]
|
type=listener
|
service=CLI
|
protocol=maxscaled
|
socket=default
|
|
[CLI-Inet-Listener]
|
type=listener
|
service=CLI
|
protocol=maxscaled
|
address=localhost
|
port=6603
|
|
##########################################################################
|
#: reading pool of slaves, it's formed by the async slaves replicating
|
#: from the MariaDB Cluster; reads are routed in round-robin fashion
|
############################################################################
|
|
[Slaves-Read-Pool-Service]
|
type=service
|
router=readconnroute
|
router_options=slave
|
servers=m03,m04,m05,async01,async02,async03
|
version_string=5.5.50-MariaDB
|
user=maxusr
|
password=2C894F278CEC5B974966EC969E006E37
|
#max_slave_replication_lag=15
|
|
|
[Slaves-Read-Pool-Monitor]
|
type=monitor
|
module=mariadbmon
|
servers=m03,m04,m05,async01,async02,async03
|
user=maxusr
|
password=2C894F278CEC5B974966EC969E006E37
|
monitor_interval=2000
|
journal_max_age=28800
|
script_timeout=90
|
detect_stale_master=false
|
#detect_replication_lag=true
|
|
[Slaves-Pool-Read-Listener]
|
type=listener
|
service=Slaves-Read-Pool-Service
|
protocol=MariaDBClient
|
port=3308
|
|
#: including the master for the sake of showing the GTIDs
|
#: out of the maxctrl list servers / maxadmin show monitors
|
[m03]
|
type=server
|
protocol=mariadbbackend
|
address=204.48.16.193
|
port=3306
|
authenticator=MySQLBackendAuth
|
|
[m04]
|
type=server
|
protocol=mariadbbackend
|
address=204.48.31.170
|
port=3306
|
authenticator=MySQLBackendAuth
|
|
[m05]
|
type=server
|
protocol=mariadbbackend
|
address=204.48.31.235
|
port=3306
|
authenticator=MySQLBackendAuth
|
#: end of adding masters
|
|
[async01]
|
type=server
|
address=165.227.225.138
|
port=3306
|
protocol=MariaDBBackend
|
|
[async02]
|
type=server
|
address=138.68.158.166
|
port=3306
|
protocol=MariaDBBackend
|
|
[async03]
|
type=server
|
address=165.227.225.121
|
port=3306
|
protocol=MariaDBBackend
|
#: end of configurations for the read pool of slaves
|
It should be able to show GTIDs out of the show monitors / list servers without the need to add the masters to the reading pool of slaves, as it works well, just does not show the GTIDs.
|