Manage MySQL / MariaDB Server with mysqladmin

mysqladmin is a command line tool that allows you to do administrative tasks in MySQL without actually login to the MySQL console.

Two most important command options are -u and -p which uses to specify MySQL username and password, respectively. The username you give to the mysqladmin command must have privileges to perform whichever task you are asking to perform.

Check if MySQL Server is Running

Check if MySQL Server is Running using the mysqladmin ping command.

# mysqladmin -u root -p '123456' ping
mysqld is alive

Create a MySQL Database Using the mysqladmin

# mysqladmin -u root -p 'Password' create 'demo_db'

Delete/Drop a MySQL Database

Drop an existing database on a MySQL server:

# mysqladmin -u root -p Password drop demo_db

By default drop command will ask for the confirmation before deleting the database but if you use -f option it will not ask for confirmation.

Reload the Grant Tables

The reload command reloads the grant table on the MySQL server.

# mysqladmin -u root -p Password reload

Check MySQL Server Uptime

The status command shows the MySQL Server Status.

# mysqladmin -u root -p Password status

Uptime: 125085 Threads: 2 Questions: 35426442 Slow queries: 0 Opens: 119769 Flush tables: 1 Open tables: 2000 Queries per second avg: 283.218

The mysqladmin status command displays useful summary information about the MySQL server.

What’s what:

• Uptime: How long the MySQL server has been running, it shows in seconds.
• Threads: The number of active clients connection
• Questions: The number of queries has been executed since the server was started.
• Slow queries: The number of queries that have taken more than long_query_time seconds.
• Opens: The number of tables the server has opened.
• Flush tables: How many times flush-*, refresh, and reload commands the server has executed.
• Open tables: The number of tables that currently are open.

Check MySQL Server Extended Status

It returns all status variables and their values.

# mysqladmin -u root -p Password extended-status

 +-----------------------------------------------+------------------------------+
 | Variable_name                                 | Value                        |
 +-----------------------------------------------+------------------------------+
 | Aborted_clients                               | 0                            |
 | Aborted_connects                              | 1312                         |
 | Binlog_cache_disk_use                         | 0                            |
 | Binlog_cache_use                              | 0                            |
 | Binlog_stmt_cache_disk_use                    | 0                            |
 | Binlog_stmt_cache_use                         | 0                            |
 | Bytes_received                                | 2956993148                   |
 | Bytes_sent                                    | 4579557288289                |
 | Com_admin_commands                            | 1438                         |
 | Com_assign_to_keycache                        | 0                            |
 | Com_alter_db                                  | 0                            |
 | Com_alter_db_upgrade                          | 0                            |
 | Com_alter_event                               | 0                            |
 .
 .
 | Table_locks_immediate                         | 34407003                     |
 | Table_locks_waited                            | 1825                         |
 | Table_open_cache_hits                         | 35640487                     |
 | Table_open_cache_misses                       | 119841                       |
 | Table_open_cache_overflows                    | 117829                       |
 | Tc_log_max_pages_used                         | 0                            |
 | Tc_log_page_size                              | 0                            |
 | Tc_log_page_waits                             | 0                            |
 | Threads_cached                                | 7                            |
 | Threads_connected                             | 2                            |
 | Threads_created                               | 15                           |
 | Threads_running                               | 1                            |
 | Uptime                                        | 125427                       |
 | Uptime_since_flush_status                     | 125427                       |
 +-----------------------------------------------+------------------------------+

Check MySQL Process List

Display a list of running MySQL queries/processes, equivalent to SHOW PROCESSLIST statement on MySQL Console.

# mysqladmin -u root -p Password processlist

 +--------+---------------+-----------+---------------+---------+------+----------+------------------+
 | Id     | User          | Host      | db            | Command | Time | State    | Info             |
 +--------+---------------+-----------+---------------+---------+------+----------+------------------+
 | 195003 | neo | localhost | thebalaj_mail | Sleep   | 0    |          |                  |
 | 195004 | root          | localhost |               | Query   | 0    | starting | show processlist |
 +--------+---------------+-----------+---------------+---------+------+----------+------------------+

Kill MySQL Client Processes

Using the command above you can identify the processes, and once you got the process ID you can kill it with the kill command.

# mysqladmin -u root -p Password kill 195003

+--------+---------------+-----------+---------------+---------+------+----------+------------------+
| Id     | User          | Host      | db            | Command | Time | State    | Info             |
+--------+---------------+-----------+---------------+---------+------+----------+------------------+
| 195004 | root          | localhost |               | Query   | 0    | starting | show processlist |
+--------+---------------+-----------+---------------+---------+------+----------+------------------+

Multiple Process ID values can be given in comma separated list (kill id1,id2).

Run Multiple MySQLAdmin Commands at Once

# mysqladmin -u root -p Password ping processlist status

mysqld is alive
+--------+-----------------+-----------+-----------------+---------+------+----------+------------------+
| Id     | User            | Host      | db              | Command | Time | State    | Info             |
+--------+-----------------+-----------+-----------------+---------+------+----------+------------------+
| 195106 | neo | localhost | neo | Sleep   | 0    |          |                  |
| 195107 | root            | localhost |                 | Query   | 0    | starting | show processlist |
+--------+-----------------+-----------+-----------------+---------+------+----------+------------------+
Uptime: 125807 Threads: 2 Questions: 35568438 Slow queries: 0 Opens: 120193 Flush tables: 1 Open tables: 2000 Queries per second avg: 282.722

Shutdown MySQL Server Safely

# mysqladmin -u root -p Password shutdown

I do not suggest to shutdown MySQL server using mysqladmin, instead you should stop the mysql/mariadb service with systemctl or service command.

Connect Remote MySQL Server

You can connect to a remote MySQL server using the MySQLAdmin command.

# mysqladmin -h 192.168.1.10 -u root -p Password

MySQLAdmin Flush Commands

Below is a list of flush commands available on the MySQLAdmin client, which allows you to get things done.

# mysqladmin -u root -p Password flush-hosts

• flush-hosts: Flush all cached hosts.
• flush-logs: Flush all logs.
• flush-privileges: Reload grant tables (same as reload).
• flush-status: Clear status variables.
• flush-tables: Flush all tables.
• flush-threads: Flush the thread cache.

Change MySQL User Password

Set a new Password for the MySQL user.

# mysqladmin -u root -p '123456' password NEW_PASSWORD