【案例讨论】数据库中的“生死时速”MySQL主从同步加速度案例讨论！欢迎大家踊跃参加!

arron刘

MySQL的主从同步是一个很成熟的架构，优点为：①在从服务器可以执行查询工作(即我们常说的读功能)，降低主服务器压力;②在从主服务器进行备份，避免备份期间影响主服务器服务;③当主服务器出现问题时，可以切换到从服务器。

相信大家对于这些好处已经非常了解了，在项目的部署中也采用这种方案。但是MySQL的主从同步一直有从库延迟的问题，那么为什么会有这种问题。这种问题如何解决呢？

本期话题：1. MySQL数据库主从同步延迟原理。

              2. MySQL数据库主从同步延迟是怎么产生的。

              3. MySQL数据库主从同步延迟解决方案。

这次我们选取了来自阿里巴巴核心系统数据库组技术专家丁奇提供的MySQL主从同步加速度方案给大家做一个范例，希望大家说说自己的见解和看法。

案例分享：

一、问题起源

MySQL的主从同步一直有从库延迟的问题，背景资料网上很多，原因简单描述如下：

    1、 MySQL从库上有一个IO线程负责从主库取binlog到写到本地。另外有一个SQL线程负责执行这些本地日志，实现命令重放；

    2、 正常网络状况下IO线程没有性能问题（这个待会会用到），问题是SQL线程只有一个，更新速度跟不上。所以经常会看到从库的CPU idle很高，但同步性能就是上不去。

                              

二、方案雏形

单线程的SQL线程是造成这个问题的主要原因。比较直接的想法是把它改成多线程版本，这个据说官方版本开发中，其实我们也有一个这样的patch，但是直接写大片代码在线上提供服务的slave机器上这种事儿，都会因为担心稳定性而很难推动（写patch的和运维的同学，你们懂的）。所以打算用一个“第三方”工具中转，来实现多线程同步。基本结构如下：

  

说明：

1、这些transefer从master上各自同步一部分的数据，分别独立更新slave。多进程还是多线程均可。

2、Transfer与master之间异步更新日志，transfer与slve之间同步更新数据。

3、从这可以看出这个方案的缺点之一：更新能够被独立分开。比较直观的想法是，按照表分。

三、关于transfer

作为这个关键的转发工具transfer，需要提供如下功能：

1、能够指定同步master中的哪部分数据，并且能够方便地修改这个配置以应对master的加表需求；

2、支持stop slave、start slave。支持快速切换到新主库的change master命令。

3、能够记录读取点，transfer自己重启或master重启后能够按照记录点继续读后面的binlog；

4、能够记录分发点，transfer自己重启或slave重启后能够按照记录点继续同步给slave

用起来就会发现还有好多要求。。。

四、方案实现

Transfer的这么多功能，自己造轮子就累了。这里直接用MySQL来充当此角色。为了方便描述，下文还将之称为transfer。Transfer更新slave在功能上可以使用federated引擎，但由于其纠结的实现导致性能上达不到要求，因此在MySQL框架层中作了一点修改――读到同步日志后，直接发送给slave。

方案简单描述如下：

1、 Slave机器上搭另外的若干个MySQL(transfer)，将其设为Master的从库，且设置replicate-do-table, 每个transfer承担一部分的表。

2、 所有Transfer的更新目标都设置为slave，其更新方式是读到日志后直接mysql_real_query执行到slave上。

从这可以看出这个方案的缺点之二：只能支持statement格式的同步方式。其实row也能支持，后面再说。

五、仍然延迟？

在transfer放弃federated引擎改用直接发送后，性能提升不少，从库同步性能增加一倍，但从本文第一个图的数据对比就知道，延迟还很大。

发现这个时候slave的机器cpu已经很忙了，idle 20%一下――这个算是好消息，总比idle很高但性能上不去好。

实际上是因为每个transfer，虽然设置只同步其中的部分表，但在实现上是IO线程把master上的所有命令都备份到本地，然后在SQL线程执行的时候再判断，若不符合replicate-do-table，再放弃。

这样存在的问题，是n个transfer，磁盘写了n倍，更严重的是导致SQL线程空转。

我们上文提到整个流程中IO线程是比较空闲的，因此修改IO线程逻辑，在写入磁盘前先判断，若不符合本transfer的replicate-do-table设置，不写盘，直接放弃。

六、效果

从库的QPS由于线程切换会有抖动，但总的执行时间与主库相同。从库的cpu idle下降，与主库几乎同时恢复到100。

七、小结

描述完了，总结一下，方案的代价：

1、要求在slave机器上多配置n个transfer（是否在从库上均可）

2、目前只能支持statement的binlog格式，实际上row可以支持，方案定了，开发计划中。

3、跨表更新的语句，会按照其更新的第一个表，分发到唯一一个transfer，没有重复更新的问题，但有时序性问题。

方案的好处：

1、功能比较齐全。直接使用MySQL，原有的管理功能基本都能用，主库从库重启/换库的代价比较小。

2、开发量小，只在transfer上修改两处，不包括配置读取部分，300行以内

3、风险相对小。不直接修改master和slve上的代码，线上比较容易接收。

活动时间：2012.8.29—2012.9.10

活动奖励：针对以上任意一个问题跟帖回答，我们会在讨论结束后，随机抽选5名讨论最积极的会员赠送《SQL语言详解》一本。

内容介绍：《SQL语言详解》全面、深入地介绍了SQL的使用方法，主要包括关系型数据模型、关系代数、SQL简介、简单的SQL查询、从多个表中检索数据、高级查询操作、操作多行数据、数据修改、模式与用户、视图／临时表／公共表表达式及索引、保持设计更新、用户与访问权限、用户／会话及事务控制、编写和执行SQL例程与模块、嵌入式SQL、动态SQL、XML支持、对象一关系数据模型以及对象一关系支持。

本期中奖会员：chinafenghao duhouchen wjlcn mchdba asdf非

htyansp

真心不怎么懂MYSQL , 纯粹支持了

chinafenghao

1. MySQL数据库主从同步延迟原理。
要说延时原理，得从mysql的数据库主从复制原理说起，mysql的主从复制都是单线程的操作，
主库对所有DDL和DML产生binlog，binlog是顺序写，所以效率很高，slave的Slave_IO_Running线程到主库取日志，效率很比较高，下一步，问题来了，slave的Slave_SQL_Running线程将主库的DDL和DML操作在slave实施。DML和DDL的IO操作是随即的，不是顺序的，成本高很多，还可能可slave上的其他查询产生lock争用，由于Slave_SQL_Running也是单线程的，所以一个DDL卡主了，需要执行10分钟，那么所有之后的DDL会等待这个DDL执行完才会继续执行，这就导致了延时。有朋友会问：“主库上那个相同的DDL也需要执行10分，为什么slave会延时？”，答案是master可以并发，Slave_SQL_Running线程却不可以。

2. MySQL数据库主从同步延迟是怎么产生的。
当主库的TPS并发较高时，产生的DDL数量超过slave一个sql线程所能承受的范围，那么延时就产生了，当然还有就是可能与slave的大型query语句产生了锁等待。

3. MySQL数据库主从同步延迟解决方案。
丁奇的transefer是一个不错的方案，不过一般公司受限于对mysql的代码修改能力的限制和对mysql的掌控能力，还是不太适合。
最简单的减少slave同步延时的方案就是在架构上做优化，尽量让主库的DDL快速执行。还有就是主库是写，对数据安全性较高，比如sync_binlog=1，innodb_flush_log_at_trx_commit = 1 之类的设置，而slave则不需要这么高的数据安全，完全可以讲sync_binlog设置为0或者关闭binlog，innodb_flushlog也可以设置为0来提高sql的执行效率。另外就是使用比主库更好的硬件设备作为slave。
mysql-5.6.3已经支持了多线程的主从复制。原理和丁奇的类似，丁奇的是以表做多线程，oracle使用的是以数据库(schema)为单位做多线程，不同的库可以使用不同的复制线程。

duhouchen

MySQL的主从同步机制非常方便的解决了高并发读的应用需求，给Web方 面开发带来了极大的便利。但这种方式有个比较大的缺陷在于MySQL的同步机制是依赖Slave主动向Master发请求来获取数据的，而且由于服务器负 载、网络拥堵等方面的原因，Master与Slave之间的数据同步延迟是完全没有保证的。短在1秒内，长则几秒、几十秒甚至更长都有可能。
由于数据延迟问题的存在，当应用程序在Master上进行数据更新，然后又立刻需要从数据库中读取数据时，这时候如果应用程序从Slave上取数据(这也是当前Web开发的常规做法)，就可能出现读取不到期望的数据，造成程序运行异常。
解决这个问题有多种方式，比如最简单的在所有的insert和update之后，强制sleep几秒钟。这是非常粗鲁的方式，对于更新操作不是很高的中小型系统，此方式基本能解决问题。
另外一种方式是应用程序把被更新的数据保存在本机的内存(或者集中式缓存)中，如果在写入数据完成后需要直接读取数据，则从本机内存中读取。这种方式的缺点是极大的增加了应用程序的复杂度，而且可靠性并不能完全得到保障。
使用MySQL Proxy可以很方便的解决这个问题。MySQL Proxy是基于MySQL Client 和 MySQLServer之间的代理程序，能够完成对Client所发请求的监控、修改。从Client角度看，通过Proxy访问Server和直接访问Server没有任何区别。对于既有的程序而言，只要把直接被访问的Server的IP地址和端口号换成Proxy的IP地址和端口号就可以。
MySQL Proxy的工作原理也较简单。在Proxy启动时可以指定Proxy所需要使用的lua脚本，在lua脚本中预先实现6个方法：
• connect_server() // 接收到Client的连接请求时调用
• read_handshake() //
• read_auth() // 读取Client的认证信息时调用
• read_auth_result() // 读取认证结果时调用
• read_query() // 读取Client的query请求时调用
• read_query_result() //读取query结果时调用
当Proxy接收到Client请求时，在请求的不同的阶段会调用上面的不同方法。这样Proxy使用者就可以根据自己的业务需求，自由的实现这6个方法达到目的。
通过在read_query()中加入代码，我们可以截取出当前的请求是insert、update还是select，然后把insert和update请求发送到Master中，把select请求发送到Slave中，这样就解决了读写分离的问题。
在解决了读写分离后，如何解决同步延迟呢？
方法是在Master上增加一个自增表，这个表仅含有1个的字段。当Master接收到任何数据更新的请求时，均会触发这个触发器，该触发器更新自增表中的记录。如下图所示：

由于Count_table也参与Mysq的主从同步，因此在Master上作的Update更新也会同步到Slave上。当Client通过Proxy进行数据读取时，Proxy可以先向Master和Slave的Count_table表发送查询请求，当二者的数据相同时，Proxy可以认定Master和Slave的数据状态是一致的，然后把select请求发送到Slave服务器上，否则就发送到Master上。如下图所示：

通 过这种方式，就可以比较完美的结果MySQL的同步延迟不可控问题。之所以所“比较完美”，是因为这种方案double了查询请求，对Master和 Slave构成了额外的压力。不过由于Proxy与真实的Mysql Server采用连接池的方式连接，因此额外的压力还是可以接受的。

asdf非

1.出现错误提示
Slave I/O: error connecting to master 'backup@192.168.1.x:3306' - retry-time: 60  retries: 86400, Error_code: 1045

解决方法

从服务器上删除掉所有的二进制日志文件，包括一个数据目录下的master.info文件和hostname-relay-bin开头的文件。

master.info:：记录了Mysql主服务器上的日志文件和记录位置、连接的密码。

2、出现错误
Error reading packet from server: File '/home/mysql/mysqlLog/log.000001' not found (Errcode: 2) ( server_errno=29)
解决方案：

由于主服务器运行了一段时间，产生了二进制文件，而slave是从log.000001开始读取的，删除主机二进制文件，包括log.index文件。

3、错误提示如下

Slave SQL: Error 'Table 'xxxx' doesn't exist' on query. Default database: 't591'. Query: 'INSERT INTO `xxxx`(type,post_id,browsenum) SELECT type,post_id,browsenum FROM xxxx WHERE hitdate='20090209'', Error_code: 1146

解决方法

由于slave没有此table表，添加这个表，使用slave start 就可以继续同步。

4、错误提示如下
Error 'Duplicate entry '1' for key 1' on query. Default database: 'movivi1'. Query: 'INSERT INTO `v1vid0_user_samename` VALUES(null,1,'123','11','4545','123')'Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1' on query. Default database: 'club'. Query: 'INSERT INTO club.point_process ( GIVEID, GETID, POINT, CREATETIME, DEMO ) VALUES ( 0, 4971112, 5, '2010-12-19 16:29:28','

1 row in set (0.00 sec)

Mysql > Slave status\G;

显示：Slave_SQL_Running 为 NO

解决方法：

Mysql > stop slave;

Mysql > set global sql_slave_skip_counter =1 ;

Mysql > start slave;

5、错误提示如下

# show slave status\G;
Master_Log_File: mysql-bin.000029

Read_Master_Log_Pos: 3154083

Relay_Log_File: c7-relay-bin.000178

Relay_Log_Pos: 633

Relay_Master_Log_File: mysql-bin.000025

Slave_IO_Running: Yes

Slave_SQL_Running: No

Replicate_Do_DB: club

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 1594

Last_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.

Skip_Counter: 0

Exec_Master_Log_Pos: 1010663436

这个问题原因是，主数据库突然停止或问题终止，更改了mysql-bin.xxx日志，slave服务器找不到这个文件，需要找到同步的点和日志文件，然后chage master即可。

解决方法：
change master to

master_host='211.103.156.198',

master_user='同步帐号',

master_password='同步密码',

master_port=3306,

master_log_file='mysql-bin.000025',

master_log_pos=1010663436;

6、错误提示如下
Error 'Unknown column 'qdir' in 'field list'' on query. Default database: 'club'. Query: 'insert into club.question_del (id, pid, ques_name, givepoint, title, subject, subject_pid, createtime, approve, did, status, intime, order_d, endtime,banzhu_uid,banzhu_uname,del_cause,qdir) select id, pid, ques_name, givepoint, title, subject, subject_pid, createtime, approve, did, status, intime, order_d, endtime,'1521859','admin0523','无意义回复',qdir from club.question where id=7330212'

1 row in set (0.00 sec)

这个错误就说club.question_del 表里面没有qdir这个字段 造成的加上就可以了~！

在主的mysql ： 里面查询 Desc club.question_del；

在 错误的从服务器上执行 ： alter table question_del add qdir varchar(30) not null;

vikkll

chinafenghao 发表于 2012-8-29 09:36
1. MySQL数据库主从同步延迟原理。
要说延时原理，得从mysql的数据库主从复制原理说起，mysql的主从复制都 ...

你好，问一下你的mysql主从同步中show master status，结果为空时怎么解决的？
#Master Config
server-id=1
log-bin=mysql-bin
binlog-do-db=test
binlog-ignore-db=mysql
read-only=0

========
这是添加的配置

老头乐

主库对所有DDL和DML产生binlog，binlog是顺序写，所以效率很高，slave的Slave_IO_Running线程到主库取日志，效率很比较高，下一步，问题来了，slave的Slave_SQL_Running线程将主库的DDL和DML操作在slave实施。DML和DDL的IO操作是随即的，不是顺序的，成本高很多，还可能可slave上的其他查询产生lock争用，由于Slave_SQL_Running也是单线程的，所以一个DDL卡主了，需要执行10分钟，那么所有之后的DDL会等待这个DDL执行完才会继续执行，这就导致了延时。

chinafenghao

vikkll 发表于 2012-8-29 09:55
你好，问一下你的mysql主从同步中show master status，结果为空时怎么解决的？
#Master Config
server- ...

抱歉，从来没有遇见过。
只要开启了binlog，就能show出结果，请检查你的binlog是否正常。

丫～头逨逨逨

看不懂 支持一下

眼泪Angell

由于数据延迟问题的存在，当应用程序在Master上进行数据更新，然后又立刻需要从数据库中读取数据时，这时候如果应用程序从Slave上取数据(这也是当前Web开发的常规做法)，就可能出现读取不到期望的数据，造成程序运行异常。
解决这个问题有多种方式，比如最简单的在所有的insert和update之后，强制sleep几秒钟。这是非常粗鲁的方式，对于更新操作不是很高的中小型系统，此方式基本能解决问题。
另外一种方式是应用程序把被更新的数据保存在本机的内存(或者集中式缓存)中，如果在写入数据完成后需要直接读取数据，则从本机内存中读取。这种方式的缺点是极大的增加了应用程序的复杂度，而且可靠性并不能完全得到保障。