Use Percona Toolkit to troubleshoot MySQL master never sync issue "Memo"

Due to various reasons, MySQL master-slave architecture often appears inconsistent data, which is roughly attributed to the following categories

• 1: Prepare the library to write data
• 2: Execute non-deterministic Query
• 3: Rollback transactions with mixed transaction tables and non-transactional tables
• 4:binlog or relay log data corruption

Data is not synchronized to the application of the harm is fatal, when there is a master-slave data inconsistency, the common response is to start off the library offline, and then find a after midnight time to stop the application, re-execute synchronization, if the database volume is very large, the workload can be imagined, will let people crash. This article describes the use of the Percona-toolkit tool to check and resynchronize the synchronization state of a MySQL master-slave database.

One: Installation Percona-toolkit

1. # yum-y Install Perl-time-hires
2. # wget
3. http://www.percona.com/downloads/percona-toolkit/2.2.13/tarball/percona-toolkit-2.2.13.tar.gz
4. # TAR-ZXVPF percona-toolkit-2.2. 13.tar.gz
5. # CD percona-toolkit-2.2. -
6. # Perl makefile.pl
7. # make
8. # make Install

Second: Modify the MySQL binlog format binlog_format parameter to row format

MySQL Binlog logs are available in three formats, statement, Mixed, and row!

1.Statement:

Each SQL that modifies the data is recorded in Binlog.

Advantages: No need to record the change of each line, reduce the Binlog log volume, save IO, improve performance. (compared to how much performance and log volume The row can save, depending on the SQL case of the application, the log volume generated by the normal record modification or the insertion of the row format is less than the amount of log generated by statement, but given the conditional update operation and the whole table deletion, ALTER TABLE operations, the row format generates a large number of logs, so the amount of log generated will increase, as well as the IO performance issues, when considering whether to use the row format log should be followed according to the actual application. )

Cons: Because the records only execute statements, in order for these statements to run correctly on the slave, it is also necessary to record some information about each statement at the time of execution, to ensure that all statements can be slave and executed at the master side of the same result. In addition to MySQL replication, like some specific function functions, slave can be consistent with master on a number of related issues (such as the Sleep () function, last_insert_id (), and user-defined functions (UDF) can cause problems ).

2.Row

The SQL statement context-sensitive information is not logged, and only which record is saved is modified.

Advantage: Binlog can not record the context-sensitive information of executed SQL statements, only need to record what the record was modified to. So the Rowlevel log content will be very clear to record the details of each row of data modification. There are no stored procedures, or function, and trigger calls and triggers that cannot be copied correctly in certain situations

Disadvantage: All executed statements when recorded in the log, will be recorded in each row of changes to record, which may produce a large number of log content, such as an UPDATE statement, modify multiple records, then binlog each modification will have a record, so that the Binlog log volume will be very large, In particular, when executing a statement such as ALTER TABLE, each record in the table is recorded in the log because of changes in its structure.

3.Mixed

is a mixture of the above two levels, the general statement modification using the statment format to save Binlog, such as some functions, statement can not complete the operation of the master-slave copy, the row format to save Binlog, MySQL differentiates the log form of the treated record according to each specific SQL statement executed, that is, choosing between statement and row. The new version of the MySQL Squadron row level mode is also optimized, and not all changes are recorded at the row level. The statement pattern is recorded when a table structure change is encountered. For statements that modify data such as update or delete, the changes are recorded for all rows.

The master-slave database modifies the my.cnf file configuration entries as follows:

1. Binlog_format=row

III: Check data consistency using the Pt-table-checksum tool

Usage Reference:

Assuming that 192.168.1.205 is the main library, 192.168.1.207 is its slave library, port at 3306.

1. First check

1. # pt-table-checksum--user=root--password=123456 \
2. --host=192.168. 1.205--port=3306 \
3. --databases=test--tables=t2--recursion-method=processlist \
4. --no-check-binlog-format--nocheck-replication-filters \
5. --replicate=test.checksums

2. Based on the verification results, only fix the 192.168.1.207 from the library and the main library:

1. # Pt-table-sync--execute--replicate \
2. Test.checksums--sync-to-master h=192.168. 1.207,p=3306,u=root,p=123456

3. After repair, re-check again. Execute the first step of the statement.

4. Check the repair results: Log in to 192.168.1.207, execute the following SQL statement returned if empty, the repair succeeds:

1. SELECT
2. *
3. From
4. Test.checksums
5. WHERE
6. master_cnt <> this_cnt
7. OR MASTER_CRC <> THIS_CRC
8. OR ISNULL (MASTER_CRC) <> ISNULL (THIS_CRC)

Meaning of each parameter

• --nocheck-replication-filters: Do not check for replication filters, it is recommended to enable. You can use--databases later to specify the database you want to check.
• --no-check-binlog-format: Do not check the copy of the Binlog mode, if the Binlog mode is row, will be an error.
• --replicate-check-only: Displays only information that is not synchronized.
• --replicate=: Writes the checksum information to the specified table, it is recommended to write directly to the database being inspected.
• --databases=: Specifies the database that needs to be checked, and multiple are separated by commas.
• --tables=: Specifies the table to be inspected, separated by commas
• H=127.0.0.1:master's address
• U=root: User Name
• p=123456: Password
• p=3306: Port

Let's simulate the pt-table-checksum of the master-slave database in the case of non-synchronization, for convenience, here we use the test schema

1: Build table on Main library, insert test data

1. mysql> create table t2  (id  Int primary key,name varchar (100)  not null , salary int)  
2. mysql> create  procedure test_insert  ()  
3. BEGIN&NBSP;
4. declare i int  Default 0;&NBSP;
5. while i<
6. do 
7. INSERT&NBSP;INTO&NBSP;T2&NBSP;
8. values 
9. (I,concat (
10. Set i=i+1;&NBSP;
11. END&NBSP;WHILE&NBSP;;&NBSP;
12. END;  
13. mysql> call test_insert ();  

The synchronization of the current data from the library colonel is normal.

Delete half of the data from the library

1. mysql> Delete from T2 where ID > ;
2. Query OK, 4999 rows affected (0.14 sec)
4. Mysql> Select COUNT (*) from T2;
5. +----------+
6. | COUNT (*) |
7. +----------+
8. | 5001 |
9. +----------+
10. 1 row in Set (0.01 sec)

2: Use the Pt-table-checksum tool to verify:

1. # pt-table-checksum--user=root--password=123456 \
2. --host=192.168. 1.205--port=3306 \
3. --databases=test--tables=t2--recursion-method=processlist \
4. --no-check-binlog-format--nocheck-replication-filters \
5. --replicate=test.checksums

3: Log in from the Library to query checksum table

1. Mysql> SELECT
2. *
3. From
4. Test.checksums
5. WHERE
6. master_cnt <> this_cnt
7. OR MASTER_CRC <> THIS_CRC
8. OR ISNULL (MASTER_CRC) <> ISNULL (THIS_CRC)

4: Data resynchronization using the Pt-table-sync tool

1. # Pt-table-sync--execute--replicate \
2. Test.checksums--sync-to-master h=192.168. 1.207,p=3306,u=root,p=123456

5: Validate data from library, Chinese "employee" becomes "??"

Check the main library and find the same situation, the Chinese "employee" becomes "??", and the conjecture and character set settings are related.

The database character set is checked to find that the test library character set is non-UTF8

Master/Slave library my.cnf file to restart the DB instance after adding the following configuration item

1. Character_set_client=utf8
2. Character_set_server=utf8

Re-execute the above 1-4 steps and find everything is OK! Key 4th step to add--charset=utf8 parameter

1. # Pt-table-sync--execute--replicate \
2. Test.checksums--charset=utf8 \
3. --sync-to-master h=192.168. 1.207,p=3306,u=root,p=123456
   

Use Percona Toolkit to troubleshoot MySQL master never sync issue "Memo"