Join Log in
Score:0
avatar Server

AWS EC2 MySQL Replication: Config To Query Slave From Master

us flag
James Butler

I have successfully set up a MySQL master-slave situation on separate AWS EC2 instances. The slave is running and is successfully replicating the master.

So far so good.

Now I want to be able to query the slave (for analytics, etc.) but I can't find the right configuration to be able to send a query from the master to the slave.

The errors I get (depending on my AWS Security Profile settings) are either "Connection Refused" or "Connection Timed out"

On the slave I had an EC2 Security Group allowance for the master IP block to use to connect to port 3306, and I used the slave's IP address in the master's connection configuration.

That resulted in the "Connection Refused" error when I tried to query the slave from the master.

On the master, I ran 'show full processlist' and saw the slave host was the AWS EC2 host name, not the IP, and port 44508 was tacked on to the end, so I changed connection info on the master to use the slave host name instead of the IP address, and the Security Group setting on the slave to allow traffic from the master on port 44508 instead of 3306.

That resulted in the "Connection Timed Out" error.

I have tried combinations of IP/hostname/port in the slave's Security Group, but just getting one of those 2 errors, with any combination.

Can anyone offer tips on how to configure things so I can run (read-only) queries on the slave from that master machine, and return the results back to the master? TIA.

Both systems are set up the same, except the master is also running Codeigniter4, which is where I'm setting the connection configuration:

Ubuntu 20.04.3 LTS and MySQL 8.0.26-0

132
0 + 0
Score:1
avatar Server
la flag
Martynas Saint

In such setup your clients (apps, software) should connect to the slave and run SELECT queries only (if you need to run write queries - they should be done on the master only).

You should check:

  1. If MySQL server listens on the network in the slave server.
  2. If security groups allow connecting to the slave server by your apps.
  3. The slave should be configured as read-only to prevent problems.
0 + 0
us flag
James Butler
The slave server is replicating correctly, with data pushed from the master binary log to the slave. 'show slave status' shows no errors and updates on the master are appearing near-immediately in the slave. 'show full processlist' on the master shows the slave. Security group on master allows access from and to the slave. Security group on slave allows access to and from the master. The slave is not configured as read-only, but I should still be able to query it, I imagine. Thank you for your attention.
0
Reply
us flag
James Butler
No doubt this is related to: On the slave server, I can not log into the slave as the 'slave' user. 'mysql.user' table shows plugin for 'slave' user as 'cached_sha2_password' instead of 'mysql_native_password' which may be why I can't log in as a user (not even as 'root' ... I can log in with 'sudo mysql')
0
Reply
us flag
James Butler
Not trying to log in as 'slave' any more, just as the usual user I use on the master. I can definitely log into the slave db using the normal user credentials. However no joy connecting from the master, so I created a new user on the master with the same name and credentials as on the master but with the slave's host IP and then with the master's IP and then with the host name reported by 'show full processlist' combining those in the db connection string with either port 3306 or the port 44508 reported by 'show full processlist' but still no joy.
0
Reply
la flag
Martynas Saint
You should issue all commands that modify data only on the master to avoid problems and create user with localhost or 127.0.0.1 as host, ie user@'127.0.0.1' if you are connecting to MySQL from the slave server.
0
Reply
us flag
James Butler
Thank you again. Yes, I issue all commands on the master and allow them to manage what happens with the slave. There is no problem with the replication. This is a fairly mature master database with all of the localhost users specified and available for use on the master. I just can't figure out how to run a query on the slave from the master. I am trying to connect to the slave from the master to run my queries, and I'm not running anything FROM the slave. I want to be able to run queries on the slave FROM the master.
0
Reply
la flag
Martynas Saint
How do you connect from master server to slave MySQL server when trying to run the query?
0
Reply
us flag
James Butler
Thank you, again for your attention. I use typical MySQL connection information ... host, user, password. Then I simply check if the database connection was made, but in every case I do not receive any result except (a) the database connection failed and (b) either "Connection refused" or "Connection timed out" errors. I completely opened up the slave server firewall to any TCP traffic from any source using any port, and I received the "Connection timed out" error. I verified that the MySQL server is running (and replicating the master), and even with everything open ... I'm baffled.
0
Reply
us flag
James Butler
When I issue 'show replicas' on the master, I do see the slave in the result, but the entry has no 'host' value. When I issue 'show full processlist' I see the slave in the result with a complete host value. Perhaps that means something?
0
Reply
la flag
Martynas Saint
Show the exact command you are using to connect and also you could paste my.cnf of the slave
0
Reply
us flag
James Butler
<?php $servername = "SLA.VIP.ADD.RES"; $username = "validusername"; $password = "validpassword"; $conn = new mysqli($servername, $username, $password); if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } echo "Connected successfully"; ?>
0
Reply
us flag
James Butler
Do you have a specific question about the MySQL configuration? server_id=2, for example.
0
Reply
la flag
Martynas Saint
Let us [continue this discussion in chat](https://chat.stackexchange.com/rooms/129860/discussion-between-martynas-saint-and-james-butler).
0
Reply
Score:0
avatar Server
us flag
James Butler

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.

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.

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

0 + 0
la flag
Martynas Saint
so basically this one: You should check: If MySQL server listens on the network in the slave server. ;:) glad you have solved your issue.
0
Reply
ธงชาติไทย
Elon Musk
I sit in a Tesla and translated this thread with Ai:

mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.