jump to navigation

Using Mysql’s SHOW Command : 10 Practical Examples December 27, 2012

Posted by Tournas Dimitrios in Mysql.
trackback

Mysql’s SHOW command is used to display all kind of information that might be helpful during administration tasks , it’s output format can be changed using command-line options . The official manual has a list of 42 command line options , this article will present ten practical examples that deserves to be in our tool-set  . Invoke SHOW  from the mysql prompt  (mysql > )  while logged into your Mysql-server as a user with “root” privileges  .

  1. Lists the databases and tables on the MySQL server :

    mysql> show databases ;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | logistics          |
    | mysql              |
    | test               |
    | test_liquibase     |
    | world              |
    +--------------------+
    6 rows in set (0.00 sec)
    
    mysql> show tables from world ;
    +-----------------------+
    | Tables_in_world       |
    +-----------------------+
    | DATABASECHANGELOG     |
    | DATABASECHANGELOGLOCK |
    | city                  |
    | city2                 |
    | country               |
    | countrylanguage       |
    | lalakis               |
    | world_country         |
    +-----------------------+
    8 rows in set (0.00 sec)
    
    mysql>
    
    
  2. Shows the statement that created the given database or table .
    mysql> show create database world ;
    +----------+------------------------------------------------------------------+
    | Database | Create Database                                                  |
    +----------+------------------------------------------------------------------+
    | world    | CREATE DATABASE `world` /*!40100 DEFAULT CHARACTER SET latin1 */ |
    +----------+------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> show create table world.city \G ;
    *************************** 1. row ***************************
           Table: city
    Create Table: CREATE TABLE `city` (
      `ID` int(11) NOT NULL AUTO_INCREMENT,
      `Name` char(35) NOT NULL DEFAULT '',
      `CountryCode` char(3) NOT NULL DEFAULT '',
      `District` char(20) NOT NULL DEFAULT '',
      `Population` int(11) NOT NULL DEFAULT '0',
      PRIMARY KEY (`ID`)
    ) ENGINE=MyISAM AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1
    1 row in set (0.00 sec)
    mysql>
    
    
  3. Displays information about the columns in a given table :
    mysql> show columns from world.city \G ;
    *************************** 1. row ***************************
      Field: ID
       Type: int(11)
       Null: NO
        Key: PRI
    Default: NULL
      Extra: auto_increment
    *************************** 2. row ***************************
      Field: Name
       Type: char(35)
       Null: NO
        Key:
    Default:
      Extra:
    *************************** 3. row ***************************
      Field: CountryCode
       Type: char(3)
       Null: NO
        Key:
    Default:
      Extra:
    *************************** 4. row ***************************
      Field: District
       Type: char(20)
       Null: NO
        Key:
    Default:
      Extra:
    *************************** 5. row ***************************
      Field: Population
       Type: int(11)
       Null: NO
        Key:
    Default: 0
      Extra:
    5 rows in set (0.00 sec)
    
    ERROR:
    No query specified
    
    mysql>
    
    
  4. Server and table status information :
    mysql> show status ;
    /////////   TRUNCATED //////////
    | Threads_running                   | 1         |
    | Uptime                            | 47673     |
    | Uptime_since_flush_status         | 47673     |
    +-----------------------------------+-----------+
    291 rows in set (0.01 sec)
    
    mysql> show table status from world like 'city' \G ;
    *************************** 1. row ***************************
               Name: city
             Engine: MyISAM
            Version: 10
         Row_format: Fixed
               Rows: 4079
     Avg_row_length: 67
        Data_length: 273293
    Max_data_length: 18858823439613951
       Index_length: 43008
          Data_free: 0
     Auto_increment: 4080
        Create_time: 2012-11-04 09:34:09
        Update_time: 2012-11-06 12:34:21
         Check_time: 2012-11-06 12:34:21
          Collation: latin1_swedish_ci
           Checksum: NULL
     Create_options:
            Comment:
    1 row in set (0.00 sec)
    mysql>
    
  5. Show a list of all possible privileges :
    mysql> show privileges \G ;
    *************************** 1. row ***************************
    Privilege: Alter
      Context: Tables
      Comment: To alter the table
    *************************** 2. row ***************************
    Privilege: Alter routine
      Context: Functions,Procedures
      Comment: To alter or drop stored functions/procedures
    *************************** 3. row ***************************
    Privilege: Create
      Context: Databases,Tables,Indexes
      Comment: To create new databases and tables
    *************************** 4. row ***************************
    Privilege: Create routine
      Context: Databases
      Comment: To use CREATE FUNCTION/PROCEDURE
    *************************** 5. row ***************************
    Privilege: Create temporary tables
      Context: Databases
      Comment: To use CREATE TEMPORARY TABLE
    *************************** 6. row ***************************
    Privilege: Create view
      Context: Tables
      Comment: To create new views
    *************************** 7. row ***************************
    Privilege: Create user
      Context: Server Admin
      Comment: To create new users
    *************************** 8. row ***************************
    Privilege: Delete
      Context: Tables
      Comment: To delete existing rows
    *************************** 9. row ***************************
    Privilege: Drop
      Context: Databases,Tables
      Comment: To drop databases, tables, and views
    *************************** 10. row ***************************
    Privilege: Event
      Context: Server Admin
      Comment: To create, alter, drop and execute events
    *************************** 11. row ***************************
    Privilege: Execute
      Context: Functions,Procedures
      Comment: To execute stored routines
    *************************** 12. row ***************************
    Privilege: File
      Context: File access on server
      Comment: To read and write files on the server
    *************************** 13. row ***************************
    Privilege: Grant option
      Context: Databases,Tables,Functions,Procedures
      Comment: To give to other users those privileges you possess
    *************************** 14. row ***************************
    Privilege: Index
      Context: Tables
      Comment: To create or drop indexes
    *************************** 15. row ***************************
    Privilege: Insert
      Context: Tables
      Comment: To insert data into tables
    *************************** 16. row ***************************
    Privilege: Lock tables
      Context: Databases
      Comment: To use LOCK TABLES (together with SELECT privilege)
    *************************** 17. row ***************************
    Privilege: Process
      Context: Server Admin
      Comment: To view the plain text of currently executing queries
    *************************** 18. row ***************************
    Privilege: References
      Context: Databases,Tables
      Comment: To have references on tables
    *************************** 19. row ***************************
    Privilege: Reload
      Context: Server Admin
      Comment: To reload or refresh tables, logs and privileges
    *************************** 20. row ***************************
    Privilege: Replication client
      Context: Server Admin
      Comment: To ask where the slave or master servers are
    *************************** 21. row ***************************
    Privilege: Replication slave
      Context: Server Admin
      Comment: To read binary log events from the master
    *************************** 22. row ***************************
    Privilege: Select
      Context: Tables
      Comment: To retrieve rows from table
    *************************** 23. row ***************************
    Privilege: Show databases
      Context: Server Admin
      Comment: To see all databases with SHOW DATABASES
    *************************** 24. row ***************************
    Privilege: Show view
      Context: Tables
      Comment: To see views with SHOW CREATE VIEW
    *************************** 25. row ***************************
    Privilege: Shutdown
      Context: Server Admin
      Comment: To shut down the server
    *************************** 26. row ***************************
    Privilege: Super
      Context: Server Admin
      Comment: To use KILL thread, SET GLOBAL, CHANGE MASTER, etc.
    *************************** 27. row ***************************
    Privilege: Trigger
      Context: Tables
      Comment: To use triggers
    *************************** 28. row ***************************
    Privilege: Update
      Context: Tables
      Comment: To update existing rows
    *************************** 29. row ***************************
    Privilege: Usage
      Context: Server Admin
      Comment: No privileges - allow connect only
    29 rows in set (0.00 sec)
    mysql>
    
    
  6. Show privileges for the specified user :
    mysql> select user,host from mysql.user \G ;
    //////////// TRUNCATED //////////////////
    user: root
    host: ip-10-204-87-121
    *************************** 5. row ***************************
    user:
    host: localhost
    *************************** 6. row ***************************
    user: dimitrios
    host: localhost
    *************************** 7. row ***************************
    user: ec2user
    host: localhost
    *************************** 8. row ***************************
    user: root
    host: localhost
    8 rows in set (0.00 sec)
    mysql> show grants for dimitrios@localhost ;
    +---------------------------------------------------------------------------------------------------------------------------+
    | Grants for dimitrios@localhost                                                                                            |
    +---------------------------------------------------------------------------------------------------------------------------+
    | GRANT ALL PRIVILEGES ON *.* TO 'dimitrios'@'localhost' IDENTIFIED BY PASSWORD '*D90DJKKKKLLL8876242F0032EBEC93A7D5D893BB9' |
    +---------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql>
    
    
  7. Display which threads are running : 
    mysql> show processlist \G ;
    *************************** 1. row ***************************
         Id: 29
       User: root
       Host: localhost
         db: NULL
    Command: Query
       Time: 0
      State: NULL
       Info: show processlist
    1 row in set (0.00 sec)
    mysql>
    
    
  8. Display information for the latest errors : 
    mysql> show errors \G ;
    *************************** 1. row ***************************
      Level: Error
       Code: 1064
    Message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'testaaa' at line 1
    1 row in set (0.00 sec)
    mysql>
    
    
  9. Display table index information (primary key , unique , foreign key … )

    mysql> show indexes from world.city \G ;
    *************************** 1. row ***************************
           Table: city
      Non_unique: 0
        Key_name: PRIMARY
    Seq_in_index: 1
     Column_name: ID
       Collation: A
     Cardinality: 4079
        Sub_part: NULL
          Packed: NULL
            Null:
      Index_type: BTREE
         Comment:
    1 row in set (0.00 sec)
    
    mysql>
    
    
  10. Display operational information about a storage engine :
    mysql> show engine InnoDB status \G ;
    *************************** 1. row ***************************
      Type: InnoDB
      Name:
    Status:
    =====================================
    121227 23:13:28 INNODB MONITOR OUTPUT
    =====================================
    Per second averages calculated from the last 35 seconds
    ----------
    SEMAPHORES
    ----------
    OS WAIT ARRAY INFO: reservation count 3, signal count 3
    Mutex spin waits 0, rounds 0, OS waits 0
    RW-shared spins 6, OS waits 3; RW-excl spins 0, OS waits 0
    ------------
    TRANSACTIONS
    ------------
    Trx id counter 0 5634
    Purge done for trx's n:o < 0 5168 undo n:o < 0 0
    History list length 5
    LIST OF TRANSACTIONS FOR EACH SESSION:
    ---TRANSACTION 0 0, not started, process no 1183, OS thread id 139889459406592
    MySQL thread id 29, query id 300 localhost root
    show engine InnoDB status
    --------
    FILE I/O
    --------
    I/O thread 0 state: waiting for i/o request (insert buffer thread)
    I/O thread 1 state: waiting for i/o request (log thread)
    I/O thread 2 state: waiting for i/o request (read thread)
    I/O thread 3 state: waiting for i/o request (write thread)
    Pending normal aio reads: 0, aio writes: 0,
     ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
    Pending flushes (fsync) log: 0; buffer pool: 0
    26 OS file reads, 7 OS file writes, 7 OS fsyncs
    0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
    -------------------------------------
    INSERT BUFFER AND ADAPTIVE HASH INDEX
    -------------------------------------
    Ibuf: size 1, free list len 0, seg size 2,
    0 inserts, 0 merged recs, 0 merges
    Hash table size 17393, node heap has 0 buffer(s)
    0.00 hash searches/s, 0.00 non-hash searches/s
    ---
    LOG
    ---
    Log sequence number 0 199771
    Log flushed up to   0 199771
    Last checkpoint at  0 199771
    0 pending log writes, 0 pending chkp writes
    10 log i/o's done, 0.00 log i/o's/second
    ----------------------
    BUFFER POOL AND MEMORY
    ----------------------
    Total memory allocated 20375506; in additional pool allocated 653824
    Dictionary memory allocated 33320
    Buffer pool size   512
    Free buffers       492
    Database pages     20
    Modified db pages  0
    Pending reads 0
    Pending writes: LRU 0, flush list 0, single page 0
    Pages read 20, created 0, written 1
    0.00 reads/s, 0.00 creates/s, 0.00 writes/s
    No buffer pool page gets since the last printout
    --------------
    ROW OPERATIONS
    --------------
    0 queries inside InnoDB, 0 queries in queue
    1 read views open inside InnoDB
    Main thread process no. 1183, id 139889413351168, state: waiting for server activity
    Number of rows inserted 0, updated 0, deleted 0, read 0
    0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
    ----------------------------
    END OF INNODB MONITOR OUTPUT
    ============================
    
    1 row in set (0.00 sec)
    mysql>
    

Final thoughts :

These are only 10 practical examples , use the official documentation and discover many more options .

Advertisements

Comments»

No comments yet — be the first.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s