Your assumptions are incorrect.
The slave does not bind to 3306 (or whatever port it is listening to) when making its outbound connection to the master, and the port the slave is "listening to" for inbound client connections is otherwise irrelevant in replication.
The slave establishes a TCP connection from a high port to the master's port (e.g. 3306), authenticates itself, optionally does a little bit of session setup (for example, telling the master its desired heartbeat configuration), and then requests a binlog stream from the master, beginning at a specific file and byte position.
The master responds by beginning to stream binlog events beginning at that position (assuming it's valid) and continuing to autonomously send new binlog events to the slave over that same TCP connection as those events are generated on the master. The master never makes a connection back toward the slave unless both machines are slaves to each other in a circular replication configuration where each of them is the other's slave.
The single TCP connection, originated by the slave, is the pipeline for transferring binlog events and the master will continue streaming events as long as it thinks this TCP connection is established. If the connection is severed for any reason, the slave is responsible for reconnecting to the master, with the same process as before, and requesting a new start point for the binlog stream, based on the last valid event it received before it lost connectivity.
Ever server has a server_id
global variable. If a master accepts a connection from a slave that identifies itself with the same server_id as one that is already connected, the master will forcibly close those already-existing connections, preventing the possibility of the master wasting resources streaming traffic into a black hole because of network issues that make the master think the former connection (which should have been from the same slave) is still established.
So your single access list entry would be "TCP, from slave IP, high random port -- to master IP, port 3306" (assuming the master is listening on 3306).
Additional details:
The replication io_thread
is the thread in MySQL that creates the outbound TCP connection to the master, receives the replication events over that connection, and writes them to the "relay log" (file) on the slave. It doesn't "process" the replication events, it stores them for execution by the sql_thread
.
The sql_thread
on the slave then reads the relay log (file) written by the io_thread
and executes the events found there. It appears like any other client thread in SHOW PROCESSLIST
but it is not created via a connection to port 3306 on the slave, because it's a thread created inside the MySQL process when you START SLAVE
.
There is no reason a thread that's running inside the MySQL process would need to make a TCP connection to the same process -- it's already running inside the MySQL server and has access to the server internals to execute the replication events received from the master and stored to the relay log.