0

We have a replication (GTID based) setup as described below:

ms1 = master mariaDB server (v. 10.3.13)

ms2 = slave mariaDB server (v. 10.3.13)

ms3 = MaxScale proxy (v. 2.3.8)

>root@ms3 ~ # maxadmin list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server             | Address         | Port  | Connections | Status              
-------------------+-----------------+-------+-------------+--------------------
ms1                | xxx             |  3306 |           0 | Master, Running
ms2                | xxx             |  3306 |           0 | Slave, Running
-------------------+-----------------+-------+-------------+--------------------

(Below maxscale.cnf)

We have tested the replication setup and was working OK with a simple PHP file with a 'INSERT' statement.

Then we tried a failover with the following scenario but it didn't work.

Below the details:

we stop ms1 server

>root@ms3 ~ # maxadmin list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server             | Address         | Port  | Connections | Status
-------------------+-----------------+-------+-------------+--------------------
ms1                | xxx                |  3306 |           0 | Down
ms2                | xxx                |  3306 |           0 | Master, Running
-------------------+-----------------+-------+-------------+--------------------

we run the same PHP file and we got the following PHP warning:

PHP Warning:  mysqli::__construct(): (HY000/1045): failed to create new session in xxx on line 8

Below is the related MaxScale log message:

2019-07-04 15:05:10   error  : Failed to create new routing session. Couldn't find eligible candidate server. Freeing allocated resources.
2019-07-04 15:05:10   error  : Failed to create new router session for service 'Write-Service'. See previous errors for more details.
2019-07-04 15:05:10   info   : Start Write-Service client session [8] for 'xxx' from xxx failed, will be closed as soon as all related DCBs have been closed.

we tried as solution to enable detect_standalone_master setting (https://mariadb.com/kb/en/mariadb-maxscale-21-mysql-monitor/#detect_standalone_master) but still the same problem.

So it seems that failover does not work

Any help?

maxscale.cnf

# MaxScale documentation:
# https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale-23/

# Global parameters
#
# Complete list of configuration options:
# https://mariadb.com/kb/en/mariadb-maxscale-23-mariadb-maxscale-configuration-usage-scenarios/

[maxscale]
threads=auto
query_retries=2
syslog=1
maxlog=1
log_warning=1
log_notice=1
log_info=1

# Server definitions
#
# Set the address of the server to the network
# address of a MariaDB server.
#

[ms1]
type=server
address=xxx
port=3306
protocol=MariaDBBackend

[ms2]
type=server
address=xxx
port=3306
protocol=MariaDBBackend

# Monitor for the servers
#
# This will keep MaxScale aware of the state of the servers.
# MariaDB Monitor documentation:
# https://mariadb.com/kb/en/mariadb-maxscale-23-mariadb-monitor/

[Replication-Monitor]
type=monitor
module=mariadbmon
servers=ms1, ms2
user=maxscale
password=max123
monitor_interval=2000
assume_unique_hostnames=ON
#failover_timeout=5
auto_failover=true
auto_rejoin=true
failcount=2
#master_failure_timeout=2
#verify_master_failure=true
switchover_timeout=10
detect_standalone_master=true
events=master_down

# Service definitions
#
# Service Definition for a read-only service and
# a read/write splitting service.
#

# ReadConnRoute documentation:
# https://mariadb.com/kb/en/mariadb-maxscale-23-readconnroute/

[Write-Service]
type=service
router=readconnroute
router_options=master
servers=ms1
user=maxscale
password=max123

# ReadWriteSplit documentation:
# https://mariadb.com/kb/en/mariadb-maxscale-23-readwritesplit/

[Read-Service]
type=service
router=readconnroute
router_options=slave
servers=ms2
user=maxscale
password=max123

# This service enables the use of the MaxAdmin interface
# MaxScale administration guide:
# https://mariadb.com/kb/en/mariadb-maxscale-23-maxadmin-admin-interface/

[MaxAdmin-Service]
type=service
router=cli

# Listener definitions for the services
#
# These listeners represent the ports the
# services will listen on.
#

[Write-Listener]
type=listener
service=Write-Service
protocol=MariaDBClient
port=3306

[Read-Listener]
type=listener
service=Read-Service
protocol=MariaDBClient
port=3307

[MaxAdmin-Listener]
type=listener
service=MaxAdmin-Service
protocol=maxscaled
socket=default

1 Answer 1

0

If you want to achieve ReadWriteSplit you need to define "readwritesplit" as router instead of "readconnroute" according to the ReadWriteSplit documentation.

So instead of defining a "Write-Service" and "Read-Service" you could define a "Split-Service" and there use:

router=readwritesplit

instead of

router=readconnroute

But frankly I'm not sure if you can really achieve what you want with either readwritesplit or readconnroute. Maybe you need to have a look at MariaDB Galera Cluster. That does require at least three database servers though.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .