mysql dba常用的查询语句

查看帮助命令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 table1\G  查询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();查询未提交会话的具体SQLshow engine innodb status;查看lock struct信息，比如下面查看到线程是81 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 1MySQL thread id 8, OS thread handle 1358473536, query id 1271 localhost root cleaning upselect 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=ONsys.schema_table_lock_waitsselect * from sys.schema_table_lock_waits where object_name=`'test'.'t'`\Gselect blocking_pid from sys.schema_table_lock_waits where object_name=`'test'.'t'`\Ghttps://dev.mysql.com/doc/refman/5.7/en/sys-schema-table-lock-waits.htmlsys.innodb_lock_waitsselect * from sys.innodb_lock_waits where locked_table=`'test'.'t'`\Gselect blocking_pid from sys.innodb_lock_waits where locked_table=`'test'.'t'`\Ghttps://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 logmysql> 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_mbcount(*) as tablesfrom information_schema.tableswhere table_schema not in('INFORMATION_SCHEMA','PERFORMANCE_SCHEMA') group by table_schema,engine order by 3 desc查询耗CPU的SQLmysql> show full processlist;找到Time最大的，其对应的ID列就是耗cpu最厉害的线程ID，对应的Info列就是具体的SQL或查看慢查询日志，找到Query_time值最大的行，会记录其线程ID号和具体的SQL在master上查看有哪些slavemysql> 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_idmysql>kill query thread_idmysql>kill thread_id所有数据库事件的查看select db,name,last_executed,status from mysql.event;单个数据库的事件查看show events from dbname\G;禁用某个数据库的某个事件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.proc\G; update mysql.proc set definer='prod_user@%' where definer='dev_user@%' 修改event事件的definer，比如把'dev_user@%'改成'prod_user@%' select db,name,definer from mysql.event\G; update mysql.event set definer='prod_user@%' where definer='dev_user@%' 批量杀sleep状态的spid，这些spid可以继续过滤出来是来自哪个用户，来自哪个客户端ip，访问哪个数据库 SELECT GROUP_CONCAT(CONCAT('kill ',id) SEPARATOR '; ') AS cmd FROM information_schema.processlist WHERE USER='ibdcmsprod1_user' AND command='Sleep' AND db='ibdcmsprod_wp' and host like '192.168.143.6%';