如何解决mysql主从复制中产生了锁的问题

发布时间：2021-11-08 09:35:22 作者：小新
来源：亿速云阅读：215

这篇文章给大家分享的是有关如何解决mysql主从复制中产生了锁的问题的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。

一套主主复制的mysql库产生了死锁，导致主从同步出现问题，

解决办法：是先kill掉了产生锁等待的那个服务器的mysql的sql进程，然后start slave，就好了。

下面具体分析下相关知识点以及解决的过程：

一：mysql主从复制的流程，以及相关的进程。

1）关于复制的步骤：

整体上来说，复制有3个步骤：

(1) master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）；

(2) slave将master的binary log events拷贝到它的中继日志(relay log)；

(3) slave重做中继日志中的事件，将改变反映它自己的数据。

下图描述了复制的过程：
如何解决mysql主从复制中产生了锁的问题

该过程的第一部分就是master记录二进制日志。在每个事务更新数据完成之前，master在二日志记录这些改变。MySQL将事务串行的写入二进制日志，即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后，master通知存储引擎提交事务。

下一步就是slave将master的binary log拷贝到它自己的中继日志。首先，slave开始一个工作线程——I/O线程。I/O线程在master上打开一个普通的连接，然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件，如果已经跟上master，它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志。

SQL slave thread（SQL从线程）处理该过程的最后一步。SQL线程从中继日志读取事件，并重放其中的事件而更新slave的数据，使其与master中的数据一致。只要该线程与I/O线程保持一致，中继日志通常会位于OS的缓存中，所以中继日志的开销很小。

此外，在master中也有一个工作线程：和其它MySQL的连接一样，slave在master中打开一个连接也会使得master开始一个线程。复制过程有一个很重要的限制——复制在slave上是串行化的，也就是说master上的并行更新操作不能在slave上并行操作。

2）关于复制的进程：

MySQL 使用3个线程来执行复制功能,其中1个在主服务器上，另两个在从服务器上。当发出START SLAVE时，从服务器创建一个I/O线程，以连接主服务器并让它发送记录在其二进制日志中的语句。

主服务器创建一个线程将二进制日志中的内容发送到从服务器。该线程可以识别为主服务器上SHOW PROCESSLIST的输出中的Binlog Dump线程。

从服务器I/O线程读取主服务器Binlog Dump线程发送的内容并将该数据拷贝到从服务器数据目录中的本地文件中，即中继日志。

第3个线程是SQL线程，是从服务器创建用于读取中继日志并执行日志中包含的更新。

有多个从服务器的主服务器创建为每个当前连接的从服务器创建一个线程；每个从服务器有自己的I/O和SQL线程。

3）如何找到具体的sql和io进程的id号。

system user对应着从库的sql和io进程，因为我们的是主主复制，所以既有主服务器Binlog Dump线程，还有从库的sql和io进程，具体如下：

1）我们看到Waiting for master to send event的字样，说明这个进程是从库的io进程，因为从服务器I/O线程就是读取主服务器Binlog Dump线程发送的内容并将该数据拷贝到从服务器数据目录中的本地文件

2）我们看到Slave has read all relay log; waiting for the slave I/O thread to update it，说明这个进程是从库的sql进程，因为从库SQL线程，就是读取中继日志并执行日志中包含的更新。

3）我们看到Master has sent all binlog to slave; waiting for binlog to be updated，说明这个进程是主库的Binlog Dump，因为主库的Binlog Dump就是将二进制日志中的内容（binlog)发送到从服务器.并且主库的这个进程一般是由主库新建的特定的用户来完成的，我们这里是info_syncer 用户。

mysql> show processlist;

| 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

二：关于mysql死锁的查询和解决办法：

1）查询相关的锁：

在5.5中，information_schema 库中增加了三个关于锁的表（MEMORY引擎）：

innodb_trx ## 当前运行的所有事务

innodb_locks ## 当前出现的锁

innodb_lock_waits ## 锁等待的对应关系

看一下表结构：

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

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

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

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

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

| lock_type | varchar(32) | NO | | | |#锁类型

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

10 rows in set (0.00 sec)

root@127.0.0.1 : information_schema 13:28:56> desc innodb_lock_waits;

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

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

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

4 rows in set (0.00 sec)

root@127.0.0.1 : information_schema 13:29:05> desc innodb_trx ;

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

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

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

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

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

| trx_foreign_key_checks | int(1) | NO | | 0 | |#是否外键检查

| 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)

感谢各位的阅读！关于“如何解决mysql主从复制中产生了锁的问题”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！

如何解决mysql主从复制中产生了锁的问题

相关阅读