本文详细介绍了MySQL主从复制的工作流程、涉及的进程以及解决主从复制中因死锁产生的问题。当遇到死锁，可以通过查询information_schema中的innodb_trx、innodb_locks和innodb_lock_waits表来定位并解决。同时，文章强调了了解主从复制原理和进程对于快速定位和解决问题的重要性。
今天我们的一套主主复制的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重做中继日志中的事件，将改变反映它自己的数据。
下图描述了复制的过程：
该过程的第一部分就是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;
| 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
二：关于mysql死锁的查询和解决办法：
1)查询相关的锁：
在5.5中，information_schema 库中增加了三个关于锁的表(MEMORY引擎)：
innodb_trx ## 当前运行的所有事务
innodb_locks ## 当前出现的锁
innodb_lock_waits ## 锁等待的对应关系
看一下表结构：
[email protected] : 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 | | | |#拥有锁的事务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 | |#被锁的数据
+————-+———————+——+—–+———+——-+
10 rows in set (0.00 sec)
[email protected] : information_schema 13:28:56> desc innodb_lock_waits;
+——————-+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——————-+————-+——+—–+———+——-+
| requesting_trx_id | varchar(18) | NO | | | |#请求锁的事务ID(也就是等待锁的id)
| requested_lock_id | varchar(81) | NO | | | |#请求锁的锁ID
| blocking_trx_id | varchar(18) | NO | | | |#当前拥有锁的事务ID
| blocking_lock_id | varchar(81) | NO | | | |#当前拥有锁的锁ID
+——————-+————-+——+—–+———+——-+
4 rows in set (0.00 sec)
[email protected] : information_schema 13:29:05> desc innodb_trx ;
+—————————-+———————+——+—–+———————+——-+
| Field | Type | Null | Key | Default | Extra |
+—————————-+———————+——+—–+———————+——-+
| trx_id | varchar(18) | NO | | | |#事务ID
| trx_state | varchar(13) | NO | | | |#事务状态：
| trx_started | datetime | NO | | 0000-00-00 00:00:00 | |#事务开始时间；
| trx_requested_lock_id | varchar(81) | YES | | NULL | |#innodb_locks.lock_id
| trx_wait_started | datetime | YES | | NULL | |#事务开始等待的时间
| trx_weight | bigint(21) unsigned | NO | | 0 | |#
| trx_mysql_thread_id | bigint(21) unsigned | NO | | 0 | |#事务线程ID
| trx_query | varchar(1024) | YES | | NULL | |#具体SQL语句
| trx_operation_state | varchar(64) | YES | | NULL | |#事务当前操作状态
| trx_tables_in_use | bigint(21) unsigned | NO | | 0 | |#事务中有多少个表被使用
| trx_tables_locked | bigint(21) unsigned | NO | | 0 | |#事务拥有多少个锁
| trx_lock_structs | bigint(21) unsigned | NO | | 0 | |#
| trx_lock_memory_bytes | bigint(21) unsigned | NO | | 0 | |#事务锁住的内存大小(B)
| trx_rows_locked | bigint(21) unsigned | NO | | 0 | |#事务锁住的行数
| trx_rows_modified | bigint(21) unsigned | NO | | 0 | |#事务更改的行数
| trx_concurrency_tickets | bigint(21) unsigned | NO | | 0 | |#事务并发票数
| trx_isolation_level | varchar(16) | NO | | | |#事务隔离级别
| 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)
小结：关于mysql的主从复制，应该熟悉他的原理和相关进程，一旦出问题，可迅速定位错误关键，并且mysql主库需要创建一个用户，专门用于传输binlog到从库，而从库接受和应用主库传过来的日志的用户为 system user,该用户是系统自带的，无须创建。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29654823/viewspace-2128136/，如需转载，请注明出处，否则将追究法律责任。
扫一扫
658
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
