http://blog.csdn.net/zengxuewen2045/article/details/52349731
https://github.com/enmotplinux/On-Site-Inspection/tree/master/lib
http://www.jianshu.com/p/5dd73a35d70f
http://makaidong.com/baidu_37107022/1/5211_9346170.html
https://i.cnblogs.com/EditPosts.aspx?postid=7726305
--------------------
# mysql sys db user
select * from host_summary \G
select * from user_summary \G
select host,current_connections,statements from host_summary \G
#show full processlist;
select conn_id,current_statement,last_statement from session \G
select * from io_global_by_file_by_bytes order by total desc limit 3 \G
select * from memory_global_total \G;
select * from innodb_buffer_stats_by_schema order by allocated desc limit 2;
select * from innodb_buffer_stats_by_table limit 2;
#每个连接使用多少内存
select b.user,current_count_used,current_allocated,current_avg_alloc,current_max_alloc,total_allocated,current_statement
from memory_by_thread_by_current_bytes a,session b where a.thread_id = b.thd_id \G
#查看表自增字段最大值和当前
select * from schema_auto_increment_columns \G
#索引使用情况
select * from schema_index_statistics limit 10;
#冗余索引
select * from schema_redundant_indexes limit 3 \G
select * from schema_unused_indexes limit 3 \G
#MYSQL 内部多少线程在运行
select user,count(*) from processlist group by user;
==================
mysql 查看所有没有主键的表
SELECT
#CONCAT("truncate table ",table_name,";")
table_name
FROM
information_schema. TABLES
WHERE
table_schema = 'scdm'
AND TABLE_NAME NOT IN (
SELECT
table_name
FROM
information_schema.table_constraints t
JOIN information_schema.key_column_usage k USING (
constraint_name,
table_schema,
table_name
)
WHERE
t.constraint_type = 'PRIMARY KEY'
AND t.table_schema = 'scdm'
)
获取缺失主键表信息的MYSQL语句
SELECT a.`TABLE_SCHEMA` AS DB_NAME , a.`TABLE_NAME` , a.`TABLE_ROWS` ,a.`ENGINE` -- , a.*
FROM
information_schema.`TABLES` a
LEFT JOIN information_schema.`TABLE_CONSTRAINTS` b
ON a.`TABLE_NAME` = b.`TABLE_NAME` AND a.`TABLE_SCHEMA` = b.`TABLE_SCHEMA`
WHERE b.`TABLE_SCHEMA` IS NULL
AND a.`TABLE_TYPE` = 'BASE TABLE'
AND a.`TABLE_SCHEMA` NOT IN ('information_schema','test','mysql','performance_schema')
ORDER BY DB_NAME ,a.`TABLE_ROWS` DESC ;
mysql如何获取主键的字段名
SELECT k.column_name
FROM information_schema.table_constraints t
JOIN information_schema.key_column_usage k
USING (constraint_name,table_schema,table_name)
WHERE t.constraint_type='PRIMARY KEY'
AND t.table_schema='db'
AND t.table_name=tbl'
两种获取MySql数据库中所有表的主键和外键约束信息的Sql语句
第一种方法：是网上的方法，可以直接使用，【MySQL 查看数据库中有主外键关系的表信息】
SELECT C.TABLE_SCHEMA 拥有者,
C.REFERENCED_TABLE_NAME 父表名称 ,
C.REFERENCED_COLUMN_NAME 父表字段 ,
C.TABLE_NAME 子表名称,
C.COLUMN_NAME 子表字段,
C.CONSTRAINT_NAME 约束名,
T.TABLE_COMMENT 表注释,
R.UPDATE_RULE 约束更新规则,
R.DELETE_RULE 约束删除规则
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE C
JOIN INFORMATION_SCHEMA. TABLES T
ON T.TABLE_NAME = C.TABLE_NAME
JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R
ON R.TABLE_NAME = C.TABLE_NAME
AND R.CONSTRAINT_NAME = C.CONSTRAINT_NAME
AND R.REFERENCED_TABLE_NAME = C.REFERENCED_TABLE_NAME
WHERE C.REFERENCED_TABLE_NAME IS NOT NULL ;
UPDATE/DELETE_RULE 类型：
1. CASCADE: 从父表中删除或更新对应的行 ，同时自动的删除或更新自表中匹配的行 。ON DELETE CANSCADE和ON UPDATE CANSCADE都被InnoDB所支持 。
2. SET NULL: 从父表中删除或更新对应的行 ，同时将子表中的外键列设为空 。注意， 这些在外键列没有被设为NOT NULL时才有效。 ON DELETE SET NULL和ON UPDATE SET SET NULL都被InnoDB所支持 。
3. NO ACTION: InnoDB拒绝删除或者更新父表 。
4. RESTRICT: 拒绝删除或者更新父表 。指定RESTRICT（ 或者NO ACTION ）和忽略ON DELETE或者ON UPDATE选项的效果是一样的 。
5. SET DEFAULT: InnoDB目前不支持 。
第二种方法：
select O.CONSTRAINT_SCHEMA,O.CONSTRAINT_NAME,O.TABLE_SCHEMA,O.TABLE_NAME,O.COLUMN_NAME,O.REFERENCED_TABLE_SCHEMA,O.REFERENCED_TABLE_NAME,O.REFERENCED_COLUMN_NAME,O.UPDATE_RULE,O.DELETE_RULE,O.UNIQUE_CONSTRAINT_NAME,T.CONSTRAINT_TYPE from (
select K.CONSTRAINT_SCHEMA,K.CONSTRAINT_NAME,K.TABLE_SCHEMA,K.TABLE_NAME,K.COLUMN_NAME,K.REFERENCED_TABLE_SCHEMA,K.REFERENCED_TABLE_NAME,K.REFERENCED_COLUMN_NAME,R.UPDATE_RULE,R.DELETE_RULE,R.UNIQUE_CONSTRAINT_NAME from information_schema.KEY_COLUMN_USAGE K LEFT join information_schema.REFERENTIAL_CONSTRAINTS R on K.CONSTRAINT_NAME=R.CONSTRAINT_NAME)
as O inner join Information_schema.TABLE_CONSTRAINTS T on O.Table_Name=T.TABLE_NAME and T.CONSTRAINT_NAME=O.CONSTRAINT_NAME where O.CONSTRAINT_SCHEMA!='mysql' and O.CONSTRAINT_SCHEMA!='sys';
第二种方法会列出所有的非系统数据所有表的主键信息和外键信息，第一种方法只会列出外键约束信息。
-----------
use information_schema;
show tables;
select * from KEY_COLUMN_USAGE
where
COLUMN_NAME='xx_id';
---------------
===mysqladmin查看mysql状态信息===
#1)
mysqladmin -S /data/3306/mysql_3306.sock -uroot -poldboy123 extended-status \
|grep "Questions\|Queries\|Innodb_rows\|Com_select \|Com_insertstions\|Queries\|Innodb_rows\|Com_select \|Com_insert \|Com_update\|Com_delete"
mysqladmin -h 192.168.1.139 -uroot -p123456 extended-status |grep "Questions\|Queries\|Innodb_rows\|Com_select \|Com_insertstions\|Queries\|Innodb_rows\|Com_select \|Com_insert \|Com_update\|Com_delete"
#2)
mysqladmin -uroot -p123456 -h192.168.1.139 -P3306 -r -i 5 ext |\
awk -F"|" '{\
if($2 ~ /Variable_name/){\
print " <------------- " strftime("%H:%M:%S") " ------------->";\
}\
if($2 ~ /Questions|Queries|Innodb_rows|Com_select |Com_insert |Com_update |Com_delete |Innodb_buffer_pool_read_requests/)\
print $2 $3;\
}'
#3）
mysqladmin -P3306 -uroot -p123456 -h192.168.1.139 -r -i 5 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\n",innodb_lor,innodb_phr);\
}}'
