Join Amazon Student FREE Two-Day Shipping for College Students!
3500

24 Practical Usages of Mysqladmin Commands for Mysql Administration in Linux

24 Practical Usages of Mysqladmin Commands for Mysql Administration in Linux

To tell the server to reload/refresh MySQL Privileges, set/change MySQL Root password, check MySQL Server status, show MySQL version, show all MySQL server Variables and Values, check all the running Process of MySQL 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 safely. All this can be done by executing a mysqladmin commands. mysqladmin is a client for performing administrative operations. You can use it to check the server's configuration and current status, to create and drop databases, and more.

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. Niel Mathew says:

    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 *

 

Your browser is out-of-date. Upgrade your browser today! Internet Explorer | FireFox | Chrome

X