[Mysql] a solution to master-slave data inconsistency, mysql master-slave

[Mysql] a solution to master-slave data inconsistency, mysql master-slave

Previous Article: re-establishing Synchronization After the Master/Slave node changes the ip address
This is the case.
Two active machines were migrated last night, and the active connection was reconnected today, but the slave database offset starts from the current time, that is, although the active database seems normal now, in fact, part of yesterday's data is missing. Because the data in the slave database is lost, it is still necessary to fill in the gaps in the morning and evening.

Problem

• The problem to be solved is how to compare the inconsistency, and then fix the data inconsistency without affecting the service, and add the missing data from the database.

The following are several solutions that can be thought of and found:
1. Synchronization starts from scratch. Although it has no impact on the use of the master database, the large data volume has a significant impact on performance and network, and the data loss should be very small.
2. dump data in the master database, lock the database, and then synchronize data. Impact on Business Use
3. Tools in percona-toolkit are used for verification and synchronization. In terms of introduction, they are in line with the current situation. You still need to learn and understand how to use them.

Below are several Reference Links

• Percona-toolkit official address
• Check and fix MySQL master-slave server data consistency
• Use pt-table-checksum to verify data consistency description tool Principle
• Use pt-table-sync to fix inconsistent data and describe the principle of the tool.

Procedure

I only explained the process and usage. For some Parameter options, please refer to the documentation. Both machines are centos6.5 and mysql versions are 5.6. Because they are online environments, ip addresses, passwords, and other sensitive information have been modified.

• Master 192.168.1.100
• From 192.168.1.98
• Fix database name radius

Tool Installation

InMaster Database ServerInstall

# Install dependency package # yum install perl-DBI perl-DBD-MySQL perl-TermReadKey perl-Time-HiRes # Installation tool # wget percona.com/get/percona-toolkit.tar.gz# tar zxvf percona-toolkit-2.2.14.tar.gz # cd percona-toolkit-2.2.14 # perl Makefile. PL & make install

Verify data consistency and establish user authorization

Note that you must create a user with the same name as the master database. You can access the slave database from the master database, and the master database can access the master database locally. Tools are used on the server of the master database.
Pt-table-checksum verifies data consistency.

Slave database mysql operations

GRANT SELECT,PROCESS, SUPER, REPLICATION SLAVE ON *.* TO 'checksums'@'192.168.1.100' IDENTIFIED BY 'slavecheck';flush privileges;

Master database mysql operations

GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE ON *.* TO 'checksums'@'192.168.1.100' IDENTIFIED BY 'slavecheck';GRANT SELECT,INSERT,UPDATE,DELETE ON radius.checksums TO 'checksums'@'192.168.1.100';flush privileges;

During verification, you must create a new table in the primary mysql instance. The new user must have the read and write permissions. Here, the validation table is created in the radius database.

Pt-table-checksum check

Verification is performed on the master database server.

Execute pt-table-checksum h = '2017. 168.1.100', u = 'checksums', p = 'slavecheck', P = 3306-d radius -- nocheck-replication-filters -- replicate = radius. checksums -- nocheck-replication-filters: do not check the replication filter. We recommend that you enable it. You can use -- databases to specify the database to be checked. -- No-check-binlog-format: the copied binlog mode is not checked. If the binlog mode is ROW, an error is returned. -- Replicate-check-only: only information that is not synchronized is displayed. -- Replicate =: Write the checksum information to the specified table. We recommend that you directly write the information to the database to be checked. -- Databases =: Specifies the database to be checked. Multiple databases are separated by commas. -- Tables =: Specifies the table to be checked. Multiple tables are separated by commas (,). h = 192.168.1.100: Master Address u = checksums: username p = slavecheck: Password P = 3306: Port

This script runs on the master database machine and will automatically find the slave database address, log on with the same user, and then compare.

-The replicate option is to create a table to store the comparison information. The table must be synchronized to the slave database. If the checksums user does not have the permission to create a table, create a table by yourself.

Table creation statement

CREATE TABLE IF NOT EXISTS `radius`.`checksums` (     db             CHAR(64)     NOT NULL,     tbl            CHAR(64)     NOT NULL,     chunk          INT          NOT NULL,     chunk_time     FLOAT            NULL,     chunk_index    VARCHAR(200)     NULL,     lower_boundary TEXT             NULL,     upper_boundary TEXT             NULL,     this_crc       CHAR(40)     NOT NULL,     this_cnt       INT          NOT NULL,     master_crc     CHAR(40)         NULL,     master_cnt     INT              NULL,     ts             TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,     PRIMARY KEY (db, tbl, chunk),     INDEX ts_db_tbl (ts, db, tbl)  ) ENGINE=INNODB;

After creating a table manually, the following error occurs:

6-16T16:10:48 The --replicate table `radius`.`checksums` exists on the master but but it has problems on these replicas:Table radius.checksums does not exist on replica localhost.localdomain

Previous errors cause problems with master-slave replication. Check the active status from the database and adjust the master status from normal.

After the error is solved, continue the execution (the result is omitted)

Next, check [root @ localhost portal] # pt-table-checksum h = '2017. 168.1.100', u = 'checksums', p = 'slavecheck', P = 3306-d radius -- nocheck-replication-filters -- replicate = radius. checksums ts errors diffs rows chunks skipped time TABLE06-16T16: 50: 21 0 1 8379 4 0 0.322 radius. account_account06-16T16: 50: 21 0 1 11429 1 0 0.278 radius. account_mac06-16T16: 50: 21 0 1 63747 1 0 0.329 radius. account_smslog06-16T16: 50: 21 0 0 0 1 0 0.016 radius. auth_group06-16T16: 50: 21 0 0 0 1 0 0.013 radius. auth_group_permissions06-16T16: 50: 22 0 0 27 1 0 0.265 radius. auth_permission06-16T16: 50: 22 0 1 8384 1 0 0.273 radius. auth_user ......

If this result is displayed, it indicates that the table data is checked and the diffs column is different, indicating that the table data is inconsistent. log on to mysql from the slave database and run the following statement:

mysql> select * from radius.checksums where master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc) \G*************************** 1. row ***************************            db: radius           tbl: account_account         chunk: 2    chunk_time: 0.028065   chunk_index: PRIMARYlower_boundary: 1847upper_boundary: 9225      this_crc: 4f43a2      this_cnt: 7336    master_crc: 9235f7a2    master_cnt: 7379            ts: 2015-06-16 17:00:31

There are a total of eight records, and the data in these eight tables is inconsistent. We can see how much data is missing.

Fix inconsistent data

Fix inconsistent data usagept-table-syncTool, usept-table-checksumTool result. However, there are still some pitfalls here. It is best to back up the data of the primary mysql database before the restoration, because there is a risk of some write operations on the primary database.

Master Database Server execution

[Root @ localhost portal] # pt-table-sync -- execute -- replicate radius. checksums -- sync-to-master h = "192.168.1.98", P = 3306, u = "checksums", p = "slavecheck" -- ignore-tables radacct, django_sessionDBI connect ('; host = 124.88.52.100; port = 3306; mysql_read_default_group = client ', 'checksums ',...) failed: Access denied for user 'checksums' @ '124. 88.52.100 '(using password: YES) at/usr/local/bin/pt-table-sync line 2220, but it is okay to connect directly to mysql.

Finally, I checked the document and found that the user permission was still a problem.
Slave database operations

mysql> GRANT all ON radius.* TO 'checksums'@'192.168.1.100';Query OK, 0 rows affected (0.00 sec)mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)

Master database operations

mysql> GRANT all ON radius.* TO 'checksums'@'192.168.1.100';Query OK, 0 rows affected (0.00 sec)mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)

NewAdd, delete, modify, and queryThe permission is enough. I am so lazy ..

Error solved

Repair data

First, repair an unimportant table to experiment (master database operation)

pt-table-sync --execute --replicate radius.checksums --sync-to-master h=192.168.1.98,P=3306,u=checksums,p="slavecheck"  --tables account_smslog,radcheck --print 

The repair is completed after a check master database operation is executed.

pt-table-checksum h='192.168.1.100',u='checksums',p='slavecheck',P=3306 -d radius --nocheck-replication-filters --replicate=radius.checksums

Check the slave database mysql

mysql> select * from radius.checksums where master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc) \G

Two tables are missing, which indicates that the repair has been completed.

Fix other tables, and check whether there is any problem.

Summary

The main problem here is
1. The script is executed there (both on the master database server and the slave database only checks the result)
2. How to Create a user and what permissions should the user Grant?

Statement:
This article from the "orangleliu notebook" blog, reproduced please be sure to retain this source http://blog.csdn.net/orangleliu/article/details/46532215 author orangleliu using the signature-non-commercial use-the same way to share the Protocol