Mysql --主从复制_set sql_log_bin=0 ddl

0 MySQL支持三种日志复制方式：

1 基于语句的复制（也称为逻辑复制）
主要是指，在主数据库上执行的SQL语句，在从数据库上会重复执行一遍 。MySQL默认采用的就是这种复制，效率比较高。但是也是有一定的问题的，如果SQL中使用uuid()、rand()等函数，那么复制到从库的数据就会有偏差。

2 基于行的复制，指将更新处理后的数据复制到从数据库，而不是执行一边语句 。从MySQL5.1的版本才被支持。

3 混合复制，默认采用语句复制，当发现语句不能进行精准复制数据时（例如语句中含有uuid()、rand()等函数），采用基于行的复制 。

1 主从复制

得从mysql的数据库主从复制原理说起，mysql的主从复制都是单线程的操作，主库对所有DDL和DML产生binlog，binlog是顺序写，所以效率很高，slave的Slave_IO_Running线程到主库取日志，效率很比较高，下一步，问题来了，slave的Slave_SQL_Running线程将主库的DDL和DML操作在slave实施。

分为三步：
1：是在主库上记录二进制日志，首先主库要开启binlog日志记录功能，并授权Slave从库可以访问的权限。这里需要注意的一点就是binlog的日志里的顺序是按照事务提交的顺序来记录的而非每条语句的执行顺序。

2：从库将binLog复制到其本地的RelayLog中。首先从库会启动一个工作线程，称为I/O线程，I/O线程跟主库建立一个普通的客户端连接，然后主库上启动一个特殊的二进制转储（binlog dump）线程，此转储线程会读取binlog中的事件。当追赶上主库后，会进行休眠，直到主库通知有新的更新语句时才继续被唤醒。
这样通过从库上的I/O线程和主库上的binlog dump线程，就将binlog数据传输到从库上的relaylog中了。

3：从库中启动一个SQL线程，从relaylog中读取事件并在备库中执行，从而实现备库数据的更新。
这种复制架构实现了

2 3种同步模式

2.1 异步模式

MySQL的默认复制模式就是异步模式，主要是 指MySQL的主服务器上的I/O线程，将数据写到binlong中就直接返回给客户端数据更新成功，不考虑数据是否传输到从服务器，以及是否写入到relaylog中 。在这种模式下，复制数据其实是有风险的，一旦数据只写到了主库的binlog中还没来得及同步到从库时，就会造成数据的丢失。

但是这种模式确也是效率最高的，因为变更数据的功能都只是在主库中完成就可以了，从库复制数据不会影响到主库的写数据操作。

2.2 半同步模式

MySQL从 5.5 版本开始通过以插件的形式开始支持半同步的主从复制模式。什么是半同步主从复制模式呢？

异步复制模式 ：上面我们已经介绍了，异步复制模式，主库在执行完客户端提交的事务后，只要将执行逻辑写入到binlog后，就立即返回给客户端，并不关心从库是否执行成功，这样就会有一个隐患，就是在主库执行的binlog还没同步到从库时，主库挂了，这个时候从库就就会被强行提升为主库，这个时候就有可能造成数据丢失。

半同步复制模式，比异步模式提高了数据的可用性，但是也产生了一定的性能延迟，最少要一个TCP/IP连接的往返时间。
半同步复制模式，可以很明确的知道，在一个事务提交成功之后，此事务至少会存在于两个地方一个是主库一个是从库中的某一个。 主要原理是，在master的dump线程去通知从库时，增加了一个ACK机制，也就是会确认从库是否收到事务的标志码，master的dump线程不但要发送binlog到从库，还有负责接收slave的ACK。当出现异常时，Slave没有ACK事务，那么将自动降级为异步复制，直到异常修复后再自动变为半同步复制

2.3同步复制模式

当主库执行完客户端提交的事务后，需要等到所有从库也都执行完这一事务后，才返回给客户端执行成功。因为要等到所有从库都执行完，执行过程中会被阻塞，等待返回结果，所以性能上会有很严重的影响。半同步复制模式 ：半同步复制模式，可以说是介于异步和同步之间的一种复制模式，主库在执行完客户端提交的事务后，要等待至少一个从库接收到binlog并将数据写入到relay log中才返回给客户端成功结果。

3 主从复制延迟的原因

3.1 DML和DDL的IO操作是随即的，不是顺序的，成本高很多，

3.2 还可能可slave上的其他查询产生lock争用

3 由于Slave_SQL_Running也是单线程的，所以一个DDL卡主了，需要执行10分钟，那么所有之后的DDL会等待这个DDL执行完才会继续执行

4 库上那个相同的DDL也需要执行10分，为什么slave会延时？”，答案是master可以并发，Slave_SQL_Running线程却不可

5 主库DML请求频繁（tps较大）

即主库写请求较多，有大量insert、delete、update并发操作，短时间产生了大量的binlog。

【原因分析】
主库并发写入数据，而从库SQL Thread为单线程应用日志，很容易造成relaylog堆积，产生延迟。

【解决思路】
做sharding，通过scale out打散写请求。或考虑升级到MySQL 5.7+，开启基于逻辑时钟的并行复制

6 主库对大表执行DDL语句

现象和主库执行大事务相近。
检查Exec_Master_Log_Pos一直未动，也有可能是在执行DDL。
分析主库binlog，看主库当前执行的事务也可知晓。

【原因分析】
1、DDL未开始，被阻塞，SHOW SLAVE STATUS检查到Slave_SQL_Running_State为waiting for table metadata lock，且Exec_Master_Log_Pos不变。
2、DDL正在执行，SQL Thread单线程应用导致延迟增加。Slave_SQL_Running_State为altering table，Exec_Master_Log_Pos不变

【解决思路】
通过processlist或information_schema.innodb_trx来找到阻塞DDL语句的查询，干掉该查询，让DDL正常在从库执行。
DDL本身造成的延迟难以避免，建议考虑：
① 业务低峰期执行
② set sql_log_bin=0后，分别在主从库上手动执行DDL（此操作对于某些DDL操作会造成数据不一致，请务必严格测试）

7 主库与从库硬件配置不一致：

【原因分析】
硬件上：主库实例服务器使用SSD，而从库实例服务器使用普通SAS盘、cpu主频不一致等
配置上：如RAID卡写策略不一致，OS内核参数设置不一致，MySQL落盘策略不一致等

【解决思路】
尽量统一DB机器的配置（包括硬件及选项参数）
甚至对于某些OLAP业务，从库实例硬件配置高于主库等

8 表缺乏主键或唯一索引

binlog_format=row的情况下，如果表缺乏主键或唯一索引，在UPDATE、DELETE的时候可能会造成从库延迟骤增。
此时Slave_SQL_Running_State为Reading event from the relay log。
并且SHOW OPEN TABLES WHERE in_use=1的表一直存在。
Exec_Master_Log_Pos不变。
mysqld进程的cpu几近100%（无读业务时），io压力不大

【原因分析】
做个极端情况下的假设，主库更新一张500w表中的20w行数据，该update语句需要全表扫描
而row格式下，记录到binlog的为20w次update操作，此时SQL Thread重放将特别慢，每一次update可能需要进行一次全表扫描

【解决思路】
检查表结构，保证每个表都有显式自增主键，并建立合适索引。

9 从库自身压力过大

【原因分析】
从库执行大量select请求，或业务大部分select请求被路由到从库实例上，甚至大量OLAP业务，或者从库正在备份等。
此时可能造成cpu负载过高，io利用率过高等，导致SQL Thread应用过慢。产生的DDL数量超过slave一个sql线程所能承受的范围，那么延时就产生了，当然还有就是可能与slave的大型query语句产生了锁等待

【解决思路】
建立更多从库，打散读请求，降低现有从库实例的压力。

**10 MyISAM存储引擎

锁表 影响同步

11 网络延时

总结：

通过SHOW SLAVE STATUS与SHOW PROCESSLIST查看现在从库的情况。（顺便也可排除在从库备份时这种原因）
若Exec_Master_Log_Pos不变，考虑大事务、DDL、无主键，检查主库对应的binlog及position即可。
若Exec_Master_Log_Pos变化，延迟逐步增加，考虑从库机器负载，如io、cpu等，并考虑主库写操作与从库自身压力是否过大。

如果上述原因都没有，那么请教请教DBA大佬们吧。

当然，Seconds_Behind_Master也不一定准确，存在在少部分场景下，虽Seconds_Behind_Master为0，但主从数据不一致的情况