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

Welcome 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] y
Database "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 *