查看事務(wù)
select * from information_schema.innodb_trx\G;
select * from information_schema.innodb_trx where trx_mysql_thread_id=? \G;
查看processlist
select? *? ?from? ?information_schema.processlist;
根據(jù)INFO查看連接
select? *? ?from? ?information_schema.processlist where INFO like '%%';
根據(jù)用戶名查看連接
select * from information_schema.processlist where user? like? '%%'
根據(jù)數(shù)據(jù)庫查看連接
select * from information_schema.processlist where? db? like? '%%';
根據(jù)command查看連接
select * from information_schema.processlist where? ? ?COMMAND? not in ('Sleep') ;
根據(jù)command查看連接,并按照time排序
select * from information_schema.processlist where? ? ?COMMAND? not in ('Sleep') order by? TIME;
根據(jù)Host查看連接
select * from information_schema.processlist where? ? ?Host? like '%%' ;
生成kill的sql
select ?concat('kill ',ID,';')from ?information_schema.processlist ?where user like ?'%%'?order by TIME ;
select concat('kill ',ID,';') from information_schema.processlist where COMMAND not in ('Sleep') and user='root' and INFO like 'select%' order by TIME\G;
查看慢日志
egrep '# Time|# Query_time'? ?slow.log
egrep '# Query_time'? ?slow.log
egrep '# Query_time'? ? slow.log|awk '{print $3}'|sort -n
01.獲取 mysql 指定表是否存在的 sql 語句：
（2）查看所有表
select * from information_schema.tables;
（2）查看指定表
SELECT? ?*? FROM INFORMATION_SCHEMA.TABLES? WHERE? TABLE_NAME = ''\G;
（3）查看表的詳細(xì)信息
SELECT * FROM information_schema.TABLES WHERE? TABLE_NAME LIKE '%%'\G;
02.獲取表字段的 sql 語句
select * from information_schema.columns;
03.獲取表主鍵值的 sql 語句
select * from information_schema.key_column_usage
where table_schema='itpux' and table_name='dept';
04.獲取表 check 約束的 sql 語句
select * from information_schema.table_constraints;
05.獲取表索引的 sql 語句
select * from information_schema.statistics;
06.MySQL 查詢某張表在哪個(gè)數(shù)據(jù)庫里：
select * from tables where table_name='dept';
07.查 deptno 字段在哪個(gè)數(shù)據(jù)庫的哪張表里:
select TABLE_SCHEMA,TABLE_NAME from columns where COLUMN_NAME='deptno';
08.查詢 MySQL 中某個(gè)數(shù)據(jù)庫中有多少張表：:
select COUNT(*) tables, table_schema from information_schema.TABLES
where table_schema = 'itpux' group by table_schema;
09.查詢 MySQL 中某個(gè)數(shù)據(jù)庫中某個(gè)表里有多少列：
select COUNT(*) from columns where TABLE_NAME='dept' and TABLE_SCHEMA='itpux';
10.獲取所有表結(jié)構(gòu)(tables)
select * from information_schema.TABLES where TABLE_SCHEMA='itpux';
11.獲取表字段(columns)
select * from information_schema.COLUMNS where TABLE_SCHEMA='itpux' and
TABLE_NAME='dept'
12.獲取表鍵值
select * from information_schema.KEY_COLUMN_USAGE where
TABLE_SCHEMA='itpux' and TABLE_NAME='dept'
13.獲取表 Check 約束
select * from information_schema.TABLE_CONSTRAINTS where
TABLE_SCHEMA='itpux' and TABLE_NAME='dept'
14.獲取表索引
select * from information_schema.STATISTICS where TABLE_SCHEMA='itpux' and
TABLE_NAME='dept';
15.查看是否存在myisam
select table_schema,table_name,engine from information_schema.tables where engine != 'innodb' and table_schema not in('mysql','information_schema','performance_schema');
