MySQL basic utility

Whenever you install MySQL inside the bin folder you can see some file other then mysqld and mysqld. These are utilities which used to different purpose. Here is the list some of the utilities. 

1.    mysqladmin: This utility can do all admin related. The exception is MySQL Service Start.  It can stop MySQL service but will not be able to start it. Below is the list for options available for it.

  create databasename   Create a new database

  debug                 Instruct server to write debug information to log

  drop databasename     Delete a database and all its tables

  extended-status       Gives an extended status message from the server

  flush-hosts           Flush all cached hosts

  flush-logs            Flush all logs

  flush-status          Clear status variables

  flush-tables          Flush all tables

  flush-threads         Flush the thread cache

  flush-privileges      Reload grant tables (same as reload)

  kill id,id,...        Kill mysql threads

  password [new-password] Change old password to new-password in current format

  old-password [new-password] Change old password to new-password in old format

  ping                  Check if mysqld is alive

  processlist           Show list of active threads in server

  reload                Reload grant tables

  refresh               Flush all tables and close and open logfiles

  shutdown              Take server down

  status                Gives a short status message from the server

  start-slave           Start slave

  stop-slave            Stop slave

  variables             Prints variables available

  version               Get version info from server

         

Below is example for same.

[root@cluster1 ~]# mysqladmin ping

mysqld is alive

[root@cluster1 ~]# mysqladmin processlist

+----+------+-----------+----+---------+------+-------+------------------+-----------+---------------+-----------+

| Id | User | Host      | db | Command | Time | State | Info             | Rows_sent | Rows_examined | Rows_read |

+----+------+-----------+----+---------+------+-------+------------------+-----------+---------------+-----------+

| 11 | root | localhost |    | Query   | 0    |       | show processlist | 0         | 0             | 1         |

+----+------+-----------+----+---------+------+-------+------------------+-----------+---------------+-----------+

2.    mysqlcheck: This command will check the status of all MySQL Tables and show you the output. In case of any error you can check/repair that table further. Below is the example for same.

[root@cluster1 ~]# mysqlcheck -B sakila -a

sakila.actor                                       OK

sakila.address                                     OK

sakila.category                                    OK

sakila.city                                        OK

sakila.country                                     OK

sakila.customer                                    OK

sakila.film                                        OK

sakila.film_actor                                  OK

sakila.film_category                               OK

sakila.film_text                                   Table is already up to date

sakila.inventory                                   OK

sakila.language                                    OK

sakila.payment                                     OK

sakila.rental                                      OK

sakila.staff                                       OK

sakila.store                                       OK

3.    mysqlshow: This command will show the list of all created database on MySQL instance. Below is the example.

[root@cluster1 ~]# mysqlshow

+--------------------+

|     Databases      |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| sakil              |

| sakila             |

| test               |

+--------------------+