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


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

asked a year ago14 views
1 Answer

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 =' 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.

answered a year ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions