MYSQL运维常用命令

1.查询进行中事务，带kill，方便执行kill

select concat("kill ",trx_mysql_thread_id,";"),a.* from information_schema.INNODB_TRX a order by trx_started asc;

2.查询数据大表，排查数据比较多的表、

select concat(round((DATA_LENGTH+INDEX_LENGTH)/1024/1024,2),'MB') as data,t.* from information_schema.TABLES t where ENGINE != 'MEMORY' order by DATA_LENGTH+INDEX_LENGTH desc;