mysql dba常用的查询语句
发布时间：2022-03-25 14:21:51 所属栏目：MySql教程 来源：互联网
导读：查看帮助命令 mysql help; --总的帮助 mysql help show; --查看show的帮助命令 mysql help create;--查看create的帮助命令 mysql help select;--查看select的帮助命令 mysql help flush;--查看flush的帮助命令 mysql help reset;--查看reset的帮助命令 查询
|
查看帮助命令 mysql> help; --总的帮助 mysql> help show; --查看show的帮助命令 mysql> help create;--查看create的帮助命令 mysql> help select;--查看select的帮助命令 mysql> help flush;--查看flush的帮助命令 mysql> help reset;--查看reset的帮助命令 查询实例的基本信息 status 查看数据库db1的创建脚本 mysql> show create database db1; 查看表table1的创建脚本 mysql> show create table table1G 查询table1表哪些字段有索引，Key有值代表该字段有索引 desc table1 查询table1表的索引，还能看到cardinality信息 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 variables like 'read_only'; 查看某个状态 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; 查看各种引擎信息,Support列为DEFAULT表示为当前实例的默认存储引擎 show engines; 查看当前实例的存储引擎设置 show variables like '%engi%' 查看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 dbnameG; 禁用某个数据库的某个事件 alter event dbname.eventname disable; 重新收集表的统计信息 analyze table tablename 重建表 alter table tablename engine=innodb 修改表的存储引擎为innodb alter table tablename engine=innodb 优化表 optimize table tablename=analyze table tablename + alter table tablename engine=innodb 修改proc存储过程的definer，比如把'dev_user@%'改成'prod_user@%' select db,name,type,definer from mysql.procG; update mysql.proc set definer='prod_user@%' where definer='dev_user@%' 修改event事件的definer，比如把'dev_user@%'改成'prod_user@%' select db,name,definer from mysql.eventG; update mysql.event set definer='prod_user@%' where definer='dev_user@%' （编辑：威海站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
