How to Administrate MySQL Server Using the MySQLAdmin Command

MySQLAdmin is a command-line utility for MySQL Server that is installed when installing the MySQL package.

The MySQLAdmin client allows you to perform some basic administrative functions on the MySQL server.

It is used to create a database, drop a database, set a root password, change the root password, check MySQL status, verify MySQL functionality, monitor mysql processes, and verify the configuration of the server.

MySQL is an open-source relational database management system (RDBMS) that is widely used in many open source applications.

It is one of the most popular open source databases in the world.

1) How to Set MySQL Root Password Using the MySQLAdmin Command

When you install the MySQL/MariaDB server on RPM based system, it doesn’t prompt you to set the root password.

At the same time, it prompts you to set the password in a DEB based system. If you have already set a password using the “mysql_secure_installation” command, you can ignore this command, otherwise use this command to set a password.

# mysqladmin -u root password [New_Password]

2) How to Change MySQL Root Password Using the MySQLAdmin Command

If you want to change the root password for a MySQL user, use the below MySQLAdmin command.

# mysqladmin -u root -pOld_Password password 'New_Password'

3) How to Check MySQL Server is Up and Running Using the MySQLAdmin Command

Use the below MySQLAdmin command to see if MySQL is running or not.

# mysqladmin -u root -pPassword ping

mysqld is alive

4) How to Check MySQL Server Uptime Using the MySQLAdmin Command

Use the below MySQLAdmin command to see how long the MySQL server has been running.

# mysqladmin -u root -pPassword version

mysqladmin Ver 8.42 Distrib 5.7.27, for Linux on x86_64
Copyright (c) 2000, 2019, 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.7.27
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 1 day 10 hours 44 min 13 sec

Alternatively, you can use the following MySQLAdmin command to see how long the MySQL server has been running.

# mysqladmin -u root -pPassword status

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

5) How to Check Installed MySQL Server Version Using the MySQLAdmin Command

Use the below MySQLAdmin command to view the installed version of MySQL server.

# mysqladmin -u root -pPassword version

mysqladmin Ver 8.42 Distrib 5.7.27, for Linux on x86_64
Copyright (c) 2000, 2019, 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.7.27
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 1 day 10 hours 44 min 13 sec

6) How to Check MySQL Server Status Using the MySQLAdmin Command

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

# mysqladmin -u root -pPassword status

Uptime: 125085 Threads: 2 Questions: 35426442 Slow queries: 0 Opens: 119769 Flush tables: 1 Open tables: 2000 Queries per second avg: 283.218
  • 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.

7) How to Check MySQL Server Extended Status Using the MySQLAdmin Command

It return all status variables and their values.

# mysqladmin -u root -pPassword 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 |
+-----------------------------------------------+------------------------------+

8) How to Check MySQL Server Variables Using the MySQLAdmin Command

It display the server system variables and their values.

# mysqladmin -u root -pPassword variables

 +-----------------------------------------+------------------------+
 | Variable_name                           | Value                  |
 +-----------------------------------------+------------------------+
 | auto_increment_increment                | 1                      |
 | auto_increment_offset                   | 1                      |
 | autocommit                              | ON                     |
 | automatic_sp_privileges                 | ON                     |
 | back_log                                | 50                     |
 | basedir                                 | /                      |
 | big_tables                              | OFF                    |
 | binlog_cache_size                       | 32768                  |
 | binlog_direct_non_transactional_updates | OFF                    |
 | binlog_format                           | STATEMENT              |
 | bulk_insert_buffer_size                 | 8388608                |
 | character_set_client                    | latin1                 |
 | character_set_connection                | latin1                 |
 | character_set_database                  | latin1                 |
 | character_set_filesystem                | binary                 |
 +-----------------------------------------+------------------------+

9) How to Check MySQL Process List Using the MySQLAdmin Command

It shows a list of running MySQL processes/queries.

# mysqladmin -u root -pPassword processlist

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

Alternatively, you can use the below command to see a list of running MySQL processes/queries.

# mysqladmin proc stat

+--------+-----------------+-----------+-----------------+---------+------+----------+------------------+
| Id     | User            | Host      | db              | Command | Time | State    | Info             |
+--------+-----------------+-----------+-----------------+---------+------+----------+------------------+
| 200095 | prmscaff_harini | localhost | prmscaff_harini | Sleep   | 0    |          |                  |
| 200096 | daygeekc_magi   | localhost | daygeekc_magi   | Sleep   | 0    |          |                  |
| 200097 | root            | localhost |                 | Query   | 0    | starting | show processlist |
+--------+-----------------+-----------+-----------------+---------+------+----------+------------------+

10) How to Kill MySQL Client Process Using the MySQLAdmin Command

Identify the process ID using the command above. Once you get the process ID, run the below command to kill it.

# mysqladmin -u root -pPassword kill 195003

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

11) How to Run Multiple MySQLAdmin Commands at Once

You can combine multiple commands together to get all the results in one go.

# mysqladmin -u root -pPassword ping processlist status

mysqld is alive
+--------+-----------------+-----------+-----------------+---------+------+----------+------------------+
| Id     | User            | Host      | db              | Command | Time | State    | Info             |
+--------+-----------------+-----------+-----------------+---------+------+----------+------------------+
| 195106 | prmscaff_harini | localhost | prmscaff_harini | 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

12) How to Create a MySQL Database Using the MySQLAdmin Command

Use the following MySQLAdmin command to create a new database on the MySQL server.

# mysqladmin -u root -pPassword create demodb

13) How to Delete/Drop MySQL Database Using the MySQLAdmin Command

Use the following MySQLAdmin command to drop an existing database on the MySQL server.

# mysqladmin -u root -pPassword drop demodb

14) How to Reload the Grant Tables Using the MySQLAdmin Command

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

# mysqladmin -u root -pPassword reload;

15) How to Start and Stop Replication on a Slave Server Using the MySQLAdmin Command

If you have already setup a MySQL replication, use the blow MySQLAdmin command to stop and start replication on a slave server.

# mysqladmin -u root -pPassword stop-slave
Slave stopped

# mysqladmin -u root -pPassword start-slave
Slave started

16) How to Shutdown MySQL Server Safely Using the MySQLAdmin Command

You can safely shutdown the MySQL server using the MySQLAdmin command.

# mysqladmin -u root -pPassword shutdown

17) How to Connect Remote MySQL Server Using the MySQLAdmin Command

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

# mysqladmin -h 192.168.1.10 -u root -pPassword

18) How to Run Command on Remote MySQL Serve Using the MySQLAdmin Command

Also, you can run command on a remote MySQL server using the MySQLAdmin command.

# mysqladmin -h 192.168.1.10 -u root -pPassword ping

19) List of Other 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 -pPassword flush-hosts
# mysqladmin -u root -pPassword flush-privileges
# mysqladmin -u root -pPassword flush-tables
# mysqladmin -u root -pPassword flush-threads
# mysqladmin -u root -pPassword flush-logs
# mysqladmin -u root -pPassword flush-status
  • 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.

About Magesh Maruthamuthu

Love to play with all Linux distribution

View all posts by Magesh Maruthamuthu

Leave a Reply

Your email address will not be published. Required fields are marked *