mysql主从同步默认延迟_如何解决主从数据库同步延迟问题？

题主说的方案感觉非常不靠谱。

不过mysql-proxy本人也几乎没怎么接触，它能否实现上诉功能有些不大确定，即使它有，也不建议为了这个就用它，官网自己都不推荐用到生产环境。

针对主从延迟，本人的经验如下：

业务量不大的主库能处理业务就全放在主库吧，从库只做灾备，备份，对实时性要求不高的统计报表类工作；

已经出现延迟的一般来说，就慢慢等吧，试图通过重启db之类的操作是无法解决的，还会因为大事务回滚再重做导致花的时间更长。

延迟N天无法解决的那就重做slave。

为什么会延迟N天，难道仅仅是因为从库单线程吗？

我感觉大部分都是主库上采用mixed的binlog_format，由于某种限制，无法基于statement，只好row模式复制。

那么如果当前sql是全表扫描，传到slave上执行时就是茫茫多次的全表扫描了。

下面是官网给出的在mixed下，自动从statement转换成row模式的条件

When running in MIXED logging format, the server automatically switches from statement-based to rowbased logging under the following conditions:

• When a function contains UUID().

• When one or more tables withAUTO_INCREMENT columns are updated and a trigger or stored

function is invoked. Like all other unsafe statements, this generates a warning ifbinlog_format =

STATEMENT.

• When the body of a view requires row-based replication, the statement creating the view also uses it. For

example, this occurs when the statement creating a view uses theUUID() function.

• When a call to a UDF is involved.

• When any INSERT DELAYED is executed for a nontransactional table.

• If a statement is logged by row and the session that executed the statement has any temporary tables,

logging by row is used for all subsequent statements (except for those accessing temporary tables) until

all temporary tables in use by that session are dropped.

This is true whether or not any temporary tables are actually logged.

Temporary tables cannot be logged using row-based format; thus, once row-based logging is used, all

subsequent statements using that table are unsafe. The server approximates this condition by treating

all statements executed during the session as unsafe until the session no longer holds any temporary

tables.

• When FOUND_ROWS() or ROW_COUNT() is used. (Bug #12092, Bug #30244)

• When USER(), CURRENT_USER(), or CURRENT_USER is used. (Bug #28086)

• When a statement refers to one or more system variables. (Bug #31168)

Exception.The following system variables, when used with session scope (only), do not cause the

logging format to switch:

• auto_increment_increment

• auto_increment_offset

• character_set_client

• character_set_connection

• character_set_database

• character_set_server

• collation_connection

• collation_database

• collation_server

• foreign_key_checks

• identity

• last_insert_id

• lc_time_names

• pseudo_thread_id

• sql_auto_is_null

• time_zone

• timestamp

• unique_checks

For information about determining system variable scope, seeSection 5.1.5, “Using System Variables”.

For information about how replication treats sql_mode, see Section 17.4.1.34, “Replication and

Variables”.

• When one of the tables involved is a log table in themysql database.

• When the LOAD_FILE() function is used. (Bug #39701)一般来说在slave上show proceslist看查看当前的system user正在执行什么，那就是问题SQL。如果pos点一直不动，也可以去主库对应的binlog上查看下执行的是什么玩意。

出现延迟时，查看下当前slave的cpu和磁盘状况一般来说如果从库没有其他业务，单线程的原因，cpu跑满一个核已经是极限了。磁盘io满的话，确认下是否有其他进程或mysql线程影响了它(比如从库正在dump或者超大的sql在执行)，也可以尝试调整下slave上关于io的几个参数

从库raid卡，务必设置成write back的写策略这点本人深受其害，查了几个月才发现为什么我的SSD io性能这么烂。

批量的dml操作批量的dml操作如果不做处理，一般必然会出现延迟，建议业务低峰期执行，并将批量操作做下调整，一次dml 10000行，sleep一会，再dml 10000行。

具体的行数和sleep需要自己根据业务确定，能保证从库不延迟就好。

一点别的tips：如果还是经常性的短时间延迟，那就尝试加大从库的硬件配置，比如上sata SSD，pcie等

延迟的监控到位，可通过pt-heart-beat来准确监控延迟值，及时发现查看。

5.5以后版本的，可以考虑采用半同步复制，能解决少量延迟引起的问题，不过对tps性能损耗较大

升级到mysql 5.7吧，多线程复制，几乎完美解决单线程复制引起的从库延迟。

给出几个同步延迟的case，平时做的小记录故障案例：slave延迟很大