====== [DOC] Mysql basic queries ======
====== Status information ======
SHOW AUTHORS
SHOW {BINARY | MASTER} LOGS
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW CHARACTER SET [like_or_where]
SHOW COLLATION [like_or_where]
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]
SHOW CONTRIBUTORS
SHOW CREATE DATABASE db_name
SHOW CREATE EVENT event_name
SHOW CREATE FUNCTION func_name
SHOW CREATE PROCEDURE proc_name
SHOW CREATE TABLE tbl_name
SHOW CREATE TRIGGER trigger_name
SHOW CREATE VIEW view_name
SHOW DATABASES [like_or_where]
SHOW ENGINE engine_name {STATUS | MUTEX}
SHOW [STORAGE] ENGINES
SHOW ERRORS [LIMIT [offset,] row_count]
SHOW EVENTS
SHOW FUNCTION CODE func_name
SHOW FUNCTION STATUS [like_or_where]
SHOW GRANTS FOR user
SHOW INDEX FROM tbl_name [FROM db_name]
SHOW MASTER STATUS
SHOW OPEN TABLES [FROM db_name] [like_or_where]
SHOW PLUGINS
SHOW PROCEDURE CODE proc_name
SHOW PROCEDURE STATUS [like_or_where]
SHOW PRIVILEGES
SHOW [FULL] PROCESSLIST
SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]
SHOW PROFILES
SHOW SLAVE HOSTS
SHOW SLAVE STATUS
SHOW [GLOBAL | SESSION] STATUS [like_or_where]
SHOW TABLE STATUS [FROM db_name] [like_or_where]
SHOW [FULL] TABLES [FROM db_name] [like_or_where]
SHOW TRIGGERS [FROM db_name] [like_or_where]
SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
SHOW WARNINGS [LIMIT [offset,] row_count]
====== Memory ======
===== Global summary =====
SELECT current_count_used AS curr_count,
sys.format_bytes(current_number_of_bytes_used) curr_alloc,
count_alloc,
sys.format_bytes(sum_number_of_bytes_alloc) total_alloc,
count_free,
sys.format_bytes(sum_number_of_bytes_free) total_free
FROM performance_schema.memory_summary_global_by_event_name;
===== memory by event =====
SELECT EVENT_NAME,
COUNT_ALLOC,
COUNT_FREE,
SUM_NUMBER_OF_BYTES_ALLOC,
SUM_NUMBER_OF_BYTES_FREE
FROM performance_schema.memory_summary_global_by_event_name
where count_alloc>1
ORDER BY count_alloc
;
===== per user =====
SELECT IFNULL(user, 'mysqld_background') AS user,
current_count_used AS curr_count,
sys.format_bytes(current_number_of_bytes_used) curr_alloc,
count_alloc,
sys.format_bytes(sum_number_of_bytes_alloc) total_alloc,
count_free,
sys.format_bytes(sum_number_of_bytes_free) total_free
FROM performance_schema.memory_summary_by_user_by_event_name
where current_number_of_bytes_used>0
ORDER BY current_number_of_bytes_used DESC;
====== Objects ======
All "SHOW " commands accept "like" to limit the query results:
like '%OBJECT_NAME%' ;
===== List functions =====
SHOW FUNCTION STATUS ;
===== List Procedures =====
SHOW PROCEDURE STATUS ;
===== List Triggers =====
SHOW TRIGGERS ;
===== List Tables =====
select table_schema, table_name, table_type, table_rows, engine, version from information_schema.tables ;
SHOW TABLES ;
SHOW FULL TABLES ;
show table status ;
===== List Table indexes =====
SHOW INDEX FROM tbl_name [FROM db_name]
SELECT DISTINCT
TABLE_NAME,
INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your_schema';
===== DDL/Metadata =====
==== Table ====
SHOW CREATE TABLE TABLENAME ;
==== Function ====
SHOW CREATE FUNCTION FUNCTIONNAME ;
o
select body_utf8 from mysql.proc where db='' and name='FUNCTIONNAME' and type='FUNCTION' ;
====== Master/Slave ======
===== Master status =====
Show master status ;
===== Slave status =====
Show slave status \G
===== binlog purge =====
PURGE BINARY LOGS TO 'mysql-bin.000060';
===== Skip slave instruction =====
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
===== Reset slave =====
In the case that the slave keep old settings (like renaming the relay-log)
reset slave ;