I'm new to MYSQL replication. I know how to set it up (thanks to many books and other online resources) however, I need to understand how it really works on the low level.
My question is, does it work in real time? Or is there a gap between changes on master and slave.
So the scenario is: a record is inserted into master, does it immediately get inserted into slave and then the insert is completed or, is it inserted into master and the insert is finished and then later the changes are replicated to slave?
The reason I am asking is because I know that there is a difference on the MS Sequel Server. On Sequel Server there is a concept called Mirroring where an query is executed first on master then on slave and then it completes. There is also replication where a query executes on master then a message is published to slave (usually extremely fast but not necessarily in real time) and the the slave (which subscribes to those messages) executes the same query.
MySQL has different mechanism of replication based on logs. Here is the part of MySQL official docs:
Replication between servers in MySQL is based on the binary logging mechanism. The MySQL instance operating as the master (the source of the database changes) writes updates and changes as “events” to the binary log. The information in the binary log is stored in different logging formats according to the database changes being recorded. Slaves are configured to read the binary log from the master and to execute the events in the binary log on the slave's local database.