Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I wanted to know how replication works in mysql.

Both slave and master have mysql server running say on port 3306. Slave acts like a client and master acts like a server.

Does slave always bind to 3306 when it makes a tcp connection to send the request? When the response is sent back by server the dest port will be 3306 and since slave mysql is listening to that port it will process the response?

Not sure if my understanding is correct.

Also I am interested in knowing ingress and egress rules that should be added for replication to work.

For TCP connection from slave will it pick any available TCP port or is it always 3306?

Any help is appreciated!

Thanks!

share|improve this question

1 Answer 1

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.

share|improve this answer
    
Thanks for explaining! I am still not clear how the replication is builtin in MySQL. I know that there are two threads IO thread and SQL thread. Lets say slave has mysql server running on port 3306 and master 8200. When slave opens TCP connection for replication any ephemeral port 1024 - 65535 can get picked with 4 tuple being <slave ip, <1024 - 65535>, master ip, 8200>. When it gets the response how will MySQL Io thread process it if TCP connection was not established using 3306? –  Android_enthusiast Aug 23 '13 at 21:07
    
I've added some additional details on how the magic takes place internally. "Internally" being the key concept. –  Michael - sqlbot Aug 23 '13 at 21:33
    
ah, makes sense now. Thanks for explaining in detail. Much appreciated! –  Android_enthusiast Aug 23 '13 at 22:15
1  
Please consider marking this answer as accepted. –  Michael - sqlbot Aug 27 '13 at 12:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.