====== [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 ;


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 ;