24 Mysqladmin

24 Practical Usages of Mysqladmin Commands for Mysql MariaDB

mysqladmin is a client for performing administrative operations. You can use it to check the MySQL/MariaDB configuration and current status, to create and drop databases, and more.

You can use mysqladmin to reload/refresh MySQL Privileges, set/change MySQL/MariaDB Root password, check MySQL/MariaDB Server status, show MySQL/MariaDB version, show all MySQL/MariaDB server Variables and Values, check all the running Process of MySQL/MariaDB server, connect remote mysql server, create/drop a Database, reload the grant tables, perform a flush-privileges operation, check mysqladmin options and usage, and shutdown MySQL/MariaDB safely. All this can be done by executing a mysqladmin commands.

Invoke mysqladmin like this:

mysqladmin [options] command [command-arg] [command [command-arg]] ...

1. Show MySQL version

The following command shows MySQL version along with the current running status

mysqladmin -u root -p version

Or you can type in MySQL's root password into the command if you already set it up

mysqladmin -u root -pPASSWORD version
mysqladmin Ver 8.42 Distrib 5.5.38, for Linux on x86_64
Copyright (c) 2000, 2014, 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.5.38
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 360 days 15 hours 41 min 13 sec
Threads: 2 Questions: 82271 Slow queries: 0 Opens: 386 Flush tables: 1 Open tables: 44 Queries per second avg: 0.260

2. To check MySQL Server is running

mysqladmin -u root -pPASSWORD ping
mysqld is alive

3. To find out current Status of MySQL server

This mysqladmin command will shows the status of uptime with running threads and queries.

mysqladmin -u root -pPASSWORD status
Uptime: 616037 Threads: 2 Questions: 82468 Slow queries: 0 Opens: 386 Flush tables: 1 Open tables: 44 Queries per second avg: 0.260

4. To set MySQL Root password

To set MySQL password for root user, use the following command.

mysqladmin -u root password NEWPASSWORD

5. To change MySQL Root password

For example, you want to change your old password 123old to the new password new321.

mysqladmin -u root -p123old password 'new321'

6. To check status of all MySQL Server Variables and values

mysqladmin -u root -pPASSWORD extended-status
+------------------------------------------+-------------+
| Variable_name | Value |
+------------------------------------------+-------------+
| Aborted_clients | 0 |
| Aborted_connects | 9 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 11126499 |
| Bytes_sent | 133222270 |
| Com_admin_commands | 2 |
| Com_assign_to_keycache | 0 |
| Com_alter_db | 0 |
| Threads_created | 3114 |
| Threads_running | 1 |
| Uptime | 316409 |
| Uptime_since_flush_status | 316409 |
+------------------------------------------+-------------+

7. To check all MySQL server Variables and Values

mysqladmin -u root -pPASSWORD variables

8. To check all the running Process of MySQL server

mysqladmin -u root -pPASSWORD processlist

9. To create a Database in MySQL server

For example: to create a new database call "wordpress" in MySQL server, use the command as shown below

mysqladmin -u root -pPASSWORD create wordpress

To check if the database is created successfully, use the command as shown below to log in to your MySQL

mysql -u root -pPASSWORDWelcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3122
Server version: 5.5.38 MySQL Community Server (GPL) by Remi
Copyright (c) 2000, 2014, 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.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

After you logged in to MySQL, use the command as shown below to check all databases

show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| wordpress |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

10. To drop a Database in MySQL server

For example: to drop a Database "wordpress" in MySQL server, use the following command. You will be asked to confirm press 'y'.

mysqladmin -u root -p drop wordpress
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 'wordpress' database [y/N] yDatabase "wordpress" dropped

11. To reload or refresh MySQL Privileges

The reload command tells the server to reload the grant tables.

mysqladmin -u root -pPASSWORD reload

The refresh command flushes all tables and reopens the log files.

mysqladmin -u root -pPASSWORD refresh

12. To flush hosts

Flush all host information from host cache.

mysqladmin -u root -pPASSWORD flush-hosts

13. To flush tables

Flush all tables.

mysqladmin -u root -pPASSWORD flush-tables

14. To flush threads

Flush all threads cache.

mysqladmin -u root -pPASSWORD flush-threads

15. To flush logs

Flush all information logs.

mysqladmin -u root -pPASSWORD flush-logs

16. To flush privileges

Reload the grant tables (same as reload).

mysqladmin -u root -pPASSWORD flush-privileges

17 To clear status variables

mysqladmin -u root -pPASSWORD flush-status

18. To shutdown MySQL server Safely

mysqladmin -u root -pPASSWORD shutdown

You can also use the follow command to stop or start the MySQL server

To stop

/etc/init.d/mysqld stop

To Start

/etc/init.d/mysqld start

To restart

/etc/init.d/mysqld restart

19. To kill Sleeping MySQL Client Process

Use #8 command to identify sleeping MySQL client process.

mysqladmin -u root -pPASSWORD processlist
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+----+---------+------+-------+------------------+
| 2 | root | localhost | | Sleep | 11 | | |
| 9 | root | localhost | | Query | 0 | | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+

ID 2 is identified as sleeping process. run the following command with kill and process ID as shown below.

mysqladmin -u root -pPASSWORD kill 2
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+----+---------+------+-------+------------------+
| 9 | root | localhost | | Query | 0 | | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+

To kill multiple IDs, pass the process ID's with comma separated as shown below.

mysqladmin -u root -pPASSWORD kill 2,3,4,5,6

20. To run multiple mysqladmin commands together

mysqladmin -u root -pPASSWORD flush-hosts flush-tables flush-threads flush-logs flush-privileges flush-status

21. To connect remote mysql server

To connect remote MySQL server, use the -h (host) with IP Address of remote machine.

mysqladmin -h 192.168.11.1 -u root -pPASSWORD

22. To execute command on remote MySQL server

For example, check if remote MySQL Server is running

mysqladmin -h 192.168.11.1 -u root -pPASSWORD ping

23. To start/stop MySQL replication on a slave server

To start

mysqladmin -u root -pPASSWORD start-slave

To stop

mysqladmin -u root -pPASSWORD stop-slave

24. To store MySQL server Debug Information to logs

mysqladmin -u root -pPASSWORD debug

To find out more options and usage of myslqadmin command

mysqladmin --help

2 comments

  1. Hey thanks for sharing this resource, I would like to share a link to some MySQL console commands that i found very useful. http://csnipp.com/s/510/-MySQL-Cheat-Sheet

Leave a Reply

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