如何解決mysql主從復制中產(chǎn)生了鎖的問題

這篇文章給大家分享的是有關(guān)如何解決mysql主從復制中產(chǎn)生了鎖的問題的內(nèi)容。小編覺得挺實用的，因此分享給大家做個參考，一起跟隨小編過來看看吧。

一套主主復制的mysql庫產(chǎn)生了死鎖，導致主從同步出現(xiàn)問題，

解決辦法：是先kill掉了產(chǎn)生鎖等待的那個服務(wù)器的mysql的sql進程，然后start slave，就好了。

下面具體分析下相關(guān)知識點以及解決的過程：

一：mysql主從復制的流程，以及相關(guān)的進程。

1）關(guān)于復制的步驟：

整體上來說，復制有3個步驟：   

        (1)    master將改變記錄到二進制日志(binary log)中（這些記錄叫做二進制日志事件，binary log events）；

        (2)    slave將master的binary log events拷貝到它的中繼日志(relay log)；

        (3)    slave重做中繼日志中的事件，將改變反映它自己的數(shù)據(jù)。

下圖描述了復制的過程：

          該過程的第一部分就是master記錄二進制日志。在每個事務(wù)更新數(shù)據(jù)完成之前，master在二日志記錄這些改變。MySQL將事務(wù)串行的寫入二進制日志，即使事務(wù)中的語句都是交叉執(zhí)行的。在事件寫入二進制日志完成后，master通知存儲引擎提交事務(wù)。

       下一步就是slave將master的binary log拷貝到它自己的中繼日志。首先，slave開始一個工作線程——I/O線程。I/O線程在master上打開一個普通的連接，然后開始binlog dump process。Binlog dump process從master的二進制日志中讀取事件，如果已經(jīng)跟上master，它會睡眠并等待master產(chǎn)生新的事件。I/O線程將這些事件寫入中繼日志。

       SQL slave thread（SQL從線程）處理該過程的最后一步。SQL線程從中繼日志讀取事件，并重放其中的事件而更新slave的數(shù)據(jù)，使其與master中的數(shù)據(jù)一致。只要該線程與I/O線程保持一致，中繼日志通常會位于OS的緩存中，所以中繼日志的開銷很小。

        此外，在master中也有一個工作線程：和其它MySQL的連接一樣，slave在master中打開一個連接也會使得master開始一個線程。復制過程有一個很重要的限制——復制在slave上是串行化的，也就是說master上的并行更新操作不能在slave上并行操作。

2）關(guān)于復制的進程：

MySQL 使用3個線程來執(zhí)行復制功能,其中1個在主服務(wù)器上，另兩個在從服務(wù)器上。當發(fā)出START SLAVE時，從服務(wù)器創(chuàng)建一個I/O線程，以連接主服務(wù)器并讓它發(fā)送記錄在其二進制日志中的語句。

主服務(wù)器創(chuàng)建一個線程將二進制日志中的內(nèi)容發(fā)送到從服務(wù)器。該線程可以識別為主服務(wù)器上SHOW PROCESSLIST的輸出中的Binlog Dump線程。

從服務(wù)器I/O線程讀取主服務(wù)器Binlog Dump線程發(fā)送的內(nèi)容并將該數(shù)據(jù)拷貝到從服務(wù)器數(shù)據(jù)目錄中的本地文件中，即中繼日志。   

第3個線程是SQL線程，是從服務(wù)器創(chuàng)建用于讀取中繼日志并執(zhí)行日志中包含的更新。

有多個從服務(wù)器的主服務(wù)器創(chuàng)建為每個當前連接的從服務(wù)器創(chuàng)建一個線程；每個從服務(wù)器有自己的I/O和SQL線程。

3）如何找到具體的sql和io進程的id號。

system user對應(yīng)著從庫的sql和io進程，因為我們的是主主復制，所以既有主服務(wù)器Binlog Dump線程，還有從庫的sql和io進程，具體如下：

1）我們看到Waiting for master to send event的字樣，說明這個進程是從庫的io進程，因為從服務(wù)器I/O線程就是讀取主服務(wù)器Binlog Dump線程發(fā)送的內(nèi)容并將該數(shù)據(jù)拷貝到從服務(wù)器數(shù)據(jù)目錄中的本地文件

2）我們看到Slave has read all relay log; waiting for the slave I/O thread to update it，說明這個進程是從庫的sql進程，因為從庫SQL線程，就是讀取中繼日志并執(zhí)行日志中包含的更新。

3）我們看到Master has sent all binlog to slave; waiting for binlog to be updated，說明這個進程是主庫的Binlog Dump，因為主庫的Binlog Dump就是將二進制日志中的內(nèi)容（binlog)發(fā)送到從服務(wù)器.并且主庫的這個進程一般是由主庫新建的特定的用戶來完成的，我們這里是info_syncer  用戶。

mysql> show processlist;  

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

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

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

二：關(guān)于mysql死鎖的查詢和解決辦法：

1）查詢相關(guān)的鎖：

在5.5中，information_schema 庫中增加了三個關(guān)于鎖的表（MEMORY引擎）：

innodb_trx         ## 當前運行的所有事務(wù)

innodb_locks       ## 當前出現(xiàn)的鎖

innodb_lock_waits  ## 鎖等待的對應(yīng)關(guān)系

看一下表結(jié)構(gòu)：

root@127.0.0.1 : information_schema 13:28:38> desc innodb_locks;

+————-+———————+——+—–+———+——-+

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

+————-+———————+——+—–+———+——-+

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

| lock_trx_id | varchar(18)         | NO   |     |         |       |#擁有鎖的事務(wù)ID

| lock_mode   | varchar(32)         | NO   |     |         |       |#鎖模式

| lock_type   | varchar(32)         | NO   |     |         |       |#鎖類型

| lock_table  | varchar(1024)       | NO   |     |         |       |#被鎖的表

| lock_index  | varchar(1024)       | YES  |     | NULL    |       |#被鎖的索引

| lock_space  | bigint(21) unsigned | YES  |     | NULL    |       |#被鎖的表空間號

| lock_page   | bigint(21) unsigned | YES  |     | NULL    |       |#被鎖的頁號

| lock_rec    | bigint(21) unsigned | YES  |     | NULL    |       |#被鎖的記錄號

| lock_data   | varchar(8192)       | YES  |     | NULL    |       |#被鎖的數(shù)據(jù)

+————-+———————+——+—–+———+——-+

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   |     |         |       |#請求鎖的事務(wù)ID（也就是等待鎖的id）

| requested_lock_id | varchar(81) | NO   |     |         |       |#請求鎖的鎖ID

| blocking_trx_id   | varchar(18) | NO   |     |         |       |#當前擁有鎖的事務(wù)ID

| blocking_lock_id  | varchar(81) | NO   |     |         |       |#當前擁有鎖的鎖ID

+——————-+————-+——+—–+———+——-+

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   |     |                     |       |#事務(wù)ID

| trx_state                  | varchar(13)         | NO   |     |                     |       |#事務(wù)狀態(tài)：

| trx_started                | datetime            | NO   |     | 0000-00-00 00:00:00 |       |#事務(wù)開始時間；

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

| trx_wait_started           | datetime            | YES  |     | NULL                |       |#事務(wù)開始等待的時間

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

| trx_mysql_thread_id        | bigint(21) unsigned | NO   |     | 0                   |       |#事務(wù)線程ID

| trx_query                  | varchar(1024)       | YES  |     | NULL                |       |#具體SQL語句

| trx_operation_state        | varchar(64)         | YES  |     | NULL                |       |#事務(wù)當前操作狀態(tài)

| trx_tables_in_use          | bigint(21) unsigned | NO   |     | 0                   |       |#事務(wù)中有多少個表被使用

| trx_tables_locked          | bigint(21) unsigned | NO   |     | 0                   |       |#事務(wù)擁有多少個鎖

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

| trx_lock_memory_bytes      | bigint(21) unsigned | NO   |     | 0                   |       |#事務(wù)鎖住的內(nèi)存大?。˙）

| trx_rows_locked            | bigint(21) unsigned | NO   |     | 0                   |       |#事務(wù)鎖住的行數(shù)

| trx_rows_modified          | bigint(21) unsigned | NO   |     | 0                   |       |#事務(wù)更改的行數(shù)

| trx_concurrency_tickets    | bigint(21) unsigned | NO   |     | 0                   |       |#事務(wù)并發(fā)票數(shù)

| trx_isolation_level        | varchar(16)         | NO   |     |                     |       |#事務(wù)隔離級別

| trx_unique_checks          | int(1)              | NO   |     | 0                   |       |#是否唯一性檢查

| trx_foreign_key_checks     | int(1)              | NO   |     | 0                   |       |#是否外鍵檢查

| trx_last_foreign_key_error | varchar(256)        | YES  |     | NULL                |       |#最后的外鍵錯誤

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

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

+—————————-+———————+——+—–+———————+——-+

22 rows in set (0.01 sec)

mysql> show processlist;    ##可以看出來，

或者：

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)

 批量kill多個進程。

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)

感謝各位的閱讀！關(guān)于“如何解決mysql主從復制中產(chǎn)生了鎖的問題”這篇文章就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，讓大家可以學到更多知識，如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！