Ciberterminal Wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== [DOC] Mysql basic queries ======


====== Status information ======

<code sql>
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]
</code>


====== Memory ======
===== Global summary =====

<code sql>
 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;
</code>


===== memory by event =====

<code sql>
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
;
</code>

===== per user =====
<code sql>
 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;
</code>


====== Objects ======

All "SHOW <OBJECT_TYPE>" commands accept "like" to limit the query results:
<code sql> like '%OBJECT_NAME%' ;
</code>



===== List functions =====

<code sql>SHOW FUNCTION STATUS ;
</code>

===== List Procedures =====

<code sql>SHOW PROCEDURE STATUS ;
</code>


===== List Triggers =====
<code sql>SHOW TRIGGERS ;
</code>

===== List Tables =====
<code sql>
select table_schema, table_name, table_type, table_rows, engine, version from information_schema.tables ;
</code>


<code sql>SHOW TABLES ;
</code>

<code sql>SHOW FULL TABLES ;
</code>

<code sql>
show table status ;
</code>

===== List Table indexes =====
<code sql>SHOW INDEX FROM tbl_name [FROM db_name]
</code>
<code sql>
SELECT DISTINCT
    TABLE_NAME,
    INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your_schema';
</code>


===== DDL/Metadata =====

==== Table ====
<code sql>SHOW CREATE TABLE TABLENAME ;
</code>

==== Function ====
<code sql>SHOW CREATE FUNCTION FUNCTIONNAME ;
</code>

o

<code sql>
select body_utf8 from mysql.proc where db='<DATABASENAME>' and name='FUNCTIONNAME' and type='FUNCTION' ;
</code>

====== Master/Slave ======
===== Master status =====
<code sql>Show master status ;
</code>
===== Slave status =====
<code sql>Show slave status \G
</code>


===== binlog purge =====
<code sql>PURGE BINARY LOGS TO 'mysql-bin.000060';
</code>

===== Skip slave instruction =====

<code sql>
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
</code>

===== Reset slave =====
In the case that the slave keep old settings (like renaming the relay-log)
<code sql>
reset slave ;
</code>

<code sql>
</code>
<code sql>
</code>
<code sql>
</code>
<code sql>
</code>
<code sql>
</code>