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

Failover does not happen with Maxscale in Master->Slave replication setup

    XMLWordPrintable

    Details

    • Type: Task
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Not a Bug
    • Affects Version/s: 2.2.5
    • Fix Version/s: 2.2.5
    • Component/s: failover
    • Labels:
    • Environment:
      Non production environment

      Description

      We have 2 servers installed Mariadb and setup Master->Slave replication. Installed and setup Maxscale 2.2.5 on SERVER3

      MariaDB versions on 2 servers

      SERVER1 Server version: 10.2.12-MariaDB-log MariaDB Server
      SERVER2 Server version: 10.2.12-MariaDB-log MariaDB Server

      Maxscale version on SERVER3
      ========================

      [maxscale@SERVER3 ~]$ maxscale --version
      MaxScale 2.2.5
      

      /etc/maxscale.cnf file contents
      =======================

      [MaxScale]
      threads=auto
      admin_auth=http
       
      [SplitterService]
      type=service
      router=readwritesplit
      servers=dbserv1,dbserv2
      user=maxscale
      passwd=*************
       
      [SplitterListener]
      type=listener
      service=SplitterService
      protocol=MariaDBClient
      port=3301
      #socket=/tmp/ClusterMaster
       
      [dbserv1]
      type=server
      address=SERVER1
      port=3307
      protocol=MariaDBBackend
       
      [dbserv2]
      type=server
      address=SERVER2
      port=3307
      protocol=MariaDBBackend
       
      [ReplicationMonitor]
      type=monitor
      module=mariadbmon
      servers=dbserv1,dbserv2
      user=maxscale
      passwd=*************
      replication_user=mysql_repl
      replication_password=***********
      #monitor_interval=1000
      monitor_interval=60
      auto_failover=1
      failover_timeout=5
      auto_rejoin=true
      failcount=5
      master_failure_timeout=2
      verify_master_failure=true
      switchover_timeout=90
      detect_standalone_master=true
       
      [CLI]
      type=service
      router=cli
       
      [CLIListener]
      type=listener
      service=CLI
      protocol=maxscaled
      address=localhost
      port=6600
      

      MaxScale> list servers
      Servers.
      -------------------+-----------------+-------+-------------+--------------------
      Server             | Address         | Port  | Connections | Status
      -------------------+-----------------+-------+-------------+--------------------
      dbserv1            | SERVER1 |  3307 |           0 | Master, Running
      dbserv2            | SERVER2 |  3307 |           0 | Slave, Running
      -------------------+-----------------+-------+-------------+--------------------
      

      When we do failover by stopping mariadb instance on SERVER1 then the failover was not happend. You can see the following information

      /mysql/maxscale/var/log/maxscale/maxscale.log
      =====================================

      2018-05-08 12:19:16   error  : Monitor was unable to connect to server [dbcct3vmyd01003.idm.sgdcelab.sabre.com]:3307 : "Can't connect to MySQL server on 'dbcct3vmyd01003.idm.sgdcelab.sabre.com' (115)"
      2018-05-08 12:19:16   notice : [mariadbmon] Server [dbcct3vmyd01003.idm.sgdcelab.sabre.com]:3307 lost the master status.
      2018-05-08 12:19:16   notice : Server changed state: dbserv1[dbcct3vmyd01003.idm.sgdcelab.sabre.com:3307]: master_down. [Master, Running] -> [Down]
      2018-05-08 12:19:23   error  : [readwritesplit] Couldn't find suitable Master from 2 candidates.
      2018-05-08 12:19:23   error  : Failed to create new router session for service 'SplitterService'. See previous errors for more details.
      2018-05-08 12:19:39   error  : [readwritesplit] Couldn't find suitable Master from 2 candidates.
      2018-05-08 12:19:39   error  : Failed to create new router session for service 'SplitterService'. See previous errors for more details.
      

      MaxScale> list servers
      Servers.
      -------------------+-----------------+-------+-------------+--------------------
      Server             | Address         | Port  | Connections | Status
      -------------------+-----------------+-------+-------------+--------------------
      dbserv1            | dbcct3vmyd01003.idm.sgdcelab.sabre.com |  3307 |           0 | Down
      dbserv2            | dbcct3vmyd01004.idm.sgdcelab.sabre.com |  3307 |           0 | Slave, Running
      -------------------+-----------------+-------+-------------+--------------------
      

      Privileges for maxscale and mysql_repl users
      ===================================

      MariaDB [(none)]> show grants for 'maxscale_admin';
      +---------------------------------------------------------------------+
      | Grants for maxscale_admin                                           |
      +---------------------------------------------------------------------+
      | GRANT INSERT, RELOAD, REPLICATION CLIENT ON *.* TO 'maxscale_admin' |
      | GRANT SELECT ON `mysql`.`tables_priv` TO 'maxscale_admin'           |
      | GRANT SELECT ON `mysql`.`user` TO 'maxscale_admin'                  |
      | GRANT SELECT ON `mysql`.`db` TO 'maxscale_admin'                    |
      +---------------------------------------------------------------------+
      4 rows in set (0.00 sec)
       
      MariaDB [(none)]> show grants for 'mysql_repl';
      +-----------------------------------------------------------------------------------+
      | Grants for mysql_repl                                                             |
      +-----------------------------------------------------------------------------------+
      | GRANT RELOAD, SUPER, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'mysql_repl' |
      +-----------------------------------------------------------------------------------+
      1 row in set (0.00 sec)
       
      MariaDB [(none)]> show grants for 'maxscale'@SERVER3;
      +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Grants for maxscale@SERVER3                                                                                                                                          |
      +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | GRANT maxscale_admin TO 'maxscale'@'SERVER3'                                                                                                                         |
      | GRANT SHOW DATABASES, SUPER, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'maxscale'@'SERVER3' IDENTIFIED BY PASSWORD '***********************' |
      +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      2 rows in set (0.01 sec)
      

      MariaDB [(none)]> show grants for 'mysql_repl'@'SERVER1';
      +------------------------------------------------------------------------------------------------------------------------------------------------+
      | Grants for mysql_repl@SERVER1                                                                                   |
      +------------------------------------------------------------------------------------------------------------------------------------------------+
      | GRANT mysql_repl TO 'mysql_repl'@'SERVER1'                                                                      |
      | GRANT USAGE ON *.* TO 'mysql_repl'@'SERVER1' IDENTIFIED BY PASSWORD '*****************' |
      +------------------------------------------------------------------------------------------------------------------------------------------------+
      2 rows in set (0.00 sec)
       
      MariaDB [(none)]> show grants for 'mysql_repl'@'SERVER2';
      +------------------------------------------------------------------------------------------------------------------------------------------------+
      | Grants for mysql_repl@SERVER2                                                                                   |
      +------------------------------------------------------------------------------------------------------------------------------------------------+
      | GRANT mysql_repl TO 'mysql_repl'@'SERVER2'                                                                      |
      | GRANT USAGE ON *.* TO 'mysql_repl'@'SERVER2' IDENTIFIED BY PASSWORD '***************' |
      +------------------------------------------------------------------------------------------------------------------------------------------------+
      2 rows in set (0.00 sec)
       
      MariaDB [(none)]> show grants for 'mysql_repl'@'SERVER3';
      +------------------------------------------------------------------------------------------------------------------------------------------------+
      | Grants for mysql_repl@SERVER3                                                                                   |
      +------------------------------------------------------------------------------------------------------------------------------------------------+
      | GRANT mysql_repl TO 'mysql_repl'@'SERVER3'                                                                      |
      | GRANT USAGE ON *.* TO 'mysql_repl'@'SERVER3' IDENTIFIED BY PASSWORD '********' |
      +------------------------------------------------------------------------------------------------------------------------------------------------+
      2 rows in set (0.00 sec)
      

      Could you please suggest me is there any thing wrong in respect to maxscale configuration and privileges or any other issue?

      Thanks,
      Venkat Beru

        Attachments

          Activity

            People

            Assignee:
            esa.korhonen Esa Korhonen
            Reporter:
            venkatberu Venkatarao Beru
            Votes:
            1 Vote for this issue
            Watchers:
            4 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: