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 *