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
Also would like to know, Is slave can be in readonly mode?
Thanks,
Venkat Beru