mysql 数据字典

2016-05-26

 

-- 1、mysql 用户、权限
SELECT
    `user`,
    `host`,
    `password`
FROM
    mysql.`user`;

-- 2、mysql 过程
SELECT
    db,
    `name` AS sp_name,
    type,
    `definer`,
    created,
    modified
FROM
    mysql.proc;

-- 3、mysql 连接的主机IP查询
SELECT
    `HOST` AS `host`,
    `CURRENT_CONNECTIONS` AS `current_con`,
    `TOTAL_CONNECTIONS` AS `total_con`
FROM
    `performance_schema`.`hosts`
ORDER BY
    `HOST`;

-- 4、mysql 线程
SELECT
    PROCESSLIST_ID AS `Id`,
    PROCESSLIST_USER AS `User`,
    PROCESSLIST_HOST AS `Host`,
    PROCESSLIST_DB AS db,
    PROCESSLIST_COMMAND AS `Command`,
    PROCESSLIST_TIME AS Time,
    PROCESSLIST_STATE AS State,
    PROCESSLIST_INFO AS Info
FROM
    `performance_schema`.threads
WHERE
    TYPE = 'FOREGROUND';

-- 5、mysql 表
SELECT
    OBJECT_SCHEMA AS `db_name`,
    OBJECT_NAME AS `table_name`
FROM
    `performance_schema`.`table_io_waits_summary_by_table`
WHERE
    OBJECT_SCHEMA NOT IN (
        'mysql',
        'performance_schema'
    )
ORDER BY
    OBJECT_SCHEMA,
    OBJECT_NAME;

-- 6、mysql 索引
SELECT
    OBJECT_SCHEMA AS `db_name`,
    OBJECT_NAME AS `table_name`,
    INDEX_NAME AS `index_name`
FROM
    `performance_schema`.`table_io_waits_summary_by_index_usage`
WHERE
    OBJECT_SCHEMA NOT IN (
        'mysql',
        'performance_schema'
    )
AND INDEX_NAME IS NOT NULL
ORDER BY
    OBJECT_SCHEMA,
    OBJECT_NAME;

-- 7、mysql 锁
SELECT
    *
FROM
    `performance_schema`.table_lock_waits_summary_by_table
WHERE
    OBJECT_SCHEMA NOT IN (
        'mysql',
        'performance_schema'
    )
ORDER BY
    OBJECT_SCHEMA,
    OBJECT_NAME;

-- 8、mysql 数据文件、日志文件
SELECT
    FILE_NAME
FROM
    `performance_schema`.file_instances
ORDER BY
    FILE_NAME;