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

Welcome to the MySQL monitor.  Commands end with ; or /g.

Your MySQL connection id is 8

Server 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 ping

Enter 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 version

mysqladmin  Ver 8.42 Distrib 5.1.25-rc, for redhat-linux-gnu on i686

Copyright (C) 2000-2006 MySQL AB

This software comes with ABSOLUTELY NO WARRANTY. This is free software,

and you are welcome to modify and redistribute it under the GPL license



Server version          5.1.25-rc-community

Protocol version        10

Connection              Localhost via UNIX socket

UNIX socket             /var/lib/mysql/mysql.sock

Uptime:                 107 days 6 hours 11 min 44 sec



Threads: 1  Questions: 231976  Slow queries: 0  Opens: 17067

Flush tables: 1  Open tables: 64  Queries per second avg: 0.25
    

4. What is the current status of MySQL server?

      # 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
    

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

Welcome to the MySQL monitor.  Commands end with ; or /g.

Your MySQL connection id is 705

Server 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 mysqlshow command that we discussed in our previous articles.

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

      # mysqladmin -u root -ptmppassword drop testdb

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 'testdb' database [y/N] y

Database “testdb” dropped



# mysql -u root -ptmppassword

Welcome to the MySQL monitor.  Commands end with ; or /g.

Your MySQL connection id is 707

Server 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 -ptmppassword

ERROR 2002 (HY000): Can't connect to local MySQL server

through 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-slave

Slave stopped



# mysqladmin  -u root -ptmppassword start-slave

mysqladmin: 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: 3135

Threads: 1  Questions: 80  Slow queries: 0  Opens: 15  Flush tables: 3

Open tables: 0  Queries per second avg: 0.25



mysqladmin  Ver 8.42 Distrib 5.1.25-rc, for redhat-linux-gnu on i686

Copyright (C) 2000-2006 MySQL AB

This software comes with ABSOLUTELY NO WARRANTY. This is free software,

and you are welcome to modify and redistribute it under the GPL license



Server version          5.1.25-rc-community

Protocol version        10

Connection              Localhost via UNIX socket

UNIX socket             /var/lib/mysql/mysql.sock

Uptime:                 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