mysql 主从配置详解3 主从配置不一致问题

最新推荐文章于 2023-04-24 10:46:34 发布

yang洋PHPer

最新推荐文章于 2023-04-24 10:46:34 发布

阅读量913

点赞数

分类专栏： mysql 文章标签： mysql主从数据不一致

本文链接：https://blog.csdn.net/qq_34341290/article/details/103344499

版权

mysql 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

主从读写与维护

有三个待解决问题：

1. 校验主从数据一致性问题

2. 数据校验恢复

3. 主从延迟问题

1. 校验主从数据一致性问题

在理想情况下，备库和主库的数据应该是完全一样的。但事实上备库可能发生错误并导致数据不一致。即使没有明显的错误，备库同样可能因为MySQL自身的特性导致数据不一致，例如MySQL的Bug感、网络中断、服务器崩溃，非正常关闭或者其他一些错误。

按照我们的经验来看，主备一致应该是一种规范，而不是例外，也就是说，检查你的主备库一致性应该是一个日常工作，特别是当使用备库来做备份时尤为重要，因为肯定不希望从一个已经损坏的备库里获得备份数据。

我们可以使用percona-toolkit工具做校验，而该工具包含

1. pt-table-checksum 负责检测MySQL主从数据一致性

2. pt-table-sync负责挡住从数据不一致时修复数据，让他们保存数据的一致性

3. pt-heartbeat 负责监控MySQL主从同步延迟

安装

[root@localhost home]# wget ttps://www.percona.com/downloads/percona-toolkit/3.0.3/binary/redhat/7/x86_64/percona-toolkit-3.0.3-
1.el7.x86_64.rpm
[root@localhost home]# yum install perl-IO-Socket-SSL perl-DBD-MySQL perl-Time-HiRes perl perl-DBI -y
[root@localhost home]# rpm -ivh percona-toolkit-3.0.3-1.el6.x86_64.rpm
[root@localhost home]# yum list | grep percona-toolkit
percona-toolkit.x86_64 3.0.3-1.el6 installed
percona-toolkit.noarch 2.2.20-1 percona-release-noarch
percona-toolkit.x86_64 3.1.0-2.el7 percona-release-x86_64
percona-toolkit-debuginfo.x86_64 3.0.13-1.el7 percona-release-x86_64
[root@localhost home]# pt-table-checksum --help

使用

pt-table-checksum [options] [dsn]

pt-table-checksum：在主（master）上通过执行校验的查询对复制的一致性进行检查，对比主从的校验值，从而产生结果。DSN指向的是主的地址，该工具的退出状态不为零，如果发现有任何差别，或者如果出现任何警告或错误，更多信息请查看官方资料。

现在我们可以准备一个动作：来模拟数据不一致的问题，同时需要确保主从是配置好了的 -》思路就是创建一个test的库随便添加一个表，使主从库的数据不一致

使用工具检测，注意常用的参数解释：

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

检测

[root@localhost home]# pt-table-checksum --nocheck-replication-filters --replicate=check_data.checksums --databases=test --tables=t --
user=root --password=root
Replica localhost.localdomain has binlog_format MIXED which could cause pt-table-checksum to break replication. Please read "Replicas
using row-based replication" in the LIMITATIONS section of the tool's documentation. If you understand the risks, specify --no-checkbinlog-format to disable this check.

上面的错误信息主要是因为，检测主库与从库的binlog日志的模式 - 通常来说可以不用改binlog添加 --no-check-binlog-format 跳过检测但是可能也会出现如下的问题

[root@localhost home]# pt-table-checksum --nocheck-replication-filters --replicate=check_data.checksums --no-check-binlog-format --
databases=test --tables=t --user=root --password=root
Diffs cannot be detected because no slaves were found. Please read the —recursion-method documentation for information.

检测的方式：

1. 是否是指定在主库运行进行校验

2. 就是配置--recursion-method参数，然后在从库中指定好对应的地址

通过在主库中查找

mysql> show processlist \G;
*************************** 1. row ***************************
Id: 113
User: repl_127
Host: 192.168.153.131:51514
db: NULL
Command: Binlog Dump
Time: 35742
State: Master has sent all binlog to slave; waiting for more updates
Info: NULL
*************************** 2. row ***************************
Id: 120
User: root
Host: localhost
db: test
Command: Query
Time: 0
State: starting
Info: show processlist
2 rows in set (0.00 sec)
ERROR:
No query specified

可以看到当前连接从库的ip地址是 192.168.153.131:51514 然后我们可以在从库中配置

report_port=3306
report_host=192.168.153.131

注意！！！不要有空格不然可能启动不了

在主库中检测

mysql> show slave hosts;
+-----------+-----------------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+-----------+-----------------+------+-----------+--------------------------------------+
| 2 | 192.168.153.131 | 3306 | 1 | 64e07acc-e16e-11e9-9285-000c29ad4fca |
+-----------+-----------------+------+-----------+--------------------------------------+
1 row in set (0.00 sec)

然后进入主库检测

[root@localhost home]# pt-table-checksum --nocheck-replication-filters --replicate=check_data.checksums --no-check-binlog-format --
databases=test --tables=t --user=root --password=root
TS             ERRORS DIFFS ROWS CHUNKS SKIPPED TIME T  ABLE
09-28T11:46:37  0       1    3     1     0       0.274 test.t

TS ：完成检查的时间。

ERRORS ：检查时候发生错误和警告的数量。

DIFFS ：0表示一致，1表示不一致。当指定--no-replicate-check时，会一直为0，当指定--replicate-check-only会显示不同的信息。

ROWS ：表的行数。

CHUNKS ：被划分到表中的块的数目。

SKIPPED ：由于错误或警告或过大，则跳过块的数目。

TIME ：执行的时间。 TABLE ：被检查的表名。

那些信息数据会记录在checksums表中，查看该表数据会发生一些变化

2. 主从数据一致性-恢复

我们可以通过使用另一个工具pt-table-sync进行数据的同步

手册地址：https://www.percona.com/doc/percona-toolkit/LATEST/pt-table-sync.html

在主库中执行

[root@localhost home]# pt-table-sync --replicate=check_data.checksums h=127.0.0.1,u=root,p=root h=192.168.153.131,u=root,p=root --print
DELETE FROM `test`.`t` WHERE `id`='3' LIMIT 1 /*percona-toolkit src_db:test src_tbl:t src_dsn:h=127.0.0.1,p=...,u=root dst_db:test
dst_tbl:t dst_dsn:h=192.168.153.131,p=...,u=root lock:1 transaction:1 changing_src:check_data.checksums replicate:check_data.checksums
bidirectional:0 pid:14262 user:root host:localhost.localdomain*/;
[root@localhost home]# pt-table-sync --replicate=check_data.checksums h=127.0.0.1,u=root,p=root h=192.168.153.131,u=root,p=root --execute

如上的操作解释：

pt-table-sync [options] dsn [dsn]

该工具先maseter的信息，然后再是从库上的信息；参数建议

--replicate= ：指定通过pt-table-checksum得到的表，这2个工具差不多都会一直用。

--databases= : 指定执行同步的数据库，多个用逗号隔开。

--tables= ：指定执行同步的表，多个用逗号隔开。

--sync-to-master ：指定一个DSN，即从的IP，他会通过show processlist或show slave status 去自动的找主。

h=127.0.0.1 ：服务器地址，命令里有2个ip，第一次出现的是Master的地址，第2次是Slave的地址。

u=root ：帐号。

p=123456 ：密码。

--print ：打印，但不执行命令。

--execute ：执行命令。

建议:

1.修复数据的时候，用--print打印出来，这样就可以知道那些数据有问题

2.修复数据之前一定要备份数据库；然后再手动执行或者添加 --execute 我们也可以把这个编辑成脚本，定期通过centos定时器定期检查, 对于我们来说我们执行在意的是通过pt-table-checksums 显示信息中的DIFFS信息

[root@localhost ~]# pt-table-checksum --nocheck-replication-filters --replicate=check_data.checksums --no-check-binlog-format --
databases=test --tables=t --user=root --password=root
     TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
09-28T16:16:52   0     1     3       1  0 0.015 test.t

对于centos来说我们可以通过 awk 命令获取到 DIFFS 中的值，然后判断这个值是否不等于0；则可以判断是否一致

[root@localhost ~]# pt-table-checksum --nocheck-replication-filters --replicate=check_data.checksums --no-check-binlog-format --
databases=test --tables=t --user=root --password=root | awk 'NR>1{sum+=$3}END{print sum}'
1

下一步要做的就是编辑shell脚本 - 额外解释，所谓的sh脚本就是可以直接模拟centos执行我们在命令太执行的命令然后根据返回的结果进行相应的逻辑处理，我们可以创建一个pt-table-checksums；注意！！！windows下编辑的sh脚本在linux中执行可能会存在一定的问题，推荐可以直接在xshell中编辑sh脚本这样问题会少很多

[root@localhost ~]# touch pt-check-sync.sh
[root@localhost ~]# vi pt-check-sync.sh
#!/usr/bin/env bash
NUM=`pt-table-checksum --nocheck-replication-filters --replicate=check_data.checksums --no-check-binlog-format --databases=test --tables=t
--user=root --password=root | awk 'NR>1{sum+=$3}END{print sum}'`
if [ $NUM -eq 0 ] ;then
echo "Data is ok!"
else
echo "Data is error!"
pt-table-sync --replicate=check_data.checksums h=127.0.0.1,u=root,p=root h=192.168.153.131,u=root,p=root --print
pt-table-sync --replicate=check_data.checksums h=127.0.0.1,u=root,p=root h=192.168.153.131,u=root,p=root --execute
fi
[root@localhost ~]# sh pt-check-sync.sh

然后可以通过编辑crontab -e 定时执行这个脚本就好

20 23 * * * /www/wwwroot/192.168.153.128/shell/pt-check-sync.sh

表示每天晚上23:20运行这个脚本

3. 主从延迟问题

3.1 主从延迟问题-原因与监控

首先需要了解为什么会存在延迟的问题

master 服务器和 slave 服务器连接时，创建 Binlog dump thread 以发送 bin log 数据：

1. 一个 Binlog dump thread 对应一个 slave 服务器；

2. Binlog dump thread 从 bin log 获取数据时会加锁，获取到数据后，立即释放锁。

当 slave 服务器收到 START_SLAVE 命令时，会创建 I/O thread 和 SQL thread：

1. I/O thread 以拉的方式，从 master 读取事件，并存储到 slave 服务器的 relay log 中；

2. SQL thread 从 relay log 中读取事件并执行；

3. slave 可以按照自己的节奏读取和更新数据，也可以随意操作复制进程(启动和停止)。

pt-heartbeat

在percona toolkit 产品中也提供了可以对于MySQL主从延时检查的工具pt-heartbeat, pt-heartbeat 的工作原理是通过使用时间戳方式在主库上更新特定表，然后再从库上读取呗更新的时间戳然后与本地系统时间对比来得出其延迟。

具体流程：

1. 在住上创建一张hearteat表，按照一定的时间频率更新改表的子弹。监控操作运行后，heartbeat表能促使主从同步

2. 连接到从库上检查复制的时间记录，和从库的当前系统时间进行比较，得出时间的差异。

注意在使用的方式就是需要在主库中创建这个表；

use test;
CREATE TABLE heartbeat (
    ts varchar(26) NOT NULL,
    server_id int unsigned NOT NULL PRIMARY KEY,
    file varchar(255) DEFAULT NULL, -- SHOW MASTER STATUS
    position bigint unsigned DEFAULT NULL, -- SHOW MASTER STATUS
    relay_master_log_file varchar(255) DEFAULT NULL, -- SHOW SLAVE STATUS
    exec_master_log_pos bigint unsigned DEFAULT NULL -- SHOW SLAVE STATUS
);

通过pt-heartbeat可以对于mysql中的heartbeat表每隔多久更新一次（注意这个启动操作要在主库服务器上执行）

$ pt-heartbeat --user=root --ask-pass --create-table --database test --interval=1 --interval=1 --update --replace --daemonize
$ ps -ef | grep pt-heartbeat

在主库运行监测同步延迟

$ pt-heartbeat --database test --table=heartbeat --monitor --user=root --password=root --master-server-id=1 
0.02s [ 0.00s, 0.00s, 0.00s ]
0.00s [ 0.00s, 0.00s, 0.00s ]

这其中 0.02s 表示延迟了，没有延迟是为0 而 [ 0.00s, 0.00s, 0.00s ] 则表示1m,5m,15m的平均值，而这期中需要注意的是 --master-server-id 为主服务器的服务id 就是在my.cnf中配置的 server_id的值

3.2 处理延迟问题

对于从库的延时问题最为重要的就是主库与从库之间连接的网咯环境，从库的写入熟读这两个点 - 其次就是对于主从的架构的优化；注意：一旦使用了主从必然是会有一定的延时问题，因此我们就需要考虑程序对于延迟的容忍度。如果是0容忍的话建议还是不用主从了

MySQL从库产生配置

网络环境跳过，，，从库的写入主要是指insert，update，delete的语句的执行速度这些语句的执行速度我们就需要考虑MySQL的执行SQL语句的一个特点 -》对于每一个写的sql会默认开启事务并提交事务；而事务是会影响到io的消耗的这和innodb_flush_log_at_trx_commit参数有关系。默认为1 我们可以尝试设置为0或 2可以提高效率，另一个就是sync_binlog

sync_binlog 配置说明：

sync_binlog”：这个参数是对于MySQL系统来说是至关重要的，他不仅影响到Binlog对MySQL所带来的性能损耗，而且还影响到MySQL中数据的完整性。对于“sync_binlog”参数的各种设置的说明如下：

sync_binlog=0，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者 cache满了之后才同步到磁盘。

sync_binlog=n，当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。

在MySQL中系统默认的设置是sync_binlog=0，也就是不做任何强制性的磁盘刷新指令，这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在 binlog_cache中的所有binlog信息都会被丢失。而当设置为“1”的时候，是最安全但是性能损耗最大的设置。因为当设置为1的时候，即使系统Crash，也最多丢失 binlog_cache中未完成的一个事务，对实际数据没有任何实质性影响。

从以往经验和相关测试来看，对于高并发事务的系统来说，“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。

nnodb_flush_log_at_trx_commit 配置说明：

默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入（flush）硬盘，这是很费时的。特别是使用电池供电缓存（Battery backed up cache）时。设成2对于很多运用，特别是从MyISAM表转过来的是可以的，它的意思是不写入硬盘而是写入系统缓存。日志仍然会每秒flush到硬盘，所以你一般不会丢失超过1-2秒的更新。设成0会更快一点，但安全方面比较差，即使MySQL挂了也可能会丢失事务的数据。而值2只会在整个操作系统挂了时才可能丢数据。

硬件

升级电脑配置。。。

架构

1. 可以考虑对于一些库进行单独分离。

2. 服务的基础架构在业务和MySQL之间加入memcache或者redis的cache层。降低MySQL的杜艳丽

3. 从库的配置要好。。。

yang洋PHPer

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
1
评论
mysql 主从配置详解3 主从配置不一致问题

主从读写与维护有三个待解决问题： 1. 校验主从数据一致性问题 2. 数据校验恢复 3. 主从延迟问题1. 校验主从数据一致性问题在理想情况下，备库和主库的数据应该是完全一样的。但事实上备库可能发生错误并导致数据不一致。即使没有明显的错误，备库同样可能因为MySQL自身的特性导致数据不一致，例如MySQL的Bug感、网络中断、服务器崩溃，非正常关闭或者其...
复制链接

扫一扫