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:

1
mysqladmin [options] command [command-arg] [command [command-arg]] ...
  1. Show MySQL version

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

1
mysqladmin -u root -p version

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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
  1. To check MySQL Server is running

1
2
3
mysqladmin -u root -pPASSWORD ping

mysqld is alive
  1. To find out current Status of MySQL server

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

1
2
3
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
  1. To set MySQL Root password

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

1
mysqladmin -u root password NEWPASSWORD
  1. To change MySQL Root password

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

1
mysqladmin -u root -p123old password 'new321'
  1. To check status of all MySQL Server Variables and values

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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
+------------------------------------------+-------------+
  1. To check all MySQL server Variables and Values

1
mysqladmin  -u root -pPASSWORD variables
  1. To check all the running Process of MySQL server

1
mysqladmin -u root -pPASSWORD processlist
  1. 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

1
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

1
2
3
4
5
6
7
8
9
10
11
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

1
2
3
4
5
6
7
8
9
10
11
show databases;

+--------------------+
Database
+--------------------+
information_schema
wordpress
mysql
performance_schema
+--------------------+
4 rows in set (0.00 sec)
  1. 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’.

1
2
3
4
5
6
7
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
  1. To reload or refresh MySQL Privileges

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

1
mysqladmin -u root -pPASSWORD reload

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

1
mysqladmin -u root -pPASSWORD refresh
  1. To flush hosts

Flush all host information from host cache.

1
mysqladmin -u root -pPASSWORD flush-hosts
  1. To flush tables

Flush all tables.

1
mysqladmin -u root -pPASSWORD flush-tables
  1. To flush threads

Flush all threads cache.

1
mysqladmin -u root -pPASSWORD flush-threads
  1. To flush logs

Flush all information logs.

1
mysqladmin -u root -pPASSWORD flush-logs
  1. To flush privileges

Reload the grant tables (same as reload).

1
mysqladmin -u root -pPASSWORD flush-privileges
  1. To clear status variables

1
mysqladmin -u root -pPASSWORD flush-status
  1. To shutdown MySQL server Safely

1
mysqladmin -u root -pPASSWORD shutdown

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

1
/etc/init.d/mysqld stop

To Start

1
/etc/init.d/mysqld start

To restart

1
/etc/init.d/mysqld restart
  1. To kill Sleeping MySQL Client Process

Use #8 command to identify sleeping MySQL client process.

1
2
3
4
5
6
7
8
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.

1
2
3
4
5
6
7
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.

1
mysqladmin -u root -pPASSWORD kill 2,3,4,5,6
  1. To run multiple mysqladmin commands together

1
mysqladmin -u root -pPASSWORD flush-hosts flush-tables flush-threads flush-logs flush-privileges flush-status
  1. To connect remote mysql server

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

1
mysqladmin -h 192.168.11.1 -u root -pPASSWORD
  1. To execute command on remote MySQL server

For example, check if remote MySQL Server is running

1
mysqladmin  -h 192.168.11.1 -u root -pPASSWORD ping
  1. To start/stop MySQL replication on a slave server

To start

1
mysqladmin  -u root -pPASSWORD start-slave

To stop

1
mysqladmin  -u root -pPASSWORD stop-slave
  1. To store MySQL server Debug Information to logs

1
mysqladmin  -u root -pPASSWORD debug

To find out more options and usage of myslqadmin command

1
mysqladmin --help
How to Check XEN/OpenVZ VPS Memory/RAM Usage Bridge Multiple LAN ports/NICs in pfSense 2.1

Comments