Uploaded image for project: 'MariaDB MaxScale'
  1. MariaDB MaxScale
  2. MXS-1898

When forming a reading pool of slaves, list servers does not show GTIDs, unless you add the masters

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 2.2.7
    • 2.3.0
    • readconnroute
    • None
    • MXS-SPRINT-63

    Description

      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.15733060           │ Slave, Synced, Running  │      │
      ├─────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────┤
      │ n02     │ 10.136.87.4033060           │ Master, Synced, Running │      │
      ├─────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────┤
      │ n03     │ 10.136.80.20333060           │ Slave, Synced, Running  │      │
      ├─────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────┤
      │ n04     │ 10.136.83.19833060           │ Slave, Synced, Running  │      │
      ├─────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────┤
      │ n05     │ 10.136.86.15233060           │ Slave, Synced, Running  │      │
      ├─────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────┤
      │ async01 │ 165.227.225.13833060           │ Slave, Running          │      │
      ├─────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────┤
      │ async02 │ 138.68.158.16633060           │ Slave, Running          │      │
      ├─────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────┤
      │ async03 │ 165.227.225.12133060           │ 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.15733060           │ Slave, Synced, Running  │              │
      ├─────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────────────┤
      │ n02     │ 10.136.87.4033060           │ Master, Synced, Running │              │
      ├─────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────────────┤
      │ n03     │ 10.136.80.20333060           │ Slave, Synced, Running  │              │
      ├─────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────────────┤
      │ n04     │ 10.136.83.19833060           │ Slave, Synced, Running  │              │
      ├─────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────────────┤
      │ n05     │ 10.136.86.15233060           │ Slave, Synced, Running  │              │
      ├─────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────────────┤
      │ m03     │ 204.48.16.19333060           │ Master, Running         │ 100-3-403918
      ├─────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────────────┤
      │ m04     │ 204.48.31.17033060           │ Master, Running         │              │
      ├─────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────────────┤
      │ m05     │ 204.48.31.23533060           │ Master, Running         │              │
      ├─────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────────────┤
      │ async01 │ 165.227.225.13833060           │ Slave, Running          │ 100-3-403918
      ├─────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────────────┤
      │ async02 │ 138.68.158.16633060           │ Slave, Running          │ 100-3-403918
      ├─────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────────────┤
      │ async03 │ 165.227.225.12133060           │ 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.

      Attachments

        Activity

          People

            esa.korhonen Esa Korhonen
            wagnerbianchi Wagner Bianchi (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.