Reply to thread

Message

<blockquote data-quote="y_rangana" data-source="post: 13971541" data-attributes="member: 15719">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.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.&nbsp; &nbsp; Installation of MySQL 5.5.28 Server on RHEL/CentOS/Fedora1. 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 YOURNEWPASSWORD2. 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 pingEnter password:mysqld is alive4. 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 versionEnter password:mysqladmin&nbsp; Ver 8.42 Distrib 5.5.28, for Linux on i686Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Server version&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5.5.28Protocol version&nbsp; &nbsp; &nbsp; &nbsp; 10Connection&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Localhost via UNIX socketUNIX socket&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; /var/lib/mysql/mysql.sockUptime:&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 7 days 14 min 45 secThreads: 2&nbsp; Questions: 36002&nbsp; Slow queries: 0&nbsp; Opens: 15&nbsp; Flush tables: 1&nbsp; Open tables: 8&nbsp; Queries per second avg: 0.0594. 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 statusEnter password:Uptime: 606704&nbsp; Threads: 2&nbsp; Questions: 36003&nbsp; Slow queries: 0&nbsp; Opens: 15&nbsp; Flush tables: 1&nbsp; Open tables: 8&nbsp; Queries per second avg: 0.0596. 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-statusEnter password:+------------------------------------------+-------------+| Variable_name&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | Value&nbsp; &nbsp; &nbsp;&nbsp; |+------------------------------------------+-------------+| Aborted_clients&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; || Aborted_connects&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; | 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; || Binlog_cache_disk_use&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; || Binlog_cache_use&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; | 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; || Binlog_stmt_cache_disk_use&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; | 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; || Binlog_stmt_cache_use&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; || Bytes_received&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; | 6400357&nbsp; &nbsp;&nbsp; || Bytes_sent&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; | 2610105&nbsp; &nbsp;&nbsp; || Com_admin_commands&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; | 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; || Com_assign_to_keycache&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; | 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; || Com_alter_db&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; | 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; || Com_alter_db_upgrade&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; | 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; || Com_alter_event&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; || Com_alter_function&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; | 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; || Com_alter_procedure&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; || Com_alter_server&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; | 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; || Com_alter_table&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; || Com_alter_tablespace&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; | 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; |+------------------------------------------+-------------+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&nbsp; -u root -p variablesEnter password:+---------------------------------------------------+----------------------------------------------+| Variable_name&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; | Value&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; |+---------------------------------------------------+----------------------------------------------+| auto_increment_increment&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; || auto_increment_offset&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; | 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; || autocommit&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | ON&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; || automatic_sp_privileges&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; | ON&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; || back_log&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | 50&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; || basedir&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; | /usr&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; || big_tables&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | OFF&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; || binlog_cache_size&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; | 32768&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; || binlog_direct_non_transactional_updates&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; | OFF&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; || binlog_format&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; | STATEMENT&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; || binlog_stmt_cache_size&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | 32768&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; || bulk_insert_buffer_size&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; | 8388608&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; || character_set_client&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | latin1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; || character_set_connection&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | latin1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; || character_set_database&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | latin1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; || character_set_filesystem&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | binary&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; || character_set_results&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; | latin1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; || character_set_server&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | latin1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; || character_set_system&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | utf8&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; || character_sets_dir&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | /usr/share/mysql/charsets/&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; || collation_connection&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | latin1_swedish_ci&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; |+---------------------------------------------------+----------------------------------------------+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 processlistEnter password:+-------+---------+-----------------+---------+---------+------+-------+------------------+| Id&nbsp; &nbsp; | User&nbsp; &nbsp; | Host&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | db&nbsp; &nbsp; &nbsp; | Command | Time | State | Info&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; |+-------+---------+-----------------+---------+---------+------+-------+------------------+| 18001 | rsyslog | localhost:38307 | rsyslog | Sleep&nbsp;&nbsp; | 5590 |&nbsp; &nbsp; &nbsp;&nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; || 18020 | root&nbsp; &nbsp; | localhost&nbsp; &nbsp; &nbsp;&nbsp; |&nbsp; &nbsp; &nbsp; &nbsp;&nbsp; | Query&nbsp;&nbsp; | 0&nbsp; &nbsp; |&nbsp; &nbsp; &nbsp;&nbsp; | 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 databasenameEnter password:# mysql -u root -pEnter password:Welcome to the MySQL monitor.&nbsp; Commands end with ; or \g.Your MySQL connection id is 18027Server version: 5.5.28 MySQL Community Server (GPL) by RemiCopyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql&gt; show databases;+--------------------+| Database&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; |+--------------------+| information_schema || databasename&nbsp; &nbsp; &nbsp;&nbsp; || mysql&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; || test&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; |+--------------------+8 rows in set (0.01 sec)mysql&gt;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 databasenameEnter 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] yDatabase &quot;databasename&quot; dropped11. 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 refresh12. How to shutdown MySQL server Safely?To shutdown MySQL server safely, type the following command.mysqladmin -u root -p shutdownEnter password:You can also use the following commands to start/stop MySQL server.# /etc/init.d/mysqld stop# /etc/init.d/mysqld start13. Some useful MySQL Flush commandsFollowing are some useful flush commands with their description.&nbsp; &nbsp; flush-hosts: Flush all host information from host cache.&nbsp; &nbsp; flush-tables: Flush all tables.&nbsp; &nbsp; flush-threads: Flush all threads cache.&nbsp; &nbsp; flush-logs: Flush all information logs.&nbsp; &nbsp; flush-privileges: Reload the grant tables (same as reload).&nbsp; &nbsp; 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-status14. How to kill Sleeping MySQL Client Process?Use the following command to identify sleeping MySQL client process.# mysqladmin -u root -p processlistEnter password:+----+------+-----------+----+---------+------+-------+------------------+| Id | User | Host&nbsp; &nbsp; &nbsp; | db | Command | Time | State | Info&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; |+----+------+-----------+----+---------+------+-------+------------------+| 5&nbsp; | root | localhost |&nbsp; &nbsp; | Sleep&nbsp;&nbsp; | 14&nbsp;&nbsp; |&nbsp; &nbsp; &nbsp;&nbsp; |&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; || 8&nbsp; | root | localhost |&nbsp; &nbsp; | Query&nbsp;&nbsp; | 0&nbsp; &nbsp; |&nbsp; &nbsp; &nbsp;&nbsp; | show processlist |+----+------+-----------+----+---------+------+-------+------------------+Now, run the following command with kill and process ID as shown below.# mysqladmin -u root -p kill 5Enter password:+----+------+-----------+----+---------+------+-------+------------------+| Id | User | Host&nbsp; &nbsp; &nbsp; | db | Command | Time | State | Info&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; |+----+------+-----------+----+---------+------+-------+------------------+| 12 | root | localhost |&nbsp; &nbsp; | Query&nbsp;&nbsp; | 0&nbsp; &nbsp; |&nbsp; &nbsp; &nbsp;&nbsp; | 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,1015. 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&nbsp; -u root -p processlist status versionEnter password:+----+------+-----------+----+---------+------+-------+------------------+| Id | User | Host&nbsp; &nbsp; &nbsp; | db | Command | Time | State | Info&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; |+----+------+-----------+----+---------+------+-------+------------------+| 8&nbsp; | root | localhost |&nbsp; &nbsp; | Query&nbsp;&nbsp; | 0&nbsp; &nbsp; |&nbsp; &nbsp; &nbsp;&nbsp; | show processlist |+----+------+-----------+----+---------+------+-------+------------------+Uptime: 3801&nbsp; Threads: 1&nbsp; Questions: 15&nbsp; Slow queries: 0&nbsp; Opens: 15&nbsp; Flush tables: 1&nbsp; Open tables: 8&nbsp; Queries per second avg: 0.003mysqladmin&nbsp; Ver 8.42 Distrib 5.5.28, for Linux on i686Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Server version&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5.5.28Protocol version&nbsp; &nbsp; &nbsp; &nbsp; 10Connection&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Localhost via UNIX socketUNIX socket&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; /var/lib/mysql/mysql.sockUptime:&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 1 hour 3 min 21 secThreads: 1&nbsp; Questions: 15&nbsp; Slow queries: 0&nbsp; Opens: 15&nbsp; Flush tables: 1&nbsp; Open tables: 8&nbsp; Queries per second avg: 0.00316. How to Connect remote mysql serverTo connect remote MySQL server, use the -h (host)&nbsp; with IP Address of remote machine.# mysqladmin&nbsp; -h 172.16.25.126 -u root -p17. How to execute command on remote MySQL serverLet’s say you would like to see the status of remote MySQL server, then the command would be.# mysqladmin&nbsp; -h 172.16.25.126 -u root -p status18. How to start/stop MySQL replication on a slave server?To start/stop MySQL replication on salve server, use the following commands.# mysqladmin&nbsp; -u root -p start-slave# mysqladmin&nbsp; -u root -p stop-slave19. 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&nbsp; -u root -p debugEnter password:20. How to view mysqladmin options and usageTo 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 --helpWe 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.</blockquote>

[QUOTE="y_rangana, post: 13971541, member: 15719"] 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. 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. Installation of MySQL 5.5.28 Server on RHEL/CentOS/Fedora 1. [B]How to set MySQL Root password?[/B] 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. # [I]mysqladmin -u root password YOURNEWPASSWORD[/I] 2. [B]How to Change MySQL Root password?[/B] 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. [I]mysqladmin -u root -p123456 password 'xyz123'[/I] 3. [B]How to check MySQL Server is running?[/B] To find out whether MySQL server is up and running, use the following command. #[I] mysqladmin -u root -p ping[/I] Enter password: mysqld is alive 4. [B]How to Check which MySQL version I am running?[/B] The following command shows MySQL version along with the current running status . # [I]mysqladmin -u root -p version[/I] 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 4.[B] How to Find out current Status of MySQL server?[/B] 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. # [I]mysqladmin -u root -ptmppassword status[/I] 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.[B] How to check status of all MySQL Server Variable’s and value’s?[/B] To check all the running status of MySQL server variables and values, type the following command. The output would be similar to below. # [I]mysqladmin -u root -p extended-status[/I] 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. [B]How to see all MySQL server Variables and Values?[/B] To see all the running variables and values of MySQL server, use the command as follows. #[I] mysqladmin -u root -p variables[/I] 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. [B]How to check all the running Process of MySQL server?[/B] The following command will display all the running process of MySQL database queries. # [I]mysqladmin -u root -p processlist[/I] 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. [B]How to create a Database in MySQL server?[/B] To create a new database in MySQL server, use the command as shown below. # [I]mysqladmin -u root -p create databasename[/I] Enter password: # [I]mysql -u root -p[/I] 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. [B]How to drop a Database in MySQL server?[/B] To drop a Database in MySQL server, use the following command. You will be asked to confirm press ‘y‘. # [I]mysqladmin -u root -p drop databasename[/I] 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. [B]How to reload/refresh MySQL Privileges?[/B] The reload command tells the server to reload the grant tables. The refresh command flushes all tables and reopens the log files. [I] # mysqladmin -u root -p reload; # mysqladmin -u root -p refresh[/I] 12. [B]How to shutdown MySQL server Safely?[/B] To shutdown MySQL server safely, type the following command. [I]mysqladmin -u root -p shutdown[/I] Enter password: You can also use the following commands to start/stop MySQL server. [I] # /etc/init.d/mysqld stop # /etc/init.d/mysqld start[/I] 13. [B]Some useful MySQL Flush commands[/B] Following are some useful flush commands with their description. flush-hosts: Flush all host information from host cache. flush-tables: Flush all tables. flush-threads: Flush all threads cache. flush-logs: Flush all information logs. flush-privileges: Reload the grant tables (same as reload). flush-status: Clear status variables. # [I]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[/I] 14. [B]How to kill Sleeping MySQL Client Process?[/B] Use the following command to identify sleeping MySQL client process. #[I] mysqladmin -u root -p processlist[/I] 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. # [I]mysqladmin -u root -p kill 5[/I] 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. #[I] mysqladmin -u root -p kill 5,10[/I] 15.[B] How to run multiple mysqladmin commands together?[/B] If you would like to execute multiple ‘mysqladmin‘ commands together, then the command would be like this. # [I]mysqladmin -u root -p processlist status version [/I] 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. [B]How to Connect remote mysql server [/B] To connect remote MySQL server, use the -h (host) with IP Address of remote machine. # [I]mysqladmin -h 172.16.25.126 -u root -p[/I] 17. [I]How to execute command on remote MySQL server[/I] Let’s say you would like to see the status of remote MySQL server, then the command would be. # [I]mysqladmin -h 172.16.25.126 -u root -p status[/I] 18. [B]How to start/stop MySQL replication on a slave server? [/B] To start/stop MySQL replication on salve server, use the following commands. # [I]mysqladmin -u root -p start-slave[/I] # [I]mysqladmin -u root -p stop-slave[/I] 19. [B]How to store MySQL server Debug Information to logs?[/B] 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. # [I]mysqladmin -u root -p debug[/I] Enter password: 20. [B]How to view mysqladmin options and usage[/B] 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. #[I] mysqladmin --help[/I] 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. [/QUOTE]

Verification

Hath warak paha keeyada? (hatha wadikireema paha)

Search

Latest ads

Reply to thread