MySQL Replication数据不一致

  1. 云栖社区>
  2. 博客>
  3. 正文

MySQL Replication数据不一致

技术小胖子 2017-11-18 20:38:00 浏览1662
展开阅读全文

mha文章:http://467754239.blog.51cto.com/4878013/1695175 j_0026.gif


关于如何验证主从数据的不一致

pt-table-checksum,它可以检测master和slave上表结构和数据是否一致


如何解决主从数据不一致

pt-table-sync,它可以修复主从数据不一致


这两款工具都是由Percona公司提供的,Percona在MySQL的日常管理提供了很多类似的工具。


适用场景

pt-table-checksum是一个在线验证主从数据一致性的工具,主要用于以下场景

1. 数据迁移前后,进行数据一致性检查

2. 当主从复制出现问题,待修改完成后,对主从数据一致性检查

3. 把从库当成主库,进行数据更新,产生了'脏数据'

4. 定期校验


Percona的文档地址:https://www.percona.com/doc/percona-toolkit/2.2/index.html


一、环境准备

准备一个主从架构的环境,建议是新配置的主从环境,并验证主从配置是正常的。

1
2
3
4
5
6
7
8
9
1、地址规划
192.168.1.    master(主机名和角色)
192.168.1.    slave(主机名和角色)
  
2、验证主从是否一致
登录slave主机,查看主从同步状态
[root@slave ~]# mysql -uroot -p123456 -e 'show slave status\G;' |egrep "(Slave_IO_Running:|Slave_SQL_Running:)"
             Slave_IO_Running: Yes    #表示主从ok
            Slave_SQL_Running: Yes


二、安装

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
# yum install perl-DBI perl-CPAN perl-devel perl-Time-HiRes perl-DBD-mysql perl-IO-Socket-SSL perl-TermReadKey -y
# wget https://www.percona.com/downloads/percona-toolkit/2.2.15/RPM/percona-toolkit-2.2.15-1.noarch.rpm
# rpm -ivh percona-toolkit-2.2.15-1.noarch.rpm 
  
查看软件包提供了哪些可用的工具
# rpm -ql percona-toolkit |grep '/usr/bin'
/usr/bin/pt-align
/usr/bin/pt-archiver
/usr/bin/pt-config-diff
/usr/bin/pt-deadlock-logger
/usr/bin/pt-diskstats
/usr/bin/pt-duplicate-key-checker
/usr/bin/pt-fifo-split
/usr/bin/pt-find
/usr/bin/pt-fingerprint
/usr/bin/pt-fk-error-logger
/usr/bin/pt-heartbeat
/usr/bin/pt-index-usage
/usr/bin/pt-ioprofile
/usr/bin/pt-kill
/usr/bin/pt-mext
/usr/bin/pt-mysql-summary
/usr/bin/pt-online-schema-change
/usr/bin/pt-pmp
/usr/bin/pt-query-digest
/usr/bin/pt-show-grants
/usr/bin/pt-sift
/usr/bin/pt-slave-delay
/usr/bin/pt-slave-find
/usr/bin/pt-slave-restart
/usr/bin/pt-stalk
/usr/bin/pt-summary
/usr/bin/pt-table-checksum
/usr/bin/pt-table-sync
/usr/bin/pt-table-usage
/usr/bin/pt-upgrade
/usr/bin/pt-variable-advisor
/usr/bin/pt-visual-explain


三、介绍pt-table-checksum

用法:

pt-table-checksum

描述:

在主master上通过执行校验的查询对复制的一致性进行检查,对比主从的校验值,从而产生结果。DSN指向的是主的地址.


不指定任何参数,会直接对本地的所有数据库的表进行检查。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
[root@master ~]# pt-table-checksum -uroot -p123456 --no-check-binlog-format -S /tmp/mysql.sock 
            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
09-18T17:35:00      0      0        2       1       0   0.012 dbtest.tb
09-18T17:35:00      0      0        0       1       0   0.013 mysql.columns_priv
09-18T17:35:00      0      0        0       1       0   0.012 mysql.db
09-18T17:35:00      0      0        0       1       0   0.014 mysql.event
09-18T17:35:00      0      0        0       1       0   0.015 mysql.func
09-18T17:35:01      0      0       40       1       0   0.290 mysql.help_category
09-18T17:35:01      0      0      608       1       0   0.275 mysql.help_keyword
09-18T17:35:01      0      0     1216       1       0   0.284 mysql.help_relation
09-18T17:35:02      0      0      583       1       0   0.343 mysql.help_topic
09-18T17:35:02      0      0        0       1       0   0.040 mysql.ndb_binlog_index
09-18T17:35:02      0      0        0       1       0   0.039 mysql.plugin
09-18T17:35:02      0      0        0       1       0   0.035 mysql.proc
09-18T17:35:02      0      0        0       1       0   0.013 mysql.procs_priv
09-18T17:35:02      0      0        1       1       0   0.014 mysql.proxies_priv
09-18T17:35:02      0      0        0       1       0   0.014 mysql.servers
09-18T17:35:02      0      0        0       1       0   0.014 mysql.tables_priv
09-18T17:35:02      0      0        0       1       0   0.015 mysql.time_zone
09-18T17:35:02      0      0        0       1       0   0.015 mysql.time_zone_leap_second
09-18T17:35:02      0      0        0       1       0   0.014 mysql.time_zone_name
09-18T17:35:02      0      0        0       1       0   0.014 mysql.time_zone_transition
09-18T17:35:02      0      0        0       1       0   0.014 mysql.time_zone_transition_type
09-18T17:35:02      0      1        4       1       0   0.015 mysql.user
09-18T17:35:02      0      1        1       1       0   0.014 test.checksums


接下来,来做个案例来测试工具的可用性。

这里可以分两种情况

(1)主从数据一致的情况下的比较

(2)模拟主从数据不一致的情况下的比较


首先,我们看第一种主从数据一致的比较

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
为了试验从0做起,这里我从开始模拟数据
1、登录master创建模拟需要的库、表并插入数据
[root@master ~]# mysql -uroot -p123456
mysql> CREATE DATABASE IF NOT EXISTS dbtest;
mysql> USE dbtest;
mysql> CREATE TABLE tb (id int primary key not null auto_increment,name varchar(30),age int) ENGINE=InnoDB;
mysql> INSERT INTO tb(name,age) VALUES ('zhengyansheng','25');
mysql> INSERT INTO tb(name,age) VALUES ('qinheng','24');
mysql> SELECT * FROM tb;
+----+---------------+------+
id | name          | age  |
+----+---------------+------+
|  1 | zhengyansheng |   25 |
|  2 | qinheng       |   24 |
+----+---------------+------+
  
2、登录slave查看是否已经同步到数据
[root@slave ~]# mysql -uroot -p123456 -e 'USE dbtest;SELECT * FROM tb;'
+----+---------------+------+
id | name          | age  |
+----+---------------+------+
|  1 | zhengyansheng |   25 |
|  2 | qinheng       |   24 |
+----+---------------+------+
如果得到这样的结果,表明主从同步是正常的。
  
3、工具的验证
pt-table-checksum [OPTIONS] [DSN]
pt-table-checksum:在master上通过执行校验的查询对复制的一致性进行检查,对比主从的校验值,从而产生结果。DSN指向的是主的地址,该工具的退出状态不为零。
  
[root@master ~]# pt-table-checksum --user=root --password=123456 --host=192.168.1.105 --port=3306 --databases=dbtest --tables=tb --nocheck-replication-filters --no-check-binlog-format --replicate=test.checksums
            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
09-18T15:27:05      0      0        2       1       0   0.308 dbtest.tb
  
TS            :完成检查的时间。
ERRORS        :检查时候发生错误和警告的数量。
DIFFS         :0表示一致,1表示不一致。当指定--no-replicate-check时,会一直为0,当指定--replicate-check-only会显示不同的信息。
ROWS          :表的行数。
CHUNKS        :被划分到表中的块的数目。
SKIPPED       :由于错误或警告或过大,则跳过块的数目。
TIME          :执行的时间。
TABLE         :被检查的表名。
  
参数的意义:
--nocheck-replication-filters :不检查复制过滤器,建议启用。后面可以用--databases来指定需要检查的数据库。
--no-check-binlog-format      : 不检查复制的binlog模式,要是binlog模式是ROW,则会报错。
--replicate-check-only :只显示不同步的信息。
--replicate=   :把checksum的信息写入到指定表中,建议直接写到被检查的数据库当中。 
--databases=   :指定需要被检查的数据库,多个则用逗号隔开。
--tables=      :指定需要被检查的表,多个用逗号隔开
h=127.0.0.1    :Master的地址
u=root         :用户名
p=123456       :密码
P=3306         :端口
  
通过DIFFS参数可以判断主从数据是否一致。并且也可以通过指定--replicate=rep_test.checksums 参数,就说明把检查信息都写到了checksums表中。
进入SLAVE相应的库中查看checksums表的信息:
[root@slave ~]# mysql -uroot -p123456 -e 'USE test;SELECT * FROM checksums\G;'
Warning: Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
            db: dbtest
           tbl: tb
         chunk: 1
    chunk_time: 0.001182
   chunk_index: NULL
lower_boundary: NULL
upper_boundary: NULL
      this_crc: 5ede99f4    #从的
      this_cnt: 2           #从的
    master_crc: 5ede99f4    #主的
    master_cnt: 2           #主的
            ts: 2015-09-18 15:27:04
              
通过上面的 this_crc <> master_crc 更能清楚的看出他们的不一致了,通过chunk知道是这个张表的哪个块上的记录出现不一致。

其次,我们来看主从数据不一致的情况

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
1、登录slave主机,手动向dbtest数据库中的tb表插入一条数据
[root@slave ~]# mysql -uroot -p123456 -e 'USE dbtest; INSERT INTO tb(name,age) VALUES ("liyuntang",25);'
[root@slave ~]# mysql -uroot -p123456 -e 'USE dbtest; SELECT * FROM tb;'
+----+---------------+------+
id | name          | age  |
+----+---------------+------+
|  1 | zhengyansheng |   25 |
|  2 | qinheng       |   24 |
|  3 | liyuntang     |   25 |
+----+---------------+------+
  
2、用工具验证
[root@master ~]# pt-table-checksum --user=root --password=123456 --host=192.168.1.105 --port=3306 --databases=dbtest --tables=tb --nocheck-replication-filters --no-check-binlog-format --replicate=test.checksums
            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
09-18T15:41:16      0      1        2       1       0   0.013 dbtest.tb
  
这个时候在DIFFS的值就编程1了,表示主从已经不同步了。如果看更具体的可以在slave主机的查看
[root@slave ~]# mysql -uroot -p123456 -e 'USE test;SELECT * FROM checksums\G;'
*************************** 1. row ***************************
            db: dbtest
           tbl: tb
         chunk: 1
    chunk_time: 0.000746
   chunk_index: NULL
lower_boundary: NULL
upper_boundary: NULL
      this_crc: 4b6ecf1    #从
      this_cnt: 3
    master_crc: 5ede99f4   #主
    master_cnt: 2
            ts: 2015-09-18 15:41:16
这里你会发现this-crc和master_crc的值已经不一样的,说明主从数据库不一致。


四、介绍pt-table-sync

用法:pt-table-sync [OPTIONS] DSN [DSN]

描述:高效的同步MySQL表之间的数据,他可以做单向和双向同步的表数据。他可以同步单个表,也可以同步整个库。它不同步表结构、索引、或任何其他模式对象。所以在修复一致性之前需要保证他们表存在。


方式1:

1
2
3
先master的IP地址,在slave的IP地址
# pt-table-sync --replicate=test.checksums --charset=utf8 h=127.0.0.1,u=root,p=123456,P=3306 h=192.168.1.106,u=root,p=123456,P=3306 --print
# pt-table-sync --replicate=test.checksums --charset=utf8 h=127.0.0.1,u=root,p=123456,P=3306 h=192.168.1.106,u=root,p=123456,P=3306 --execute


方式2:

1
2
3
通过--sync-to-master参数指定一个slave的IP地址就可以了
# pt-table-sync --replicate=test.checksums --databases=dbtest --tables=tb --charset=utf8 --sync-to-master h=192.168.1.106,u=root,p=123456,P=3306 --print
# pt-table-sync --replicate=test.checksums --databases=dbtest --tables=tb --charset=utf8 --sync-to-master h=192.168.1.106,u=root,p=123456,P=3306 --execute


参数的意义

1
2
3
4
5
6
7
8
9
10
--replicate=          :指定通过pt-table-checksum得到的表,这2个工具差不多都会一直用。
--databases=          : 指定执行同步的数据库,多个用逗号隔开。
--tables=          :指定执行同步的表,多个用逗号隔开。
--sync-to-master   :指定一个DSN,即从的IP,他会通过show processlist或show slave status 去自动的找主。
h=127.0.0.1          :服务器地址,命令里有2个ip,第一次出现的是M的地址,第2次是Slave的地址。
u=root              :帐号。
p=123456              :密码。
--ask-pass          : 交互式输入密码。
--print              :打印,但不执行命令。
--execute          :执行命令。


该工具检查的表,需要检查连接的帐号需要有很高的权限,在一般权限行需要加SELECT, PROCESS, SUPER, REPLICATION SLAVE等权限,测试方便我直接给了ALL的权限,pt-table-checksum 和 pt-table-sync 一起互补使用


错误汇总

1
2
3
4
5
6
7
8
9
10
11
错误1:
Can't make changes on the master because no unique index exists at /usr/bin/pt-table-sync line 10660
原因是:表中没有唯一索引或则主键则会报错
 
错误2:
09-14T18:04:36 install_driver(mysql) failed: Attempt to reload DBD/mysql.pm aborted.
Compilation failed in require at (eval 23) line 3.
 
 at /usr/bin/pt-table-checksum line 1581
解决办法:
ln -sv /usr/lib64/mysql/libmysqlclient.so.16 /lib64/




     本文转自zys467754239 51CTO博客,原文链接:http://blog.51cto.com/467754239/1696262,如需转载请自行联系原作者





网友评论

登录后评论
0/500
评论
技术小胖子
+ 关注