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

This article is about how to solve the problem of locks in mysql master-slave replication. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

A set of master-master replicated mysql libraries produces deadlocks, resulting in master-slave synchronization problems.

Solution: first kill lost the sql process of the mysql of the server that produced the lock wait, and then start slave, just fine.

The following is a detailed analysis of the relevant knowledge points and the process of solving them:

One: the process of mysql master-slave replication, and the related processes.

1) about the steps of replication:

Overall, there are three steps to replication:

(1) master records changes in binary log (binary log) (these records are called binary log events, binary log events)

(2) slave copies the binary log events of master to its relay log (relay log)

(3) slave redoes the events in the relay log and changes the data that reflects itself.

The following figure describes the replication process:

The first part of the process is that master records binary logs. Before each transaction updates the data, master records these changes in the second log. MySQL writes transactions serially to the binary log, even if the statements in the transaction are executed across each other. After the event is written to the binary log, master notifies the storage engine to commit the transaction.

The next step is for slave to copy master's binary log to its own relay log. First, slave starts a worker thread-- the Imax O thread. The iUnip O thread opens a normal connection on master and then starts binlog dump process. Binlog dump process reads events from master's binary log, and if it has caught up with master, it sleeps and waits for master to generate new events. The Icano thread writes these events to the relay log.

SQL slave thread (SQL from the thread) handles the last step of the process. The SQL thread reads events from the relay log and replays the events to update the data in slave to make it consistent with the data in master. As long as the thread is consistent with the Ibank O thread, the relay log is usually in the cache of OS, so the overhead of the relay log is small.

In addition, there is a worker thread in master: like other MySQL connections, slave opening a connection in master causes master to start a thread. There is an important limitation to the replication process-replication is serialized on slave, which means that parallel update operations on master cannot operate in parallel on slave.

2) about the process of replication:

MySQL uses three threads to perform the replication function, one on the master server and two on the slave server. When the START SLAVE is issued, create an Imax O thread from the server to connect to the master server and have it send statements recorded in its binary log.

The master server creates a thread to send the contents of the binary log to the slave server. This thread can identify the Binlog Dump thread in the output of SHOW PROCESSLIST on the primary server.

The content sent by the master server Binlog Dump thread is read from the slave server Icano thread and copied to the local file in the slave server data directory, that is, the relay log.

The third thread, the SQL thread, is created from the server to read the relay log and perform the updates contained in the log.

There are multiple slave servers created by the master server to create a thread for each currently connected slave server; each slave server has its own Ibino and SQL threads.

3) how to find the id number of a specific sql and io process.

System user corresponds to the sql and io processes of the slave library. Since we have master master replication, there are not only master server Binlog Dump threads, but also sql and io processes of the slave library, as shown below:

1) We see the word Waiting for master to send event, which indicates that this process is the io process of the slave library, because the slave server Icano thread reads the content sent by the master server Binlog Dump thread and copies the data to a local file in the slave server's data directory.

2) We see Slave has read all relay log; waiting for the slave I thread to update it O thread to update it, indicating that this process is the sql process from the library, because the SQL thread from the library is to read the relay log and perform the updates contained in the log.

3) We see Master has sent all binlog to slave; waiting for binlog to be updated, which indicates that this process is the Binlog Dump of the master library, because the Binlog Dump of the master library sends the contents of the binary log (binlog) to the slave server. And the process of the main library is usually done by a specific user newly created in the main library, we are info_syncer users here.

Mysql > show processlist

| | 13910311 | system user | | NULL | Connect |-1650 | Slave has read all relay log; waiting for the slave Izuro thread to update it |

| | 13418520 | system user | | NULL | Connect | 3638625 | Waiting for master to send event |

| | 13409506 | info_syncer | 192.168.0.243 Binlog Dump 36191 | NULL | Binlog Dump | 3690795 | Master has sent all binlog to slave; waiting for binlog to be updated |

Second: queries and solutions to mysql deadlocks:

1) query related locks:

In 5. 5, three tables about locks (MEMORY engine) have been added to the information_schema library:

Innodb_trx # # all currently running transactions

Innodb_locks # # current locks

Innodb_lock_waits # # correspondence of lock waiting

Take a look at the table structure:

Root@127.0.0.1: information_schema 13:28:38 > desc innodb_locks

+-+-

| | Field | Type | Null | Key | Default | Extra | |

+-+-

| | lock_id | varchar (81) | NO | # Lock ID |

| | lock_trx_id | varchar (18) | NO | # ID of transactions with locks |

| | lock_mode | varchar (32) | NO | # Lock mode |

| | lock_type | varchar (32) | NO | # Lock type |

| | lock_table | varchar (1024) | NO | # locked table |

| | lock_index | varchar (1024) | YES | | NULL | | # locked index |

| | lock_space | bigint (21) unsigned | YES | | NULL | | # locked table space number |

| | lock_page | bigint (21) unsigned | YES | | NULL | | # locked page number |

| | lock_rec | bigint (21) unsigned | YES | | NULL | | # locked record number |

| | lock_data | varchar (8192) | YES | | NULL | | # locked data |

+-+-

10 rows in set (0.00 sec)

Root@127.0.0.1: information_schema 13:28:56 > desc innodb_lock_waits

+-+-

| | Field | Type | Null | Key | Default | Extra | |

+-+-

| | requesting_trx_id | varchar (18) | NO | # transaction ID requesting lock (that is, id waiting for lock) |

| | requested_lock_id | varchar (81) | NO | # ID of the lock requested |

| | blocking_trx_id | varchar (18) | NO | # ID of the transaction that currently owns the lock |

| | blocking_lock_id | varchar (81) | NO | # the lock ID that currently owns the lock |

+-+-

4 rows in set (0.00 sec)

Root@127.0.0.1: information_schema 13:29:05 > desc innodb_trx

+-- +

| | Field | Type | Null | Key | Default | Extra | |

+-- +

| | trx_id | varchar (18) | NO | # transaction ID |

| trx_state | varchar (13) | NO | # transaction status:

| | trx_started | datetime | NO | | 0000-0000: 00:00 | | # transaction start time |

| | trx_requested_lock_id | varchar (81) | YES | | NULL | | # innodb_locks.lock_id |

| | trx_wait_started | datetime | YES | | NULL | | # time for transaction to start waiting |

| | trx_weight | bigint (21) unsigned | NO | | 0 | | #

| | trx_mysql_thread_id | bigint (21) unsigned | NO | | 0 | | # transaction thread ID |

| | trx_query | varchar (1024) | YES | | NULL | | # specific SQL statement |

| | trx_operation_state | varchar (64) | YES | | NULL | | # current operation status of the transaction |

| | trx_tables_in_use | bigint (21) unsigned | NO | | 0 | | # how many tables are used in the transaction |

| | trx_tables_locked | bigint (21) unsigned | NO | | 0 | | # how many locks does the transaction have? |

| | trx_lock_structs | bigint (21) unsigned | NO | | 0 | | #

| | trx_lock_memory_bytes | bigint (21) unsigned | NO | | 0 | | # memory locked by the transaction (B) |

| | trx_rows_locked | bigint (21) unsigned | NO | | 0 | | # number of rows locked by the transaction |

| | trx_rows_modified | bigint (21) unsigned | NO | | 0 | | # number of rows changed by the transaction |

| | trx_concurrency_tickets | bigint (21) unsigned | NO | | 0 | | # number of transactions and invoices |

| | trx_isolation_level | varchar (16) | NO | # transaction isolation level |

| | trx_unique_checks | int (1) | NO | | 0 | | # check whether it is unique or not |

| | trx_foreign_key_checks | int (1) | NO | | 0 | | # whether to check with foreign keys |

| | trx_last_foreign_key_error | varchar (256) | YES | | NULL | | # Last foreign key error |

| | trx_adaptive_hash_latched | int (1) | NO | | 0 | | #

| | trx_adaptive_hash_timeout | bigint (21) unsigned | NO | | 0 | | #

+-- +

22 rows in set (0.01 sec)

You can see that mysql > show processlist; # #

Or:

Mysql > select concat ('KILL', id,';') from information_schema.processlist where user='root'

+-- +

| | concat ('KILL', id,';') | |

+-- +

| | KILL 3101; | |

| | KILL 2946; | |

+-- +

2 rows in set (0.00 sec)

Batch kill multiple processes.

Mysql > select concat ('KILL', id,';') from information_schema.processlist where user='root' into outfile'/ tmp/a.txt'

Query OK, 2 rows affected (0.00 sec)

Thank you for reading! This is the end of this article on "how to solve the problem of lock in mysql master-slave replication". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

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.