我们有一个复制(基于 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。不过,这确实需要至少三台数据库服务器。