mysql 常用命令

-- 查看多个参数
SHOW VARIABLES where  Variable_name in('max_connections','version','slow_query_log','log_output');

-- 查看多个状态
show global status where Variable_name in('Memory_used','uptime','max_connections','Threads_connected','Bytes_received','Bytes_sent','Com_rollback','Com_commit','innodb_buffer_pool_reads','innodb_buffer_pool_read_requests','innodb_buffer_pool_pages_free','innodb_buffer_pool_pages_total','innodb_buffer_pool_pages_dirty','innodb_data_written','innodb_data_read','innodb_buffer_pool_write_requests');

-- 查看锁表信息
SELECT
     a.REQUESTING_ENGINE_TRANSACTION_ID as 'r_trx_id'
    ,a.BLOCKING_ENGINE_TRANSACTION_ID   as 'b_trx_id'
    ,b.trx_query                        as 'r_trx_query'
    ,c.trx_query                        as 'b_trx_query'
    ,b.trx_mysql_thread_id              as 'r_tid'
    ,c.trx_mysql_thread_id              as 'b_tid'
    ,b.trx_started                      as 'r_start_time'
    ,c.trx_started                      as 'b_start_time'
    ,d.LOCK_MODE                        as 'r_lock_mode'
    ,e.LOCK_MODE                        as 'b_lock_mode'
    ,d.LOCK_TYPE                        as 'r_lock_type'
    ,e.LOCK_TYPE                        as 'b_lock_type'
    ,d.OBJECT_NAME                      as 'r_table_name'
    ,e.OBJECT_NAME                      as 'b_table_name'
    ,f.HOST                             as 'r_host'
    ,g.HOST                             as 'b_host'
    ,f.USER                             as 'r_user'
    ,g.USER                             as 'b_user'
    ,f.DB                               as 'r_db'
    ,g.DB                               as 'b_db'
    ,TIMESTAMPDIFF(
            SECOND,
            b.trx_wait_started,
            CURRENT_TIMESTAMP
    )                                   as wait_second
from performance_schema.data_lock_waits  a
inner join information_schema.INNODB_TRX b on a.REQUESTING_ENGINE_TRANSACTION_ID=b.trx_id
inner join information_schema.INNODB_TRX c on a.BLOCKING_ENGINE_TRANSACTION_ID=c.trx_id
left join performance_schema.data_locks  d on a.REQUESTING_ENGINE_LOCK_ID = d.ENGINE_LOCK_ID
left join performance_schema.data_locks  e on a.BLOCKING_ENGINE_LOCK_ID = e.ENGINE_LOCK_ID
left join information_schema.PROCESSLIST f on f.ID = b.trx_mysql_thread_id
left join information_schema.PROCESSLIST g on g.ID = c.trx_mysql_thread_id
where TIMESTAMPDIFF(
        SECOND,
        b.trx_wait_started,
        CURRENT_TIMESTAMP
    ) > 20
order by wait_second desc
limit 10
;



-- 批量kill SQL
select
     concat('kill ', ID,';') as kill_sql
    ,time
    ,USER
    ,HOST
    ,db
    -- ,info
from information_schema.processlist
where INFO is not null
    and time > 5
    and db = 'xxxxxxxxxxxxxxxxx'
    -- and id = '1267733'
order by time desc
limit 10
;