mysql 主从复制错误

Posted by wukaiqiang; tagged with none

Replica Errors During Replication

If a statement produces the same error (identical error code) on both the source and the replica, the error is logged, but replication continues.
主从都出现相同的错误，同步继续

If a statement produces different errors on the source and the replica, the replication SQL thread terminates, and the replica writes a message to its error log and waits for the database administrator to decide what to do about the error. This includes the case that a statement produces an error on the source or the replica, but not both. To address the issue, connect to the replica manually and determine the cause of the problem. SHOW SLAVE STATUS is useful for this. Then fix the problem and run START SLAVE. For example, you might need to create a nonexistent table before you can start the replica again.

主从产生不同的错误，复制语句中断，写入错误日志并等待管理员操作，错误在主或者从产生，需要手动连接到备库并处理。 查看状态SHOW SLAVE STATUS；启动 START SLAVE

If a temporary error is recorded in the replica's error log, you do not necessarily have to take any action suggested in the quoted error message. Temporary errors should be handled by the client retrying the transaction. For example, if the replication SQL thread records a temporary error relating to a deadlock, you do not need to restart the transaction manually on the replica, unless the replication SQL thread subsequently terminates with a nontemporary error message.

临时错误不需要处理，例如死锁

If this error code validation behavior is not desirable, some or all errors can be masked out (ignored) with the --slave-skip-errors option.
如果不需要验证数据，可以使用--slave-skip-errors

For nontransactional storage engines such as MyISAM, it is possible to have a statement that only partially updates a table and returns an error code. This can happen, for example, on a multiple-row insert that has one row violating a key constraint, or if a long update statement is killed after updating some of the rows. If that happens on the source, the replica expects execution of the statement to result in the same error code. If it does not, the replication SQL thread stops as described previously.
非事务数据库中， 如果有sql导致错误，主从错误不一致，会停止同步进程。

If you are replicating between tables that use different storage engines on the source and replica, keep in mind that the same statement might produce a different error when run against one version of the table, but not the other, or might cause an error for one version of the table, but not the other. For example, since MyISAM ignores foreign key constraints, an INSERT or UPDATE statement accessing an InnoDB table on the source might cause a foreign key violation but the same statement performed on a MyISAM version of the same table on the replica would produce no such error, causing replication to stop.

主从表用不同的存储引擎，可能导致主从停止
Stored functions execute with the same NOW() value as the calling statement. However, this is not true of stored procedures.
存储函数now（） 调用相同的语句，但是存储过程不会。
Deterministic loadable functions must be applied on the replicas.
确定性函数

Table definitions must be (nearly) identical on source and replica. See Section 16.4.1.10, “Replication with Differing Table Definitions on Source and Replica”, for more information.
主从表要一样

Advantages of row-based replication
All changes can be replicated. This is the safest form of replication.
所有更改都能复制

Statements that update the information in the mysql system database, such as GRANT, REVOKE and the manipulation of triggers, stored routines (including stored procedures), and views, are all replicated to replicas using statement-based replication.

For statements such as CREATE TABLE ... SELECT, a CREATE statement is generated from the table definition and replicated using statement-based format, while the row insertions are replicated using row-based format.
DDL 主要从语句复制、DML主要是行复制
Fewer row locks are required on the source, which thus achieves higher concurrency, for the following types of statements:
以下语句实现高并发产生更少的行锁
INSERT ... SELECT

INSERT statements with AUTO_INCREMENT

UPDATE or DELETE statements with WHERE clauses that do not use keys or do not change most of the examined rows.
Fewer row locks are required on the replica for any INSERT, UPDATE, or DELETE statement.

Disadvantages of row-based replication
行复制的缺点
RBR can generate more data that must be logged. To replicate a DML statement (such as an UPDATE or DELETE statement), statement-based replication writes only the statement to the binary log. By contrast, row-based replication writes each changed row to the binary log. If the statement changes many rows, row-based replication may write significantly more data to the binary log; this is true even for statements that are rolled back. This also means that making and restoring a backup can require more time. In addition, the binary log is locked for a longer time to write the data, which may cause concurrency problems. Use binlog_row_image=minimal to reduce the disadvantage considerably.
大量数据操作的DML语句会产生更多的BINLOG，备份和恢复需要更多的时间,锁定BINLOG的时间更长，无法写入数据，可能影响并发性能，binlog_row_image=minimal 可以大大减少缺点。但是生产系统上不建议使用，原因有2，一是大表更新过程中取消会有问题，闪回等功能无法使用。因为该参数省略了修改前结果的保存。