Tuesday, May 22, 2012

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               |
+--------------------+

1 comment:

sathish said...

Hi Anil...

I am following with your blog ... its very much informative .. keep posting and share with us

Thanks
Sathish Gnanam
MYSQL DBA