Mysql 一主二从实现主从复制以及数据一致性校验_一主多从怎么保证数值一致性

1.一主二从实现主从复制

准备好服务器分配，以及mysql部署安装，一下是我配置好的服务器（虚拟机）

查看131主库是都开启binlog日志

为主库创建测试账号

mysql> create user `slave`@`%` identified by "root";
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    24
Current database: *** NONE ***

Query OK, 0 rows affected (0.02 sec)

mysql> grant all  on *.* to `slave`@`%` with grant option;
Query OK, 0 rows affected (0.08 sec)

接下来就是开启从库133 134mysql终端

说明配置成功。。。

接下来就是设置131主库的数据库，看是否都能同步数据库。。。

查看133 134 是否一致 看截图

数据库一致，说明主从配置很ok。。接下来就是设置主库131数据

mysql> use mytest;
Database changed
mysql> show tables;
Empty set (0.03 sec)

mysql> create table t (
    -> id int primary key,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.15 sec)

mysql> insert into t values(1,'1');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t values(2,'2');
Query OK, 1 row affected (0.02 sec)

mysql> insert into t values(3,'3');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+----+------+
| id | name |
+----+------+
|  1 | 1    |
|  2 | 2    |
|  3 | 3    |
+----+------+
3 rows in set (0.00 sec)
 

设置从库133数据新增（故意造成数据不一致问题）

134从库数据不变

2.主从复制数据一致性校验

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

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

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

1. pt-table-checksum 负责检测MySQL主从数据一致性
2. pt-table-sync负责挡住从数据不一致时修复数据，让他们保存数据的一致性
3. pt-heartbeat 负责监控MySQL主从同步延迟

安装

[root@localhost ~]# yum install perl-IO-Socket-SSL perl-DBD-MySQL perl-Time-HiRes perl perl-DBI -y
[root@localhost home]# yum localinstall percona-toolkit-3.2.1-1.el7.x86_64.rpm
[root@localhost home]# yum list | grep percona-toolkit
percona-toolkit.x86_64                      3.2.1-1.el7                @/percona-toolkit-3.2.1-1.el7.x86_64
[root@localhost home]# pt-table-checksum --help

从上面可以看到实际上数据是不同步的，也就是主库的数据少于从库的数据

使用工具检测

注意常用的参数解释：

--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=mytest --tables=t --user=slave --password=root
Checking if all tables can be checksummed ...
Starting checksum ...
Replica localhost.localdomain has binlog_format ROW 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-check-binlog-format to disable this check.
Replica localhost.localdomain has binlog_format ROW 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-check-binlog-format to disable this check.

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

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

[root@localhost home]# pt-table-checksum --nocheck-replication-filters --replicate=check_data.checksums --no-check-binlog-format --databases=mytest --tables=t --user=slave --password=root
Checking if all tables can be checksummed ...
Starting checksum ...
            TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE
11-11T23:27:10      0      1        3          1       1       0   0.064 mytest.t
 
TS ：完成检查的时间。
ERRORS ：检查时候发生错误和警告的数量。
DIFFS ：0表示一致，1表示不一致。当指定--no-replicate-check时，会一直为0，当指定--replicate-check-only会显示不同的信息。
ROWS ：表的行数。
CHUNKS ：被划分到表中的块的数目。
SKIPPED ：由于错误或警告或过大，则跳过块的数目。
TIME ：执行的时间。
TABLE ：被检查的表名。

3.pt-table-sync工具恢复数据

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

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

在主库中执行

root@localhost home]# pt-table-sync --sync-to-master h=192.168.24.133,u=slave,p=root,P=3306 --databases=mytest --tables=t --print
DELETE FROM `mytest`.`t` WHERE `id`='4' LIMIT 1 /*percona-toolkit src_db:mytest src_tbl:t src_dsn:P=3306,h=192.168.24.131,p=...,u=slave dst_db:mytest dst_tbl:t dst_dsn:P=3306,h=192.168.24.133,p=...,u=slave lock:1 transaction:1 changing_src:1 replicate:0 bidirectional:0 pid:2550 user:root host:localhost.localdomain*/;

如上的操作解释：

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

vi /home/pt-check-sync.sh

#!/usr/bin/env bash

NUM=`pt-table-checksum --nocheck-replication-filters --replicate=check_data.checksums --no-check-binlog-format --databases=mytest --tables=t --user=slave --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 --sync-to-master h=192.168.24.133,u=slave,p=root,P=3306 --databases=mytest --print
pt-table-sync --sync-to-master h=192.168.24.133,u=slave,p=root,P=3306 --databases=mytest --execute
pt-table-sync --sync-to-master h=192.168.24.134,u=slave,p=root,P=3306 --databases=mytest --print
pt-table-sync --sync-to-master h=192.168.24.134,u=slave,p=root,P=3306 --databases=mytest --execute
fi                   

数据不一致恢复成功啦。。。

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

50 23 * * * /home/pt-check-sync.sh

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