Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Recovery from MySQL master data consistency summary     - Generate two-dimensional code parsing code Java (Programming)

- How to set cache valid time in Apache (Server)

- CentOS 7 virt-manager can not connect a local hypervisor (Linux)

- Grub2 Boots the openSUSE installation image (Linux)

- RedHat yum configuration and the prompt (Linux)

- Sorting Algorithm (1) Quick Sort C ++ implementation (Programming)

- CentOS 6.6 install Oracle 11gR2 database (Database)

- To install MySQL on Linux (Database)

- Learning the Linux powerful network management capabilities (Linux)

- I use the desktop environment in GNU / Linux combination tool (Linux)

- Java regular expression syntax (Programming)

- SUSE Linux network configuration and firewall configuration (Linux)

- Ubuntu 14.04 running ASP.NET Configuration Mono + Jexus (Server)

- Ubuntu install Oracle 10g process and problem solution (Linux)

- Redis Linux system installation guide (Database)

- Java abstract class instantiation (Programming)

- How to disable UEFI to install Linux on Win8 (Linux)

- Linux System Getting Started Learning: Linux common log file (Linux)

- To setup the Swift language learning environment under linux (Linux)

- Create the best introductory tutorial REST API (Linux)

 
         
  Recovery from MySQL master data consistency summary
     
  Add Date : 2018-11-21      
         
         
         
  This morning, a colleague told, MySQL master data from the database is inconsistent, the standby database guess there is a problem in the synchronization process, therefore, prepared by the library board, use mysql> show slave status G Show, indeed, prepared by the library in the insert statement for violation of a primary key constraint, leading to stop by the library synchronization. Now the question is clear, it is how to recover from a master repository data consistency.

Options are as follows:

A view of the Position latest Master, Slave as a starting point of replication.

This idea is reflected in inconsistent past let bygones be bygones, we can now keep pace. It seems that this idea has been violated and restore the master database from the original intention of the consistency of the data, but this method is simple, efficient and can be used in a test environment, historical data less demanding scenes.

Second, the recovery must be strictly from the master repository data consistency.

Here, there are two ideas:

1. Back up the master database data and recovery from the library, the historical data consistency on the basis of open simultaneously, but this method is too much trouble, you must lock the table to perform operations in the main library, stop the client to update table data operation, and in the case of large volumes of data, the backup is a time-consuming project. In fact, this method in actual production environments are rarely used.

2. Skip out related errors

In fact, this is not very strict, he said live, ready to say, skip the relevant transaction. In this case I am here today is to skip out insert statements because it violates the primary key constraint and failure.

 

How to skip related matters

First, stop the slave service

Two, SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

Third, open the slave service.

Here is a skipped transaction. Of course, you can also skip more than one transaction, but be careful, after all, you do not know what the transaction is skipped.

Recommendation: the above steps may be performed repeatedly, look carefully at the statement can not cause synchronization from the library. Sometimes, too much to prevent the transaction from the library, this method becomes slightly inefficient.

Main library can analyze the transaction log to determine the appropriate value of SQL_SLAVE_SKIP_COUNTER. Specific steps are as follows:

I. Executive show slave status G in the backup repository, confirm the following two parameters

According to the above two values ​​of the parameters, view the transaction is currently hampered copied from the library, and after the transaction in the main library.
mysql> SHOW BINLOG EVENTS in 'mysql-bin.000217' from 673146776;

This is the view the log file mysql-bin.000217 transaction ID for all transactions 673 146 776 after.

Of course, SHOW BINLOG EVENTS usage is still quite flexible and can be in the following manner.

mysql> SHOW BINLOG EVENTS in 'mysql-bin.000217' from 673146776 G

mysql> SHOW BINLOG EVENTS in 'mysql-bin.000217' from 673146776 limit 10;

In a hosted environment can also be viewed through mysqlbinlog command

# Mysqlbinlog mysql-bin.000217 --start-position = 673146776

 

How to check the implementation of the statement

After skipping related matters from the library, restart the Slave, Slave_IO_Running, Slave_SQL_Running two showed "YES", but Seconds_Behind_Master did not fall immediately, but slowly rising.

This time to check the show processlist statement execution threads, found the first statement execution time is too long, "State" column shows "Sending data". About "Sending data" meaning
Visible, the statement involves a large number of disk reads.
For further analysis of time-consuming distribution of the statement, set variable profiling. Proceed as follows:

First, before the start of the query, set the set profiling = on;

Second, after the statement is finished, view Query_ID statement by show profiles.

Third, by show profile for query Query_ID view of the specific implementation of the statement.

Finally found, the statement takes too long in Sending data phase.

to sum up:

1. In performing the stop slave, stop slave command is hang live, online query relevant information, may have long-Slave SQL or SQL execution Locked relevant here, in addition to show processlist outside, it is best not to perform show slave status and other slave and slave stop commands. So how to solve this problem? Slave SQL thread waiting for the lock end, or restart the database. I chose the latter.

2. In the process of restarting the standby database, there is a small section of episode, when executing start slave command reported the following error: ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository. Lot of information online are recommended to reconfigure the master from the cluster, so back to the beginning of the program selection section. Strange, I closed the library from the restart, the good. The two start command only difference is that the previous start using mysqld, after first start using mysqld_safe, and more with a --user argument.
     
         
         
         
  More:      
 
- Oracle can not change the tablespace to backup mode in non-archive mode (Database)
- Linux foundation tutorial: how to modify the host name on CentOS or RHEL 7 (Linux)
- Linux Services Comments (Linux)
- Java garbage collection (Programming)
- MySQL development common query summary (Database)
- View and modify Linux machine name (Linux)
- Ubuntu 14.04 install PostgreSQL 9.2 (Database)
- Linux how to view the graphics models notebook (Linux)
- How to make Linux a non-root user uses less than 1024 ports (Linux)
- Ubuntu Linux use MAC binding against ARP attacks (Linux)
- Installation JDK1.7 under Ubuntu14.04 64-bit (Linux)
- Linux iptables: combat scenes (Linux)
- OpenJDK 7 compiled under Ubuntu 14.04.3 64-bit (Linux)
- Linux Troubleshooting: How to save the status of the SSH session is closed (Linux)
- To share Internet access through NAT mode under Virtual Linux VMware Workstation (Linux)
- CoreOS use register mirror to build private warehouse (Linux)
- Spring + MyBatis Multi data source switching (Database)
- Intrusion analysis and prevention tools Knark under Linux platform (Linux)
- RedHat6.4 installation tutorial --- Minimal Edition (Linux)
- Linux remote wake the computer original code [C] (Linux)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.