ITPub博客

首页 > 数据库 > MySQL > mysql dba常用的查询语句

mysql dba常用的查询语句

原创 MySQL 作者:lusklusklusk 时间:2018-12-10 22:11:37 0 删除 编辑

查询实例的基本信息

status


查询table1表的索引

show index from table1


查看select语句的执行计划

explain extended select * from t1;

desc extended select * from t1;


查看某个参数

show global variables like '%XX%';

show global variables where variable_name in ('XX');


查看某个状态

show status like '%YY%';


查看当前连接的客户端数量

show status like 'Threads_connected';


查看服务器的连接次数

show status like 'Connections';


查看曾经的最大连接数

show status like 'Max_used_connections';


查看mysql线程

show full processlist;


查看有多少个数据库

show databases;


查看当前数据库下有多少张表

show tables;


查看各种引擎信息

show engines;


查看LSN(Log sequence number当前redo log的最新号)

show engine innodb status;


查看当前数据库

select database();


查看当前数据库服务器版本

select version();


查看当前用户

select user();


查询未提交会话的具体SQL

show engine innodb status;查看lock struct信息,比如下面查看到线程是8

1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 1

MySQL thread id 8, OS thread handle 1358473536, query id 1271 localhost root cleaning up

select sql_text from performance_schema.events_statements_current where THREAD_ID in (select THREAD_ID from performance_schema.threads where PROCESSLIST_ID=8)

select b.conn_id,b.thd_id,a.last_statement from sys.session a,sys.processlist b where a.thd_id=b.thd_id and a.conn_id=b.conn_id and b.conn_id=8


查询锁源线程

select a.trx_mysql_thread_id,a.* from information_schema.INNODB_TRX a where trx_id  in (select blocking_trx_id from information_schema.INNODB_LOCK_WAITS)


查询被锁线程

select a.trx_mysql_thread_id,a.* from information_schema.INNODB_TRX a where trx_id  in (select requesting_trx_id from information_schema.INNODB_LOCK_WAITS)


查询XX线程被谁堵塞了select trx_mysql_thread_id blocking_thread,trx_started,trx_query from information_schema.INNODB_TRX where trx_id in 

(select blocking_trx_id from information_schema.INNODB_LOCK_WAITS where requesting_trx_id in 

(select trx_id from information_schema.INNODB_TRX where trx_mysql_thread_id='XX')

)


5.7.9版本后,建议使用sys.schema_table_lock_waits和sys.innodb_lock_waits来查堵塞,不过需要开启参数performance_schema=ON

sys.schema_table_lock_waits

select * from sys.schema_table_lock_waits where object_name=`'test'.'t'`\G 

select blocking_pid from sys.schema_table_lock_waits where object_name=`'test'.'t'`\G 

https://dev.mysql.com/doc/refman/5.7/en/sys-schema-table-lock-waits.html


sys.innodb_lock_waits

select * from sys.innodb_lock_waits where locked_table=`'test'.'t'`\G

select blocking_pid from sys.innodb_lock_waits where locked_table=`'test'.'t'`\G

https://dev.mysql.com/doc/refman/5.7/en/sys-innodb-lock-waits.html


查询user1用户的权限

show grants for user1


查看所有binary日志

show binary logs;

show master logs;


查看当前binary日志文件状态

show master status;


刷新binary日志

flush binary logs;


删除某个binary日志之前的所有日志

purge binary logs to 'mysql-bin.000003';


删除所有的binary log

mysql> reset master;


查询有多少条慢查询记录

mysql> show global status like '%Slow_queries%';


执行一个10秒的查询

mysql> select sleep(10);


查找持续时间超过 60s 的事务

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60


查询所有数据库的数据和索引的大小

select round(sum(data_length+index_length)/1024/1024) as total_mb,round(sum(data_length)/1024/1024) as data_mb,round(sum(index_length)/1024/1024) as index_mb from information_schema.tables


查询每个数据库的引擎、容量、总表数

select table_schema,engine,

round(sum(data_length+index_length)/1024/1024) as total_mb,

round(sum(data_length)/1024/1024) as data_mb,

round(sum(index_length)/1024/1024) as index_mb

count(*) as tables 

from information_schema.tables 

where table_schema not in('INFORMATION_SCHEMA','PERFORMANCE_SCHEMA') group by table_schema,engine order by 3 desc


查询耗CPU的SQL

mysql> show full processlist;

找到Time最大的,其对应的ID列就是耗cpu最厉害的线程ID,对应的Info列就是具体的SQL

查看慢查询日志,找到Query_time值最大的行,会记录其线程ID号和具体的SQL


在master上查看有哪些slave

mysql> select * from information_schema.processlist as p where p.command = 'Binlog Dump'; 

mysql> show slave hosts;(此方法需要在从服务启动时指定--report-host=HOSTNAME选项,此处HOSTNAME为任意名称。)


杀线程的SQL,以下两者必须同时使用,其中kill thread_id=kill connection thread_id

mysql>kill query thread_id

mysql>kill thread_id


所有数据库事件的查看

select db,name,last_executed,status from mysql.event;

单个数据库的事件查看

show events from dbname\G;


禁用某个数据库的某个事件

alter event dbname.eventname disable;



来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30126024/viewspace-2284788/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Welcome to Lukes DB HOME。11G OCM, 5年以上DBA工作经验,博客仅记录自己的一个学习过程,不代表完全准确,如有需要,欢迎转载。

注册时间:2015-02-02

  • 博文量
    345
  • 访问量
    331477