Server Tip: Most commonly used mysqladmin command for managing MySQL Server

For all the examples of mysqladmin command-line series below, test123 is used as the MySQL root user password. Please use your own password to your MySQL root password.

How to check MySQL Server version?

root@sajan-desktop:/home/sajan# mysqladmin -V
mysqladmin  Ver 8.42 Distrib 5.1.61, for debian-linux-gnu on x86_64

or

root@sajan-desktop:/home/sajan# mysqladmin -u root -ptest version
mysqladmin  Ver 8.42 Distrib 5.1.61, for debian-linux-gnu on x86_64
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Server version 5.1.61-0ubuntu0.11.10.1
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock
Uptime: 1 hour 39 min 30 sec

Threads: 1  Questions: 696  Slow queries: 0  Opens: 3015  Flush tables: 1
Open tables: 64  Queries per second avg: 0.116

How to check whether MySQL Server is up and running?

root@sajan-desktop:/home/sajan# mysqladmin -u root -p ping
Enter password: 
mysqld is alive

or

root@sajan-desktop:/home/sajan# service mysql status

mysql start/running, process 1047

How to change the MySQL root user password?

root@sajan-desktop:/home/sajan# mysqladmin -u root -ptest123 password 'test'

root@sajan-desktop:/home/sajan# mysql -u root -pnewpassword
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.61-rc-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

What is the current status of MySQL server?

root@sajan-desktop:/home/sajan# mysqladmin -u root -ptmppassword status
Uptime: 9267148
Threads: 1  Questions: 231977  Slow queries: 0  Opens: 17067
Flush tables: 1  Open tables: 64  Queries per second avg: 0.25


Uptime: Uptime of the mysql server in seconds


Threads: Total number of clients connected to the server.


Questions: Total number of queries the server has executed since the startup.


Slow queries: Total number of queries whose execution time waas more than long_query_time variable’s value.


Opens: Total number of tables opened by the server.


Flush tables: How many times the tables were flushed.


Open tables: Total number of open tables in the database.


The status command displays the following information:


How to view all the MySQL Server status variable and it’s current value?



root@sajan-desktop:/home/sajan# mysqladmin -u root -ptest extended-status
+-----------------------------------+----------+
| Variable_name                     | Value    |
+-----------------------------------+----------+
| Aborted_clients                   | 0        |
| Aborted_connects                  | 52       |
| Binlog_cache_disk_use             | 0        |
| Binlog_cache_use                  | 0        |
| Bytes_received                    | 44184    |
| Bytes_sent                        | 63273    |
| Com_admin_commands                | 3        |
| Com_assign_to_keycache            | 0        |
| Com_alter_db                      | 0        |
| Com_alter_db_upgrade              | 0        |
| Com_alter_event                   | 0        |
| Com_alter_function                | 0        |
| Com_alter_procedure               | 0        |



How to display all MySQL server system variables and the values?


root@sajan-desktop:/home/sajan# mysqladmin -u root -ptest variables
+-----------------------------------------+-------------------------------------------------------------------------------------------+
| Variable_name                           | Value                                                                                     |
+-----------------------------------------+-------------------------------------------------------------------------------------------+
| auto_increment_increment                | 1                                                                                         |
| auto_increment_offset                   | 1                                                                                         |
| autocommit                              | ON                                                                                        |
| automatic_sp_privileges                 | ON                                                                                        |
| back_log                                | 50                                                                                        |
| basedir                                 | /usr/                                                                                     |
| big_tables                              | OFF                                                                                       |
| binlog_cache_size                       | 32768                                                                                     |
| binlog_direct_non_transactional_updates | OFF                                                                                       |


How to display all the running process/queries in the mysql database?


root@sajan-desktop:/home/sajan# mysqladmin -u root -ptest processlist
+-----+------+-----------+----+---------+------+-------+------------------+
| Id  | User | Host      | db | Command | Time | State | Info             |
+-----+------+-----------+----+---------+------+-------+------------------+
| 291 | root | localhost |    | Query   | 0    |       | show processlist |
+-----+------+-----------+----+---------+------+-------+------------------+



you can debug it with:



root@sajan-desktop:/home/sajan# mysqladmin -u root -ptest processlist -i 1
+-----+------+-----------+----+---------+------+-------+------------------+
| Id  | User | Host      | db | Command | Time | State | Info             |
+-----+------+-----------+----+---------+------+-------+------------------+
| 292 | root | localhost |    | Query   | 0    |       | show processlist |
+-----+------+-----------+----+---------+------+-------+------------------+


How to create a MySQL Database?


root@sajan-desktop:/home/sajan# mysqladmin -u root -ptest create myblog


How to Delete/Drop an existing MySQL database?


root@sajan-desktop:/home/sajan# mysqladmin -u root -ptest drop myblog
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

Do you really want to drop the 'myblog' database [y/N] y
Database "myblog" dropped


How to reload/refresh the privilege or the grants tables?


root@sajan-desktop:/home/sajan# mysqladmin -u root -ptest reload



Refresh command will flush all the tables and close/open log files.
root@sajan-desktop:/home/sajan# mysqladmin -u root -ptest refresh





What is the safe method to shutdown the MySQL server?


root@sajan-desktop:/home/sajan# mysqladmin -u root -ptmppassword shutdown


What is the safe method to shutdown the MySQL server?

root@sajan-desktop:/home/sajan# mysqladmin -u root -ptmppassword flush-hosts

root@sajan-desktop:/home/sajan# mysqladmin -u root -ptmppassword flush-logs

root@sajan-desktop:/home/sajan# mysqladmin -u root -ptmppassword flush-privileges

root@sajan-desktop:/home/sajan# mysqladmin -u root -ptmppassword flush-status

root@sajan-desktop:/home/sajan# mysqladmin -u root -ptmppassword flush-tables

root@sajan-desktop:/home/sajan# mysqladmin -u root -ptmppassword flush-threads


flush-hosts: Flush all information in the host cache.
flush-privileges: Reload the grant tables (same as reload).
flush-status: Clear status variables.
flush-threads: Flush the thread cache.



 How to kill a hanging MySQL Client Process?



root@sajan-desktop:/home/sajan# mysqladmin -u root -ptmppassword processlist
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 20 | root | localhost |    | Sleep   | 64   |       |                  |
| 24 | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+

Now, use the kill command and pass the process_id as shown below. To kill multiple process you can pass comma separated process id’s.


root@sajan-desktop:/home/sajan# mysqladmin -u root -ptmppassword kill 20

root@sajan-desktop:/home/sajan# mysqladmin -u root -ptmppassword processlist
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 26 | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+



How to combine multiple mysqladmin commands together?

root@sajan-desktop:/home/sajan# mysqladmin -u root -ptest refresh reload



Thats it! Cheers!