Home PC Games Linux Windows Database Network Programming Server Mobile  
  Home \ Database \ Recovery from MySQL master data consistency summary     - To batch create users under Linux (Linux)

- MariaDB 10.0.X, the dynamic column support JSON format to obtain data (Database)

- JDK comes with tools jinfo (Linux)

- You really do need to know a variety of programming languages (Programming)

- Difference Redhat5 and 6 YUM source configuration (Linux)

- Management and application Oracle external table (Database)

- MySQL script incremental backups (innobackupex) (Database)

- PostgreSQL query lock and kill the process (Database)

- Android imageView in the Src and Background (Programming)

- Java NIO in the world (Programming)

- Java source implementation of the observer pattern instance (Programming)

- Linux package management operations Basic entry (Linux)

- Linux Disk and File Management (Linux)

- Linux server operating system security configuration (Linux)

- C ++ complex class of operator overloading (Programming)

- Elasticsearch Kibana installation notes (Linux)

- MogileFS system installation configuration example (Server)

- Oracle 11g tracking and monitoring system-level triggers to drop misuse (Database)

- Introduction and bash history command to quickly call (Linux)

- Implement binary search algorithm in C language (Programming)

  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


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.
- mysqldump issue a note (Database)
- CentOS6.5 installation Docker (Linux)
- Hibernate + JUnit test entity class generate database table (Programming)
- How to use Xmanager Remote Desktop and VNC Log (Linux)
- CentOS 6.4 Telecom ADSL dial-up network configuration (Linux)
- CentOS7 install MySQL5.6.22 (Linux)
- Oracle Duplicate build DataGuard (Database)
- How to adjust the system time CentOS (Linux)
- CentOS 6.x and CentOS7 install MPlayer (Linux)
- Achieve single backup of MySQL by innobackupex (Database)
- Mistakenly deleted redo log file group being given the lead to start the database ORA-03113 (Database)
- CentOS 7.0 Experience with previous versions (Linux)
- C # using the HttpClient Note: Preheat the long connection (Programming)
- ethtool command Detailed (Linux)
- VMware Workstation virtual machine startup error: Could not open / dev / vmmon in CentOS 6 (Linux)
- ActiveMQ5.10.2 version configuration JMX (Linux)
- Apache Linux firewall reverse proxy configuration (Linux)
- Linux shell script to adjust the Task Scheduler (Linux)
- Ubuntu 32 compile Android 4.0.4 Problems (Linux)
- How to generate Linux, random password encryption or decryption (Linux)
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.