Details
-
Task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
2.2.5
-
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
I don't see any clear reason for the failover not happening. Could you try running "show monitors" in MaxAdmin and paste the output here? The monitor disables automatic failover if it (or switchover) has failed previously. Also, try running manual failover and check the error message. Manual failover is started with the MaxAdmin command "call command mariadbmon failover ReplicationMonitor".
Having slave servers in read-only mode is ok and even recommended.
Regards,
Esa
Hi Esa,
Thank you for looking into this issue. We followed the following document exactly and configured maxscale according to that.
Yesterday we tested the same failover and switchover by including the following configuration into /etc/maxscale.cnf under "[ReplicationMonitor]" section then both switchover and failover happened properly.
detect_standalone_master=true
events=master_down
[ReplicationMonitor]
type=monitor
module=mariadbmon
servers=dbserv1,dbserv2
user=maxscale
passwd=B3D6615B8ACD02EC5D9F2C2D2BBC72C5
replication_user=mysql_repl
replication_password=E3BBC2414C10E0163C8C452EDC67B143
#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
events=master_down
Manual steps have been tested also and went fine after including the above 2 parameters by using the following commands
#####################Switch over##################
[maxscale@dbcct3vmyd01006 ~]$ maxctrl call command mariadbmon switchover ReplicationMonitor dbserv2 dbserv1
OK
2018-05-09 02:03:50 notice : [mariadbmon] Stopped the monitor ReplicationMonitor for the duration of switchover.
2018-05-09 02:03:50 warning: [mariadbmon] Slave 'dbserv2' has gtid_strict_mode disabled. Enabling this setting is recommended. For more information, see https://mariadb.com/kb/en/library/gtid/#gtid_strict_mode
2018-05-09 02:03:50 notice : [mariadbmon] Demoting server 'dbserv1'.
2018-05-09 02:03:50 notice : [mariadbmon] Promoting server 'dbserv2' to master.
2018-05-09 02:03:50 notice : [mariadbmon] Old master 'dbserv1' starting replication from 'dbserv2'.
2018-05-09 02:03:50 notice : [mariadbmon] Redirecting slaves to new master.
2018-05-09 02:03:50 notice : [mariadbmon] Switchover dbserv1 -> dbserv2 performed.
2018-05-09 02:03:50 notice : Loaded server states from journal file: /mysql/maxscale/var/lib/maxscale/ReplicationMonitor/monitor.dat
2018-05-09 02:03:50 notice : Server changed state: dbserv1[dbcct3vmyd01003.idm.sgdcelab.sabre.com:3307]: new_slave. [Master, Running] -> [Slave, Running]
2018-05-09 02:03:50 notice : Server changed state: dbserv2[dbcct3vmyd01004.idm.sgdcelab.sabre.com:3307]: new_master. [Slave, Running] -> [Master, Running]
2018-05-09 02:03:50 notice : [mariadbmon] Gtid domain id of master has changed: 1 -> 2.
[maxscale@dbcct3vmyd01006 ~]$ maxctrl call command mariadbmon switchover ReplicationMonitor dbserv1 dbserv2
OK
2018-05-09 02:08:05 notice : [mariadbmon] Stopped the monitor ReplicationMonitor for the duration of switchover.
2018-05-09 02:08:05 warning: [mariadbmon] Slave 'dbserv1' has gtid_strict_mode disabled. Enabling this setting is recommended. For more information, see https://mariadb.com/kb/en/library/gtid/#gtid_strict_mode
2018-05-09 02:08:05 notice : [mariadbmon] Demoting server 'dbserv2'.
2018-05-09 02:08:05 notice : [mariadbmon] Promoting server 'dbserv1' to master.
2018-05-09 02:08:05 notice : [mariadbmon] Old master 'dbserv2' starting replication from 'dbserv1'.
2018-05-09 02:08:05 notice : [mariadbmon] Redirecting slaves to new master.
2018-05-09 02:08:05 notice : [mariadbmon] Switchover dbserv2 -> dbserv1 performed.
2018-05-09 02:08:05 notice : Loaded server states from journal file: /mysql/maxscale/var/lib/maxscale/ReplicationMonitor/monitor.dat
2018-05-09 02:08:05 notice : Server changed state: dbserv1[dbcct3vmyd01003.idm.sgdcelab.sabre.com:3307]: new_master. [Slave, Running] -> [Master, Running]
2018-05-09 02:08:05 notice : Server changed state: dbserv2[dbcct3vmyd01004.idm.sgdcelab.sabre.com:3307]: new_slave. [Master, Running] -> [Slave, Running]
2018-05-09 02:08:05 notice : [mariadbmon] Gtid domain id of master has changed: 2 -> 1.
###################Failover###################
maxctrl call command mariadbmon failover ReplicationMonitor
2018-05-08 13:49:49 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 13:49:49 notice : [mariadbmon] Server [dbcct3vmyd01003.idm.sgdcelab.sabre.com]:3307 lost the master status.
2018-05-08 13:49:49 notice : Server changed state: dbserv1[dbcct3vmyd01003.idm.sgdcelab.sabre.com:3307]: master_down. [Master, Running] -> [Down]
2018-05-08 13:49:49 warning: [mariadbmon] Master has failed. If master status does not change in 4 monitor passes, failover begins.
2018-05-08 13:49:50 notice : [mariadbmon] Performing automatic failover to replace failed master 'dbserv1'.
2018-05-08 13:49:50 warning: [mariadbmon] Slave 'dbserv2' has gtid_strict_mode disabled. Enabling this setting is recommended. For more information, see https://mariadb.com/kb/en/library/gtid/#gtid_strict_mode
2018-05-08 13:49:50 notice : [mariadbmon] Promoting server 'dbserv2' to master.
2018-05-08 13:49:50 notice : [mariadbmon] Redirecting slaves to new master.
2018-05-08 13:49:50 warning: [mariadbmon] Setting standalone master, server 'dbserv2' is now the master.
2018-05-08 13:49:50 notice : Server changed state: dbserv2[dbcct3vmyd01004.idm.sgdcelab.sabre.com:3307]: new_master. [Slave, Running] -> [Master, Running]
2018-05-08 13:49:50 notice : [mariadbmon] Gtid domain id of master has changed: 1 -> 2.
2018-05-08 13:51:23 notice : Server changed state: dbserv1[dbcct3vmyd01003.idm.sgdcelab.sabre.com:3307]: server_up. [Down] -> [Running]
2018-05-08 13:51:23 notice : [mariadbmon] Directing standalone server 'dbserv1' to replicate from 'dbserv2'.
2018-05-08 13:51:23 notice : [mariadbmon] Standalone server 'dbserv1' starting replication.
2018-05-08 13:51:23 notice : [mariadbmon] 1 server(s) redirected or rejoined the cluster.
2018-05-08 13:54:03 notice : Server changed state: dbserv1[dbcct3vmyd01003.idm.sgdcelab.sabre.com:3307]: new_slave. [Running] -> [Slave, Running]
2018-05-08 13:54:03 notice : Server changed state: dbserv1[dbcct3vmyd01003.idm.sgdcelab.sabre.com:3307]: lost_slave. [Slave, Running] -> [Running]
2018-05-08 13:55:15 notice : Server changed state: dbserv1[dbcct3vmyd01003.idm.sgdcelab.sabre.com:3307]: new_slave. [Running] -> [Slave, Running]
#####################
Please let us know, is the following parameters manadatory to add into /etc/maxscale.cnf file? After adding the following parameters only failover and switchover works fine.
detect_standalone_master=true
events=master_down
Thank,
Venkat
If slave is in "ReadOnly" mode , how the write operations can perform once this slave becomes master after failover/switchover. Is there any thing manually we need to change the mode from ReadOnly to ReadWrite mode or is there any configuration parameter needs to be included in /etc/maxscale.cnf file?
Thanks,
Venkat Beru
Looks like it is now working correctly. Please ask if there are more problems.
Regarding the settings:
"detect_standalone_master=true" is a good setting if you predict a situation where there is only one server left and that should be the master. Without this setting, MaxScale won't consider a lone server as "Master". This setting is on by default.
"events=master_down" is not required by failover. Leave it on if you wish to run your own scripts when a master goes down.
The monitor manages the "read_only"-flag: enabling it when a server is demoted to slave and disabling it when a slave is promoted to master.
The automatic failover/rejoin are disabled if the operation fails. Restarting MaxScale enables them again (or maxadmin command "alter monitor ReplicationMonitor auto_failover=1"). The maxadmin command "show monitors" shows the current status of the features.
The log shows messages such as "Gtid domain id of master has changed: 1 -> 2." This means that different servers have different domain id values. Unless the events created by the servers are meant to be in different replication streams, it's usually best to have the same gtid domain in the cluster. The server id:s are enough to identify servers.
Also would like to know, Is slave can be in readonly mode?
Thanks,
Venkat Beru