Mysql Replication 数据不一致解决方案

最新推荐文章于 2023-05-18 14:33:24 发布

weixin_33936401

最新推荐文章于 2023-05-18 14:33:24 发布

阅读量332

点赞数

文章标签：数据库 shell python

原文链接：https://my.oschina.net/ij5IYLKW/blog/621180

版权

2019独角兽企业重金招聘Python工程师标准>>>

Mysql Replication 数据不一致解决方案

1、应用场景

目前很多公司的DB都是部署Master-Slave、Master-Master的结构，简单的高可用可以支持大多数应用情况，且配置起来非常方便，目前常用的几种方式我就不赘述了（网络上多的是资料），但这种Mysql Cluster结构总会因为各种原因导致Master-Slave、Master-Master之间的数据不一致，而数据不一致带来的后果是非常严重的，所以笔者在这里记录下当Mysql Cluster数据不一致的情况下怎么处理：

1.1、主从架构更健壮

首先引用Mysql官方参考手册的解释来说明这种不一致情况更多的是出现在主主的结构，主从的结构有利于程序的健壮性和速度（实际上我们确实在使用Master-Master过程中遇到过问题）

1.2、了解Mysql Cluster

Replication 原理：Master服务器将更新写入二进制日志文件(binlog)，并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到Slave服务器的更新。当一个Slave服务器连接主服务器时，它通知Master服务器从服务器在日志中读取的最后一次成功更新的位置（Position）。Slave服务器接收在那之后起发生的任何更新，然后封锁并等待主服务器通知新的更新。
Replication 细节：MySQL使用3个线程来执行复制功能(其中1个在主服务器上，另两个在从服务器上。当发出START SLAVE时，从服务器创建一个I/O线程，以连接主服务器并让它发送记录在其二进制日志中的语句。主服务器创建一个线程将二进制日志中的内容发送到从服务器。该线程可以识别为主服务器上SHOW PROCESSLIST的输出中的Binlog Dump线程。从服务器I/O线程读取主服务器Binlog Dump线程发送的内容并将该数据拷贝到从服务器数据目录中的本地文件中，即中继日志。第3个线程是SQL线程，是从服务器创建用于读取中继日志并执行日志中包含的更新。

操作一：在Master上面执行 Show ProcesList，根据Status分析当前线程的状况，因为比较简单的英文描述，我就不解释了

操作二：在Slave上执行 Show ProcessList 2.row：接收binlog数据的IO线程，3.row：SQL执行线程，同样看Status字段

2、解决Master-Slave数据不一致的问题

2.1 可行方案

经过几天的研究，发现两个还算靠谱的方案。

半同步复制：原理就是Slave往Master发送ack消息，告诉Master我已经收到Binlog数据了，然后Master提交事物并修改Position返回结果，再配合MHA做故障转移，可以很多程度上保证数据的一致性(非绝对)。这种方式当事物很多的时候会导致性能下降，当ack超时可能自动转换成普通复制
　异步复制：很多方式MHA、Mysql Cluster等等，保证数据一致性的方法是：监控复制进程是否正常，如果异常促发主从数据修复工作， pt-table-checksum & pt-table-sync 两个工具来校验并修复数据
当然还有一个很牛逼的工具DRC，淘宝内部做的一个系统，不仅可以在数据库之间进行同步，还能同步到缓存，搜索引擎(道听途说)，阿里云的RDS应该也就是基于这个工具，不过别人不开源。

2.2 方案2实施

2.2.1 DB结构的思路

因为公司现在生产的现有条件及环境，所以我选择了第二种使用percona-toolkit工具，percona-toolkit给我们提供了很多小工具，其中两个就是pt-table-checksum & pt-table-sync，checksum用来检测主从数据是否一致，不一致时sync用来恢复不一致数据，整个DB的结构应该是：

配置Master-Slave结构
监控Master-Slave的Replication是否正常
如果不正常，使应用无法访问(保证同步的时候没有新的update操作数据)，404或者维修页面，使用checksum工具校验一致性，再使用sync修复数据，然后恢复应用访问，如果不出意外，整个过程应该在几分钟以内，这应该也可以满足大部分需求？

2.2.2 percona-toolkit 使用介绍

安装：不介绍了，给出地址 percona-toolkit

使用：只把过程和用到的东西解释了下，有些参数选项等还需要查阅文档。两台机器都是centos6.5 mysql版本都是5.6 , 由于是线上环境，这里ip和密码等敏感信息修改了下。

主 192.168.1.100
从 192.168.1.98
修复数据库名 radius
校验数据一致性

建立用户并授权

注意这里要在主从创建一个同名的用户，可以从主库访问从库，主库本地可以访问主库。工具的使用都是在主库的服务器上进行,使用 
pt-table-checksum校验数据一致性。

从库mysql操作

mysql>GRANT SELECT,PROCESS, SUPER, REPLICATION SLAVE ON *.* TO 'checksums'@'192.168.1.100' IDENTIFIED BY 'slavecheck';

mysql>flush privileges;

 
主库mysql操作

mysql>GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE ON *.* TO 'checksums'@'192.168.1.100' IDENTIFIED BY 'slavecheck';

mysql>GRANT SELECT,INSERT,UPDATE,DELETE ON radius.checksums TO 'checksums'@'192.168.1.100';

mysql>flush privileges;

校验时候需要在主mysql 中新建一张表，新建用户需要有读写的权限，这里是把校验表建立在radius库中。

pt-table-checksum 校验

校验是在主库服务器上进行的

主库shell中执行
pt-table-checksum h='192.168.1.100',u='checksums',p='slavecheck',P=3306 -d radius --nocheck-replication-filters --replicate=radius.checksums

--nocheck-replication-filters ：不检查复制过滤器，建议启用。后面可以用--databases来指定需要检查的数据库。
--no-check-binlog-format      : 不检查复制的binlog模式，要是binlog模式是ROW，则会报错。
--replicate-check-only :只显示不同步的信息。
--replicate=    ：把checksum的信息写入到指定表中，建议直接写到被检查的数据库当中。 
--databases=    ：指定需要被检查的数据库，多个则用逗号隔开。
--tables=       ：指定需要被检查的表，多个用逗号隔开
h=192.168.1.100 ：Master的地址
u=checksums         ：用户名
p=slavecheck        ：密码
P=3306          ：端口

这个脚本在主库机器上运行，会自动找到从库地址，并用相同的用户登录，然后对比。

–replicate 选项是建立一个表来存储对比信息，这个表一定要能同步到从库中，如果checksums用户没有建表权限，请自行建立好表

建表语句

CREATE TABLE IF NOT EXISTS `radius`.`checksums` (
     db             CHAR(64)     NOT NULL,
     tbl            CHAR(64)     NOT NULL,
     chunk          INT          NOT NULL,
     chunk_time     FLOAT            NULL,
     chunk_index    VARCHAR(200)     NULL,
     lower_boundary TEXT             NULL,
     upper_boundary TEXT             NULL,
     this_crc       CHAR(40)     NOT NULL,
     this_cnt       INT          NOT NULL,
     master_crc     CHAR(40)         NULL,
     master_cnt     INT              NULL,
     ts             TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
     PRIMARY KEY (db, tbl, chunk),
     INDEX ts_db_tbl (ts, db, tbl)
  ) ENGINE=INNODB;


下面继续在主库的shell上检查
[root@localhost portal]# pt-table-checksum h='192.168.1.100',u='checksums',p='slavecheck',P=3306 -d radius --nocheck-replication-filters --replicate=radius.checksums
            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
06-16T16:50:21      0      1     8379       4       0   0.322 radius.account_account
06-16T16:50:21      0      1    11429       1       0   0.278 radius.account_mac
06-16T16:50:21      0      1    63747       1       0   0.329 radius.account_smslog
06-16T16:50:21      0      0        0       1       0   0.016 radius.auth_group
06-16T16:50:21      0      0        0       1       0   0.013 radius.auth_group_permissions
06-16T16:50:22      0      0       27       1       0   0.265 radius.auth_permission
06-16T16:50:22      0      1     8384       1       0   0.273 radius.auth_user

出现这种结果，说明已经check了，diffs一栏有不同，说明那些表数据不一致. 现在登录从库的mysql，执行如下语句

mysql> select * from radius.checksums where master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc) \G
*************************** 1. row ***************************
            db: radius
           tbl: account_account
         chunk: 2
    chunk_time: 0.028065
   chunk_index: PRIMARY
lower_boundary: 1847
upper_boundary: 9225
      this_crc: 4f43a2
      this_cnt: 7336
    master_crc: 9235f7a2
    master_cnt: 7379
            ts: 2015-06-16 17:00:31

一共有8条记录，这8张表数据不一致。 大概能看出来缺少了多少数据chunk等。

修复不一致数据

修复不一致数据使用pt-table-sync 工具，使用pt-table-checksum工具的结果。不过这里还是有些坑。在修复之前最好把主mysql数据备份一下，因为会对主库有些写操作，有一点风险。

主库服务器执行

[root@localhost portal]# pt-table-sync --execute --replicate radius.checksums --sync-to-master h="192.168.1.98",P=3306,u="checksums",p="slavecheck" --ignore-tables radacct,django_session
DBI connect(';host=124.88.52.100;port=3306;mysql_read_default_group=client','checksums',...) failed: Access denied for user 'checksums'@'124.88.52.100' (using password: YES) at /usr/local/bin/pt-table-sync line 2220
但是直接用mysql连接就没问题,最后查了下文档，发现还是用户权限的问题。 

从库操作:

mysql> GRANT all ON radius.* TO 'checksums'@'192.168.1.100';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

主库操作:

mysql> GRANT all ON radius.* TO 'checksums'@'192.168.1.100';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

新增增删改查权限其实就够了 ,我这偷懒下。。

错误基本解决完了

修复数据

先修复一个不重要的表来实验下(主库操作)

pt-table-sync --execute --replicate radius.checksums --sync-to-master h=192.168.1.98,P=3306,u=checksums,p="slavecheck"  --tables account_smslog,radcheck --print 

修复完成在执行一次check 主库操作

pt-table-checksum h='192.168.1.100',u='checksums',p='slavecheck',P=3306 -d radius --nocheck-replication-filters --replicate=radius.checksums

在从库mysql中检查下

mysql> select * from radius.checksums where master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc) \G