Some useful MYSQL admin commands

I’d like to show you some useful commands to do some MYSQL administration tasks:

• show processlist; – displays a list of active processes in the database:

  mysql> show processlist;
  +-----+------+-----------+------------+---------+------+-------+------------------+
  | Id  | User | Host      | db         | Command | Time | State | Info             |
  +-----+------+-----------+------------+---------+------+-------+------------------+
  | 564 | root | localhost | videoguide | Query   |    0 | NULL  | show processlist |
  +-----+------+-----------+------------+---------+------+-------+------------------+
  
  

  You can use ID of process to kill it.

• kill <process ID>; – kill the active database process with specified ID.
• show table status like ‘table name’ – displays an information about specified table. Use ‘\G’ instead of ‘;’ to show each column at the new line. I’s much more readable:

  mysql> show table status like 'Films' \G
             Name: Films
           Engine: InnoDB
          Version: 9
       Row_format: Dynamic
             Rows: 630
   Avg_row_length: 219
      Data_length: 138368
  Max_data_length: 4294967295
     Index_length: 9216
        Data_free: 0
   Auto_increment: 1679
      Create_time: 2005-11-28 14:41:41
      Update_time: 2005-11-28 14:41:41
       Check_time: NULL
        Collation: latin1_swedish_ci
         Checksum: NULL
   Create_options:
          Comment:
  

• SHOW MASTER STATUS \G – displays an information about master host in case of using a claster.
• SHOW SLAVE STATUS \G – displays an information about slave host in case of using a claster.