MySQL5.6.12 Waiting for commit lock导致从库hang住的问题剖析
来源：互联网 发布：js抛物线添加到购物车 编辑：程序博客网 时间：2024/06/04 23:24
nagios报警，线上一台从库检测不到slave状态，于是远程上去查看问题：
1，show slave status\G卡住：
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show slave status\Gshow slave status卡住了，动弹不了，这种情况还是第一次遇到。
2，看w负载无压力：
[root@tmp3_72 ~]# w 13:23:31 up 828 days, 21:54, 3 users, load average: 0.08, 0.05, 0.00USER TTY FROM LOGIN@ IDLE JCPU PCPU WHATroot tty1 - 29Sep13 827days 0.15s 0.15s -bashroot pts/0 192.168.120.28 13:19 3:49 0.18s 0.14s mysqlroot pts/1 192.168.120.28 13:23 0.00s 0.08s 0.02s w[root@tmp3_72 ~]# [root@tmp3_72 ~]# [root@tmp3_72 ~]#3，Top看mysqld进程占据cpu才0.3%
4，查看error日志，没有发现异常记录：
[root@tmp3_72 mysql]# tail -f /usr/local/mysql/mysqld.log 2015-12-29 20:35:31 29254 [Note] - '::' resolves to '::';2015-12-29 20:35:31 29254 [Note] Server socket created on IP: '::'.2015-12-29 20:35:31 29254 [Note] Event Scheduler: Loaded 0 events2015-12-29 20:35:31 29254 [Note] /usr/local/mysql/bin/mysqld: ready for connections.Version: '5.6.12-log' socket: '/usr/local/mysql/mysql.sock' port: 3306 Source distribution2015-12-29 20:35:40 29254 [Note] 'CHANGE MASTER TO executed'. Previous state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='', master_port= 3306, master_log_file='mysql-bin.036282', master_log_pos= 120, master_bind=''.2015-12-30 10:00:04 29254 [Note] 'CHANGE MASTER TO executed'. Previous state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='192.168.12.71', master_port= 3306, master_log_file='mysql-bin.036282', master_log_pos= 120, master_bind=''.2015-12-30 10:00:04 29254 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.036282' at position 120, relay log './mysql-relay-bin.000001' position: 42015-12-30 10:00:04 29254 [Warning] Storing MySQL user name or password information in the master.info repository is not secure and is therefore not recommended. Please see the MySQL Manual for more about this issue and possible alternatives.2015-12-30 10:00:04 29254 [Note] Slave I/O thread: connected to master 'repl@192.168.12.71:3306',replication started in log 'mysql-bin.036282' at position 1205，查看到后台的mysql进程，有一个mysqldump备份进程，执行了很久，我kill掉试试看
[root@tmp3_72 mysql]# ps -eaf|grep mysqlroot 16100 16094 0 12:00 ? 00:00:00 /bin/sh -c /home/data/mysql/backup/scripts/backup_full.shroot 16104 16100 0 12:00 ? 00:00:00 /usr/local/mysql/bin/mysqldump -uroot --password= -R -E -h localhost --skip-opt --single-transaction --flush-logs --master-data=2 --add-drop-table --create-option --quick --extended-insert --set-charset --disable-keys --databases user_db plocc_system parking_dbroot 18986 18967 0 13:19 pts/0 00:00:00 mysqlnagios 19486 19485 0 13:33 ? 00:00:00 /usr/local/nagios/libexec/check_mysql -unagios -P3306 -S -s /usr/local/mysql/mysql.sock -Hlocalhost --password=XXXXXXXXXXXX -d test -w 60 -c 100root 19511 19121 0 13:34 pts/1 00:00:00 grep mysqlroot 28242 1 0 2015 ? 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/home/data/mysql/data --pid-file=/usr/local/mysql/mysqld.pidmysql 29254 28242 9 2015 ? 16:56:36 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/home/data/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/usr/local/mysql/mysqld.log --open-files-limit=8192 --pid-file=/usr/local/mysql/mysqld.pid --socket=/usr/local/mysql/mysql.sock --port=3306[root@tmp3_72 mysql]# kill -9 16104[root@tmp3_72 mysql]# [root@tmp3_72 mysql]# [root@tmp3_72 mysql]# ps -eaf|grep mysqlroot 18986 18967 0 13:19 pts/0 00:00:00 mysqlroot 19555 19121 0 13:34 pts/1 00:00:00 grep mysqlroot 28242 1 0 2015 ? 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/home/data/mysql/data --pid-file=/usr/local/mysql/mysqld.pidmysql 29254 28242 9 2015 ? 16:56:36 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/home/data/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/usr/local/mysql/mysqld.log --open-files-limit=8192 --pid-file=/usr/local/mysql/mysqld.pid --socket=/usr/local/mysql/mysql.sock --port=3306[root@tmp3_72 mysql]#6，kill掉备份进程后，执行一条简单的delete语句，会卡住，看来kill解决不了问题
mysql> select * from test;+------+------+| id | name |+------+------+| 0 | NULL || 1 | NULL || 2 | NULL || 3 | NULL |+------+------+4 rows in set (0.02 sec)mysql> mysql> mysql> delete from test where id=0;去查询当前进程：
mysql> select User,State,count(1) from information_schema.processlist group by User,State;+--------------+----------------------------------------+----------+| User | State | count(1) |+--------------+----------------------------------------+----------+| cacti_user | | 17 || cacti_user | executing | 657 || nagios | executing | 38 || ploccsys_web | | 10 || root | executing | 1 || root | init | 2 || system user | Queueing master event to the relay log | 1 || system user | Waiting for commit lock | 1 |+--------------+----------------------------------------+----------+8 rows in set (0.01 sec)mysql>有许多类似下面的线程：
| 516544 | cacti_user | 192.168.11.12:42082 | NULL | Query | 375 | executing | SHOW /*!50002 GLOBAL */ STATUS || 516564 | cacti_user | 192.168.11.12:42208 | NULL | Query | 350 | executing | SHOW /*!50002 GLOBAL */ STATUS || 516571 | cacti_user | 192.168.11.12:42215 | NULL | Query | 330 | executing | SHOW /*!50002 GLOBAL */ STATUS || 516575 | cacti_user | 192.168.11.12:42218 | NULL | Query | 320 | executing | SHOW /*!50002 GLOBAL */ STATUS || 516592 | cacti_user | 192.168.11.12:42230 | NULL | Query | 315 | executing | SHOW /*!50002 GLOBAL */ STATUS || 516598 | cacti_user | 192.168.11.12:42359 | NULL | Query | 300 | executing | SHOW /*!50002 GLOBAL */ STATUS |要不要重启mysql db或者是重启cacti服务器？想来还是继续再看看，万不得已不能用绝招。^_^
7，写了个脚本来kill这些来自cacti的show status的线程
[root@tmp3_72 scripts]# vim killsleep_cacti.sh #It is used to kill processlist of mysql sleep#!/bin/shwhile :do n=`mysqladmin processlist -uroot --password=""|grep -i sleep |wc -l` date=`date +%Y%m%d\[%H:%M:%S]` echo $n if [ "$n" -gt 10 ] then echo "begin kill the sleep processlist ......" for i in `mysqladmin processlist -uroot --password=""|grep -i sleep |awk '{print $2}'` do mysqladmin -uroot --password="" kill $i done echo "begin kill the executing cacti show status processlist ......" for i in `mysqladmin processlist -uroot --password=""|grep -i executing |grep -i SHOW|grep -i STATUS |grep -i cacti |awk '{print $2}'` do mysqladmin -uroot --password="" kill $i done echo "sleep is too many I killed it " >> /tmp/sleep.log echo "$date : $n" >> /tmp/sleep.log fi sleep 1done执行后，通过show full processlist;这些线程仍然存在，只是状态从excuteing变成了killed而已：其它问题仍然没有改观
mysql> select User,Command,count(1) from information_schema.processlist group by User,Command;+--------------+---------+----------+| User | Command | count(1) |+--------------+---------+----------+| cacti_user | Killed | 792 || cacti_user | Query | 4 || cacti_user | Sleep | 1 || nagios | Query | 48 || ploccsys_web | Sleep | 10 || root | Killed | 1 || root | Query | 2 || root | Refresh | 1 || system user | Connect | 2 |+--------------+---------+----------+9 rows in set (0.01 sec)mysql>7，重新查看slave状态，还是无效
mysql> show slave status\GERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect...Connection id: 523275Current database: test8，去查看engine状态
mysql> show engine innodb status\G*************************** 1. row *************************** Type: InnoDB Name: Status: =====================================2016-01-06 15:08:52 7f3ef5a19700 INNODB MONITOR OUTPUT=====================================Per second averages calculated from the last 50 seconds-----------------BACKGROUND THREAD-----------------srv_master_thread loops: 475489 srv_active, 0 srv_shutdown, 195322 srv_idlesrv_master_thread log flush and writes: 670800----------SEMAPHORES----------OS WAIT ARRAY INFO: reservation count 697461OS WAIT ARRAY INFO: signal count 710269Mutex spin waits 2340260, rounds 18635522, OS waits 498279RW-shared spins 132111, rounds 3631441, OS waits 119612RW-excl spins 25200, rounds 2213112, OS waits 70336Spin rounds per wait: 7.96 mutex, 27.49 RW-shared, 87.82 RW-excl------------TRANSACTIONS------------Trx id counter 1247855846Purge done for trx's n:o < 1247855835 undo n:o < 0 state: running but idleHistory list length 2442LIST OF TRANSACTIONS FOR EACH SESSION:---TRANSACTION 0, not startedMySQL thread id 523731, OS thread handle 0x7f3ef5a19700, query id 150089612 localhost root initshow engine innodb status---TRANSACTION 0, not startedMySQL thread id 522540, OS thread handle 0x7f3ef7ea8700, query id 150086762 localhost root executingSHOW /*!50002 GLOBAL */ STATUS---TRANSACTION 1247853306, not startedMySQL thread id 35671, OS thread handle 0x7f4bf8083700, query id 0 Queueing master event to the relay log---TRANSACTION 1247855447, ACTIVE 11330 secmysql tables in use 1, locked 12 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1MySQL thread id 35672, OS thread handle 0x7f4bf8052700, query id 150059111 Waiting for commit lock--------FILE I/O--------I/O thread 0 state: waiting for i/o request (insert buffer thread)I/O thread 1 state: waiting for i/o request (log thread)I/O thread 2 state: waiting for i/o request (read thread)I/O thread 3 state: waiting for i/o request (read thread)I/O thread 4 state: waiting for i/o request (read thread)I/O thread 5 state: waiting for i/o request (read thread)I/O thread 6 state: waiting for i/o request (write thread)I/O thread 7 state: waiting for i/o request (write thread)I/O thread 8 state: waiting for i/o request (write thread)I/O thread 9 state: waiting for i/o request (write thread)Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] , ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0Pending flushes (fsync) log: 0; buffer pool: 09067 OS file reads, 129305477 OS file writes, 12754944 OS fsyncs0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s-------------------------------------INSERT BUFFER AND ADAPTIVE HASH INDEX-------------------------------------Ibuf: size 1, free list len 11, seg size 13, 0 mergesmerged operations: insert 0, delete mark 0, delete 0discarded operations: insert 0, delete mark 0, delete 0Hash table size 75692179, node heap has 2437 buffer(s)0.00 hash searches/s, 0.00 non-hash searches/s---LOG---Log sequence number 418264709881Log flushed up to 418264709881Pages flushed up to 418264709881Last checkpoint at 4182647098810 pending log writes, 0 pending chkp writes110060310 log i/o's done, 0.00 log i/o's/second----------------------BUFFER POOL AND MEMORY----------------------Total memory allocated 35165044736; in additional pool allocated 0Dictionary memory allocated 1420595Buffer pool size 2097144Free buffers 1375231Database pages 719476Old database pages 265424Modified db pages 0Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 1474, not young 00.00 youngs/s, 0.00 non-youngs/sPages read 91286, created 628190, written 348075820.00 reads/s, 0.00 creates/s, 0.00 writes/sNo buffer pool page gets since the last printoutPages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 719476, unzip_LRU len: 0I/O sum[0]:cur[0], unzip sum[0]:cur[0]----------------------INDIVIDUAL BUFFER POOL INFO-------------------------BUFFER POOL 0Buffer pool size 262143Free buffers 172002Database pages 89836Old database pages 33142Modified db pages 0Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 97, not young 00.00 youngs/s, 0.00 non-youngs/sPages read 11534, created 78302, written 56449430.00 reads/s, 0.00 creates/s, 0.00 writes/sNo buffer pool page gets since the last printoutPages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 89836, unzip_LRU len: 0I/O sum[0]:cur[0], unzip sum[0]:cur[0]---BUFFER POOL 1Buffer pool size 262143Free buffers 171914Database pages 89924Old database pages 33174Modified db pages 0Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 259, not young 00.00 youngs/s, 0.00 non-youngs/sPages read 11425, created 78499, written 33526470.00 reads/s, 0.00 creates/s, 0.00 writes/sNo buffer pool page gets since the last printoutPages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 89924, unzip_LRU len: 0I/O sum[0]:cur[0], unzip sum[0]:cur[0]---BUFFER POOL 2Buffer pool size 262143Free buffers 171570Database pages 90267Old database pages 33301Modified db pages 0Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 158, not young 00.00 youngs/s, 0.00 non-youngs/sPages read 11720, created 78547, written 21827210.00 reads/s, 0.00 creates/s, 0.00 writes/sNo buffer pool page gets since the last printoutPages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 90267, unzip_LRU len: 0I/O sum[0]:cur[0], unzip sum[0]:cur[0]---BUFFER POOL 3Buffer pool size 262143Free buffers 172120Database pages 89724Old database pages 33100Modified db pages 0Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 141, not young 00.00 youngs/s, 0.00 non-youngs/sPages read 11117, created 78607, written 81554480.00 reads/s, 0.00 creates/s, 0.00 writes/sNo buffer pool page gets since the last printoutPages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 89724, unzip_LRU len: 0I/O sum[0]:cur[0], unzip sum[0]:cur[0]---BUFFER POOL 4Buffer pool size 262143Free buffers 171665Database pages 90167Old database pages 33264Modified db pages 0Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 142, not young 00.00 youngs/s, 0.00 non-youngs/sPages read 11675, created 78492, written 74507090.00 reads/s, 0.00 creates/s, 0.00 writes/sNo buffer pool page gets since the last printoutPages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 90167, unzip_LRU len: 0I/O sum[0]:cur[0], unzip sum[0]:cur[0]---BUFFER POOL 5Buffer pool size 262143Free buffers 172081Database pages 89746Old database pages 33108Modified db pages 0Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 185, not young 00.00 youngs/s, 0.00 non-youngs/sPages read 11258, created 78488, written 35051570.00 reads/s, 0.00 creates/s, 0.00 writes/sNo buffer pool page gets since the last printoutPages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 89746, unzip_LRU len: 0I/O sum[0]:cur[0], unzip sum[0]:cur[0]---BUFFER POOL 6Buffer pool size 262143Free buffers 173275Database pages 88569Old database pages 32674Modified db pages 0Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 336, not young 00.00 youngs/s, 0.00 non-youngs/sPages read 10622, created 77947, written 24882650.00 reads/s, 0.00 creates/s, 0.00 writes/sNo buffer pool page gets since the last printoutPages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 88569, unzip_LRU len: 0I/O sum[0]:cur[0], unzip sum[0]:cur[0]---BUFFER POOL 7Buffer pool size 262143Free buffers 170604Database pages 91243Old database pages 33661Modified db pages 0Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 156, not young 00.00 youngs/s, 0.00 non-youngs/sPages read 11935, created 79308, written 20276920.00 reads/s, 0.00 creates/s, 0.00 writes/sNo buffer pool page gets since the last printoutPages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 91243, unzip_LRU len: 0I/O sum[0]:cur[0], unzip sum[0]:cur[0]--------------ROW OPERATIONS--------------0 queries inside InnoDB, 0 queries in queue0 read views open inside InnoDBMain thread process no. 29254, id 139925894604544, state: sleepingNumber of rows inserted 71967166, updated 94419979, deleted 45975, read 73660291840.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s----------------------------END OF INNODB MONITOR OUTPUT============================1 row in set (0.02 sec)mysql>9，去查看是否有locks信息
mysql> select * from information_schema.INNODB_TRX\G;*************************** 1. row *************************** trx_id: 1247855447 trx_state: RUNNING trx_started: 2016-01-06 12:00:02 trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 3 trx_mysql_thread_id: 35672 trx_query: NULL trx_operation_state: NULL trx_tables_in_use: 1 trx_tables_locked: 1 trx_lock_structs: 2 trx_lock_memory_bytes: 376 trx_rows_locked: 1 trx_rows_modified: 1 trx_concurrency_tickets: 0 trx_isolation_level: READ COMMITTED trx_unique_checks: 1 trx_foreign_key_checks: 1trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 10000 trx_is_read_only: 0trx_autocommit_non_locking: 01 row in set (0.00 sec)ERROR: No query specifiedmysql>mysql> select * from INNODB_LOCKS;Empty set (0.00 sec)mysql> mysql> mysql> mysql> select * from INNODB_LOCK_WAITS;Empty set (0.00 sec)mysql>有一个事务是running状态，根据trx_mysql_thread_id: 35672查询processlist表:
mysql> select * from processlist where id=35672;+-------+-------------+------+------+---------+-------+-------------------------+------+| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |+-------+-------------+------+------+---------+-------+-------------------------+------+| 35672 | system user | | NULL | Connect | 11948 | Waiting for commit lock | NULL |+-------+-------------+------+------+---------+-------+-------------------------+------+1 row in set (0.01 sec)mysql>正常的从服务器是：
mysql> select * from processlist order by id limit 2;+----------+-------------+------+------+---------+--------+------------------------------------------------------------------+------+| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |+----------+-------------+------+------+---------+--------+------------------------------------------------------------------+------+| 29903251 | system user | | NULL | Connect | 624229 | Waiting for master to send event | NULL || 29903252 | system user | | NULL | Connect | 5 | Slave has read all relay log; waiting for the slave I/O thread t | NULL |+----------+-------------+------+------+---------+--------+------------------------------------------------------------------+------+2 rows in set (0.00 sec)mysql>而我的这台出故障的从服务器是：
mysql> select * from processlist order by id limit 2;+-------+-------------+------+------+---------+--------+----------------------------------------+------+| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |+-------+-------------+------+------+---------+--------+----------------------------------------+------+| 35671 | system user | | NULL | Connect | 624374 | Queueing master event to the relay log | NULL || 35672 | system user | | NULL | Connect | 12379 | Waiting for commit lock | NULL |+-------+-------------+------+------+---------+--------+----------------------------------------+------+2 rows in set (0.01 sec)mysql>再次查看统计，确定问题所在为system user | Connect | Waiting for commit lock 线程导致：
mysql> select User,Command,State,count(1) from information_schema.processlist group by User,Command,State;+--------------+---------+----------------------------------------+----------+| User | Command | State | count(1) |+--------------+---------+----------------------------------------+----------+| cacti_user | Killed | executing | 792 || cacti_user | Query | executing | 344 || nagios | Query | executing | 72 || ploccsys_web | Sleep | | 10 || root | Killed | executing | 1 || root | Killed | init | 5 || root | Query | executing | 1 || root | Query | Waiting for table flush | 1 || root | Refresh | init | 1 || system user | Connect | Queueing master event to the relay log | 1 || system user | Connect | Waiting for commit lock | 1 |+--------------+---------+----------------------------------------+----------+11 rows in set (0.02 sec)mysql>10，问题解决
事情到此地步，已经很明确了就是Waiting for commit lock 这个导致的全局锁，所以kill掉innodb_trx中的running事务的mysql线程：
mysql> kill 35672 -> ;Query OK, 0 rows affected (0.00 sec)mysql>然后再去check processlist；已经正常了，如下所示：
mysql> show full processlist;+--------+--------------+-------------------+--------------------+---------+-------+----------------------------------+-----------------------+| Id | User | Host | db | Command | Time | State | Info |+--------+--------------+-------------------+--------------------+---------+-------+----------------------------------+-----------------------+| 527023 | root | localhost | information_schema | Query | 0 | init | show full processlist || 527165 | system user | | NULL | Connect | 54 | Waiting for master to send event | NULL || 527166 | system user | | NULL | Connect | 12548 | System lock | NULL || 527167 | ploccsys_web | 192.168.12.28:33006 | plocc_system | Sleep | 28 | | NULL || 527168 | ploccsys_web | 192.168.12.28:33007 | plocc_system | Sleep | 28 | | NULL || 527169 | ploccsys_web | 192.168.12.28:33008 | plocc_system | Sleep | 28 | | NULL || 527170 | ploccsys_web | 192.168.12.28:33009 | plocc_system | Sleep | 28 | | NULL || 527171 | ploccsys_web | 192.168.12.28:33010 | plocc_system | Sleep | 28 | | NULL || 527172 | ploccsys_web | 192.168.12.29:43473 | plocc_system | Sleep | 16 | | NULL || 527173 | ploccsys_web | 192.168.12.29:43474 | plocc_system | Sleep | 16 | | NULL || 527174 | ploccsys_web | 192.168.12.29:43476 | plocc_system | Sleep | 16 | | NULL || 527175 | ploccsys_web | 192.168.12.29:43477 | plocc_system | Sleep | 16 | | NULL || 527176 | ploccsys_web | 192.168.12.29:43478 | plocc_system | Sleep | 16 | | NULL |+--------+--------------+-------------------+--------------------+---------+-------+----------------------------------+-----------------------+13 rows in set (0.00 sec)mysql>在查看slave状态，是No状态，如下所示：
mysql> show slave status\G*************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.12.71 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.037579 Read_Master_Log_Pos: 2172311 Relay_Log_File: mysql-relay-bin.002685 Relay_Log_Pos: 13453336 Relay_Master_Log_File: mysql-bin.037578 Slave_IO_Running: No Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 13453173 Relay_Log_Space: 15626372 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULLMaster_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 71 Master_UUID: 9b0dcf62-29f4-11e3-9471-677b33903869 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 01 row in set (0.00 sec)mysql>之后启动slave，start slave;搞定。
11，网友 Aeolus@普 的提示
DEADLOCK ON FLUSH TABLES WITH READ LOCK + SHOW SLAVE STATUS Problem: If a client thread on an slave does FLUSH TABLES WITH READ LOCK; then master does some updates, SHOW SLAVE STATUS in the same client will be blocked. Analysis: Execute FLUSH TABLES WITH READ LOCK on slave and at the same time execute a DML on the master. Then the DML should be made to stop at a state “Waiting for commit lock”. This state means that sql thread is holding rli->data_lock and waiting for MDL_COMMIT lock. Now in the same client session where FLUSH TABLES WITH READ LOCK was executed issue SHOW SLAVE STATUS command. This command will be blocked waiting for rli->data_lock causing a dead lock. Once this happens it will not be possible to release the global read lock as “UNLOCK TABLES” command has to be issued in the same client where global read lock was acquired. This causes the dead lock. Fix: Existing code holds the rli->data_lock for the whole duration of commit operation. Instead of holding the lock for entire commit duration the code has been restructured in such a way that the lock is held only during the period when rli object is being updated.
然后去查了下我这台db从服务器，确实有每我这台从库是 有做mysqldump备份的，一个小时备份一次，然后cacti监控也会每分钟就来抽取一次数据，可能还真的是 mysqldump+cacti的show slave status一起执行导致的bug 出现。我先去将备份改成4个小时一次，继续观察后续情况。
3 0
- MySQL5.6.12 Waiting for commit lock导致从库hang住的问题剖析
- Waiting for Commit Lock
- Waiting for table metadata lock问题的解决
- 再论mysql线程出现Waiting for table flush被hang住问题
- mysql5.7.10 出现waiting for table metadata lock
- Waiting for lock on */.ivy2/.sbt.ivy.lock to be available问题的解决
- waiting for table metadata lock 问题深入分析
- 故障案例--mysql5.5 myisam引擎出现Waiting for table metadata lock
- Waiting for table metadata lock
- Waiting for table metadata lock
- Waiting for table metadata lock
- Waiting for table metadata lock
- 一次关于Waiting for table metadata lock的处理
- MySQL出现Waiting for table metadata lock的场景浅析
- 一次Waiting for table metadata lock的处理
- MySQL出现Waiting for table metadata lock的场景浅析
- oracle 11g 多次业务用户被锁定 library cache lock导致数据hang住问题解决
- Anroid studio遇到的问题5--Gradle sync failed: Timeout waiting to lock cp_proj class cache for build
- 时间复杂度和空间复杂度(2)
- 《Spring实战》学习笔记-第二章：装配Bean
- 155Min Stack
- error: symbol(s) not found for architecture x86_64
- nginx 1.9 支持http2 协议
- MySQL5.6.12 Waiting for commit lock导致从库hang住的问题剖析
- CentOS7 上部署Elasticsearch，Fluentd and Kibana
- 提高篇 第9节-函数的嵌套使用
- 数据库表空间
- 实例10：带选择头像的用户注册页面
- 前端开发工具sumlime
- 1010. 一元多项式求导 (25)
- 学习写Android.mk
- Qt设计界面的初学笔记
