Did You Know?
Donate to TecMint

We are pleased to announce our new TecMint Q/A section to submit your Linux questions

20 MySQL (Mysqladmin) Commands for Database Administration in Linux

Download Your Free eBooks NOW - 10 Free Linux eBooks for Administrators

mysqladmin is a command-line utility the comes with MySQL server and it is used by Database Administrators to perform some basic MySQL tasks easily such as setting root password, changing root password, monitoring mysql processes, reloading privileges, checking server status etc.

In this article we’ve compiled some very useful ‘mysqladmin‘ commands that are used by system/database administrators in their day-to-day work. You must have MySQL server installed on your system to perform these tasks.

mysqladmin commands

20 mysqladmin commands examples

If you don’t have MySQL server installed or you are using older version of MySQL server, then we recommend you all to install or update your version by following our below article.

  1. Installation of MySQL 5.5.28 Server on RHEL/CentOS/Fedora

1. How to set MySQL Root password?

If you have fresh installation of MySQL server, then it doesn’t required any password to connect it as root user. To set MySQL password for root user, use the following command.

# mysqladmin -u root password YOURNEWPASSWORD

2. How to Change MySQL Root password?

If you would like to change or update MySQL root password, then you need to type the following command. For example, say your old password is 123456 and you want to change it with new password say xyz123.

mysqladmin -u root -p123456 password 'xyz123'

3. How to check MySQL Server is running?

To find out whether MySQL server is up and running, use the following command.

# mysqladmin -u root -p ping

Enter password:
mysqld is alive

4. How to Check which MySQL version I am running?

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

# mysqladmin -u root -p version

Enter password:
mysqladmin  Ver 8.42 Distrib 5.5.28, for Linux on i686
Copyright (c) 2000, 2012, 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.28
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/lib/mysql/mysql.sock
Uptime:                 7 days 14 min 45 sec

Threads: 2  Questions: 36002  Slow queries: 0  Opens: 15  Flush tables: 1  Open tables: 8  Queries per second avg: 0.059

5. How to Find out current Status of MySQL server?

To find out current status of MySQL server, use the following command. The mysqladmin command shows the status of uptime with running threads and queries.

# mysqladmin -u root -ptmppassword status

Enter password:
Uptime: 606704  Threads: 2  Questions: 36003  Slow queries: 0  Opens: 15  Flush tables: 1  Open tables: 8  Queries per second avg: 0.059

6. How to check status of all MySQL Server Variable’s and value’s?

To check all the running status of MySQL server variables and values, type the following command. The output would be similar to below.

# mysqladmin -u root -p extended-status

Enter password:
+------------------------------------------+-------------+
| Variable_name                            | Value       |
+------------------------------------------+-------------+
| Aborted_clients                          | 3           |
| Aborted_connects                         | 3           |
| Binlog_cache_disk_use                    | 0           |
| Binlog_cache_use                         | 0           |
| Binlog_stmt_cache_disk_use               | 0           |
| Binlog_stmt_cache_use                    | 0           |
| Bytes_received                           | 6400357     |
| Bytes_sent                               | 2610105     |
| Com_admin_commands                       | 3           |
| Com_assign_to_keycache                   | 0           |
| Com_alter_db                             | 0           |
| Com_alter_db_upgrade                     | 0           |
| Com_alter_event                          | 0           |
| Com_alter_function                       | 0           |
| Com_alter_procedure                      | 0           |
| Com_alter_server                         | 0           |
| Com_alter_table                          | 0           |
| Com_alter_tablespace                     | 0           |
+------------------------------------------+-------------+

7. How to see all MySQL server Variables and Values?

To see all the running variables and values of MySQL server, use the command as follows.

# mysqladmin  -u root -p variables

Enter password:
+---------------------------------------------------+----------------------------------------------+
| Variable_name                                     | Value                                        |
+---------------------------------------------------+----------------------------------------------+
| auto_increment_increment                          | 1                                            |
| auto_increment_offset                             | 1                                            |
| autocommit                                        | ON                                           |
| automatic_sp_privileges                           | ON                                           |
| back_log                                          | 50                                           |
| basedir                                           | /usr                                         |
| big_tables                                        | OFF                                          |
| binlog_cache_size                                 | 32768                                        |
| binlog_direct_non_transactional_updates           | OFF                                          |
| binlog_format                                     | STATEMENT                                    |
| binlog_stmt_cache_size                            | 32768                                        |
| bulk_insert_buffer_size                           | 8388608                                      |
| character_set_client                              | latin1                                       |
| character_set_connection                          | latin1                                       |
| character_set_database                            | latin1                                       |
| character_set_filesystem                          | binary                                       |
| character_set_results                             | latin1                                       |
| character_set_server                              | latin1                                       |
| character_set_system                              | utf8                                         |
| character_sets_dir                                | /usr/share/mysql/charsets/                   |
| collation_connection                              | latin1_swedish_ci                            |
+---------------------------------------------------+----------------------------------------------+

8. How to check all the running Process of MySQL server?

The following command will display all the running process of MySQL database queries.

# mysqladmin -u root -p processlist

Enter password:
+-------+---------+-----------------+---------+---------+------+-------+------------------+
| Id    | User    | Host            | db      | Command | Time | State | Info             |
+-------+---------+-----------------+---------+---------+------+-------+------------------+
| 18001 | rsyslog | localhost:38307 | rsyslog | Sleep   | 5590 |       |                  |
| 18020 | root    | localhost       |         | Query   | 0    |       | show processlist |
+-------+---------+-----------------+---------+---------+------+-------+------------------+

9. How to create a Database in MySQL server?

To create a new database in MySQL server, use the command as shown below.

# mysqladmin -u root -p create databasename

Enter password:
# mysql -u root -p

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18027
Server version: 5.5.28 MySQL Community Server (GPL) by Remi

Copyright (c) 2000, 2012, 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.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| databasename       |
| mysql              |
| test               |
+--------------------+
8 rows in set (0.01 sec)

mysql>

10. How to drop a Database in MySQL server?

To drop a Database in MySQL server, use the following command. You will be asked to confirm press ‘y‘.

# mysqladmin -u root -p drop databasename

Enter password:
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 'databasename' database [y/N] y
Database "databasename" dropped

11. How to reload/refresh MySQL Privileges?

The reload command tells the server to reload the grant tables. The refresh command flushes all tables and reopens the log files.

# mysqladmin -u root -p reload;
# mysqladmin -u root -p refresh

12. How to shutdown MySQL server Safely?

To shutdown MySQL server safely, type the following command.

mysqladmin -u root -p shutdown

Enter password:

You can also use the following commands to start/stop MySQL server.

# /etc/init.d/mysqld stop
# /etc/init.d/mysqld start

13. Some useful MySQL Flush commands

Following are some useful flush commands with their description.

  1. flush-hosts: Flush all host information from host cache.
  2. flush-tables: Flush all tables.
  3. flush-threads: Flush all threads cache.
  4. flush-logs: Flush all information logs.
  5. flush-privileges: Reload the grant tables (same as reload).
  6. flush-status: Clear status variables.
# mysqladmin -u root -p flush-hosts
# mysqladmin -u root -p flush-tables
# mysqladmin -u root -p flush-threads
# mysqladmin -u root -p flush-logs
# mysqladmin -u root -p flush-privileges
# mysqladmin -u root -p flush-status

14. How to kill Sleeping MySQL Client Process?

Use the following command to identify sleeping MySQL client process.

# mysqladmin -u root -p processlist

Enter password:
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 5  | root | localhost |    | Sleep   | 14   |       |					 |
| 8  | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+

Now, run the following command with kill and process ID as shown below.

# mysqladmin -u root -p kill 5

Enter password:
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 12 | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+

If you like to kill multiple process, then pass the process ID‘s with comma separated as shown below.

# mysqladmin -u root -p kill 5,10

15. How to run multiple mysqladmin commands together?

If you would like to execute multiple ‘mysqladmin‘ commands together, then the command would be like this.

# mysqladmin  -u root -p processlist status version

Enter password:
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 8  | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+
Uptime: 3801  Threads: 1  Questions: 15  Slow queries: 0  Opens: 15  Flush tables: 1  Open tables: 8  Queries per second avg: 0.003
mysqladmin  Ver 8.42 Distrib 5.5.28, for Linux on i686
Copyright (c) 2000, 2012, 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.28
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/lib/mysql/mysql.sock
Uptime:                 1 hour 3 min 21 sec

Threads: 1  Questions: 15  Slow queries: 0  Opens: 15  Flush tables: 1  Open tables: 8  Queries per second avg: 0.003

16. How to Connect remote mysql server

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

# mysqladmin  -h 172.16.25.126 -u root -p

17. How to execute command on remote MySQL server

Let’s say you would like to see the status of remote MySQL server, then the command would be.

# mysqladmin  -h 172.16.25.126 -u root -p status

18. How to start/stop MySQL replication on a slave server?

To start/stop MySQL replication on salve server, use the following commands.

# mysqladmin  -u root -p start-slave
# mysqladmin  -u root -p stop-slave

19. How to store MySQL server Debug Information to logs?

It tells the server to write debug information about locks in use, used memory and query usage to the MySQL log file including information about event scheduler.

# mysqladmin  -u root -p debug

Enter password:

20. How to view mysqladmin options and usage

To find out more options and usage of myslqadmin command use the help command as shown below. It will display a list of available options.

# mysqladmin --help

We have tried our best to include almost all of ‘mysqladmin‘ commands with their examples in this article, If still, we’ve missed anything, please do let us know via comments and don’t forget to share with your friends.

Ravi Saive

Owner at TecMint.com
Simple Word a Computer Geek and Linux Guru who loves to share tricks and tips on Internet. Most Of My Servers runs on Open Source Platform called Linux.

Linux Services & Free WordPress Setup

Our post is simply ‘DIY’ aka ‘Do It Yourself, still you may find difficulties and want us to help you out. We offer wide range of Linux and Web Hosting Solutions at fair minimum rates. Please submit your orders by Clicking Here.

31 Responses

  1. Alex says:

    Small typo at #18
    # mysqladmin -u root -p start-slve
    should be
    # mysqladmin -u root -p start-slave

  2. Tierno Bah says:

    the list duplicates no. 4

  3. Hi there,
    Nice post, I guess it will be a good start point to beginner on MySQL admin.
    keep writing.

  4. Sathishkumar says:

    Useful commands for every admin !

  5. RoseHosting says:

    Great collection of MySQL commands, thanks for sharing.

  6. Thanks for another informative website. The place else could I am getting that type of info written in such a perfect means? I have a challenge that I’m simply now working on, and I’ve been on the glance out for such information.

  7. Beg.Mysql says:

    HI ,

    I am beginner to mysql database administration .these commands are really help full for me .

    can any body tell me how to create a replica of the database in the mysql.

    Thanks in Advance.

  8. Paolo says:

    Let me show you another list of useful admin commands;
    http://zaboilab.com/mysql-tool-box/queries-to-see-rapidly-what-your-mysql-is-doing-now

    Here you can find something to check performance events too.

  9. Jothi Vasu says:

    hI..,
    Realy its very useful and simply path queries to creating the commands..

  10. rajesh says:

    Fantastic work

  11. Sachin says:

    Thanks a lot Mr. Ravi Saive, very informative and in simple understandable language. Must say apart from good tech knowledge you also have great writing skills. keep it up

  12. AVL PRASAD says:

    Hi Sir,

    Very very thanks… Great

  13. Baba says:

    Hi Ravi,

    Thanks for this article.

    Could you please share some knowledge on MySQL replication.

  14. Dileep says:

    Thanks a lot Dear Ravi Saive for your valuable “20 MySQL (Mysqladmin) Commands for Database Administration in Linux”.

    As i’m new to mysql , helped me a lot for the basic understanding of mysql usage. Now got confidence Dear.

  15. Bakshu says:

    Hi,

    am learning MySQL DBA, am a beginner…..when am practicing i received below message…but the database exist there and i deleted it by using drop DDL command…!

    Is there any other things to do after creating a database by using mysqladmin…please clarify me…!

    Note: This is test machine, no root password assigned…!

    mysql> show databases;
    +——————–+
    | Database |
    +——————–+
    | information_schema |
    | bakshu |
    | bumper |
    | mysql |
    | performance_schema |
    | test |
    +——————–+
    6 rows in set (0.00 sec)

    mysql> exit
    Bye

    [root@host-7-89 ~]# mysqladmin -u root drop bumber
    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 ‘bumber’ database [y/N] y
    mysqladmin: DROP DATABASE bumber failed;
    error: ‘Can’t drop database ‘bumber’; database doesn’t exist’
    [root@host-7-89 ~]# mysql -u root
    Welcome to the MySQL monitor. Commands end with ; or \g.
    Your MySQL connection id is 11
    Server version: 5.5.14 MySQL Community Server (GPL)

    Copyright (c) 2000, 2010, 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.

    mysql> drop database bumper;
    Query OK, 0 rows affected (0.06 sec)

    mysql> show databases;
    +——————–+
    | Database |
    +——————–+
    | information_schema |
    | bakshu |
    | mysql |
    | performance_schema |
    | test |
    +——————–+
    5 rows in set (0.00 sec)

  16. Mohanraj Jayaraman says:

    Hi,
    Can you please share the script for monitoring the CPU/Memory/IO usage for the MySQL Process alone in the Linux OS.

    Thanks,
    Mohanraj Jayaraman

  17. venkat says:

    How to change database name in mysql ubuntu ex: db1 to db2

    • Ravi Saive says:

      First, dump the old database using ‘mysqldump’ command and then create a new database and then dump the old data into new database. For example, follow the commands.

      # mysqldump -u username -p olddatabase > olddbdump.sql
      # mysqladmin -u username -p create newdatabase
      # mysql -u username -p newdatabase < olddbdump.sql
      
  18. R Muthukumar says:

    Hi,

    Can you please tell me how to unlock the tables in linux mysql and how to recover the mysql crashed tables

    I am waiting for your reply sir,

  19. shufil says:

    HI Ravi,

    Can we get how many memory and cpu load taken each mysql process .

    Regards,
    Shufil

  20. Halesh says:

    Ravi,

    Thanks for useful information.

    I want to automatically re-start mysql service daily at specified time, pls suggest how to achieive it. Thanks..

    • Ravi Saive says:

      Use a Cron to achieve such task, for example, to start mysql daily at around 9:30am put the following entry in your crontab file.

      30 9 * * * /etc/init.d/mysqld restart
      
  21. Nithya says:

    Hi Ravi,

    How to identify a sleeping process/ connection?

    Regards,
    Nithya

  22. surya says:

    Hi Ravi,

    Thanks, Its really help full post….

    Regards,
    Surya

  23. shabbir younas awan says:

    Dear please help me to connect oracle database with MySQL database on Linux platform. I shall be very thankful to you

    my official email address is “shabbir_ywm@chakwalgroup.com.pk”

Leave a Reply

This work is licensed under a (cc) BY-NC | TecMint uses cookies. By using our services, you comply to use of our cookies. More info: Privacy Policy.
© 2012-2014 All Rights Reserved.