我设置了MaxScale，所以我试图测试它是否工作。我尝试使用下面的命令从应用程序服务器访问$ mysql --host=192.168.30.145 --user=client --password -P 3309
然后，我得到了这个错误
ERROR 1045 (28000): Access denied for user 'client'@'::ffff:192.168.30.161' (using password: YES)这是一个错误消息在/var/log/maxscale/maxscale. log
warning: (2) [MariaDBProtocol] Authentication failed for user 'client'@[::ffff:192.168.30.161] to service 'Write-Service'. Originating listener: 'Write-Listener'. MariaDB error: 'Access denied for user 'client'@'::ffff:192.168.30.161' (using password: YES)'.这里是我设置的MaxScale和MariaDb的信息。
·masterDB：192.168.30.140
·replicaDB1：192.168.30.141
·replicaDB2：192.168.30.142
·replicaDB3：192.168.30.143
·maxscale：192.168.30.145
·应用服务器：192.168.30.161
在masterDB上，我创建了用户帐户，如maxscale@%、monitor@%和email protected(https://stackoverflow.com/cdn-cgi/l/email-protection)对于每个replicaDB，我创建了email protected(https://stackoverflow.com/cdn-cgi/l/email-protection)
现在，我在所有服务器上执行了Systemctl stop ufw，并在所有数据库上将bind-address = 127.0.0.1更改为bind-address = 0.0.0.0
[maxscale]
threads=auto
[MariaDB-Monitor]
type=monitor
module=mariadbmon
servers=masterDB, replicaDB1, replicaDB2, replicaDB3
user=monitor
password=alj123
monitor_interval=2s
[masterDB]
type=server
address=192.168.30.140
port=3306
protocol=MariaDBBackend
[replicaDB1]
type=server
address=192.168.30.141
port=3306
protocol=MariaDBBackend
[replicaDB2]
type=server
address=192.168.30.142
port=3306
protocol=MariaDBBackend
[replicaDB3]
type=server
address=192.168.30.143
port=3306
protocol=MariaDBBackend
[Write-Service]
type=service
router=readconnroute
router_options=master
servers=masterDB
user=maxscale
password=alj123
[Read-Service]
type=service
router=readconnroute
router_options=slave
servers=replicaDB1, replicaDB2, replicaDB3
user=read
password=alj123
[Write-Listener]
type=listener
service=Write-Service
protocol=MariaDBClient
port=3309
[Read-Listener]
type=listener
service=Read-Service
protocol=MariaDBClient
port=3310users on masterDB
+----------------------------------------------------------------------------------------------------------------------------------+
| Grants for maxscale@% |
+----------------------------------------------------------------------------------------------------------------------------------+
| GRANT SHOW DATABASES, BINLOG MONITOR ON *.* TO `maxscale`@`%` IDENTIFIED BY PASSWORD '*9639F24DBDAFA8F106318B335C271B581801E286' |
| GRANT SELECT ON `mysql`.`db` TO `maxscale`@`%` |
| GRANT SELECT ON `mysql`.`proxies_priv` TO `maxscale`@`%` |
| GRANT SELECT ON `mysql`.`columns_priv` TO `maxscale`@`%` |
| GRANT SELECT ON `mysql`.`roles_mapping` TO `maxscale`@`%` |
| GRANT SELECT ON `mysql`.`user` TO `maxscale`@`%` |
| GRANT SELECT ON `mysql`.`tables_priv` TO `maxscale`@`%` |
| GRANT SELECT ON `mysql`.`procs_priv` TO `maxscale`@`%` |
+----------------------------------------------------------------------------------------------------------------------------------+
+--------------------------------------------------------------------------------------------------------------------------------+
| Grants for monitor@% |
+--------------------------------------------------------------------------------------------------------------------------------+
| GRANT RELOAD, SUPER, BINLOG MONITOR ON *.* TO `monitor`@`%` IDENTIFIED BY PASSWORD '*9639F24DBDAFA8F106318B335C271B581801E286' |
+--------------------------------------------------------------------------------------------------------------------------------+
+---------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for [email protected] |
+---------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO `client`@`192.168.30.145` IDENTIFIED BY PASSWORD '*9639F24DBDAFA8F106318B335C271B581801E286' |
+---------------------------------------------------------------------------------------------------------------------------------------------+user on each replicaDB
+-------------------------------------------------------------------------------------------------------------------+
| Grants for [email protected] |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT ON *.* TO `read`@`192.168.30.145` IDENTIFIED BY PASSWORD '*9639F24DBDAFA8F106318B335C271B581801E286' |
+-------------------------------------------------------------------------------------------------------------------+user on application sever
+---------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for [email protected] |
+---------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO `client`@`192.168.30.161` IDENTIFIED BY PASSWORD '*9639F24DBDAFA8F106318B335C271B581801E286' |
+---------------------------------------------------------------------------------------------------------------------------------------------+·做了grant ALL PRIVILEGESemail protected(https://stackoverflow.com/cdn-cgi/l/email-protection)，然后我没有得到错误，但我可以在replicaDB上重新启动，更新，重新启动。我想让客户端在replicaDB上只执行SELECT操作
·将客户端的主机从192.168.30.145更改为%，然后我没有得到错误，但我不想允许客户端从maxscale服务器以外的服务器访问数据库（192.168.30.145）
有人能帮帮我吗？
1条答案
按热度按时间sigwle7e1#
失败的原因是因为您使用一个特定的IP地址（
[[email protected]](https://stackoverflow.com/cdn-cgi/l/email-protection)）定义了用户，并且您需要定义两次（除非您使用proxy_protocol）：一次用于客户端IP，另一次用于MaxScale IP。这在MaxScale故障排除文章以及MaxScale教程中提到。解决这个问题的最好方法是将MaxScale IP添加到MariaDB中的proxy_protocol_networks中，并为MaxScale中的所有服务器打开proxy_protocol，而不是定义两次用户或使用一个代理来匹配所有IP。
这使得MaxScale成为可信的身份验证代理，然后允许MaxScale将原始IP中继到MariaDB服务器以用于最终身份验证。这基本上使MaxScale对整个身份验证过程透明，这是您通常希望从代理中获得的。