Difference between different Mysql master-slave formats (ReplicationFormats) _ MySQL

Difference between different Mysql master/slave formats (ReplicationFormats) bitsCN.com

The reason why Mysql can synchronize between master and slave servers is that the events (events) on the master server are written to the binary log, and then these events are executed again on the slave server.

Events are written to BINLOG in the following formats:

1. Statement-Based Replication)
It is to record the statements executed on the master server and execute them again on the slave server. This is the only method before MYSQL 5.1.4;

Advantages of this type include:

A. since MYSQL 3.23, only this type is available, so this type has been proven by practice;

B. less data is written into the BINLOG. in particular, statements that update many rows do not need to record changes in each row;

C. all statements for changing the database will be recorded and can be used to supervise the database.

The disadvantage of this type is also obvious. it is a non-secure record method:

A. The row cannot be updated without LIMIT. for example, the UPDATE statement contains a limit without ORDER;

B,

LOAD_FILE ()
UUID (), UUID_SHORT ()
USER ()
FOUND_ROWS ()
SYSDATE () (unless both the master and the slave are started with the -- sysdate-is-now option)
GET_LOCK ()
IS_FREE_LOCK ()
IS_USED_LOCK ()
MASTER_POS_WAIT ()
RAND ()
RELEASE_LOCK ()
SLEEP ()
VERSION ()

For example, to synchronize this STATEMENT using STATEMENT-based replication:

update table set column=uuid() limit 2

After the update, the value of this field on the master server is as follows:

45734358-9df1-11e3-b0fd-782bcb74f37e

Slave server value:

3fa4171e-9df0-11e3-a0e5-782bcb74f37e

C. More row-level locks are required than row-based locks;

2. Row-Based Replication)

Is to record the changes made to each row on the master server, and then implement them on the slave server;

Advantages of this type:

A. It is A secure method, that is, the content of the master server can be accurately synchronized. for example, the result of the previous example using RBR (ROW-based replication:

The master server and slave server are both:

B290f3a0-9df5-11e3-b0fd-782bcb74f37e

B. The number of rows to be locked is small.

The disadvantage of this type is:

A. too many records are required. if A statement updates tens of millions of data records, tens of millions of binlogs will be recorded ......

B. If some generated variables are huge, such as the BLOB type, the records will be huge.

C. unable to know which statement is being executed, and unable to monitor the master-slave progress

3. Row-Based Replication)

These two types are available. The system selects the appropriate type in real time to record the records of the master server.

BitsCN.com