mysql主从同步(2)-问题梳理

之前部署了Mysql主从复制环境(Mysql主从同步(1)-主从/主主环境部署梳理),在mysql同步过程中会出现很多问题,导致数据同步异常。 以下梳理了几种主从同步中可能存在的问题: 1)slave运行过慢不能与master同步,也就是MySQL数据库主从同步延迟 MySQL数据库slave服务器延迟的现象是非常普遍的,MySQL复制允许从机进行SELECT操作,但是在实际线上环境下,由于从机延迟的关系,很难将读取操作转向到从机。这就导致了有了以下一些潜规则:“实时性要求不高的读取操作可以放到slave服务器,实时性要求高的读取操作放到master服务器”,“从机仅能做前一天的统计类查询”。 slave滞后即slave不能快速执行来自于master的所有事件,从而不能避免更新slave数据延迟。 mysql的master-slave架构中master仅做写入、更新、删除操作,slave做select操作。造成slave滞后的原因有很多。 slave同步延迟的原理 MySQL的主从复制都是单线程的操作,主库对所有DDL和DML产生的日志写进binlog,由于binlog是顺序写,所以效率很高。 Slave的IO Thread线程从主库中bin log中读取取日志。 Slave的SQL Thread线程将主库的DDL和DML操作事件在slave中重放。DML和DDL的IO操作是随即的,不是顺序的,成本高很多。 由于SQL Thread也是单线程的,如果slave上的其他查询产生lock争用,又或者一个DML语句(大事务、大查询)执行了几分钟卡住了,那么所有之后的DML会等待这个DML执行完才会继续执行,这就导致了延时。也许有人会质疑:主库上那个相同的DDL也会执行几分钟,为什么slave会延时?原因是master可以并发执行,而Slave_SQL_Running线程却不可以。 slave同步延迟的可能原因     1--slave的I/O线程推迟读取日志中的事件信息;最常见原因是slave是在单线程中执行所有事务,而master有很多线程可以并行执行事务。     2--带来低效连接的长查询、磁盘读取的I/O限制、锁竞争和innodb线程同步启动等。     3--Master负载;Slave负载     4--网络延迟     5--机器配置(cpu、内存、硬盘) (主从同步延迟怎么产生的?)总之,当主库的TPS并发较高时,产生的DDL数量超过slave一个sql线程所能处理的承受范围时,主从同步就会产生延时;或者当slave中有大型query语句产生了锁等待也会产生延时。 如何查看同步延迟     1--可以通过比对master、slave上的日志位置     2--通过"show slave status"查看Seconds_Behind_Master的值,这个值代表主从同步延迟的时间,值越大说明延迟越严重。值为0为正常情况,正值表示已经出现延迟,数字越大从库落后主库越多。     3--使用percona-toolkit的pt-hearbeat工具进行查看。 减少同步延迟的操作方案     1--减少锁竞争 如果查询导致大量的表锁定,需要考虑重构查询语句,尽量避免过多的锁。     2--负载均衡 搭建多少slave,并且使用lvs或nginx进行查询负载均衡,可以减少每个slave执行查询的次数和时间,从而将更多的时间用于去处理主从同步。     3--salve较高的机器配置     4--Slave调整参数 为了保障较高的数据安全性,配置sync_binlog=1,innodb_flush_log_at_trx_commit=1等设置。而Slave可以关闭binlog,innodb_flush_log_at_trx_commit也可以设置为0来提高sql的执行效率(这两个参数很管用) 5--并行复制 即有单线程的复制改成多线程复制。 从库有两个线程与复制相关:io_thread 负责从主库拿binlog并写到relaylog, sql_thread 负责读relaylog并执行。 多线程的思路就是把sql_thread 变成分发线程,然后由一组worker_thread来负责执行。 几乎所有的并行复制都是这个思路,有不同的,便是sql_thread 的分发策略。 MySQL5.7的真正并行复制enhanced multi-threaded slave(MTS)很好的解决了主从同步复制的延迟问题。

2)slave同步状态中出现Slave_IO_Running: NO 报错:Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'

原因1:清理数据导致主从库不同步(前提是主库的binlog日志没有被暴力删除或错误删除,即要确保正在使用的那个最新binlog文件在master主库机器上存在)。
解决办法:
1)先进入slave中执行:"slave stop;"来停止从库同步;
2)再去master中执行:"flush logs;"来清空日志;
3)然后在master中执行:"show master status;"查看下主库的状态,主要是日志的文件和position;
4)然后回到slave中,执行:"CHANGE MASTER TO ......执行同步指令
 
原因2:该错误发生在从库的io进程从主库拉取日志时,发现主库的mysql_bin.index文件中第一个文件不存在。出现此类报错可能是由于你的slave 由于某种原因停止了好长一段
时间,当你重启slave 复制的时候,在主库上找不到相应的binlog ,会报此类错误。或者是由于某些设置主库上的binlog被删除了,导致从库获取不到对应的binglog file。
解决办法:
1)为了避免数据丢失,需要重新进行slave同步操作。
2)注意主库binlog的清理策略,选择基于时间过期的删除方式还是基于空间利用率的删除方式。
3)记住最好不要使用"rm -rf"命令删除binlog file,这样不会同步修改mysql_bin.index 记录的binlog 条目。在删除binlog的时候确保主库保留了从库"show slave status"
  的Relay_Master_Log_File对应的binlog file。任何时候都不能删除正在使用的那个最新binlog文件;最好把bin-log文件不要删除,最好给备份出来。
 
原因2的情况下,使用原因1的处理方案显然是解决不了的!此时的解决方案是:
在从库上执行:
mysql> stop slave;
mysql> reset slave;
mysql> start slave;
mysql> show slave status \G;

3)slave同步状态中出现Slave_IO_Running: Connecting 导致这个错误的原因一般是:     1--网络不通     2--权限问题(连接master的用户名和密码跟master授权不一致)     3--连接时用的log file和pos节点跟"show master status"的结果不一致

4)slave同步状态中出现Slave_SQL_Running: No ,即slave不同步! 解决办法: 第一种方法:忽略错误后,继续同步。 该方法适用于主从库数据相差不大,或者要求数据可以不完全统一的情况,数据要求不严格的情况(下面均为在slave机器上的操作) mysql> stop slave; mysql> set global sql_slave_skip_counter =1;   //表示跳过一步错误,后面的数字可变;或者在my.cnf里添加slave-skip-errors = all(上面已在配置中添加) mysql> start slave; mysql> show slave status\G      //查看: 第二种方法:重新做主从,完全同步 该方法适用于主从库数据相差较大,或者要求数据完全统一的情况 1--master主库上操作 mysql> flush tables with read lock;  //进行锁表,防止数据写入。注意该处是锁定为只读状态,语句不区分大小写 #mysqldump --lock-all-tables --all-databases --flush-logs --master-data=2 > /root/allsql.sql   //主库完全备份(如果是指定库同步,就备份指定库),注意数据库备份一定要定期进行,确保数据万无一失 mysql> show master status;      //查看master状态,注意log file和pos节点,slave同步会用到 # scp mysql.bak.sql root@192.168.1.102:/tmp/        //把备份文件传到slave从库机器,进行数据恢复 2--slave从库操作 mysql> stop slave; mysql> source /tmp/mysql.bak.sql mysql> change master to master_host = '192.168.1.101', master_user = 'slave', master_port=3306.......; mysql> start slave; mysql> show slave status\G ....... Slave_IO_Running: Yes Slave_SQL_Running: Yes 此种方法中最为关键主要有两步: ①主服务器上锁表做完全备份,并滚动日志; ②从服务器上进行半道恢复.

5)slave中继日志relay-log损坏? 什么是中继日志? relay-log存放在从服务器上,从服务器将主服务器的二进制日志文件拷贝到自己的主机上放在中继日志中,然后调用SQL线程按照拷中继日志文件中的二进制日志文件执行以便就可达到数据的同步 。 如何中继日志避免: mysql 5.6版本后,在my.cnf文件中开启relay_log_recover=1即可避免。

6)slave连接超时且重新连接频繁 若有多少slave,且没有设置server_id或两个slave设置相同的server_id,将有可能会出现服务器的ID冲突。这种情况下,其中一台slave可能会频繁超时或丢失后重新连接序列。 所以一定要确保每台slave及master在my.cnf中都要设置不一样的server_id。

7)主库与从库使用不同的存储引擎造成不同步

8)从库同步时,提示表不存在 错误:Last_Error: Error executing row event: 'Table 'test.t1' doesn't exist' 解决方法:在从库重建这张表。

9)max_allowed_packet设置过小导致slave报错 max_allowed_packet默认是16M,主从库的max_allowed_packet值和备库上的不匹配。 在这情况下,主库可能会记录一个备库认为过大的包。当备库获取到该二进制日志事件时,可能会碰到各种问题,如无限报错和重试、中继日志损坏等。 具体表现: 从库的Slave_IO_Thread死掉了,查看后,出现以下错误提示: Got a packet bigger than 'max_allowed_packet' bytes 很明显是由于max_allowed_packet的设置太小导致的,然后查检主从库上的设置,主库的设置大于从库,因为max_allowed_packet是动态参数,先调整从库上的max_allowed_packet 与主库相同,重新单独启动I/O线程就正常了。 原理说明:binlog的事件以RBR格式记录,且当前的事件长度大于了从库的max_allowed_packet, 导致无法Slave IO不能正常读取master binlog event.

10)在master上删除一条记录时出现的故障 在master上删除一条记录后,slave上因找不到这条记录而报错。 解决方法: 由于主库上已经对这条语句进行了删除操作,故可以跳过。 在这种情况下,说明主从同步可能数据会有不一致的情况发生,所以需要使用pt-table-checksum进行数据库一致性比对。 (参考:mysql主从同步(3)-percona-toolkit工具(数据一致性监测、延迟监控)使用梳理)

11)在master更新一条记录,而slave却找不到。 主从数据不致时,master有某条记录,但在salve上没有这条记录,若在master上进行更新这条记录,则在slave中可能报错。 解决方法:    1--根据从库发生异常的位置,查主库上的二进制日志。    2--根据主库二进制日志信息,找到更新后的整条记录。    3--在从库上执行在主库上找到的记录信息,进行insert操作。    4--跳过这条语句,再同步slave。    5--使用pt-table-checksum查看主从库表数据否一致。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏云计算教程系列

如何在Ubuntu 14.04上使用memcached将NoSQL查询添加到MySQL

在许多优秀的文章中已经描述了使用memcached及其独立服务器实现与MySQL的一般概念,但是,作为独立服务器的memcached在MySQL客户端访问层之前...

982
来自专栏破晓之歌

Restful API实战 原

举例:需要请求获取服务器允许我对服务器进行哪些操作,可以用OPTIONS请求。还有类似访问频率参数

1622
来自专栏cs

MySQL 问题解决

问题如下: Failed to Connect to MySQL at 127.0.0.1:3306 with user root Access denied...

3908
来自专栏Golang语言社区

网游内存数据库的设计(2)

续第一篇,前两天对核心存储做了些修改,以前只打算与关系数据库的行与表做对应,value类型只能使array或list, 现在把7种基本类型也加入到value支持...

3708

MySQL集群:高可用性DBMS

如果你在寻找一个不会发生单点故障的数据库管理系统,那么水平拓展的MySQL集群分布式多主架构将是您的最佳选择。MySQL集群可以通过MySQL和...

2867
来自专栏禹都一只猫博客

几个关于linux的笔试题目

1784
来自专栏文渊之博

SQLServer中的死锁的介绍

简介 什么是死锁?      我认为,死锁是由于两个对象在拥有一份资源的情况下申请另一份资源,而另一份资源恰好又是这两对象正持有的,导致两对象无法完成操作,且...

2365
来自专栏云计算教程系列

如何在Ubuntu 18.04上使用PostgreSQL 10设置逻辑复制

在为生产设置应用程序时,准备好多个数据库副本通常很有用。保持数据库副本同步的过程称为复制。复制可以为大量同时读取操作提供高可用性水平扩展,同时减少读取延迟。它还...

3015
来自专栏Golang语言社区

网游内存数据库的设计(2)

续第一篇,前两天对核心存储做了些修改,以前只打算与关系数据库的行与表做对应,value类型只能使array或list, 现在把7种基本类型也加入到value支持...

4078
来自专栏散尽浮华

Oracle日常运维操作总结-数据库的启动和关闭

下面是工作中对Oracle日常管理操作的一些总结,都是一些基本的oracle操作和SQL语句写法,在此梳理成手册,希望能帮助到初学者(如有梳理不准确之处,希望指...

3255

扫码关注云+社区

领取腾讯云代金券