dba:mysql:mysql_basic_queries
**This is an old revision of the document!**
Table of Contents
[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 <OBJECTTYPE>” commands accept “like” to limit the query results: <code sql> like '%OBJECTNAME%' ; </code>
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='<DATABASENAME>' 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 ;
dba/mysql/mysql_basic_queries.1645120639.txt.gz · Last modified: 2022/02/17 17:57 by dodger