Mysql replication memo(2)

16.1. Replication Configuration

Mysql instance 在master 的設定下會把 update 和 change 的動作寫到 binary log 裡
The MySQL instance operating as the master (the source of the database changes) writes updates and changes as “events” to the binary log

在binary log 裡的資料會根據database OOXX存成不同的格式, slave 設定則會去拿master 的binary log 來放在local 讀
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

如果binary logging 的設定被enable 那麼所有的改變就會存在binary log 裡, 每個slave 都會完整個master binary log 備份, slave 會決定binary log裡的哪些動作要執行
The master is “dumb” in this scenario. Once binary logging has been enabled, all statements are recorded in the binary log. Each slave will receive a copy of the entire contents of the binary log. It is the responsibility of the slave to decide which statements in the binary log should be executed;

這樣的意思是說可以很多的slave 連 master , 執行相同binary log 的不同部份, 因為slave 可能會連不上slave 但是依然不會影響master 的操作, 因為slave 會去記得他跑到binary log 的位置, 所以slave 斷線之後, 可以從當初的位置繼續處理沒處理完的部份
This means that multiple slaves can be connected to the master and executing different parts of the same binary log. Because the slaves control this process, individual slaves can be connected and disconnected from the server without affecting the master's operation. Also, because each slave remembers the position within the binary log, it is possible for slaves to be disconnected, reconnect and then “catch up” by continuing from the recorded position.

master 和 slave 都必須設定一個unique ID (用 server-id 的參數), 此外slave 必須設定master host 的 name 和 log 的 name 和 位置, 這些細節可以透過CHANGE MASTER TO 做更改, 這些細節都存在master.info 裡
Both the master and each slave must be configured with a unique ID (using the server-id option). In addition, the slave must be configured with information about the master host name, log file name and position within that file. These details can be controlled from within a MySQL session using the CHANGE MASTER TO statement. The details are stored within the master.info file.

存在binary log 的Event 有不同的格式
Events in the binary log are recorded using a number of formats. These are referred to as statement-based replication (SBR) or row-based replication (RBR). A third type, mixed-format replication (MIXED), uses SBR or RBR replication automatically to take advantage of the benefits of both SBR and RBR formats when appropriate.