Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to solve the problem of master-slave non-synchronization of Mysql". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "how to solve the problem of master-slave non-synchronization of Mysql".

Due to various reasons, data inconsistencies often occur in mysql master-slave architecture, which can be summarized into the following categories

1: prepare the database to write data

2: execute non-deterministic query

3: roll back transactions mixed with transaction tables and non-transaction tables

4:binlog or relay log data corruption

The harm that the data is out of sync to the application is fatal. When the master-slave data is inconsistent, the common way to deal with it is to offline the slave database first, and then find a time in the middle of the night to stop the application and re-perform synchronization. If the size of the database is very large, it is conceivable that the workload will cause people to collapse. This article introduces the use of percona-toolkit tools to check and resynchronize the synchronization status of the mysql master-slave database.

One: install percona-toolkit

# yum-y install perl-Time-HiRes # wget http://www.percona.com/downloads/percona-toolkit/2.2.13/tarball/percona-toolkit-2.2.13.tar.gz # tar-zxvpf percona-toolkit-2.2.13.tar.gz # cd percona-toolkit-2.2.13 # perl Makefile.PL # make # make install

Second: modify the binlog format of mysql and change the 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 the binlog.

Advantages: no need to record the changes of each line, reduce the number of binlog logs, save IO, and improve performance. (how much performance and log volume can be saved compared with row, which depends on the SQL of the application. The log volume generated by modifying or inserting the row format of the same record is still less than that generated by Statement, but considering that if the conditional update operation, as well as the whole table deletion, alter table and other operations, ROW format will generate a large number of logs, so when considering whether to use ROW format logs should be based on the actual situation of the application. How much more logs will be generated, and the resulting IO performance problems.)

Disadvantages: since only execution statements are recorded, in order for these statements to run correctly on the slave, it is also necessary to record some information about the execution of each statement to ensure that all statements get the same results in slave as they are executed on the masterside. In addition, the replication of mysql, like some specific functions, slave can be consistent with the master will have a lot of related problems (such as sleep () function, last_insert_id (), and user-defined functions (udf) will have problems).

2.Row

Sql statement context-sensitive information is not recorded, only which record is modified.

Pros: binlog does not record context-sensitive information about executed sql statements, but only needs to record what that record has been modified to. So the log content of rowlevel will clearly record the details of each line of data modification. And there will be no problems that stored procedures, or function, and the calls and triggers of trigger can not be copied correctly in certain cases.

Disadvantages: when all executed statements are recorded in the log, they will be recorded as changes in each row, which may result in a large amount of log content, such as a update statement. If multiple records are modified, each change in binlog will be recorded, resulting in a large number of binlog logs, especially when executing statements such as alter table, each record will be changed due to table structure changes. Then each record in the table is recorded in the log.

3.Mixed

Is the mixed use of the above two kinds of level, the general statement modification uses statment format to save binlog, such as some functions, statement can not complete the master-slave copy operation, then saving binlog,MySQL in row format will distinguish the log form of records according to each specific sql statement executed, that is, choose one between Statement and Row. The new version of MySQL Squadron row level mode is also optimized. Not all changes are recorded in row level, such as statement mode in the event of table structure changes. Statements that modify data, such as update or delete, still record changes to all rows.

The master-slave database modifies the relevant configuration items of the my.cnf file respectively as follows:

Binlog_format=ROW

Third, use pt-table-checksum tools to check data consistency

Usage reference:

Suppose 192.168.1.205 is the master library, 192.168.1.207 is its slave library, and the port is 3306.

1. Check first

# pt-table-checksum-user=root-- password=123456\-- host=192.168.1.205-- port=3306\-- databases=test-- tables=t2-- recursion-method=processlist\-- no-check-binlog-format-- nocheck-replication-filters\-- replicate=test.checksums

two。 According to the verification result, only the inconsistencies between the 192.168.1.207 slave database and the master database are fixed:

# pt-table-sync-- execute-- replicate\ test.checksums-- sync-to-master hobbies 192.168.1.207, Prun3306, ubiquitous rootball, 123456

3. After repair, recheck it again. Just execute the statement of step * *.

4. Check the repair result: log in to 192.168.1.207 and execute the following sql statement. If the return value is empty, the repair is successful:

SELECT * FROM test.checksums WHERE master_cnt this_cnt OR master_crc this_crc OR ISNULL (master_crc) ISNULL (this_crc)

The meaning of each parameter

-- nocheck-replication-filters: do not check the replication filter. It is recommended to enable it. Later, you can use-- databases to specify the database to be checked.

-- no-check-binlog-format: does not check the replicated binlog mode. If the binlog mode is ROW, an error will be reported.

-- replicate-check-only: only messages that are out of sync are displayed.

-- replicate=: writes the information of checksum to the specified table, and it is recommended to write it directly to the database being checked.

-- databases=: specifies the databases to be checked, separated by commas.

-- tables=: specifies the tables to be checked, separated by commas

The address of h=127.0.0.1:Master

U=root: user name

Pair123456: password

Play3306: Port

Let's simulate the pt-table-checksum when the master-slave database is out of sync. For convenience, we use test schema here.

1: create a table on the main library and insert test data

Mysql > create table T2 (id int primary key,name varchar) not null,salary int; mysql > CREATE PROCEDURE test_insert () BEGIN DECLARE i INT DEFAULT 0; WHILE i CALL test_insert ()

Verify that the synchronization of the current data from the library is normal.

Delete half of the data from the library

Mysql > delete from T2 where id > 5000; Query OK, 4999 rows affected (0.14 sec) mysql > select count (*) from T2; +-+ | count (*) | +-+ | 5001 | +-+ 1 row in set (0.01 sec)

2: use the pt-table-checksum tool for verification:

# pt-table-checksum-user=root-password=123456\

-- host=192.168.1.205-- port=3306\

-databases=test-tables=t2-recursion-method=processlist\

-- no-check-binlog-format-- nocheck-replication-filters\

-- replicate=test.checksums

3: log in to the slave database to query the checksum table

Mysql > SELECT

*

FROM

Test.checksums

WHERE

Master_cnt this_cnt

OR master_crc this_crc

OR ISNULL (master_crc) ISNULL (this_crc)

4: use the pt-table-sync tool for data resynchronization

# pt-table-sync-- execute-- replicate\ test.checksums-- sync-to-master hobbies 192.168.1.207, Prun3306, ubiquitous rootball, 123456

5: validate the data from the database, and the Chinese word "employee" becomes "?"

Check the main database and find that the same situation occurs. The Chinese word "employee" becomes "?". The guess is related to the character set setting.

So check the database character set setting and find that the test library character set is not utf8.

Restart the database instance after adding the following configuration items to the master-slave library my.cnf file

Character_set_client=utf8

Character_set_server=utf8

Re-perform the above 1-4 steps and find that everything is normal! The key step 4 is to add the-charset=utf8 parameter.

# pt-table-sync-execute-replicate\

Test.checksums-- charset=utf8\

-- sync-to-master hobbies 192.168.1.207, Prun3306, upright rootpage123456

At this point, I believe you have a deeper understanding of "how to solve the problem of master-slave non-synchronization of Mysql". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.