mysql常用排查命令

1.查看服务器使用状态

mysqladmin -uroot -p -S /tmp/mysql.sock -r -i 1 ext |
awk -F"|" 
"BEGIN{ count=0; }"
'{ if($2 ~ /Variable_name/ && ++count == 1){
    print "----------|---------|--- MySQL Command Status --|----- Innodb row operation ----|-- Buffer Pool Read --";
    print "---Time---|---QPS---|select insert update delete|  read inserted updated deleted|   logical    physical";
}
else if ($2 ~ /Queries/){queries=$3;}
else if ($2 ~ /Com_select /){com_select=$3;}
else if ($2 ~ /Com_insert /){com_insert=$3;}
else if ($2 ~ /Com_update /){com_update=$3;}
else if ($2 ~ /Com_delete /){com_delete=$3;}
else if ($2 ~ /Innodb_rows_read/){innodb_rows_read=$3;}
else if ($2 ~ /Innodb_rows_deleted/){innodb_rows_deleted=$3;}
else if ($2 ~ /Innodb_rows_inserted/){innodb_rows_inserted=$3;}
else if ($2 ~ /Innodb_rows_updated/){innodb_rows_updated=$3;}
else if ($2 ~ /Innodb_buffer_pool_read_requests/){innodb_lor=$3;}
else if ($2 ~ /Innodb_buffer_pool_reads/){innodb_phr=$3;}
else if ($2 ~ /Uptime / && count >= 2){
  printf(" %s |%9d",strftime("%H:%M:%S"),queries);
  printf("|%6d %6d %6d %6d",com_select,com_insert,com_update,com_delete);
  printf("|%6d %8d %7d %7d",innodb_rows_read,innodb_rows_inserted,innodb_rows_updated,innodb_rows_deleted);
  printf("|%10d %11d
",innodb_lor,innodb_phr);
}}'

2.处理generlog

pt-query-digest --limit 100% --type=genlog mysql-general.log > general.log

3.查看无主键的表

select

table_schema,table_name,TABLE_TYPE,ENGINE,ROW_FORMAT,TABLE_ROWS from information_schema.tables where (table_schema,table_name) not

in (select distinct table_schema,table_name from information_schema.columns where COLUMN_KEY='PRI') and table_schema not in ('sys','mysql','information_schema','performance_schema' );

4.查看非innodb引擎表

select table_name,table_schema,engine from information_schema.tables where engine!='innodb' and table_schema not in('mysql','information_schema','performance_schema');

5.插卡行锁

select trx_id,trx_state,trx_query from information_schema.innodb_trx where trx_state like '%LOCK%';
 
select trx_tables_locked,trx_lock_structs,trx_wait_started,trx_state,trx_requested_lock_id from  INNODB_TRX where trx_tables_locked != 0 or trx_wait_started is not NULL;

6.查看进程信息

按客户端 IP 分组，看哪个客户端的链接数最多
select client_ip,count(client_ip) as client_num from (select substring_index(host,':' ,1) as client_ip from processlist ) as connect_info group by client_ip order by client_num desc;
 
查看正在执行的线程，并按 Time 倒排序，看看有没有执行时间特别长的线程
select * from information_schema.processlist where Command != 'Sleep' and INFO like "%select%" order by Time descG;
 
找出所有执行时间超过 5 分钟的线程，拼凑出 kill 语句，方便后面查杀
select concat('kill ', id, ';') from information_schema.processlist where Command != 'Sleep' and Time > 300 order by Time desc;

7.查看innodb行锁信息

innodb行锁信息

---------------查看innodb行锁信息----------------------
SELECT p2.`HOST` Blockedhost,p2.`USER` BlockedUser,
       r.trx_id BlockedTrxId,
       r.trx_mysql_thread_id BlockedThreadId,   
       TIMESTAMPDIFF(SECOND,r.trx_wait_started,CURRENT_TIMESTAMP) WaitTime,   
       r.trx_query BlockedQuery,   
       l.lock_table BlockedTable, 
       m.`lock_mode` BlockedLockMode,
       m.`lock_type` BlockedLockType,
       m.`lock_index` BlockedLockIndex,
       m.`lock_space` BlockedLockSpace,
       m.lock_page BlockedLockPage,
       m.lock_rec BlockedLockRec,
       m.lock_data BlockedLockData,
       p.`HOST` blocking_host,
       p.`USER` blocking_user,
       b.trx_id BlockingTrxid,   
       b.trx_mysql_thread_id BlockingThreadId,
       b.trx_query BlockingQuery,
       l.`lock_mode` BlockingLockMode,
       l.`lock_type` BlockingLockType,
       l.`lock_index` BlockingLockIndex,
       l.`lock_space` BlockingLockSpace,
       l.lock_page BlockingLockPage,
       l.lock_rec BlockingLockRec,
       l.lock_data BlockingLockData,        
       IF (p.COMMAND = 'Sleep', CONCAT(p.TIME,' seconds'), 0) idel_in_trx            
    FROM   
        information_schema.INNODB_LOCK_WAITS w   
    INNER JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id   
    INNER JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id   
    INNER JOIN information_schema.INNODB_LOCKS l ON w.blocking_lock_id = l.lock_id  AND l.`lock_trx_id`=b.`trx_id`
    INNER JOIN information_schema.INNODB_LOCKS m ON m.`lock_id`=w.`requested_lock_id` AND m.`lock_trx_id`=r.`trx_id`
    INNER JOIN information_schema. PROCESSLIST p ON p.ID = b.trx_mysql_thread_id  
    INNER JOIN information_schema. PROCESSLIST p2 ON p2.ID = r.trx_mysql_thread_id
    ORDER BY WaitTime DESC G;
 
 
----------------------对应的中文------------------------
SELECT p2.`HOST` 被阻塞方host,
       p2.`USER` 被阻塞方用户,
       r.trx_id 被阻塞方事务id,   
       r.trx_mysql_thread_id 被阻塞方线程号,   
       TIMESTAMPDIFF(SECOND,r.trx_wait_started,CURRENT_TIMESTAMP) 等待时间,   
       r.trx_query 被阻塞的查询,   
       l.lock_table 阻塞方锁住的表, 
       m.`lock_mode` 被阻塞方的锁模式,
       m.`lock_type`  "被阻塞方的锁类型(表锁还是行锁)",
       m.`lock_index` 被阻塞方锁住的索引,
       m.`lock_space` 被阻塞方锁对象的space_id,
       m.lock_page 被阻塞方事务锁定页的数量,
       m.lock_rec 被阻塞方事务锁定行的数量,
       m.lock_data  被阻塞方事务锁定记录的主键值, 
       p.`HOST` 阻塞方主机,
       p.`USER` 阻塞方用户,
       b.trx_id 阻塞方事务id,   
       b.trx_mysql_thread_id 阻塞方线程号,
       b.trx_query 阻塞方查询,
       l.`lock_mode` 阻塞方的锁模式,
       l.`lock_type` "阻塞方的锁类型(表锁还是行锁)",
       l.`lock_index` 阻塞方锁住的索引,
       l.`lock_space` 阻塞方锁对象的space_id,
       l.lock_page 阻塞方事务锁定页的数量,
       l.lock_rec 阻塞方事务锁定行的数量,
       l.lock_data 阻塞方事务锁定记录的主键值,        
       IF (p.COMMAND = 'Sleep', CONCAT(p.TIME,' 秒'), 0) 阻塞方事务空闲的时间          
    FROM   
        information_schema.INNODB_LOCK_WAITS w   
    INNER JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id   
    INNER JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id   
    INNER JOIN information_schema.INNODB_LOCKS l ON w.blocking_lock_id = l.lock_id  AND l.`lock_trx_id`=b.`trx_id`
    INNER JOIN information_schema.INNODB_LOCKS m ON m.`lock_id`=w.`requested_lock_id` AND m.`lock_trx_id`=r.`trx_id`
    INNER JOIN information_schema. PROCESSLIST p ON p.ID = b.trx_mysql_thread_id  
    INNER JOIN information_schema. PROCESSLIST p2 ON p2.ID = r.trx_mysql_thread_id
    ORDER BY 等待时间 DESC G;