mysql 主从详细介绍
Posted by wukaiqiang; tagged with none
原理：
Replication is based on the replication source server keeping track of all changes to its databases (updates, deletes, and so on) in its binary log. The binary log serves as a written record of all events that modify database structure or content (data) from the moment the server was started. Typically, SELECT statements are not recorded because they modify neither database structure nor content.
复制是基于源库BINLOG中记录的所有改变。
Each replica that connects to the source requests a copy of the binary log. That is, it pulls the data from the source, rather than the source pushing the data to the replica. The replica also executes the events from the binary log that it receives. This has the effect of repeating the original changes just as they were made on the source. Tables are created or their structure modified, and data is inserted, deleted, and updated according to the changes that were originally made on the source.
每个恢复连接进程请求一份源的BINARY LOG复制，这是备从源拉取，而不是源主动推送的，备考重做源库的所有操作。
Because each replica is independent, the replaying of the changes from the source's binary log occurs independently on each replica that is connected to the source. In addition, because each replica receives a copy of the binary log only by requesting it from the source, the replica is able to read and update the copy of the database at its own pace and can start and stop the replication process at will without affecting the ability to update to the latest database status on either the source or replica side.
由于恢复的I/O进程是从库主动发起的，主库和从库是独立的，主从不会影响主库和从库的正常运行。
复制方式（格式）
Replication works because events written to the binary log are read from the source and then processed on the replica. The events are recorded within the binary log in different formats according to the type of event. The different replication formats used correspond to the binary logging format used when the events were recorded in the source's binary log. The correlation between binary logging formats and the terms used during replication are:
一般情况用混合复制，特殊情况，可以根据需要指定基于行的或者语句的复制方式。
Advantages of statement-based replication
Disadvantages of statement-based replication
语句复制的优点
Proven technology.
成熟的技术
Less data written to log files. When updates or deletes affect many rows, this results in much less storage space required for log files. This also means that taking and restoring from backups can be accomplished more quickly.
当更新、删除多个行时，需要更少的空间写入日志文件，同时意味着更高效的备份和恢复
Log files contain all statements that made any changes, so they can be used to audit the database.
可以用于数据库审计
语句复制的缺点
Statements that are unsafe for SBR. Not all statements which modify data (such as INSERT DELETE, UPDATE, and REPLACE statements) can be replicated using statement-based replication. Any nondeterministic behavior is difficult to replicate when using statement-based replication. Examples of such Data Modification Language (DML) statements include the following:
不支持全部的sql语句
A statement that depends on a loadable function or stored program that is nondeterministic, since the value returned by such a function or stored program or depends on factors other than the parameters supplied to it. (Row-based replication, however, simply replicates the value returned by the function or stored program, so its effect on table rows and data is the same on both the source and replica.) See Section 16.4.1.16, “Replication of Invoked Features”, for more information.
例如函数和存储过程
DELETE and UPDATE statements that use a LIMIT clause without an ORDER BY are nondeterministic. See Section 16.4.1.17, “Replication and LIMIT”.
使用limit但未包含order by的语句
Deterministic loadable functions must be applied on the replicas.
复制到备库的确定性的函数
不支持的函数：
LOAD_FILE()
UUID(), UUID_SHORT()
USER()
FOUND_ROWS()
SYSDATE() (unless both the source and the replica are started with the --sysdate-is-now option)
GET_LOCK()
IS_FREE_LOCK()
IS_USED_LOCK()
MASTER_POS_WAIT()
RAND()
RELEASE_LOCK()
SLEEP()
VERSION()
语句使用基于sql的复制，失败的报错如下；
[Warning] Statement is not safe to log in statement format.INSERT ... SELECT requires a greater number of row-level locks than with row-based replication.
INSERT ... SELECT 比基于行的复制产生更多的行级锁
UPDATE statements that require a table scan (because no index is used in the WHERE clause) must lock a greater number of rows than with row-based replication.
没有索引的update 语句会产生大量行锁
For InnoDB: An INSERT statement that uses AUTO_INCREMENT blocks other nonconflicting INSERT statements.
innodb中，自增的insert会阻塞其他的insert
For complex statements, the statement must be evaluated and executed on the replica before the rows are updated or inserted. With row-based replication, the replica only has to modify the affected rows, not execute the full statement.
对于复杂语句，需要影响更多的行，而基于行的复制，只影响被修改的行。
Advantages of row-based replication
Disadvantages of row-based replication
行复制的缺点
https://www.processon.com/view/link/60e4f6d65653bb46e90ece0b
参考：https://dev.mysql.com/doc/refman/5.7/en/replication-implementation.html