15 Practical Usages of Mysqladmin Command For Administering MySQL Server

In all the 15 mysqladmin command-line examples below, tmppassword is used as the MySQL root user password. Please change this to your MySQL root password.

1. How to change the MySQL root user password?

# mysqladmin -u root -ptmppassword password 'newpassword'# mysql -u root -pnewpasswordWelcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 8Server version: 5.1.25-rc-community MySQL Community Server (GPL)Type 'help;' or '\h' for help. Type '\c' to clear the buffer.mysql>

2. How to check whether MySQL Server is up and running?

# mysqladmin -u root -p pingEnter password:mysqld is alive

3. How do I find out what version of MySQL I am running?

Apart from giving the ‘Server version’, this command also displays the current status of the mysql server.

# mysqladmin -u root -ptmppassword versionmysqladmin  Ver 8.42 Distrib 5.1.25-rc, for redhat-linux-gnu on i686Copyright (C) 2000-2006 MySQL ABThis software comes with ABSOLUTELY NO WARRANTY. This is free software,and you are welcome to modify and redistribute it under the GPL licenseServer version          5.1.25-rc-communityProtocol version        10Connection              Localhost via UNIX socketUNIX socket             /var/lib/mysql/mysql.sockUptime:                 107 days 6 hours 11 min 44 secThreads: 1  Questions: 231976  Slow queries: 0  Opens: 17067Flush tables: 1  Open tables: 64  Queries per second avg: 0.25

4. What is the current status of MySQL server?

# mysqladmin -u root -ptmppassword statusUptime: 9267148Threads: 1  Questions: 231977  Slow queries: 0  Opens: 17067Flush tables: 1  Open tables: 64  Queries per second avg: 0.25

The status command displays the following information:

• 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.

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

# mysqladmin -u root -ptmppassword extended-status+-----------------------------------+-----------+| Variable_name                     | Value     |+-----------------------------------+-----------+| Aborted_clients                   | 579       || Aborted_connects                  | 8         || Binlog_cache_disk_use             | 0         || Binlog_cache_use                  | 0         || Bytes_received                    | 41387238  || Bytes_sent                        | 308401407 || Com_admin_commands                | 3524      || Com_assign_to_keycache            | 0         || Com_alter_db                      | 0         || Com_alter_db_upgrade              | 0         |

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

# mysqladmin  -u root -ptmppassword variables+---------------------------------+---------------------------------+| Variable_name                   | Value                           |+---------------------------------+---------------------------------+| auto_increment_increment        | 1                               || basedir                         | /                               || big_tables                      | OFF                             || binlog_format                   | MIXED                           || bulk_insert_buffer_size         | 8388608                         || character_set_client            | latin1                          || character_set_database          | latin1                          || character_set_filesystem        | binary                          |skip.....| time_format                     | %H:%i:%s                        || time_zone                       | SYSTEM                          || timed_mutexes                   | OFF                             || tmpdir                          | /tmp                            || tx_isolation                    | REPEATABLE-READ                 || unique_checks                   | ON                              || updatable_views_with_limit      | YES                             || version                         | 5.1.25-rc-community             || version_comment                 | MySQL Community Server (GPL)    || version_compile_machine         | i686                            || version_compile_os              | redhat-linux-gnu                || wait_timeout                    | 28800                           |+---------------------------------+---------------------------------+

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

# mysqladmin -u root -ptmppassword processlist+----+------+-----------+----+---------+------+-------+------------------+| Id | User | Host      | db | Command | Time | State | Info             |+----+------+-----------+----+---------+------+-------+------------------+| 20 | root | localhost |    | Sleep   | 36   |       |                  || 23 | root | localhost |    | Query   | 0    |       | show processlist |+----+------+-----------+----+---------+------+-------+------------------+

You can use this command effectively to debug any performance issue and identify the query that is causing problems, by running the command automatically every 1 second as shown below.

# mysqladmin -u root -ptmppassword -i 1 processlist+----+------+-----------+----+---------+------+-------+------------------+| Id | User | Host      | db | Command | Time | State | Info             |+----+------+-----------+----+---------+------+-------+------------------+| 20 | root | localhost |    | Sleep   | 36   |       |                  || 23 | root | localhost |    | Query   | 0    |       | show processlist |+----+------+-----------+----+---------+------+-------+------------------++----+------+-----------+----+---------+------+-------+------------------+| Id | User | Host      | db | Command | Time | State | Info             |+----+------+-----------+----+---------+------+-------+------------------+| 24 | root | localhost |    | Query   | 0    |       | show processlist |+----+------+-----------+----+---------+------+-------+------------------+

8. How to create a MySQL Database?

# mysqladmin -u root -ptmppassword create testdb# mysql -u root -ptmppasswordWelcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 705Server version: 5.1.25-rc-community MySQL Community Server (GPL)Type 'help;' or '\h' for help. Type '\c' to clear the buffer.mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || sugarcrm           || testdb             |+--------------------+4 rows in set (0.00 sec)

Note: To display all tables in a database, total number of columns, row, column types, indexes etc., use the  that we discussed in our previous articles.

9. How to Delete/Drop an existing MySQL database?

# mysqladmin -u root -ptmppassword drop testdbDropping 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 'testdb' database [y/N] yDatabase "testdb" dropped# mysql -u root -ptmppasswordWelcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 707Server version: 5.1.25-rc-community MySQL Community Server (GPL)Type 'help;' or '\h' for help. Type '\c' to clear the buffer.mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || sugarcrm           |+--------------------+3 rows in set (0.00 sec)

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

# mysqladmin -u root -ptmppassword reload;

Refresh command will flush all the tables and close/open log files.

# mysqladmin -u root -ptmppassword refresh

11. What is the safe method to shutdown the MySQL server?

# mysqladmin -u root -ptmppassword shutdown# mysql -u root -ptmppasswordERROR 2002 (HY000): Can't connect to local MySQL serverthrough socket '/var/lib/mysql/mysql.sock'

Note: You can also use “/etc/rc.d/init.d/mysqld stop” to shutdown the server. To start the server, execute “/etc/rc.d/init.d/mysql start”

12. List of all mysqladmin flush commands.

# mysqladmin -u root -ptmppassword flush-hosts# mysqladmin -u root -ptmppassword flush-logs# mysqladmin -u root -ptmppassword flush-privileges# mysqladmin -u root -ptmppassword flush-status# mysqladmin -u root -ptmppassword flush-tables# 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.

13. How to kill a hanging MySQL Client Process?

First identify the hanging MySQL client process using the processlist command.

# 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.

# mysqladmin -u root -ptmppassword kill 20# mysqladmin -u root -ptmppassword processlist+----+------+-----------+----+---------+------+-------+------------------+| Id | User | Host      | db | Command | Time | State | Info             |+----+------+-----------+----+---------+------+-------+------------------+| 26 | root | localhost |    | Query   | 0    |       | show processlist |+----+------+-----------+----+---------+------+-------+------------------+

14. How to start and stop MySQL replication on a slave server?

# mysqladmin  -u root -ptmppassword stop-slaveSlave stopped# mysqladmin  -u root -ptmppassword start-slavemysqladmin: Error starting slave: The server is not configured as slave;fix in config file or with CHANGE MASTER TO

15. How to combine multiple mysqladmin commands together?

In the example below, you can combine process-list, status and version command to get all the output together as shown below.

# mysqladmin  -u root -ptmppassword process status version+----+------+-----------+----+---------+------+-------+------------------+| Id | User | Host      | db | Command | Time | State | Info             |+----+------+-----------+----+---------+------+-------+------------------+| 43 | root | localhost |    | Query   | 0    |       | show processlist |+----+------+-----------+----+---------+------+-------+------------------+Uptime: 3135Threads: 1  Questions: 80  Slow queries: 0  Opens: 15  Flush tables: 3Open tables: 0  Queries per second avg: 0.25mysqladmin  Ver 8.42 Distrib 5.1.25-rc, for redhat-linux-gnu on i686Copyright (C) 2000-2006 MySQL ABThis software comes with ABSOLUTELY NO WARRANTY. This is free software,and you are welcome to modify and redistribute it under the GPL licenseServer version          5.1.25-rc-communityProtocol version        10Connection              Localhost via UNIX socketUNIX socket             /var/lib/mysql/mysql.sockUptime:                 52 min 15 sec

You can also use the short form as shown below:

# mysqladmin  -u root -ptmppassword pro stat ver

Use the option -h, to connect to a remote MySQL server and execute the mysqladmin commands as shown below.

# mysqladmin  -h 192.168.1.112 -u root -ptmppassword pro stat ver