Help with Permissions for MySQL select query from master EC2 to slave EC2

0

2 EC2 instances both running Ubuntu 20.04.3 LTS and MySQL 8.0.26-0
I need to be able to run read-only queries FROM the master TO the slave and retrieve the result back on the master
PROBLEM: I can not create a configuration that allows me to connect to the SLAVE databases FROM the MASTER
Regardless of host or Security Group settings, I receive either 'Connection Refused' or 'Connection Timed Out'

Master MySQL set up on Instance A (running for several months) along with the rest of the LAMPP stack
Slave MySQL set up on Instance B (running for a week, replication works flawlessly) only running MySQL, no web services

FROM SLAVE: I can log into MySQL using the same user/password/mysql_native_password as on the master.

I have tried setting the Security Group to allow 3306 inbound as well as 44508 inbound, which is the port the master says the slave's host is using (issuing the 'show full processlist' command on the master), using slave/master IP addresses or host names, and every combination of those that I can think of.

I have even tried allowing ALL TCP traffic from any source into the slave, but still received the 'Connection Refused' error

Is there a guide for which Security Group and MySQL connection settings to use to allow the master to query the slave? TIA

DGP
質問済み 3年前221ビュー
1回答
0

RESOLVED:
In the slave's /etc/mysql/mysql.conf.d/mysqld.cnf I needed to comment out the 'bind-address' and 'mysqlx-bind-address' lines, stop the slave, restart mysqld and start the slave, again.

Having 'bind-address = 127.0.0.1' prevented connections from anywhere else.
Commenting it out allows access from all sources, not just localhost.
EC2 Security Group restricts access to port 3306 to just the master server's IP block.

Working great all around, now.
Thank you for your attention.

DGP
回答済み 3年前

ログインしていません。 ログイン 回答を投稿する。

優れた回答とは、質問に明確に答え、建設的なフィードバックを提供し、質問者の専門分野におけるスキルの向上を促すものです。

質問に答えるためのガイドライン

関連するコンテンツ