Mariadb 主/从配置故障转移问题

Mariadb 主/从配置故障转移问题

我们有一个复制(基于 GTID)设置,如下所述:

ms1 = 主 mariaDB 服务器(v. 10.3.13)

ms2 = 从属 mariaDB 服务器(v. 10.3.13)

ms3 = MaxScale 代理(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
-------------------+-----------------+-------+-------------+--------------------

(低于maxscale.cnf)

我们已经测试了复制设置,并且使用带有“INSERT”语句的简单 PHP 文件正常运行。

然后,我们尝试了以下场景的故障转移,但没有成功。

详情如下:

我们停止了 ms1 服务器

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

我们运行相同的 PHP 文件,并收到以下 PHP 警告:

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

以下是相关的MaxScale日志消息:

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.

我们尝试解决方案来启用detect_standalone_master设置(https://mariadb.com/kb/en/mariadb-maxscale-21-mysql-monitor/#detect_standalone_master),但问题仍然存在。

因此,故障转移似乎不起作用

有什么帮助吗?

最大比例因子

# 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

如果您想实现 ReadWriteSplit,则需要根据 ReadWriteSplit 文档将“readwritesplit”定义为路由器而不是“readconnroute”。

因此,您可以定义“拆分服务”而不是定义“写服务”和“读服务”,然后使用:

router=readwritesplit

代替

router=readconnroute

但坦率地说,我不确定您是否真的可以通过 readwritesplit 或 readconnroute 实现您想要的目标。也许您需要看看 MariaDB Galera Cluster。不过,这确实需要至少三台数据库服务器。

相关内容