15 Practical Usages of Mysqladmin Command For Administering MySQL Server

by Ramesh Natarajan on January 14, 2009

MySQL LogoIn all the 15 mysqladmin command-line examples below, tmppassword is used as the MySQL root user password. Please change this to your MySQL root password.

1. How to change the MySQL root user password?

# mysqladmin -u root -ptmppassword password 'newpassword'

# mysql -u root -pnewpassword
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.1.25-rc-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

2. How to check whether MySQL Server is up and running?

# mysqladmin -u root -p ping
Enter password:
mysqld is alive

3. How do I find out what version of MySQL I am running?

Apart from giving the ‘Server version’, this command also displays the current status of the mysql server.

# mysqladmin -u root -ptmppassword version
mysqladmin  Ver 8.42 Distrib 5.1.25-rc, for redhat-linux-gnu on i686
Copyright (C) 2000-2006 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version          5.1.25-rc-community
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/lib/mysql/mysql.sock
Uptime:                 107 days 6 hours 11 min 44 sec

Threads: 1  Questions: 231976  Slow queries: 0  Opens: 17067
Flush tables: 1  Open tables: 64  Queries per second avg: 0.25

4. What is the current status of MySQL server?

# mysqladmin -u root -ptmppassword status
Uptime: 9267148
Threads: 1  Questions: 231977  Slow queries: 0  Opens: 17067
Flush tables: 1  Open tables: 64  Queries per second avg: 0.25

The status command displays the following information:

  • Uptime: Uptime of the mysql server in seconds
  • Threads: Total number of clients connected to the server.
  • Questions: Total number of queries the server has executed since the startup.
  • Slow queries: Total number of queries whose execution time waas more than long_query_time variable’s value.
  • Opens: Total number of tables opened by the server.
  • Flush tables: How many times the tables were flushed.
  • Open tables: Total number of open tables in the database.

5. How to view all the MySQL Server status variable and it’s current value?

# mysqladmin -u root -ptmppassword extended-status
+-----------------------------------+-----------+
| Variable_name                     | Value     |
+-----------------------------------+-----------+
| Aborted_clients                   | 579       |
| Aborted_connects                  | 8         |
| Binlog_cache_disk_use             | 0         |
| Binlog_cache_use                  | 0         |
| Bytes_received                    | 41387238  |
| Bytes_sent                        | 308401407 |
| Com_admin_commands                | 3524      |
| Com_assign_to_keycache            | 0         |
| Com_alter_db                      | 0         |
| Com_alter_db_upgrade              | 0         |

6. How to display all MySQL server system variables and the values?

# mysqladmin  -u root -ptmppassword variables
+---------------------------------+---------------------------------+
| Variable_name                   | Value                           |
+---------------------------------+---------------------------------+
| auto_increment_increment        | 1                               |
| basedir                         | /                               |
| big_tables                      | OFF                             |
| binlog_format                   | MIXED                           |
| bulk_insert_buffer_size         | 8388608                         |
| character_set_client            | latin1                          |
| character_set_database          | latin1                          |
| character_set_filesystem        | binary                          |

skip.....

| time_format                     | %H:%i:%s                        |
| time_zone                       | SYSTEM                          |
| timed_mutexes                   | OFF                             |
| tmpdir                          | /tmp                            |
| tx_isolation                    | REPEATABLE-READ                 |
| unique_checks                   | ON                              |
| updatable_views_with_limit      | YES                             |
| version                         | 5.1.25-rc-community             |
| version_comment                 | MySQL Community Server (GPL)    |
| version_compile_machine         | i686                            |
| version_compile_os              | redhat-linux-gnu                |
| wait_timeout                    | 28800                           |
+---------------------------------+---------------------------------+

7. How to display all the running process/queries in the mysql database?

# mysqladmin -u root -ptmppassword processlist
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 20 | root | localhost |    | Sleep   | 36   |       |                  |
| 23 | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+

You can use this command effectively to debug any performance issue and identify the query that is causing problems, by running the command automatically every 1 second as shown below.

# mysqladmin -u root -ptmppassword -i 1 processlist
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 20 | root | localhost |    | Sleep   | 36   |       |                  |
| 23 | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+

+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 24 | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+

8. How to create a MySQL Database?

# mysqladmin -u root -ptmppassword create testdb

# mysql -u root -ptmppassword
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 705
Server version: 5.1.25-rc-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| sugarcrm           |
| testdb             |
+--------------------+
4 rows in set (0.00 sec)



Note: To display all tables in a database, total number of columns, row, column types, indexes etc., use the mysqlshow command that we discussed in our previous articles.

9. How to Delete/Drop an existing MySQL database?

# mysqladmin -u root -ptmppassword drop testdb
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 'testdb' database [y/N] y
Database "testdb" dropped

# mysql -u root -ptmppassword
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 707
Server version: 5.1.25-rc-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| sugarcrm           |
+--------------------+
3 rows in set (0.00 sec)

10. How to reload/refresh the privilege or the grants tables?

# mysqladmin -u root -ptmppassword reload;

Refresh command will flush all the tables and close/open log files.

# mysqladmin -u root -ptmppassword refresh

11. What is the safe method to shutdown the MySQL server?

# mysqladmin -u root -ptmppassword shutdown

# mysql -u root -ptmppassword
ERROR 2002 (HY000): Can't connect to local MySQL server
through socket '/var/lib/mysql/mysql.sock'

Note: You can also use “/etc/rc.d/init.d/mysqld stop” to shutdown the server. To start the server, execute “/etc/rc.d/init.d/mysql start”

12. List of all mysqladmin flush commands.

# mysqladmin -u root -ptmppassword flush-hosts
# mysqladmin -u root -ptmppassword flush-logs
# mysqladmin -u root -ptmppassword flush-privileges
# mysqladmin -u root -ptmppassword flush-status
# mysqladmin -u root -ptmppassword flush-tables
# mysqladmin -u root -ptmppassword flush-threads
  • flush-hosts: Flush all information in the host cache.
  • flush-privileges: Reload the grant tables (same as reload).
  • flush-status: Clear status variables.
  • flush-threads: Flush the thread cache.

13. How to kill a hanging MySQL Client Process?

First identify the hanging MySQL client process using the processlist command.

# mysqladmin -u root -ptmppassword processlist
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 20 | root | localhost |    | Sleep   | 64   |       |                  |
| 24 | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+

Now, use the kill command and pass the process_id as shown below. To kill multiple process you can pass comma separated process id’s.

# mysqladmin -u root -ptmppassword kill 20

# mysqladmin -u root -ptmppassword processlist
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 26 | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+

14. How to start and stop MySQL replication on a slave server?

# mysqladmin  -u root -ptmppassword stop-slave
Slave stopped

# mysqladmin  -u root -ptmppassword start-slave
mysqladmin: Error starting slave: The server is not configured as slave;
fix in config file or with CHANGE MASTER TO

15. How to combine multiple mysqladmin commands together?

In the example below, you can combine process-list, status and version command to get all the output together as shown below.

# mysqladmin  -u root -ptmppassword process status version
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 43 | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+

Uptime: 3135
Threads: 1  Questions: 80  Slow queries: 0  Opens: 15  Flush tables: 3
Open tables: 0  Queries per second avg: 0.25

mysqladmin  Ver 8.42 Distrib 5.1.25-rc, for redhat-linux-gnu on i686
Copyright (C) 2000-2006 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version          5.1.25-rc-community
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/lib/mysql/mysql.sock
Uptime:                 52 min 15 sec

You can also use the short form as shown below:

# mysqladmin  -u root -ptmppassword pro stat ver

Use the option -h, to connect to a remote MySQL server and execute the mysqladmin commands as shown below.

# mysqladmin  -h 192.168.1.112 -u root -ptmppassword pro stat ver


If you like this article, please bookmark it on del.icio.us and Stumble It.


Linux Sysadmin Course Linux provides several powerful administrative tools and utilities which will help you to manage your systems effectively. If you don’t know what these tools are and how to use them, you could be spending lot of time trying to perform even the basic administrative tasks. The focus of this course is to help you understand system administration tools, which will help you to become an effective Linux system administrator.
Get the Linux Sysadmin Course Now!

If you enjoyed this article, you might also like..

  1. 50 Linux Sysadmin Tutorials
  2. 50 Most Frequently Used Linux Commands (With Examples)
  3. Top 25 Best Linux Performance Monitoring and Debugging Tools
  4. Mommy, I found it! – 15 Practical Linux Find Command Examples
  5. Linux 101 Hacks 2nd Edition eBook Linux 101 Hacks Book

Bash 101 Hacks Book Sed and Awk 101 Hacks Book Nagios Core 3 Book Vim 101 Hacks Book

{ 12 comments… read them below or add one }

1 billyduc February 9, 2009 at 2:12 am

Thank you so much…Can you tell me how can I benchmark my MySQL database ?

2 anurag sharma May 14, 2009 at 11:21 am

Hi Ramesh
Thanks a ton for your tips and tricks
Please tell us how to migrate PostGreSQL database to mysql
using command line or using some tool

Thanks in advance
anurag sharma

3 Kamal Maiti June 25, 2009 at 6:43 am

Hello,

It’s really excellent……Great……

Regards,
Kamal Maiti
System Engineer.

4 Kamal Maiti June 25, 2009 at 6:46 am

Hi Ramesh,

Can you please let me know how to optimize the mysql databse and which exact query of which user is taking huge time and cpu resources?

Regards,
Kamal

5 Gautam Kashyap September 24, 2009 at 1:25 am

Hi Ramesh,
Thanks for publishing this useful article.keep . Plz send me morel command related to mysql .

With Regards
Gautam Kashyap

6 Neeraj December 23, 2010 at 12:43 am

Thanks for you tips,
I am using this command on Putty, But it’s not working
mysqladmin -u mymysqlusername -pmysqlpassword flush-hosts
following error coming,
mysqladmin: refresh failed; error: ‘Access denied; you need the RELOAD privilege for this operation’
Kindly tell me what should we do.

7 Karthik.P.R February 10, 2012 at 6:35 am

@Neeraj . It means that you are not having the enough privilege on mysql server. Your system administrator would have restricted your privileges.

Regards,
Karthik.P.R
MySQL DBA

8 salathulla March 19, 2013 at 11:45 pm

hi i want to mysql server uptime and down time. pls help me the query

9 Karthik.P.R March 22, 2013 at 11:35 pm

Hi Salathulla,

The uptime can be check by below command. It shows the time in seconds.

mysql> show global status like ‘uptime’;

or mysql> \s ( It will show the status of server like socket,version,uptime, etc)

Regards,
Karthik.P.R
MySQL DBA

10 Eduardo Heredia September 18, 2013 at 11:48 pm

As always, useful and straightforward !

Ramesh for president …

11 bruce November 15, 2013 at 3:21 pm

I notice that you use the following a lot “-ptmppassword”. That does not work for me. On my box I must use “-p” or get and error msg. ERROR 1045 (28000): Access denied for user ‘root’@'localhost’ (using password: YES). I’m wonder what is different in your set, verses mine?

12 Rod Elias June 10, 2014 at 7:53 am

So many useful information.
Thanks a lot for such a great article.
Well done Sir.

Leave a Comment

Previous post:

Next post: