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년 전240회 조회
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년 전

로그인하지 않았습니다. 로그인해야 답변을 게시할 수 있습니다.

좋은 답변은 질문에 명확하게 답하고 건설적인 피드백을 제공하며 질문자의 전문적인 성장을 장려합니다.

질문 답변하기에 대한 가이드라인

관련 콘텐츠