mysql从库比主库数据多_MySQL常见的主从复制错误有哪些？如何处理？

目录

想把MySQL有变更的数据分发到其他异构数据库，都有什么可选方案

MySQL什么情况下DML操作不会记录binlog？

MySQL中ANALYZE TABLE的作用是？生产上操作会有什么风险？

MySQL生产中上亿数据大表怎么在线增加一个字段并设定默认值？

MySQL表中有自增列id，但是表中的id列数据不连续，产生的可能原因是什么？

如何定位造成MySQL慢的罪魁祸首慢SQL及分析优化思路？

MySQL中InnoDB表自增主键如何从0开始？

MySQL常见的主从复制错误有哪些？如何处理？

Redis中如何发现并优化生产环境的big key？

一、想把MySQL有变更的数据分发到其他异构数据库，都有什么可选方案

(一)触发器方式(不推荐)

触发器处理的方式，在表上建立触发器，将增删改动作捕捉到增量表

(二)基于时间戳

表上如果有更新时间/插入时间的时间戳，可以根据时间戳捕捉增量数据

(三)基于binlog方式(推荐)

MySQL目前比较好也是目前业务用的比较多的增量方案都是基于binlog实现

1、在MySQL提交的DML操作正常情况都会记录到binlog(也是主从复制的基础)，首先上游通过解析binlog获取到增量日志

2、将解析到的更新事件存入消息队列，如kafka/MQ等

3、根据下游数据库种类，分别用不同的消费组件进行队列消费，拼接成SQL进行数据更新

(四)业界典型的方案

开源方案

1、阿里巴巴开源组件canal+kafka+自写消费组件

2、阿里巴巴开源组件otter(支持MySQL到MySQL的数据同步)

3、阿里巴巴开源迁移工具yugong(支持Oracle->Oracle/Oracle->MySQL的全量/增量迁移)

4、linkedin开源组件databus

5、美团点评开源组件puma

商业方案

各云厂商提供的DTS方案

二、MySQL什么情况下DML操作不会记录binlog？

以下情况可能会造成DML不会记录binlog：

1、并未开启binlog

2、在执行DML之前set sql_log_bin=0;

3、DML操作提交后，未完成binlog写入，此时MySQL crash，MySQL重新启动后，会回滚该操作

4、DML操作提交后，由于sync_binlog配置为非0，MySQL服务器down机导致binlog并未持久化

5、主库提交事务，同步到从库，但从库并未开启log_slave_updates，也无法记录binlog

6、DML操作在temporary表(非memory引擎)上，由于是会话级的数据变更，并不记录binlog

7、update/delete等DML并没有达到数据更新

8、其他原因如：binlog文件权限、文件句柄、inode耗尽、磁盘空间满等原因导致binlog无法写入

三、MySQL中ANALYZE TABLE的作用是？生产上操作会有什么风险？

(一)ANALYZE TABLE的作用

1、ANALYZE TABLE 会统计索引分布信息

2、对于 MyISAM 表，相当于执行了一次myisamchk --analyze

3、支持 InnoDB、NDB、MyISAM 等存储引擎，但不支持视图(view)

4、执行 ANALYZE TABLE 时，会对表加上读锁(read lock)

5、该操作会记录binlog

(二)生产上操作的风险

1、ANALYZE TABLE的需要扫描的page代价粗略估算公式：sample_pages 索引数 表分区数

2、因此，索引数量较多，或者表分区数量较多时，执行ANALYZE TABLE可能会比较费时，要自己评估代价，并默认只在负载低谷时执行

3、特别提醒，如果某个表上当前有慢SQL，此时该表又执行ANALYZE TABLE，则该表后续的查询均会处于waiting for table flush的状态，严重的话会影响业务，因此执行前必须先检查有无慢查询

备注：上面多次提到MyISAM，仅是本次总结所需，并不推荐大家使用MyISAM引擎，使用InnoDB才是正道

四、MySQL生产中上亿数据大表怎么在线增加一个字段并设定默认值？

(一)MySQL8.0版本

如果满足以下条件：

1、不是压缩表

2、不是data dictionary tablespace

3、不是全文索引表

4、不是临时表

5、新增列在最后位置

满足以上条件，可以通过instant方式完成快速加列，即修改metadata信息，代价非常小，秒级完成

(二)MySQL5.6、MySQL5.7

1、可以这么说，虽然支持inplace(不阻塞同时在跑的DML)，但还需要rebuild表，代价可能较大并且耗时可能会非常长，也会造成非常严重的主从延迟；

2、因此生产环境还是推荐使用pt-osc/gh-ost等第三方工具进行在线加列；

3、需要注意的是pt-osc会建触发器，如果原先表上本身就有触发器就不能使用了

4、另外需要注意的是通过第三方工具加列时需要考虑到空间占用问题(影子表及加列过程中产生的binlog)

5、第三方工具加列同样可能造成主从延迟，因此注意控制速率

五、MySQL表中有自增列id，但是表中的id列数据不连续，产生的可能原因是什么？

可能的情况有如下几种：

1、虽然表定义了自增主键，但是插入时从全局ID生成器获取ID，导致每个表分配到的ID并不是连续的

2、表中数据进行过删除

3、session A插入了数据，获取了自增id为10，session B也插入了数据，但session A回滚了，导致id=10的空洞

4、设置了innodb_autoinc_lock_mode=2，高并发或批量插入的情况下导致自增ID不连续

5、手动调整过AUTO_INCREMENT，导致中间有空洞

6、设置步长非1，导致每次自增并不是加1

PS:8.0以前修改自增值重启后会丢失，务必注意一下

六、如何定位造成MySQL慢的罪魁祸首慢SQL及分析优化思路？

(一)首先需要打开慢查询

1、设置slow_query_log = 1打开慢查询

2、通过设置long_query_time调整慢查询SQL的阈值

3、通过设置min_examined_row_limit记录慢查询的最小扫描行数

(二)其次关注慢查询记录中的重要关注点

1、Query_time：慢查询的实际查询时间

2、Rows_sent：发送给客户端的行数

3、Rows_examined：扫描的行数

4、最后需要关注具体的sql及对应的执行计划

(三)执行计划需要关注的部分可以参考

(四)慢SQL分析需要的元素

1、表结构信息：show create table

2、数据量信息：select count(*) from table/show table status like 'xxx';/information_schema.tables

3、索引统计信息：show index from

4、执行计划：EXPALIN select * from t

(五)主要的优化手段有如下：

1、SQL语句的改写，优化不良语句

2、符合业务场景需求的索引建立

3、表结构上对列属性、表结构的调整

4、MySQL参数的调整

5、和开发应用"撕"性能

(六)最后有一些注意点：

1、实战优化很难一步到位，模拟测试环境很重要(不要随便在生产环境测试)

2、业务至上，数据至上，而后才是优化

3、衡量得失，索引也有维护成本，不是越多越好

4、没有对比就没有成就感，记得留存"胖胖"的SQL，形成优化报告

5、防范于未然，不要等到问题严重了才来做优化

七、MySQL中InnoDB表自增主键如何从0开始？

1、实际上，MySQL无法真正设置InnoDB表AUTO_INCREMENT=0

2、但是我们可以人为修改SQL_MODE='NO_AUTO_VALUE_ON_ZERO'

3、指定自增列插入0值，如insert into t(id,name) values(0,'zhangsan');

另外有几点说明：

1、尽管MySQL允许设置SQL_MODE为NO_AUTO_VALUE_ON_ZERO并插入0值，但是还是不建议这样做，例如主从SQL_MODE不一致可能造成数据不一致

2、8.0以后AUTO_INCREMENT支持持久化，妈妈再也不用担心自增值丢啦

八、MySQL常见的主从复制错误有哪些？如何处理？

(一)1032错误

1032错误即从库比主库少数据，分为UPDATE场景和DELETE场景

UPDATE场景处理思路是：

1、主要处理思路是将丢失的数据在从库补上

2、找到sql_thread停止位置(Relay_Master_Log_File + Exec_Master_Log_Pos)

3、去主库解析对应位置binlog，获取到UPDATE相关的数据

4、在slave上插入数据

5、重启sql_thread

DELETE场景处理思路：

1、主要的处理思路就是跳过该事务

2、非GTID环境下通过设置sql_slave_skip_counter=1跳过该事务

3、GTID环境下通过注入空事务方式跳过该事务，简要步骤如下：

3.1、set gtid_next='xxx';

3.2、begin;commit;

3.3、set gtid_next='AUTOMATIC';

3.4、详细可参考：http://imysql.com/2014/07/31/mysql-faq-exception-replication-with-gtid.shtml

4、重启sql_thread

(二)1062错误

1062错误即从库比主库多数据(主键冲突情况)

处理思路比较简单，即删除对应的主键冲突数据即可：

1、删除从库上对应的数据(根据主键)

2、重启sql_thread

(三)1236错误

1236错误即从库需要开始同步的binlog位置已经不存在主库上，该错误出现分几种情况

1、GTID从库需要开始复制的GTID要比主库上已经purged GTID位置要小

2、GTID从库GTID_SET比主库上缺失部分(如主库有多个GTID_SET，而从库只有一个)

3、非GTID环境下，从库去拉binlog的时间找不到对应的binlog(即从库当前的binlog比主库最旧的binlog之间存在缺失)

那么对应的处理方式是：

1、对于1、3两种情况，建议是直接通过备份的方式直接重新做主从

2、对于第2种情况，可以通过set global gtid_purged='xxx'的方式将差异的GTID_SET部分补全

九、Redis中如何发现并优化生产环境的big key？

Redis中的大key一直是重点需要优化的对象，big key既占用比较多的内存，也可能占用比较多的网卡资源，造成redis阻塞，因此我们需要找到这些big key进行优化

(一)寻找big key

通常来说找到redis中的big key有如下几种方法

1、redis-cli自带--bigkeys，例如：redis-cli -h -a --bigkeys

2、获取生产Redis的rdb文件，通过rdbtools分析rdb生成csv文件，再导入MySQL或其他数据库中进行分析统计，根据size_in_bytes统计bigkey

3、通过python脚本，迭代scan key，每次scan 1000，对扫描出来的key进行类型判断，例如：string长度大于10K，list长度大于10240认为是big bigkeys

4、其他第三方工具，例如：redis-rdb-cli

(二)优化big key

1、优化big key的原则就是string减少字符串长度，list、hash、set、zset等减少成员数

2、以hash类型举例来说，对于field过多的场景，可以根据field进行hash取模，生成一个新的key，例如原来的

hash_key:{filed1:value, filed2:value, filed3:value ...}，可以hash取模后形成如下key:value形式

hash_key:mod1:{filed1:value}

hash_key:mod2:{filed2:value}

hash_key:mod3:{filed3:value}

取模后，将原先单个key分成多个key，每个key filed个数为原先的1/N

3、string类型的big key，如文章正文，建议不要存入redis，用文档型数据库MongoDB代替或者直接缓存到CDN上等方式优化

有任何问题都可以加微信讨论，欢迎沟通~~ 互相进步！

微信：lvqingshan_