SHOW VARIABLES like '%pro%'
SHOW VARIABLES like 'binlog_format'
SHOW VARIABLES like'binlog_row_image'
show global variables like 'log_bin';

查看用户登陆信息

select user,host from mysql.user;

查看主数据库信息

show master status

慢查询命令

SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 50;

查看慢查询设置

show variables like 'long_query_time';

正在处理的事务进程

show PROCESSLIST;

查询正在执行的语句

show full processlist

Seconds_Behind_Master参数的值来判断

show slave status

查看是否有活跃sql

show processlist; show slave status

show OPEN TABLES where In_use > 0;

更新root登录权限为任意

update mysql.user set host='%' where user='root';

使其立即生效

flush privileges;

查看MYSQL表大小索引大小查询命令

SELECT
    CONCAT(
        table_schema,
        '.',
        table_name
    ) AS 'Table Name',
    CONCAT(
        ROUND(table_rows / 1000000, 4),
        'M'
    ) AS 'Number of Rows',
    CONCAT(
        ROUND(
            data_length / (1024 * 1024 * 1024),
            4
        ),
        'G'
    ) AS 'Data Size',
    CONCAT(
        ROUND(
            index_length / (1024 * 1024 * 1024),
            4
        ),
        'G'
    ) AS 'Index Size',
    CONCAT(
        ROUND(
            (data_length + index_length) / (1024 * 1024 * 1024),
            4
        ),
        'G'
    ) AS 'Total' FROM information_schema. TABLES