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 年前222 查看次数
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 年前

您未登录。 登录 发布回答。

一个好的回答可以清楚地解答问题和提供建设性反馈,并能促进提问者的职业发展。

回答问题的准则