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();

查詢未提交會話的具體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;

重新收集表的統計資訊

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%';