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

1. 前言

   
   

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

   
   

2. 安装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

   
   

3. 使用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表中

mysql> show tables from percona;
+-------------------+
| Tables_in_percona |
+-------------------+
| checksums         |
| dsns              |
+-------------------+
2 rows in set (0.00 sec)

    查看这个结果表的内容：

 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

    第二列已经全部是0了，没有数据差异。