导航：首页 > 数据库 >

多源复制环境下数据的校验与修正

发表于：2024-10-08 作者：千家信息网编辑

千家信息网最后更新 2024年10月08日，前言前些日子，做多源GTID复制时，因为没有对mysql库的复制做隔离，导致复制产生冲突，在修正错误时又使用了错误的方法，导致GTID产生了断层。有断层必然会存在数据的不一致安装percona-too

千家信息网最后更新 2024年10月08日多源复制环境下数据的校验与修正

前言

前些日子，做多源GTID复制时，因为没有对mysql库的复制做隔离，导致复制产生冲突，在修正错误时又使用了错误的方法，导致GTID产生了断层。有断层必然会存在数据的不一致
安装percona-tools工具

①wget -N http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm -N /root/
②cd /root/
③yum install percona-release-0.1-4.noarch.rpm
使用pt-table-checksum命令查找主从之间数据的异同

①要排查的主和从之间一个端口是3306一个是3310，端口不同，在checksum工具中要使用--recursion-method选项调用dsns表中预置的从库信息
②在主库上创建percona库，dsns表，
mysql>CREATE TABLE `dsns` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`parent_id` int(11) DEFAULT NULL,
`dsn` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
③插入从库信息
mysql>insert into dsns values(1,1,'h=192.168.1.11,u=root,p=sa123456,P=3310')
④检查主从差异（在主库上执行）
centos#:pt-table-checksum --user=root \
--password=sa123456 \

--port=3306 \

--socket=/data/mysql/3306/tmp/mysql3306.sock \

--no-check-binlog-format \不检查binlog模式

（默认不是statment格式就停止）

（设置后，会将主库设置成statment格式）

--max-load=Threads_running=11000 \控制最大负载

--databases=zabbix \指定检查的库

--nocheck-replication-filters \有过滤器时仍继续,默认停止（binlog_igore_db）

(replicate_do_db）

--recursion-method=dsn=h=192.168.1.31,D=percona,t=dsns

输出结果如下：

TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
05-24T16:08:37 0 0 0 1 0 0.149 mysql.columns_priv
05-24T16:08:37 0 0 2 1 0 0.021 mysql.db
05-24T16:08:37 0 0 2 1 0 0.270 mysql.engine_cost
05-24T16:08:37 0 0 0 1 0 0.270 mysql.event
05-24T16:08:38 0 0 0 1 0 0.301 mysql.func
05-24T16:08:38 0 1 1 1 0 0.016 mysql.gtid_executed
05-24T16:08:38 0 0 40 1 0 0.056 mysql.help_category
05-24T16:08:38 0 0 682 1 0 0.271 mysql.help_keyword
05-24T16:08:38 0 0 1340 1 0 0.034 mysql.help_relation
05-24T16:08:38 0 0 637 1 0 0.307 mysql.help_topic
05-24T16:08:39 0 0 0 1 0 0.297 mysql.ndb_binlog_index
05-24T16:08:39 0 0 0 1 0 0.269 mysql.procs_priv
05-24T16:08:39 0 0 1 1 0 0.020 mysql.proxies_priv
05-24T16:08:39 0 0 6 1 0 0.019 mysql.server_cost
05-24T16:08:39 0 0 0 1 0 0.019 mysql.servers
05-24T16:08:39 0 0 1 1 0 0.016 mysql.tables_priv
05-24T16:08:39 0 0 0 1 0 0.268 mysql.time_zone
05-24T16:08:40 0 0 0 1 0 0.271 mysql.time_zone_leap_second
05-24T16:08:40 0 0 0 1 0 0.020 mysql.time_zone_name
05-24T16:08:40 0 0 0 1 0 0.273 mysql.time_zone_transition
05-24T16:08:40 0 0 0 1 0 0.267 mysql.time_zone_transition_type
05-24T16:08:40 0 1 7 1 0 0.020 mysql.user
05-24T16:08:40 0 0 1 1 0 0.030 percona.dsns
05-24T16:08:41 0 0 0 1 0 0.344 zabbix.acknowledges
05-24T16:08:41 0 0 5 1 0 0.285 zabbix.actions
05-24T16:08:41 0 0 0 1 0 0.142 zabbix.alerts
05-24T16:08:41 0 0 60 1 0 0.323 zabbix.application_template
05-24T16:08:42 0 0 196 1 0 0.304 zabbix.applications
05-24T16:08:42 0 0 358 1 0 0.289 zabbix.auditlog
05-24T16:08:42 0 0 27 1 0 0.056 zabbix.auditlog_details
05-24T16:08:42 0 0 0 1 0 0.312 zabbix.autoreg_host
05-24T16:08:43 0 0 8 1 0 0.290 zabbix.conditions
05-24T16:08:43 0 0 1 1 0 0.270 zabbix.config
05-24T16:08:43 0 0 1 1 0 0.043 zabbix.dbversion
05-24T16:08:43 0 0 1 1 0 0.286 zabbix.dchecks
05-24T16:08:43 0 0 0 1 0 0.021 zabbix.dhosts
05-24T16:08:44 0 0 1 1 0 0.269 zabbix.drules
05-24T16:08:44 0 0 0 1 0 0.286 zabbix.dservices
05-24T16:08:44 0 0 0 1 0 0.017 zabbix.escalations
05-24T16:08:44 0 0 1585 1 0 0.321 zabbix.events
05-24T16:08:44 0 0 4 1 0 0.032 zabbix.expressions
05-24T16:08:44 0 0 436 1 0 0.269 zabbix.functions
05-24T16:08:44 0 0 1 1 0 0.033 zabbix.globalmacro
05-24T16:08:45 0 0 0 1 0 0.290 zabbix.globalvars
05-24T16:08:45 0 0 16 1 0 0.286 zabbix.graph_discovery
05-24T16:08:45 0 0 4 1 0 0.306 zabbix.graph_theme
05-24T16:08:46 0 0 223 1 0 0.320 zabbix.graphs
05-24T16:08:46 0 0 784 1 0 0.294 zabbix.graphs_items
05-24T16:08:47 0 0 0 1 0 0.572 zabbix.group_discovery
05-24T16:08:47 0 0 5 1 0 0.033 zabbix.group_prototype
05-24T16:08:47 0 0 9 1 0 0.271 zabbix.groups
05-24T16:08:59 0 11 444663 14 0 12.563 zabbix.history

05-24T16:09:00 0 0 0 1 0 0.289 zabbix.history_log

05-24T16:09:00 0 0 460 1 0 0.307 zabbix.history_str
05-24T16:09:00 0 0 0 1 0 0.017 zabbix.history_str_sync
05-24T16:09:00 0 0 0 1 0 0.034 zabbix.history_sync
05-24T16:09:00 0 0 0 1 0 0.269 zabbix.history_text
05-24T16:09:04 0 4 137672 6 0 3.692 zabbix.history_uint
05-24T16:09:04 0 0 0 1 0 0.034 zabbix.history_uint_sync
05-24T16:09:04 0 0 2 1 0 0.289 zabbix.host_discovery
05-24T16:09:05 0 0 0 1 0 0.023 zabbix.host_inventory
05-24T16:09:05 0 0 0 1 0 0.278 zabbix.hostmacro
05-24T16:09:05 0 0 46 1 0 0.264 zabbix.hosts
05-24T16:09:05 0 0 45 1 0 0.285 zabbix.hosts_groups
05-24T16:09:05 0 0 26 1 0 0.034 zabbix.hosts_templates
05-24T16:09:05 0 0 0 1 0 0.015 zabbix.housekeeper
05-24T16:09:06 0 0 0 1 0 0.283 zabbix.httpstep
05-24T16:09:06 0 0 0 1 0 0.298 zabbix.httpstepitem
05-24T16:09:06 0 0 0 1 0 0.034 zabbix.httptest
05-24T16:09:06 0 0 0 1 0 0.281 zabbix.httptestitem
05-24T16:09:06 0 0 0 1 0 0.043 zabbix.icon_map
05-24T16:09:06 0 0 0 1 0 0.059 zabbix.icon_mapping
05-24T16:09:07 0 0 22 1 0 0.289 zabbix.ids
05-24T16:09:07 0 0 187 1 0 0.362 zabbix.p_w_picpaths
05-24T16:09:07 0 0 5 1 0 0.019 zabbix.interface
05-24T16:09:07 0 0 0 1 0 0.027 zabbix.interface_discovery
05-24T16:09:07 0 0 217 1 0 0.021 zabbix.item_discovery
05-24T16:09:07 0 0 1297 1 0 0.308 zabbix.items
05-24T16:09:08 0 0 1470 1 0 0.313 zabbix.items_applications
05-24T16:09:08 0 0 0 1 0 0.037 zabbix.maintenances
05-24T16:09:08 0 0 0 1 0 0.319 zabbix.maintenances_groups
05-24T16:09:08 0 0 0 1 0 0.030 zabbix.maintenances_hosts
05-24T16:09:08 0 0 0 1 0 0.286 zabbix.maintenances_windows
05-24T16:09:09 0 0 55 1 0 0.268 zabbix.mappings
05-24T16:09:09 0 0 0 1 0 0.292 zabbix.media
05-24T16:09:09 0 0 3 1 0 0.295 zabbix.media_type
05-24T16:09:10 0 0 0 1 0 0.274 zabbix.node_cksum
05-24T16:09:10 0 0 0 1 0 0.292 zabbix.nodes
05-24T16:09:10 0 0 0 1 0 0.276 zabbix.opcommand
05-24T16:09:10 0 0 0 1 0 0.036 zabbix.opcommand_grp
05-24T16:09:11 0 0 0 1 0 0.284 zabbix.opcommand_hst
05-24T16:09:11 0 0 0 1 0 0.288 zabbix.opconditions
05-24T16:09:11 0 0 6 1 0 0.322 zabbix.operations
05-24T16:09:11 0 0 1 1 0 0.304 zabbix.opgroup
05-24T16:09:11 0 0 4 1 0 0.025 zabbix.opmessage
05-24T16:09:12 0 0 4 1 0 0.292 zabbix.opmessage_grp
05-24T16:09:12 0 0 0 1 0 0.048 zabbix.opmessage_usr
05-24T16:09:12 0 0 1 1 0 0.283 zabbix.optemplate
05-24T16:09:12 0 0 387 1 0 0.337 zabbix.profiles
05-24T16:09:13 0 0 0 1 0 0.265 zabbix.proxy_autoreg_host
05-24T16:09:13 0 0 0 1 0 0.265 zabbix.proxy_dhistory
05-24T16:09:13 0 0 0 1 0 0.270 zabbix.proxy_history
05-24T16:09:13 0 0 3 1 0 0.029 zabbix.regexps
05-24T16:09:14 0 0 0 1 0 0.280 zabbix.rights
05-24T16:09:14 0 0 13 1 0 0.045 zabbix.screens
05-24T16:09:14 0 0 92 1 0 0.271 zabbix.screens_items
05-24T16:09:14 0 0 3 1 0 0.421 zabbix.scripts
05-24T16:09:15 0 0 0 1 0 0.286 zabbix.service_alarms
05-24T16:09:15 0 0 0 1 0 0.271 zabbix.services
05-24T16:09:15 0 0 0 1 0 0.285 zabbix.services_links
05-24T16:09:16 0 0 0 1 0 0.287 zabbix.services_times
05-24T16:09:16 0 0 2 1 0 0.045 zabbix.sessions
05-24T16:09:16 0 0 0 1 0 0.036 zabbix.slides
05-24T16:09:16 0 0 0 1 0 0.300 zabbix.slideshows
05-24T16:09:16 0 0 0 1 0 0.039 zabbix.sysmap_element_url
05-24T16:09:16 0 0 0 1 0 0.045 zabbix.sysmap_url
05-24T16:09:16 0 0 1 1 0 0.310 zabbix.sysmaps
05-24T16:09:16 0 0 1 1 0 0.057 zabbix.sysmaps_elements
05-24T16:09:16 0 0 0 1 0 0.058 zabbix.sysmaps_link_triggers
05-24T16:09:17 0 0 0 1 0 0.308 zabbix.sysmaps_links
05-24T16:09:17 0 0 0 1 0 0.289 zabbix.timeperiods
05-24T16:09:18 0 0 3591 1 0 0.523 zabbix.trends
05-24T16:09:18 0 0 13271 1 0 0.371 zabbix.trends_uint
05-24T16:09:18 0 0 55 1 0 0.281 zabbix.trigger_depends
05-24T16:09:18 0 0 24 1 0 0.270 zabbix.trigger_discovery
05-24T16:09:19 0 0 401 1 0 0.283 zabbix.triggers
05-24T16:09:19 0 0 1 1 0 0.296 zabbix.user_history
05-24T16:09:19 0 0 2 1 0 0.294 zabbix.users
05-24T16:09:20 0 0 2 1 0 0.287 zabbix.users_groups
05-24T16:09:20 0 0 5 1 0 0.021 zabbix.usrgrp
05-24T16:09:20 0 0 13 1 0 0.324 zabbix.valuemaps

我们可以看到，在业务数据库zabbix中，主从有两个表有数据不一致的情况出现，这里是以主库数据为模板的。

同时这个结果会保存在percona库下的checksum表中

查看这个结果表的内容：

4.用pt-table-sync命令修正主从数据之间的不一致（sync与checksum的dsn语法并不全部相同）

pt-table-sync can run in one of two ways: with --replicate or without. The default is to run without --replicate which causes pt-table-sync to automatically find differences efficiently with one of several algorithms (see "ALGORITHMS"). Alternatively, the value of --replicate, if specified, causes pt-table-sync to use the differences already found by having previously ran pt-table-checksum with its own --replicate option. Strictly speaking, you don't need to use --replicatebecause pt-table-sync can find differences, but many people use --replicate if, for example, they checksum regularly using pt-table-checksum then fix differences as needed with pt-table-sync.这个工具可以自动查找主从差异，可以不依靠checksum得出的结果

在主库上执行：

centos#:pt-table-sync --print \ #先用print，将会输出修正从库数据与主库数据不一致的SQL语句

--no-check-slave \ #percona官方建议用户修正从主库上做（修正语句在主上执行，然后通过binlog传递到从库，即sync-to-master参数，且是从从库上找到主库上，但多源复制存在多个主库，则sync-to-master比较麻烦。所以这儿不采用sync-to-master参数），

--databases=zabbix \ #指定需要修正的业务库

h=192.168.1.31,u=root,p=sa123456 \ #第一个指定主库

h=192.168.1.11,u=root,p=sa123456,P=3310 \ #第二个指定从库

|cat >sync_zabbix.sql #将语句通过管道记录到本机sql文件中。

查看输出的语句：

centos#:less sync_zabbix.sql

可以看出，是从库比主库上多了几条历史记录。为确保数据安全，先在主库上select一下

mysql> select * FROM `zabbix`.`history` WHERE `itemid`='23253' AND ` clock`='1495365513' AND `value`=0.0169 AND `ns`='621077118' LIMIT 1
-> ;
Empty set (0.00 sec)
主库上的确是没有这些数据

确认无误后，对从库数据进行修正（在主库上执行：）

centos#:pt-table-sync --execute \ #直接执行，不输出在屏幕上

--no-check-slave \

--databases=zabbix \ #指定需要修正的业务库

h=192.168.1.31,u=root,p=sa123456 \ #第一个指定主库

h=192.168.1.11,u=root,p=sa123456,P=3310 \ #第二个指定从库

5.最后对数据进行再次校验

06-07T15:02:10 0 0 0 1 0 0.026 zabbix.acknowledges
06-07T15:02:10 0 0 5 1 0 0.024 zabbix.actions
06-07T15:02:10 0 0 0 1 0 0.022 zabbix.alerts
06-07T15:02:10 0 0 60 1 0 0.024 zabbix.application_template
06-07T15:02:10 0 0 196 1 0 0.270 zabbix.applications
06-07T15:02:10 0 0 358 1 0 0.071 zabbix.auditlog
06-07T15:02:10 0 0 27 1 0 0.048 zabbix.auditlog_details
06-07T15:02:10 0 0 0 1 0 0.020 zabbix.autoreg_host
06-07T15:02:11 0 0 8 1 0 0.272 zabbix.conditions
06-07T15:02:11 0 0 1 1 0 0.288 zabbix.config
06-07T15:02:11 0 0 1 1 0 0.053 zabbix.dbversion
06-07T15:02:11 0 0 1 1 0 0.024 zabbix.dchecks
06-07T15:02:11 0 0 0 1 0 0.016 zabbix.dhosts
06-07T15:02:11 0 0 1 1 0 0.018 zabbix.drules
06-07T15:02:11 0 0 0 1 0 0.267 zabbix.dservices
06-07T15:02:11 0 0 0 1 0 0.015 zabbix.escalations
06-07T15:02:12 0 0 2707 1 0 0.273 zabbix.events
06-07T15:02:12 0 0 4 1 0 0.289 zabbix.expressions
06-07T15:02:12 0 0 436 1 0 0.016 zabbix.functions
06-07T15:02:12 0 0 1 1 0 0.058 zabbix.globalmacro
06-07T15:02:12 0 0 0 1 0 0.021 zabbix.globalvars
06-07T15:02:12 0 0 16 1 0 0.021 zabbix.graph_discovery
06-07T15:02:12 0 0 4 1 0 0.017 zabbix.graph_theme
06-07T15:02:12 0 0 223 1 0 0.017 zabbix.graphs
06-07T15:02:12 0 0 784 1 0 0.270 zabbix.graphs_items
06-07T15:02:12 0 0 0 1 0 0.019 zabbix.group_discovery
06-07T15:02:12 0 0 5 1 0 0.034 zabbix.group_prototype
06-07T15:02:12 0 0 9 1 0 0.015 zabbix.groups
06-07T15:02:17 0 0 1215172 12 0 4.993 zabbix.history
06-07T15:02:18 0 0 0 1 0 0.039 zabbix.history_log
06-07T15:02:18 0 0 944 1 0 0.280 zabbix.history_str
06-07T15:02:18 0 0 0 1 0 0.291 zabbix.history_str_sync
06-07T15:02:18 0 0 0 1 0 0.041 zabbix.history_sync
06-07T15:02:18 0 0 0 1 0 0.046 zabbix.history_text
06-07T15:02:19 0 0 287650 1 0 1.276 zabbix.history_uint
06-07T15:02:20 0 0 0 1 0 0.020 zabbix.history_uint_sync
06-07T15:02:20 0 0 2 1 0 0.018 zabbix.host_discovery
06-07T15:02:20 0 0 0 1 0 0.016 zabbix.host_inventory
06-07T15:02:20 0 0 0 1 0 0.015 zabbix.hostmacro
06-07T15:02:20 0 0 46 1 0 0.017 zabbix.hosts
06-07T15:02:20 0 0 45 1 0 0.016 zabbix.hosts_groups
06-07T15:02:20 0 0 26 1 0 0.015 zabbix.hosts_templates
06-07T15:02:20 0 0 0 1 0 0.017 zabbix.housekeeper
06-07T15:02:20 0 0 0 1 0 0.040 zabbix.httpstep
06-07T15:02:20 0 0 0 1 0 0.020 zabbix.httpstepitem
06-07T15:02:20 0 0 0 1 0 0.015 zabbix.httptest
06-07T15:02:20 0 0 0 1 0 0.015 zabbix.httptestitem
06-07T15:02:20 0 0 0 1 0 0.043 zabbix.icon_map
06-07T15:02:20 0 0 0 1 0 0.017 zabbix.icon_mapping
06-07T15:02:20 0 0 22 1 0 0.266 zabbix.ids
06-07T15:02:20 0 0 187 1 0 0.296 zabbix.p_w_picpaths
06-07T15:02:20 0 0 5 1 0 0.034 zabbix.interface
06-07T15:02:20 0 0 0 1 0 0.062 zabbix.interface_discovery
06-07T15:02:21 0 0 217 1 0 0.025 zabbix.item_discovery
06-07T15:02:21 0 0 1297 1 0 0.281 zabbix.items
06-07T15:02:21 0 0 1470 1 0 0.283 zabbix.items_applications
06-07T15:02:21 0 0 0 1 0 0.015 zabbix.maintenances
06-07T15:02:21 0 0 0 1 0 0.026 zabbix.maintenances_groups
06-07T15:02:21 0 0 0 1 0 0.017 zabbix.maintenances_hosts
06-07T15:02:21 0 0 0 1 0 0.016 zabbix.maintenances_windows
06-07T15:02:21 0 0 55 1 0 0.018 zabbix.mappings
06-07T15:02:21 0 0 0 1 0 0.270 zabbix.media
06-07T15:02:22 0 0 3 1 0 0.044 zabbix.media_type
06-07T15:02:22 0 0 0 1 0 0.023 zabbix.node_cksum
06-07T15:02:22 0 0 0 1 0 0.019 zabbix.nodes
06-07T15:02:22 0 0 0 1 0 0.036 zabbix.opcommand
06-07T15:02:22 0 0 0 1 0 0.016 zabbix.opcommand_grp
06-07T15:02:22 0 0 0 1 0 0.015 zabbix.opcommand_hst
06-07T15:02:22 0 0 0 1 0 0.015 zabbix.opconditions
06-07T15:02:22 0 0 6 1 0 0.014 zabbix.operations
06-07T15:02:22 0 0 1 1 0 0.266 zabbix.opgroup
06-07T15:02:22 0 0 4 1 0 0.018 zabbix.opmessage
06-07T15:02:22 0 0 4 1 0 0.018 zabbix.opmessage_grp
06-07T15:02:22 0 0 0 1 0 0.016 zabbix.opmessage_usr
06-07T15:02:22 0 0 1 1 0 0.017 zabbix.optemplate
06-07T15:02:22 0 0 387 1 0 0.282 zabbix.profiles
06-07T15:02:22 0 0 0 1 0 0.081 zabbix.proxy_autoreg_host
06-07T15:02:22 0 0 0 1 0 0.021 zabbix.proxy_dhistory
06-07T15:02:22 0 0 0 1 0 0.017 zabbix.proxy_history
06-07T15:02:23 0 0 3 1 0 0.270 zabbix.regexps
06-07T15:02:23 0 0 0 1 0 0.038 zabbix.rights
06-07T15:02:23 0 0 13 1 0 0.039 zabbix.screens
06-07T15:02:23 0 0 92 1 0 0.020 zabbix.screens_items
06-07T15:02:23 0 0 3 1 0 0.016 zabbix.scripts
06-07T15:02:23 0 0 0 1 0 0.268 zabbix.service_alarms
06-07T15:02:23 0 0 0 1 0 0.062 zabbix.services
06-07T15:02:23 0 0 0 1 0 0.021 zabbix.services_links
06-07T15:02:23 0 0 0 1 0 0.018 zabbix.services_times
06-07T15:02:23 0 0 2 1 0 0.016 zabbix.sessions
06-07T15:02:23 0 0 0 1 0 0.016 zabbix.slides
06-07T15:02:23 0 0 0 1 0 0.015 zabbix.slideshows
06-07T15:02:24 0 0 0 1 0 0.268 zabbix.sysmap_element_url
06-07T15:02:24 0 0 0 1 0 0.015 zabbix.sysmap_url
06-07T15:02:24 0 0 1 1 0 0.043 zabbix.sysmaps
06-07T15:02:24 0 0 1 1 0 0.015 zabbix.sysmaps_elements
06-07T15:02:24 0 0 0 1 0 0.057 zabbix.sysmaps_link_triggers
06-07T15:02:24 0 0 0 1 0 0.022 zabbix.sysmaps_links
06-07T15:02:24 0 0 0 1 0 0.025 zabbix.timeperiods
06-07T15:02:24 0 0 97327 1 0 0.420 zabbix.trends
06-07T15:02:24 0 0 28132 1 0 0.332 zabbix.trends_uint
06-07T15:02:25 0 0 55 1 0 0.028 zabbix.trigger_depends
06-07T15:02:25 0 0 24 1 0 0.304 zabbix.trigger_discovery
06-07T15:02:25 0 0 401 1 0 0.281 zabbix.triggers
06-07T15:02:25 0 0 1 1 0 0.016 zabbix.user_history
06-07T15:02:25 0 0 2 1 0 0.269 zabbix.users
06-07T15:02:25 0 0 2 1 0 0.014 zabbix.users_groups
06-07T15:02:25 0 0 5 1 0 0.025 zabbix.usrgrp
06-07T15:02:25 0 0 13 1 0 0.018 zabbix.valuemaps