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