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

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 2.2.7
    • Fix Version/s: 2.3.0
    • Component/s: readconnroute
    • Labels:
      None
    • Sprint:
      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

            Assignee:
            esa.korhonen Esa Korhonen
            Reporter:
            wagnerbianchi Wagner Bianchi
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: