《MySQL主从不一致情形与解决方法》

《MySQL主从不一致情形与解决方法》

一、MySQL主从不同步情况

1.1 网络的延迟

由于mysql主从复制是基于binlog的一种异步复制

通过网络传送binlog文件,理所当然网络延迟是主从不同步的绝大多数的原因,特别是跨机房的数据同步出现这种几率非常的大,所以做读写分离,注意从业务层进行前期设计。

1.2 主从两台机器的负载不一致

由于mysql主从复制是主数据库上面启动1个io线程,而从上面启动1个sql线程和1个io线程,当中任何一台机器的负载很高,忙不过来,导致其中的任何一个线程出现资源不足,都将出现主从不一致的情况。

1.3 max_allowed_packet设置不一致

主数据库上面设置的max_allowed_packet比从数据库大,当一个大的sql语句,能在主数据库上面执行完毕,从数据库上面设置过小,无法执行,导致的主从不一致。

1.4 自增键不一致

key自增键开始的键值跟自增步长设置不一致引起的主从不一致。

1.5 同步参数设置问题

mysql异常宕机情况下,如果未设置sync_binlog=1或者innodb_flush_log_at_trx_commit=1很有可能出现binlog或者relaylog文件出现损坏,导致主从不一致。

1.6 自身bug

mysql本身的bug引起的主从不同步

1.7 版本不一致

特别是高版本是主,低版本为从的情况下,主数据库上面支持的功能,从数据库上面不支持该功能。

1.8 主从不一致优化配置

基于以上情况,先保证max_allowed_packet,自增键开始点和增长点设置一致

再者牺牲部分性能在主上面开启sync_binlog,对于采用innodb的库,推荐配置下面的内容

innodb_flush_logs_at_trx_commit = 1

innodb-support_xa = 1 # Mysql 5.0 以上

innodb_safe_binlog # Mysql 4.0

同时在从上面推荐加入下面两个参数

skip_slave_start

read_only

二、解决主从不同步的方法

2.1 主从不同步场景描述

今天发现Mysql的主从数据库没有同步

先上Master库:

mysql>show processlist;

查看下进程是否Sleep太多。发现很正常。

show master status;

查看主库状态也正常。

mysql> show master status;FilePositionBinlog_Do_DBBinlog_Ignore_DBmysqld-bin.0000013260mysql,test,information_schema

1 row in set (0.00 sec)

复制代码再到Slave上查看

mysql> show slave statusG

Slave_IO_Running: Yes

Slave_SQL_Running: No

复制代码由此可见是Slave不同步

2.2 解决方法一:忽略错误后,继续同步

该方法适用于主从库数据相差不大,或者要求数据可以不完全统一的情况,数据要求不严格的情况

解决:

stop slave;

复制代码

表示跳过一步错误,后面的数字可变

set global sql_slave_skip_counter =1;

start slave;

复制代码

之后再用mysql> show slave statusG 查看:

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

复制代码ok,现在主从同步状态正常了。。。

2.3 方式二:重新做主从,完全同步

该方法适用于主从库数据相差较大,或者要求数据完全统一的情况

解决步骤如下:

1.先进入主库,进行锁表,防止数据写入

使用命令:

mysql> flush tables with read lock;

注意:该处是锁定为只读状态,语句不区分大小写

2.进行数据备份

把数据备份到mysql.bak.sql文件

[root@server01 mysql]#mysqldump -uroot -p -hlocalhost > mysql.bak.sql

这里注意一点:数据库备份一定要定期进行,可以用shell脚本或者python脚本,都比较方便,确保数据万无一失

3.查看master 状态

mysql> show master status;

+——————-+———-+————–+——————————-+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+——————-+———-+————–+——————————-+

| mysqld-bin.000001 | 3260 | | mysql,test,information_schema |

+——————-+———-+————–+——————————-+

1 row in set (0.00 sec)

复制代码

4.把mysql备份文件传到从库机器,进行数据恢复

使用scp命令

[root@server01 mysql]# scp mysql.bak.sql root@192.168.1.206:/tmp/

5.停止从库的状态

mysql> stop slave;

6.然后到从库执行mysql命令,导入数据备份

mysql> source /tmp/mysql.bak.sql

7.设置从库同步,注意该处的同步点,就是主库show master status信息里的| File| Position两项

change master to master_host = ‘192.168.1.206’, master_user = ‘rsync’, master_port=3306, master_password=”, master_log_file = ‘mysqld-bin.000001’, master_log_pos=3260;

8.重新开启从同步

mysql> start slave;

9.查看同步状态

mysql> show slave statusG 查看:

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

好了,同步完成啦

在此给大家分享一份学习资料,里面包括:(BATJ面试资料、高可用、高并发、高性能及分布式、Jvm性能调优、Spring源码,MyBatis,Netty,Redis,Kafka,Mysql,Zookeeper,Tomcat,Docker,Dubbo,Nginx等多个知识点的架构资料)和Java进阶学习路线图, 免费领取加 q群:478052716  即可! 

三、如何监控mysql主从之间的延迟

3.1 前言:

日常工作中,对于MYSQL主从复制的检查有两方面

保证复制的整体结构是否完整;

需要检查数据是否一致;

对于前者我们可以通过监控复制线程是否工作正常以及主从延时是否在容忍范围内,对于后者则可以通过分别校验主从表中数据的md5码是否一致,来保证数据一致,可以使用Maatkit工具包中的mk-table-checksum工具去检查。

本文档介绍下关于如何检查主从延迟的问题。

主从延迟判断的方法,通常有两种方法:Seconds_Behind_Master和mk-heartbeat

3.2方法1.

通过监控show slave statusG命令输出的Seconds_Behind_Master参数的值来判断,是否有发生主从延时。

mysql> show slave statusG;

1. row **

 Slave_IO_State: Waiting for master to send event
 Master_Host: 192.168.1.205
 Master_User: repl
 Master_Port: 3306
 Connect_Retry: 30
 Master_Log_File: edu-mysql-bin.000008
 Read_Master_Log_Pos: 120
 Relay_Log_File: edu-mysql-relay-bin.000002
 Relay_Log_Pos: 287
 Relay_Master_Log_File: edu-mysql-bin.000008
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes
 Replicate_Do_DB: 
 Replicate_Ignore_DB: 
 Replicate_Do_Table: 
 Replicate_Ignore_Table: 
 Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

 Last_Errno: 0
 Last_Error: 
 Skip_Counter: 0
 Exec_Master_Log_Pos: 120
 Relay_Log_Space: 464
 Until_Condition: None
 Until_Log_File: 
 Until_Log_Pos: 0
 Master_SSL_Allowed: No
 Master_SSL_CA_File: 
 Master_SSL_CA_Path: 
 Master_SSL_Cert: 
 Master_SSL_Cipher: 
 Master_SSL_Key: 
 Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

 Last_IO_Errno: 0
 Last_IO_Error: 
 Last_SQL_Errno: 0
 Last_SQL_Error:

Replicate_Ignore_Server_Ids:

 Master_Server_Id: 205
 Master_UUID: 7402509d-fd14-11e5-bfd0-000c2963dd15
 Master_Info_File: /home/mysql/data/master.info
 SQL_Delay: 0
 SQL_Remaining_Delay: NULL
 Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
 Master_Retry_Count: 86400
 Master_Bind: 
 Last_IO_Error_Timestamp: 
 Last_SQL_Error_Timestamp: 
 Master_SSL_Crl: 
 Master_SSL_Crlpath: 
 Retrieved_Gtid_Set: 
 Executed_Gtid_Set: 
 Auto_Position: 0

1 row in set (0.00 sec)

复制代码以上是show slave statusG的输出结果,这些结构给我们的监控提供了很多有意义的参数。

Slave_IO_Running

该参数可作为io_thread的监控项,Yes表示io_thread的和主库连接正常并能实施复制工作,No则说明与主库通讯异常,多数情况是由主从间网络引起的问题;

Slave_SQL_Running

该参数代表sql_thread是否正常,具体就是语句是否执行通过,常会遇到主键重复或是某个表不存在。

Seconds_Behind_Master

是通过比较sql_thread执行的event的timestamp和io_thread复制好的event的timestamp(简写为ts)进行比较,而得到的这么一个差值;NULL—表示io_thread或是sql_thread有任何一个发生故障,也就是该线程的Running状态是No,而非Yes。0 — 该值为零,是我们极为渴望看到的情况,表示主从复制良好,可以认为lag不存在。

正值 — 表示主从已经出现延时,数字越大表示从库落后主库越多。负值 — 几乎很少见,我只是听一些资深的DBA说见过,其实,这是一个BUG值,该参数是不支持负值的,也就是不应该出现。

备注Seconds_Behind_Master的计算方式可能带来的问题

我们都知道的relay-log和主库的bin-log里面的内容完全一样,在记录sql语句的同时会被记录上当时的ts,所以比较参考的值来自于binlog,其实主从没有必要与NTP进行同步,也就是说无需保证主从时钟的一致。你也会发现,其实比较真正是发生在io_thread与sql_thread之间,而io_thread才真正与主库有关联,于是,问题就出来了,

当主库I/O负载很大或是网络阻塞

io_thread不能及时复制binlog(没有中断,也在复制),而sql_thread一直都能跟上io_thread的脚本,这时Seconds_Behind_Master的值是0,

也就是我们认为的无延时,但是,实际上不是,你懂得。

这也就是为什么大家要批判用这个参数来监控数据库是否发生延时不准的原因,但是这个值并不是总是不准,

如果当io_thread与master网络很好的情况下,那么该值也是很有价值的。’‘之前,提到Seconds_Behind_Master这个参数会有负值出现,我们已经知道该值是io_thread的最近跟新的ts与sql_thread执行到的ts差值,

前者始终是大于后者的,唯一的肯能就是某个event的ts发生了错误,比之前的小了,那么当这种情况发生时,负值出现就成为可能。

3.2 方法2.

mk-heartbeat:Maatkit万能工具包中的一个工具,被认为可以准确判断复制延时的方法。

mk-heartbeat的实现也是借助timestmp的比较实现的,它首先需要保证主从服务器必须要保持一致,通过与相同的一个NTP server同步时钟。它需要在主库上创建一个heartbeat的表,里面至少有id与ts两个字段,id为server_id,ts就是当前的时间戳now(),该结构也会被复制到从库上,表建好以后,会在主库上以后台进程的模式去执行一行更新操作的命令,定期去向表中的插入数据,这个周期默认为1秒,同时从库也会在后台执行一个监控命令,与主库保持一致的周期去比较,复制过来记录的ts值与主库上的同一条ts值,差值为0表示无延时,差值越大表示延时的秒数越多。我们都知道复制是异步的ts不肯完全一致,所以该工具允许半秒的差距,在这之内的差异都可忽略认为无延时。这个工具就是通过实打实的复制,巧妙的借用timestamp来检查延时。

Image placeholder
aanxiaoran
未设置
  71人点赞

没有讨论,发表一下自己的看法吧

推荐文章
【Golang+MySQL】记一次 MySQL 数据库迁移(一)

【Golang+mysql】记一次mysql数据库迁移(一)文章地址:https://github.com/stayfoo/stayfoo-hub一、准备目标: 腾讯云CVM自建mysql数据迁移到腾

MySQL是怎么保证数据一致性的

在《写数据库同时发mq消息事务一致性的一种解决方案》一文的方案中把分布式事务巧妙转成了数据库事务。我们都知道关系型数据库事务能保证数据一致性,那数据库到底是怎么设计事务这一特性的呢?一、MySQL事务

由Linux内核bug引起SSH登录缓慢问题的排查与解决

今年7月,有一位用户反馈,使用该镜像创建出的快杰云主机每次启动时,第一次SSH登录会很慢,有时候需几十秒甚至几分钟才能登录成功,影响了使用体验。经过排查,定位到是Linux内核随机数熵池初始化慢的原因

HTML主体结构及头部常用标签笔记

上面这种方式是HTML4中设置页面字符集的标签 5秒一刷新 5秒以后跳转到百度 网页关键词

MySQL 性能优化:8 种常见 SQL 错误用法!

1、LIMIT语句分页查询是最常用的场景之一,但也通常也是最容易出问题的地方。比如对于下面简单的语句,一般DBA想到的办法是在type,name,create_time字段上加组合索引。这样条件排序都

Oracle/云MySQL/MsSQL“大迁移”真相及最优方案

最近一段时间碰到一些数据迁移的项目,如:Oracle迁移到MySQL,MsSQL迁移到MySQL,云MySQL迁移到本地MySQL。对于这方面做了系统的整理。包括:迁移方案的选择、如何跳出迁移遇到的坑

一条SQL语句在MySQL中如何执行的

前两天发了一条SQL慢的原因有哪些,在那篇文章我没有说到优化器之类的,我觉得如果配合一条SQL是如何执行的,会更好,所以特地找了一篇。来源:JavaGuide  |作者:木木匠本篇文章会分析一个sql

mysql 进行update时,要更新的字段中有单引号或者双引号导致不能批量生成sql的问题

前言将数据从一张表迁移到另外一张表的过程中,通过mysql的concat方法批量生成sql时遇到了一个问题,即进行UPDATE更新操作时如果原表中的字段中包含单引号'或者双引号",那么就会生成不正确的

《MongoDB高手课》学习记录(第十六天)

第十六天今天开始事务的学习,要学习的章节为《19|事务开发:写操作事务》,其实主要就是讲一个参数writeConcern。什么是writeConcern?之前第一章我们学过,生产环境中MongoDB最

《MongoDB高手课》学习记录(第十七天)

写在前面最近有新项目上线,实在太忙了,学习的进度有点拖沓,但会坚持。第十七天今天要学的是《20|事务开发:读操作事务之一》章节。主要讲解的是怎么哪里读取数据的问题。readRreference参数值有

写数据库同时发mq消息事务一致性的一种解决方案

一、引子《事务注解(@Transactional)引起的数据覆盖故障》一文收到不少反馈。事务里不要有rpc,基本原则,sb封装的太好了,把很多人养傻了,function级别的事务,坑太大。网友一这个是

utf8 加密与解密

utf8加密 functionUTF8Encoder(options){ varfatal=options.fatal; this.encode=function(output_byte_stream

utf8 加密与解密

utf8加密functionUTF8Encoder(options){ varfatal=options.fatal; this.encode=function(output_byte_stream,

MySQL 百万级数据量分页查询方法及其优化

作者|大神养成记原文|  http://t.cn/RnvCJnm方法1:直接使用数据库提供的SQL语句语句样式: MySQL中,可用如下方法:SELECT*FROM表名称LIMITM,N适应场景: 适

MySQL Batched Key Access (BKA)原理和设置使用方法举例

MySQL5.6版本开始增加了提高表join性能的算法:BatchedKeyAccess(BKA)的新特性。BKA算法原理:将外层循环的行/结果集存入joinbuffer,内存循环的每一行数据与整个b

MySQL 中 JSON 字段的使用技巧

mysql5.7.8之后开始原生支持json.在类似mongodb这种nosql数据库中,json存储数据是非常自然的,在mysql中合理的使用json,能够带来极大的便利 Json字段的使用场景 在

Ubuntu18.04 安装 MySQL 以及设置远程访问

安装MySQL sudoapt-getinstallmysql-server sudoaptisntallmysql-client sudoaptinstalllibmysqlclient-dev

基础信息:MySQL 特性

MySQL数据库的优缺点: 关系型数据库管理系统(RDBMS):MySQL是一个典型的关系型数据库管理系统。 易用:MySQL很容易上手。只要你掌握一些简单的SQL知识,就可以构建SQL语句与My

基础信息:什么是 MySQL?

MySQL是一个开源的深受欢迎的关系型数据库管理系统(简称RDBMS)。目前排名第二,仅次于Oracle数据库。 MySQL可以免费下载,但是,还提供了几个付费版本,这些版本提供了附加功能。 顾名思义

MySQL 数据库操作:创建和查看数据库

数据库是数据的集合。MySQL允许我们高效地存储和检索数据库中的数据。在MySQL中,我们可以使用CREATEDATABASE语句创建数据库。但是,如果数据库已经存在,则会引发错误。为了避免该错误,我

MySQL 表结构生成 Markdown 文档 | 工具篇

背景 在实施软件工程的时候,当要将某一版本归档时,需要汇总的文档要求还是比较高的、各类文档齐全,包括项目架构、项目安装、接口等文档,而数据库表结构说明文档亦属于其一。记得很早之前想找一个可以导出MyS

MySQL 数据库操作:删除数据库

使用MySQL的DROPDATABASE命令可以很容易的删除一个数据库。数据库删除的同时,所属的数据表将一起被删除。如果删除的数据库不存在,则会引发错误。为了避免错误的发生,可以在DROPDATABA

MySQL 定时备份

1设置好crontab定时任务备份 #每天备份三次数据库 05,15,22***sh/data/script/alldatabase_back.sh&>/dev/null #每天2点备份 02***s

Laravel-Binlog 扩展(用于实时监听 MySQL 数据变更、数据同步等场景)

Laravel-Binlogv0.2.1 (该扩展当前用于我司测试环境实时同步Mysql数据变更到ElasticSearch,稳定性待测试!!哈哈哈)我司正式环境走的阿里云DTS数据订阅 基于Sw