How to use PerconaToolkit to solve the problem of Mysql master-slave synchronization _ MySQL

A new feature of the new generation of products is that users can choose to use full-flash storage or use both hard drives and flash devices to balance customers' different needs for superior performance and storage capabilities. For various reasons, data inconsistency often occurs in the mysql master/slave architecture, which is roughly attributed to the following categories:

• 1: write data to the slave database

• 2: execute non-deterministic query

• 3: Roll back transactions with mixed transaction Tables and Non-transaction tables

• 4: binlog or relay log data corruption

The danger of data synchronization to applications is fatal. when the master/slave data is inconsistent, the common solution is to take the slave database offline first, then, we can find a third time in the middle of the night to stop the application and re-execute the synchronization. if the database volume is very large, the workload can be imagined and people will crash. This article describes how to use percona-toolkit to check and re-synchronize the synchronization status of the mysql master-slave database.

I. 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

II. modify the binlog_format parameter of mysql to the row format.

Mysql binlog has three formats: Statement, Mixed, and ROW!

1. Statement:

Each SQL statement that modifies the data is recorded in the binlog.

Advantage: you do not need to record changes in each row, which reduces the binlog volume, saves IO, and improves performance. (The performance and log volume can be reduced compared to the row, which depends on the application's SQL condition. the log volume generated by modifying the same record normally or inserting the row format is smaller than the log volume generated by Statement, however, considering the conditional update operations, as well as the entire table deletion and alter table operations, the ROW format will generate a large number of logs, therefore, when considering whether to use the ROW format log, it should be based on the actual situation of the application, the amount of logs generated will increase, and the IO performance problems .)

Disadvantage: because only execution statements are recorded, in order that these statements can run correctly on slave, some related information of each statement must be recorded during execution, to ensure that all the statements can get the same results as the execution on the master end in slave. In addition, mysql replication, such as some specific function functions, slave can be consistent with the master, there will be many related problems (such as sleep () function, last_insert_id (), and user-defined functions (udf) problems ).

2. Row

The context information of the SQL statement is not recorded. only the record that is saved is modified.

Advantage: binlog does not record the context-related information of the executed SQL statement. you only need to record what the record is modified. Therefore, rowlevel logs clearly record the details of each row of data modification. In addition, there will be no problems in certain situations where stored procedures, functions, trigger calls and triggers cannot be correctly copied.

Disadvantage: when all statements executed are recorded in the log, they are all recorded with modifications recorded in each line. This may produce a large amount of log content, such as an update statement, if you modify multiple records, each modification to the binlog will be recorded, resulting in a large amount of binlog logs. especially when you execute statements such as alter table, because of the table structure modification, if each record changes, each record in the table is recorded in the log.

3. Mixed

The preceding two levels are used in combination. for general statement modification, binlog is saved in statment format. for example, if statement cannot complete master-slave replication, binlog is saved in row format, mySQL will differentiate the log format of the record based on each specific SQL Statement executed, that is, select one between Statement and Row. the row level mode of the new MySQL Squadron is also optimized. not all modifications are recorded by row level, as if the table structure is changed, it will be recorded in statement mode. For statements that modify data such as update or delete, changes to all rows are recorded.

Modify the configuration items of the my. cnf file in the master/slave database as follows:

 
 

  
  
binlog_format=ROW 

 
 

The preceding figure shows how to use Percona Toolkit to solve the problem of Mysql master-slave synchronization _ MySQL. For more information, see PHP Chinese website (www.php1.cn )!