从192.168.11.186 上登录 192.168.11.185 数据库: root 13246 547 0 13:39 pts/1 00:00:00 mysql -uroot -px xxxxxxx -h192.168.11.185 进程号 13246 zabbix:/root/mysql# netstat -nap |grep 3306 | grep 185 tcp 0 0 192.168.11.186:40366 192.168.11.185:3306 ESTABLISHED 13246/mysql 从192.168.11.185 上查看登录的线程号: centos6.5:/root#mysql -uroot -p'kjk123123' -h192.168.11.185 -e"show processlist" Warning: Using a password on the command line interface can be insecure. +------+------+----------------------+------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +------+------+----------------------+------+---------+------+-------+------------------+ | 1367 | root | 192.168.11.186:40366 | NULL | Sleep | 78 | | NULL | 线程号为1367 zabbix:/root/mysql# sh ./mon_mysql_all.sh Warning: Using a password on the command line interface can be insecure. Warning: Using a password on the command line interface can be insecure. mysql[192.168.11.185] processid[1367] root@192.168.11.186:40366 in db[DEVOPS] hold transaction time 141 centos6.5:/root#mysql -uroot -p'kjk123123' -h192.168.11.185 -e"select * from INFORMATION_SCHEMA.INNODB_TRXG " Warning: Using a password on the command line interface can be insecure. *************************** 1. row *************************** trx_id: 5451 trx_state: RUNNING trx_started: 2016-11-22 13:48:24 trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 3 trx_mysql_thread_id: 1367 trx_query: NULL trx_operation_state: NULL trx_tables_in_use: 0 trx_tables_locked: 0 trx_lock_structs: 2 trx_lock_memory_bytes: 360 trx_rows_locked: 4 trx_rows_modified: 1 trx_concurrency_tickets: 0 trx_isolation_level: REPEATABLE READ trx_unique_checks: 1 trx_foreign_key_checks: 1 trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 10000 trx_is_read_only: 0 trx_autocommit_non_locking: 0 INNODB_TRX 表包含信息关于每个事务(不包含只读事务) 当前在InnoDB内执行的, 包含交易是否是等待一个lock,当 事务开始后,和SQL 语句正在执行的 INNODB_TRX Columns: TRX_ID:唯一的事务ID表示,针对于InnoDB内部的(从MySQL5.6开始,那些IDs是不会被创建用于事务 只读的或者是非锁定的) TRX_WEIGHT:一个事务的权重,反映(但不一定是确切的计数) 影响的行的数量,和被事务锁定的行的数量。 为了解决死锁,InnoDB 选择一个小的权重的事务来回滚。 TRX_STATE 事务执行状态,允许值为RUNNING, LOCK WAIT, ROLLING BACK, and COMMITTING. TRX_STARTED: 事务开始时间 centos6.5:/root#mysql -uroot -p'kjk123123' -h192.168.11.185 -e"show processlist" Warning: Using a password on the command line interface can be insecure. +------+------+----------------------+--------+---------+------+----------+------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +------+------+----------------------+--------+---------+------+----------+------------------------------------------+ | 1367 | root | 192.168.11.186:40366 | DEVOPS | Sleep | 46 | | NULL | | 1404 | root | 192.168.11.186:46149 | DEVOPS | Query | 3 | updating | delete from test where username='admin' | | 1405 | root | 192.168.11.185:43080 | NULL | Query | 0 | init | show processlist | +------+------+----------------------+--------+---------+------+----------+------------------------------------------+ 1367 持有行锁 1404 被堵塞 centos6.5:/root#mysql -uroot -p'kjk123123' -h192.168.11.185 -e"select * from INFORMATION_SCHEMA.INNODB_TRXG " Warning: Using a password on the command line interface can be insecure. *************************** 1. row *************************** trx_id: 5458 trx_state: LOCK WAIT trx_started: 2016-11-22 14:01:57 trx_requested_lock_id: 5458:14:3:2 trx_wait_started: 2016-11-22 14:01:57 trx_weight: 2 trx_mysql_thread_id: 1404 trx_query: delete from test where username='admin' trx_operation_state: starting index read trx_tables_in_use: 1 trx_tables_locked: 1 trx_lock_structs: 2 trx_lock_memory_bytes: 360 trx_rows_locked: 1 trx_rows_modified: 0 trx_concurrency_tickets: 0 trx_isolation_level: REPEATABLE READ trx_unique_checks: 1 trx_foreign_key_checks: 1 trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 10000 trx_is_read_only: 0 trx_autocommit_non_locking: 0 *************************** 2. row *************************** trx_id: 5453 trx_state: RUNNING trx_started: 2016-11-22 14:01:14 trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 3 trx_mysql_thread_id: 1367 trx_query: NULL trx_operation_state: NULL trx_tables_in_use: 0 trx_tables_locked: 0 trx_lock_structs: 2 trx_lock_memory_bytes: 360 trx_rows_locked: 4 trx_rows_modified: 1 trx_concurrency_tickets: 0 trx_isolation_level: REPEATABLE READ trx_unique_checks: 1 trx_foreign_key_checks: 1 trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 10000 trx_is_read_only: 0 trx_autocommit_non_locking: 0 TRX_REQUESTED_LOCK_ID: 持有者为trx_requested_lock_id: NULL 被堵塞者trx_requested_lock_id: 5458:14:3:2 事务当前等待的锁的ID,如果TRX_STATE 是LOCK WAIT; 否则值为NULL。 得到 lock的详细信息,关联这个列和 INNODB_LOCKS 表的LOCK_ID列 SELECT NOW(), (UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(a.trx_started)) diff_sec, b.id, b.user, b.host, b.db, c.lock_type, c.lock_table, c.lock_index FROM information_schema.innodb_trx a INNER JOIN information_schema.PROCESSLIST b ON a.TRX_MYSQL_THREAD_ID = b.id INNER JOIN information_schema.INNODB_LOCKS c on a.trx_requested_lock_id=c.lock_id; INFORMATION_SCHEMA.INNODB_LOCKS 这个表只有在堵塞的时候才有数据 TRX_WAIT_STARTED: 事务开始等待锁的时间,只有是TRX_STATE 是LOCK WAIT; 否则为空 TRX_MYSQL_THREAD_ID: MySQL 事务ID 得到thread 的详细信息,关联这个列和INFORMATION_SCHEMA PROCESSLIST table的ID列。 SELECT NOW(), (UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(a.trx_started)) diff_sec, b.id, b.user, b.host, b.db FROM information_schema.innodb_trx a INNER JOIN information_schema.PROCESSLIST b ON a.TRX_MYSQL_THREAD_ID = b.id; TRX_QUERY 语句当前事务执行的 TRX_OPERATION_STATE: 事务的当前的操作,如果any 否则NULL TRX_TABLES_IN_USE InnoDB tables 的数量当前用于处理SQL语句 TRX_TABLES_LOCKED: InnoDB 表的数量