{"id":11568,"date":"2019-03-14T12:56:39","date_gmt":"2019-03-14T12:56:39","guid":{"rendered":"http:\/\/www.appservgrid.com\/paw92\/?p=11568"},"modified":"2019-03-14T12:56:39","modified_gmt":"2019-03-14T12:56:39","slug":"20-mysql-mysqladmin-commands-for-database-administration-in-linux","status":"publish","type":"post","link":"https:\/\/www.appservgrid.com\/paw92\/index.php\/2019\/03\/14\/20-mysql-mysqladmin-commands-for-database-administration-in-linux\/","title":{"rendered":"20 MySQL (Mysqladmin) Commands for Database Administration in Linux"},"content":{"rendered":"

mysqladmin<\/strong>\u00a0is a command-line utility the comes with\u00a0MySQL<\/strong>\u00a0server and it is used by\u00a0Database Administrators<\/strong>to perform some basic\u00a0MySQL<\/strong>\u00a0tasks easily such as setting root password, changing root password, monitoring mysql processes, reloading privileges, checking server status etc.<\/p>\n

In this article we\u2019ve compiled some very useful \u2018mysqladmin<\/strong>\u2018 commands that are used by system\/database administrators in their day-to-day work. You must have\u00a0MySQL<\/strong>\u00a0server installed on your system to perform these tasks.<\/p>\n

\n

\"mysqladmin<\/a><\/p>\n

20 mysqladmin commands examples<\/p>\n<\/div>\n

If you don\u2019t have\u00a0MySQL<\/strong>\u00a0server installed or you are using older version of\u00a0MySQL<\/strong>\u00a0server, then we recommend you all to install or update your version by following our below article.<\/p>\n

    \n
  1. Installation of MySQL 5.5.28 Server on RHEL\/CentOS\/Fedora<\/a><\/li>\n<\/ol>\n

    1. How to set MySQL Root password?<\/h3>\n

    If you have fresh installation of\u00a0MySQL<\/strong>\u00a0server, then it doesn\u2019t required any password to connect it as root user. To set\u00a0MySQL<\/strong>\u00a0password for root user, use the following command.<\/p>\n

    # mysqladmin -u root password YOURNEWPASSWORD<\/strong><\/pre>\n
    2. How to Change MySQL Root password?<\/h3>\n
    If you would like to change or update\u00a0MySQL<\/strong>\u00a0root password, then you need to type the following command. For example, say your old password is\u00a0123456<\/strong>\u00a0and you want to change it with new password say\u00a0xyz123<\/strong>.<\/p>\n
    mysqladmin -u root -p123456<\/strong> password 'xyz123<\/strong>'<\/pre>\n
    3. How to check MySQL Server is running?<\/h3>\n
    To find out whether\u00a0MySQL<\/strong>\u00a0server is up and running, use the following command.<\/p>\n
    # mysqladmin -u root -p ping\r\n\r\nEnter password:\r\nmysqld is alive<\/strong><\/pre>\n
    4. How to Check which MySQL version I am running?<\/h3>\n
    The following command shows\u00a0MySQL<\/strong>\u00a0version along with the current running status .<\/p>\n
    # mysqladmin -u root -p version\r\n\r\nEnter password:\r\nmysqladmin  Ver 8.42 Distrib 5.5.28<\/strong>, for Linux on i686\r\nCopyright (c) 2000, 2012, Oracle and\/or its affiliates. All rights reserved.\r\n\r\nOracle is a registered trademark of Oracle Corporation and\/or its\r\naffiliates. Other names may be trademarks of their respective\r\nowners.\r\n\r\nServer version          5.5.28\r\nProtocol version        10\r\nConnection              Localhost via UNIX socket\r\nUNIX socket             \/var\/lib\/mysql\/mysql.sock\r\nUptime:                 7 days 14 min 45 sec\r\n\r\nThreads: 2  Questions: 36002  Slow queries: 0  Opens: 15  Flush tables: 1  Open tables: 8  Queries per second avg: 0.059<\/pre>\n
    5. How to Find out current Status of MySQL server?<\/h3>\n
    To find out current status of\u00a0MySQL<\/strong>\u00a0server, use the following command. The\u00a0mysqladmin<\/strong>\u00a0command shows the status of\u00a0uptime<\/strong>\u00a0with running\u00a0threads<\/strong>\u00a0and\u00a0queries<\/strong>.<\/p>\n
    # mysqladmin -u root -ptmppassword status\r\n\r\nEnter password:\r\nUptime: 606704  Threads: 2  Questions: 36003  Slow queries: 0  Opens: 15  Flush tables: 1  Open tables: 8  Queries per second avg: 0.059<\/pre>\n
    6. How to check status of all MySQL Server Variable\u2019s and value\u2019s?<\/h3>\n
    To check all the running status of\u00a0MySQL<\/strong>\u00a0server variables and values, type the following command. The output would be similar to below.<\/p>\n
    # mysqladmin -u root -p extended-status\r\n\r\nEnter password:\r\n+------------------------------------------+-------------+\r\n| Variable_name                            | Value       |\r\n+------------------------------------------+-------------+\r\n| Aborted_clients                          | 3           |\r\n| Aborted_connects                         | 3           |\r\n| Binlog_cache_disk_use                    | 0           |\r\n| Binlog_cache_use                         | 0           |\r\n| Binlog_stmt_cache_disk_use               | 0           |\r\n| Binlog_stmt_cache_use                    | 0           |\r\n| Bytes_received                           | 6400357     |\r\n| Bytes_sent                               | 2610105     |\r\n| Com_admin_commands                       | 3           |\r\n| Com_assign_to_keycache                   | 0           |\r\n| Com_alter_db                             | 0           |\r\n| Com_alter_db_upgrade                     | 0           |\r\n| Com_alter_event                          | 0           |\r\n| Com_alter_function                       | 0           |\r\n| Com_alter_procedure                      | 0           |\r\n| Com_alter_server                         | 0           |\r\n| Com_alter_table                          | 0           |\r\n| Com_alter_tablespace                     | 0           |\r\n+------------------------------------------+-------------+<\/pre>\n
    7. How to see all MySQL server Variables and Values?<\/h3>\n
    To see all the running variables and values of\u00a0MySQL<\/strong>\u00a0server, use the command as follows.<\/p>\n
    # mysqladmin  -u root -p variables\r\n\r\nEnter password:\r\n+---------------------------------------------------+----------------------------------------------+\r\n| Variable_name                                     | Value                                        |\r\n+---------------------------------------------------+----------------------------------------------+\r\n| auto_increment_increment                          | 1                                            |\r\n| auto_increment_offset                             | 1                                            |\r\n| autocommit                                        | ON                                           |\r\n| automatic_sp_privileges                           | ON                                           |\r\n| back_log                                          | 50                                           |\r\n| basedir                                           | \/usr                                         |\r\n| big_tables                                        | OFF                                          |\r\n| binlog_cache_size                                 | 32768                                        |\r\n| binlog_direct_non_transactional_updates           | OFF                                          |\r\n| binlog_format                                     | STATEMENT                                    |\r\n| binlog_stmt_cache_size                            | 32768                                        |\r\n| bulk_insert_buffer_size                           | 8388608                                      |\r\n| character_set_client                              | latin1                                       |\r\n| character_set_connection                          | latin1                                       |\r\n| character_set_database                            | latin1                                       |\r\n| character_set_filesystem                          | binary                                       |\r\n| character_set_results                             | latin1                                       |\r\n| character_set_server                              | latin1                                       |\r\n| character_set_system                              | utf8                                         |\r\n| character_sets_dir                                | \/usr\/share\/mysql\/charsets\/                   |\r\n| collation_connection                              | latin1_swedish_ci                            |\r\n+---------------------------------------------------+----------------------------------------------+<\/pre>\n
    8. How to check all the running Process of MySQL server?<\/h3>\n
    The following command will display all the running process of\u00a0MySQL<\/strong>\u00a0database queries.<\/p>\n
    # mysqladmin -u root -p processlist\r\n\r\nEnter password:\r\n+-------+---------+-----------------+---------+---------+------+-------+------------------+\r\n| Id    | User    | Host            | db      | Command | Time | State | Info             |\r\n+-------+---------+-----------------+---------+---------+------+-------+------------------+\r\n| 18001 | rsyslog | localhost:38307 | rsyslog | Sleep   | 5590 |       |                  |\r\n| 18020 | root    | localhost       |         | Query   | 0    |       | show processlist |\r\n+-------+---------+-----------------+---------+---------+------+-------+------------------+<\/pre>\n
    9. How to create a Database in MySQL server?<\/h3>\n
    To create a new database in\u00a0MySQL<\/strong>\u00a0server, use the command as shown below.<\/p>\n
    # mysqladmin -u root -p create databasename\r\n\r\nEnter password:<\/pre>\n
    # mysql -u root -p\r\n\r\nEnter password:\r\nWelcome to the MySQL monitor.  Commands end with ; or \\g.\r\nYour MySQL connection id is 18027\r\nServer version: 5.5.28 MySQL Community Server (GPL) by Remi\r\n\r\nCopyright (c) 2000, 2012, Oracle and\/or its affiliates. All rights reserved.\r\n\r\nOracle is a registered trademark of Oracle Corporation and\/or its\r\naffiliates. Other names may be trademarks of their respective\r\nowners.\r\n\r\nType 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.\r\n\r\nmysql> show databases;\r\n+--------------------+\r\n| Database           |\r\n+--------------------+\r\n| information_schema |\r\n| databasename<\/strong>       |\r\n| mysql              |\r\n| test               |\r\n+--------------------+\r\n8 rows in set (0.01 sec)\r\n\r\nmysql><\/pre>\n
    10. How to drop a Database in MySQL server?<\/h3>\n
    To drop a Database in\u00a0MySQL<\/strong>\u00a0server, use the following command. You will be asked to confirm press \u2018y<\/strong>\u2018.<\/p>\n
    # mysqladmin -u root -p drop databasename\r\n\r\nEnter password:\r\nDropping the database is potentially a very bad thing to do.\r\nAny data stored in the database will be destroyed.\r\n\r\nDo you really want to drop the 'databasename' database [y\/N] y\r\nDatabase \"databasename\" dropped<\/pre>\n
    11. How to reload\/refresh MySQL Privileges?<\/h3>\n
    The\u00a0reload<\/strong>\u00a0command tells the server to reload the grant tables. The\u00a0refresh<\/strong>\u00a0command flushes all tables and reopens the log files.<\/p>\n
    # mysqladmin -u root -p reload;\r\n# mysqladmin -u root -p refresh<\/pre>\n
    12. How to shutdown MySQL server Safely?<\/h3>\n
    To shutdown\u00a0MySQL<\/strong>\u00a0server safely, type the following command.<\/p>\n
    mysqladmin -u root -p shutdown\r\n\r\nEnter password:<\/pre>\n
    You can also use the following commands to start\/stop MySQL server.<\/p>\n
    # \/etc\/init.d\/mysqld stop\r\n# \/etc\/init.d\/mysqld start<\/pre>\n
    13. Some useful MySQL Flush commands<\/h3>\n
    Following are some useful flush commands with their description.<\/p>\n
      \n
    1. flush-hosts<\/strong>: Flush all host information from host cache.<\/li>\n
    2. flush-tables<\/strong>: Flush all tables.<\/li>\n
    3. flush-threads<\/strong>: Flush all threads cache.<\/li>\n
    4. flush-logs<\/strong>: Flush all information logs.<\/li>\n
    5. flush-privileges<\/strong>: Reload the grant tables (same as reload).<\/li>\n
    6. flush-status<\/strong>: Clear status variables.<\/li>\n<\/ol>\n
      # mysqladmin -u root -p flush-hosts\r\n# mysqladmin -u root -p flush-tables\r\n# mysqladmin -u root -p flush-threads\r\n# mysqladmin -u root -p flush-logs\r\n# mysqladmin -u root -p flush-privileges\r\n# mysqladmin -u root -p flush-status<\/pre>\n
      14. How to kill Sleeping MySQL Client Process?<\/h3>\n
      Use the following command to identify sleeping\u00a0MySQL<\/strong>\u00a0client process.<\/p>\n
      # mysqladmin -u root -p processlist\r\n\r\nEnter password:\r\n+----+------+-----------+----+---------+------+-------+------------------+\r\n| Id | User | Host      | db | Command | Time | State | Info             |\r\n+----+------+-----------+----+---------+------+-------+------------------+\r\n| 5  | root | localhost |    | Sleep   | 14   |       |\t\t\t\t\t |\r\n| 8  | root | localhost |    | Query   | 0    |       | show processlist |\r\n+----+------+-----------+----+---------+------+-------+------------------+<\/pre>\n
      Now, run the following command with\u00a0kill<\/strong>\u00a0and\u00a0process ID<\/strong>\u00a0as shown below.<\/p>\n
      # mysqladmin -u root -p kill 5\r\n\r\nEnter password:\r\n+----+------+-----------+----+---------+------+-------+------------------+\r\n| Id | User | Host      | db | Command | Time | State | Info             |\r\n+----+------+-----------+----+---------+------+-------+------------------+\r\n| 12 | root | localhost |    | Query   | 0    |       | show processlist |\r\n+----+------+-----------+----+---------+------+-------+------------------+<\/pre>\n
      If you like to\u00a0kill<\/strong>\u00a0multiple process, then pass the\u00a0process ID<\/strong>\u2018s with comma separated as shown below.<\/p>\n
      # mysqladmin -u root -p kill 5,10<\/pre>\n
      15. How to run multiple mysqladmin commands together?<\/h3>\n
      If you would like to execute multiple \u2018mysqladmin<\/strong>\u2018 commands together, then the command would be like this.<\/p>\n
      # mysqladmin  -u root -p processlist status version\r\n\r\nEnter password:\r\n+----+------+-----------+----+---------+------+-------+------------------+\r\n| Id | User | Host      | db | Command | Time | State | Info             |\r\n+----+------+-----------+----+---------+------+-------+------------------+\r\n| 8  | root | localhost |    | Query   | 0    |       | show processlist |\r\n+----+------+-----------+----+---------+------+-------+------------------+\r\nUptime: 3801  Threads: 1  Questions: 15  Slow queries: 0  Opens: 15  Flush tables: 1  Open tables: 8  Queries per second avg: 0.003\r\nmysqladmin  Ver 8.42 Distrib 5.5.28, for Linux on i686\r\nCopyright (c) 2000, 2012, Oracle and\/or its affiliates. All rights reserved.\r\n\r\nOracle is a registered trademark of Oracle Corporation and\/or its\r\naffiliates. Other names may be trademarks of their respective\r\nowners.\r\n\r\nServer version          5.5.28\r\nProtocol version        10\r\nConnection              Localhost via UNIX socket\r\nUNIX socket             \/var\/lib\/mysql\/mysql.sock\r\nUptime:                 1 hour 3 min 21 sec\r\n\r\nThreads: 1  Questions: 15  Slow queries: 0  Opens: 15  Flush tables: 1  Open tables: 8  Queries per second avg: 0.003<\/pre>\n
      16. How to Connect remote mysql server<\/h3>\n
      To connect remote\u00a0MySQL<\/strong>\u00a0server, use the\u00a0-h<\/strong>\u00a0(host<\/strong>)\u00a0 with\u00a0IP Address<\/strong>\u00a0of remote machine.<\/p>\n
      # mysqladmin  -h 172.16.25.126 -u root -p<\/pre>\n
      17. How to execute command on remote MySQL server<\/h3>\n
      Let\u2019s say you would like to see the\u00a0status<\/strong>\u00a0of remote\u00a0MySQL<\/strong>\u00a0server, then the command would be.<\/p>\n
      # mysqladmin  -h 172.16.25.126 -u root -p status<\/pre>\n
      18. How to start\/stop MySQL replication on a slave server?<\/h3>\n
      To start\/stop\u00a0MySQL replication<\/a>\u00a0on salve server, use the following commands.<\/p>\n
      # mysqladmin  -u root -p start-slave<\/pre>\n
      # mysqladmin  -u root -p stop-slave<\/pre>\n
      19. How to store MySQL server Debug Information to logs?<\/h3>\n
      It tells the server to write debug information about locks in use, used memory and query usage to the\u00a0MySQL<\/strong>\u00a0log file including information about event scheduler.<\/p>\n
      # mysqladmin  -u root -p debug\r\n\r\nEnter password:<\/pre>\n
      20. How to view mysqladmin options and usage<\/h3>\n
      To find out more options and usage of\u00a0myslqadmin<\/strong>\u00a0command use the help command as shown below. It will display a list of available options.<\/p>\n
      # mysqladmin --help<\/pre>\n
      We have tried our best to include almost all of \u2018mysqladmin<\/strong>\u2018 commands with their examples in this article, If still, we\u2019ve missed anything, please do let us know via comments and don\u2019t forget to share with your friends.<\/p>\n
      Source<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
      mysqladmin\u00a0is a command-line utility the comes with\u00a0MySQL\u00a0server and it is used by\u00a0Database Administratorsto perform some basic\u00a0MySQL\u00a0tasks easily such as setting root password, changing root password, monitoring mysql processes, reloading privileges, checking server status etc. In this article we\u2019ve compiled some very useful \u2018mysqladmin\u2018 commands that are used by system\/database administrators in their day-to-day work. You … <\/p>\n
      Continue reading “20 MySQL (Mysqladmin) Commands for Database Administration in Linux”<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-11568","post","type-post","status-publish","format-standard","hentry","category-linux"],"_links":{"self":[{"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/posts\/11568","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/comments?post=11568"}],"version-history":[{"count":1,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/posts\/11568\/revisions"}],"predecessor-version":[{"id":11569,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/posts\/11568\/revisions\/11569"}],"wp:attachment":[{"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/media?parent=11568"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/categories?post=11568"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/tags?post=11568"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}