Shulou(Shulou.com)06/01 Report--

Preface

A few days ago, when doing multi-source GTID replication, because there was no isolation for the replication of the mysql library, the replication conflict occurred, and the wrong method was used to correct the error, resulting in a fault in the GTID. If there is a fault, there is bound to be data inconsistency.

Install percona-tools 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

Use the pt-table-checksum command to find the similarities and differences between master and slave data

One port between the master and slave to be checked by ① is 3306 and the other is 3310. The port is different. Use the-- recursion-method option in the checksum tool to call the slave library information preset in the dsns table.

② creates a percona library, dsns table, on the main library

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

③ insert slave library information

Mysql > insert into dsns values (192.168.1.11, 192.168.1.11, 192.168.1.11, 123456, 123456, 3310')

④ checks for master-slave differences (executed on the master library)

Centos#:pt-table-checksum-- user=root\

-- password=sa123456\

-- port=3306\

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

-- no-check-binlog-format\ does not check the binlog mode

(stop if it is not in statment format by default)

(after setting, the main library will be set to statment format)

-- max-load=Threads_running=11000\ controls the maximum load

-- databases=zabbix\ specifies the library to check

-- nocheck-replication-filters\ continues when there is a filter, and stops by default (binlog_igore_db)

(replicate_do_db)

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

The output is as follows:

TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE

05-24T16:08:37 0 0 1 0 0.149 mysql.columns_priv

05-24T16:08:37 0 021 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 1 0 0.270 mysql.event

05-24T16:08:38 0 0 01 0 0.301 mysql.func

05-24T16:08:38 01 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 0682 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 0637 1 0 0.307 mysql.help_topic

05-24T16:08:39 0 0 1 0 0.297 mysql.ndb_binlog_index

05-24T16:08:39 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 01 0 0.019 mysql.servers

05-24T16:08:39 0 01 1 0 0.016 mysql.tables_priv

05-24T16:08:39 0 0 1 0 0.268 mysql.time_zone

05-24T16:08:40 0 0 1 0 0.271 mysql.time_zone_leap_second

05-24T16:08:40 0 0 1 0 0.020 mysql.time_zone_name

05-24T16:08:40 0 0 1 0 0.273 mysql.time_zone_transition

05-24T16:08:40 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 1 0 0.344 zabbix.acknowledges

05-24T16:08:41 0 05 1 0 0.285 zabbix.actions

05-24T16:08:41 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 0196 1 0 0.304 zabbix.applications

05-24T16:08:42 0 0358 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 1 0 0.312 zabbix.autoreg_host

05-24T16:08:43 0 08 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 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 1 0 0.286 zabbix.dservices

05-24T16:08:44 0 0 01 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 0436 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 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 0223 1 0 0.320 zabbix.graphs

05-24T16:08:46 0 0784 1 0 0.294 zabbix.graphs_items

05-24T16:08:47 0 0 1 0 0.572 zabbix.group_discovery

05-24T16:08:47 0 05 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 00 0 1 0 0.289 zabbix.history_log

05-24T16:09:00 00 460 1 0 0.307 zabbix.history_str

05-24T16:09:00 00 01 0 0.017 zabbix.history_str_sync

05-24T16:09:00 00 0 1 0 0.034 zabbix.history_sync

05-24T16:09:00 00 0 1 0 0.269 zabbix.history_text

05-24T16:09:04 04 137672 6 0 3.692 zabbix.history_uint

05-24T16:09:04 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 1 0 0.023 zabbix.host_inventory

05-24T16:09:05 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 01 0 0.015 zabbix.housekeeper

05-24T16:09:06 0 0 1 0 0.283 zabbix.httpstep

05-24T16:09:06 0 0 1 0 0.298 zabbix.httpstepitem

05-24T16:09:06 0 0 1 0 0.034 zabbix.httptest

05-24T16:09:06 0 0 1 0 0.281 zabbix.httptestitem

05-24T16:09:06 0 0 1 0 0.043 zabbix.icon_map

05-24T16:09:06 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 0187 1 0 0.362 zabbix.p_w_picpaths

05-24T16:09:07 0 05 1 0 0.019 zabbix.interface

05-24T16:09:07 0 0 1 0 0.027 zabbix.interface_discovery

05-24T16:09:07 0 0217 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 1 0 0.037 zabbix.maintenances

05-24T16:09:08 0 0 1 0 0.319 zabbix.maintenances_groups

05-24T16:09:08 0 0 1 0 0.030 zabbix.maintenances_hosts

05-24T16:09:08 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 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 10 0.274 zabbix.node_cksum

05-24T16:09:10 0 0 10 0.292 zabbix.nodes

05-24T16:09:10 0 0 10 0.276 zabbix.opcommand

05-24T16:09:10 0 0 10 0.036 zabbix.opcommand_grp

05-24T16:09:11 0 0 1 0 0.284 zabbix.opcommand_hst

05-24T16:09:11 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 11 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 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 0387 1 0 0.337 zabbix.profiles

05-24T16:09:13 0 0 1 0 0.265 zabbix.proxy_autoreg_host

05-24T16:09:13 0 0 1 0 0.265 zabbix.proxy_dhistory

05-24T16:09:13 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 1 0 0.280 zabbix.rights

05-24T16:09:14 0 0 13 1 0 0.045 zabbix.screens

05-24T16:09:14 0 092 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 1 0 0.286 zabbix.service_alarms

05-24T16:09:15 0 0 1 0 0.271 zabbix.services

05-24T16:09:15 0 0 1 0 0.285 zabbix.services_links

05-24T16:09:16 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 1 0 0.036 zabbix.slides

05-24T16:09:16 00 0 1 0 0.300 zabbix.slideshows

05-24T16:09:16 0 0 1 0 0.039 zabbix.sysmap_element_url

05-24T16:09:16 0 0 1 0 0.045 zabbix.sysmap_url

05-24T16:09:16 0 0 1 10 0.310 zabbix.sysmaps

05-24T16:09:16 0 0 1 1 0 0.057 zabbix.sysmaps_elements

05-24T16:09:16 0 0 1 0 0.058 zabbix.sysmaps_link_triggers

05-24T16:09:17 0 0 1 0 0.308 zabbix.sysmaps_links

05-24T16:09:17 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 0401 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 05 1 0 0.021 zabbix.usrgrp

05-24T16:09:20 0 0 13 1 0 0.324 zabbix.valuemaps

We can see that there are data inconsistencies between master and slave tables in the business database zabbix. Here, the master database data is used as a template.

At the same time, the result will be saved in the checksum table under the percona library.

Mysql > show tables from percona

+-+

| | Tables_in_percona |

+-+

| | checksums |

| | dsns |

+-+

2 rows in set (0.00 sec)

View the contents of this results table:

4. Correct the inconsistency between master and slave data with the pt-table-sync command (the dsn syntax of sync and checksum are not all the same)

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. This tool can automatically find master-slave differences without relying on the results of checksum.

Execute on the main library:

Centos#:pt-table-sync-- print\ # using print first will output SQL statements that correct the discrepancy between the slave database data and the master database data

-- no-check-slave\ # percona officially recommends that users make corrections from the master database (the correction statement is executed on the master database, and then passed to the slave database through binlog, that is, the sync-to-master parameter, and is found on the master database from the slave database. However, if there are multiple master libraries in multi-source replication, sync-to-master is more troublesome. So the sync-to-master parameter is not used here)

-- databases=zabbix\ # specify the business library that needs to be modified

Hobbies 192.168.1.31 pamphlet sa123456\ # first designated main library

Hobbies 192.168.1.11 the second designated slave library is ubiquitous rootdiary pendant sa123456

| cat > sync_zabbix.sql # record the statement to the native sql file through the pipeline.

View the output statement:

Centos#:less sync_zabbix.sql

As you can see, there are several more historical records from the library than from the main library. To ensure data security, select the main database first.

Mysql > select * FROM `zabbix`.`Secrety`WHERE `itemid` = '23253' AND `clock` =' 1495365513' AND `value` = 0.0169 AND `ns` = '621077118' LIMIT 1

->

Empty set (0.00 sec)

There is indeed no such data on the main database.

After confirming the correctness, correct the slave database data (execute on the master database:)

Centos#:pt-table-sync-execute\ # execute directly and do not output to the screen

-- no-check-slave\

-- databases=zabbix\ # specify the business library that needs to be modified

Hobbies 192.168.1.31 pamphlet sa123456\ # first designated main library

Hobbies 192.168.1.11 the second designated slave library is ubiquitous rootdiary pendant sa123456

5. Finally, the data is checked again.

06-07T15:02:10 0 0 10 0.026 zabbix.acknowledges

06-07T15:02:10 0 0 5 10 0.024 zabbix.actions

06-07T15:02:10 0 0 10 0.022 zabbix.alerts

06-07T15:02:10 0 0 60 10 0.024 zabbix.application_template

06-07T15:02:10 0 0196 10 0.270 zabbix.applications

06-07T15:02:10 0 0358 10 0.071 zabbix.auditlog

06-07T15:02:10 0 0 27 10 0.048 zabbix.auditlog_details

06-07T15:02:10 0 0 10 0.020 zabbix.autoreg_host

06-07T15:02:11 0 0 8 1 0 0.272 zabbix.conditions

06-07T15:02:11 0 0 11 0 0.288 zabbix.config

06-07T15:02:11 0 0 11 0 0.053 zabbix.dbversion

06-07T15:02:11 0 0 11 0 0.024 zabbix.dchecks

06-07T15:02:11 0 0 01 0 0.016 zabbix.dhosts

06-07T15:02:11 0 01 1 0 0.018 zabbix.drules

06-07T15:02:11 0 00 1 0 0.267 zabbix.dservices

06-07T15:02:11 0 0 01 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 0436 1 0 0.016 zabbix.functions

06-07T15:02:12 0 0 1 1 0 0.058 zabbix.globalmacro

06-07T15:02:12 0 00 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 0223 1 0 0.017 zabbix.graphs

06-07T15:02:12 0 0784 1 0 0.270 zabbix.graphs_items

06-07T15:02:12 0 0 01 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 00 1 0 0.039 zabbix.history_log

06-07T15:02:18 0 0944 1 0 0.280 zabbix.history_str

06-07T15:02:18 0 00 1 0 0.291 zabbix.history_str_sync

06-07T15:02:18 0 00 1 0 0.041 zabbix.history_sync

06-07T15:02:18 0 00 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 00 1 0 0.020 zabbix.history_uint_sync

06-07T15:02:20 0 02 1 0 0.018 zabbix.host_discovery

06-07T15:02:20 0 0 01 0 0.016 zabbix.host_inventory

06-07T15:02:20 0 0 01 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 01 0 0.017 zabbix.housekeeper

06-07T15:02:20 0 00 1 0 0.040 zabbix.httpstep

06-07T15:02:20 0 00 1 0 0.020 zabbix.httpstepitem

06-07T15:02:20 0 0 01 0 0.015 zabbix.httptest

06-07T15:02:20 0 0 01 0 0.015 zabbix.httptestitem

06-07T15:02:20 0 00 1 0 0.043 zabbix.icon_map

06-07T15:02:20 0 0 01 0 0.017 zabbix.icon_mapping

06-07T15:02:20 0 0 22 1 0 0.266 zabbix.ids

06-07T15:02:20 0 0187 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 00 1 0 0.062 zabbix.interface_discovery

06-07T15:02:21 0 0217 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 01 0 0.015 zabbix.maintenances

06-07T15:02:21 0 00 1 0 0.026 zabbix.maintenances_groups

06-07T15:02:21 0 0 01 0 0.017 zabbix.maintenances_hosts

06-07T15:02:21 0 0 01 0 0.016 zabbix.maintenances_windows

06-07T15:02:21 0 0 55 1 0 0.018 zabbix.mappings

06-07T15:02:21 0 00 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 00 1 0 0.023 zabbix.node_cksum

06-07T15:02:22 0 0 01 0 0.019 zabbix.nodes

06-07T15:02:22 0 00 1 0 0.036 zabbix.opcommand

06-07T15:02:22 0 0 01 0 0.016 zabbix.opcommand_grp

06-07T15:02:22 0 0 01 0 0.015 zabbix.opcommand_hst

06-07T15:02:22 0 0 01 0 0.015 zabbix.opconditions

06-07T15:02:22 0 06 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 01 0 0.016 zabbix.opmessage_usr

06-07T15:02:22 0 01 1 0 0.017 zabbix.optemplate

06-07T15:02:22 0 0387 1 0 0.282 zabbix.profiles

06-07T15:02:22 0 00 1 0 0.081 zabbix.proxy_autoreg_host

06-07T15:02:22 0 00 1 0 0.021 zabbix.proxy_dhistory

06-07T15:02:22 0 0 01 0 0.017 zabbix.proxy_history

06-07T15:02:23 0 0 3 1 0 0.270 zabbix.regexps

06-07T15:02:23 0 00 1 0 0.038 zabbix.rights

06-07T15:02:23 0 0 13 1 0 0.039 zabbix.screens

06-07T15:02:23 0 092 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 00 1 0 0.268 zabbix.service_alarms

06-07T15:02:23 0 00 1 0 0.062 zabbix.services

06-07T15:02:23 0 00 1 0 0.021 zabbix.services_links

06-07T15:02:23 0 0 01 0 0.018 zabbix.services_times

06-07T15:02:23 0 02 1 0 0.016 zabbix.sessions

06-07T15:02:23 0 0 01 0 0.016 zabbix.slides

06-07T15:02:23 0 0 01 0 0.015 zabbix.slideshows

06-07T15:02:24 0 00 1 0 0.268 zabbix.sysmap_element_url

06-07T15:02:24 0 0 01 0 0.015 zabbix.sysmap_url

06-07T15:02:24 0 0 1 1 0 0.043 zabbix.sysmaps

06-07T15:02:24 0 01 1 0 0.015 zabbix.sysmaps_elements

06-07T15:02:24 0 00 1 0 0.057 zabbix.sysmaps_link_triggers

06-07T15:02:24 0 00 1 0 0.022 zabbix.sysmaps_links

06-07T15:02:24 0 00 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 0401 1 0 0.281 zabbix.triggers

06-07T15:02:25 0 01 1 0 0.016 zabbix.user_history

06-07T15:02:25 0 02 1 0 0.269 zabbix.users

06-07T15:02:25 0 02 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

The second column is all zero, and there is no data difference.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.