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.
