MYSQL-TOOLS

Comparison of graphical tools for MySQL

Home Articles MySQL 15 practical examples of using commands Mysqladmin to administer a MySQL server

15 practical examples of using commands Mysqladmin to administer a MySQL server

Print PDF
User Rating: / 12
PoorBest 

15 examples mysqladminIn the 15 examples of the use mysqladmin command below, using the password root Mysql tmppassword. Change it to your password

1. How to change the root password for Mysql?

# 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 the MySQL server is running using mysqladmin?

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

3. How do I see what version of MySQL is running using mysqladmin?

This command also displays the current status of the 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. How to see the status of the MySQL server using mysqladmin?

# 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

Status command displays the following informatsiyucommand displays the following information:

  • Uptime: uptime in seconds
  • Threads: Total number of clients connected to the server.
  • Questions: The total number of requests to the server since startup.
  • Slow queries: Total number of queries, whose execution time was more than the value of long_query_time.
  • 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 do I view MySQL status variables and their current value using mysqladmin?

# 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 the MySQL server system variables and their values using mysqladmin?

# 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 running processes / queries in the database mysql using mysqladmin?

# 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 to effectively debug any performance problems and determine the process that causes problems by running on autoupdate every second.

# 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 database a MySQL using mysqladmin?

# 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 the tables in the database, the total number of columns, rows, indexes and so on .... use the command mysqlshow.

9. How do I delete the existing database MySQL using mysqladmin?

# 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 overload all the privileges and rights to the table using mysqladmin?

# mysqladmin -u root -ptmppassword reload;

Refresh command resets the table and close / open the log files.

# mysqladmin -u root -ptmppassword refresh

11. How to perform a safe way to stop MySQL server using mysqladmin?

# 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'

You can use the command "/ etc / rc.d / init.d / mysqld stop" to stop the server. To start the run "/ 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: Reset all the information in the cache hosts.
  • Flush-privileges: Reload right.
  • Flush-status: Clear status variables.
  • Flush-threads: Flush the thread cache.

13. How to kill hung client process to MySQL using mysqladmin?

First, define the hanging process using the command processlist.

# 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 |
+----+------+-----------+----+---------+------+-------+------------------+

Then use the kill command and the desired process_id. To complete several processes separate process id commas.

# 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 or stop the replication of MySQL on the slave-server using mysqladmin?

# 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 several commands mysqladmin together ?

In the example below combined team process-list, status and version for the complete withdrawal of the status of the server.

# 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 of writing:

# mysqladmin  -u root -ptmppassword pro stat ver

Use the-h option to connect to remote MySQL server and execute the command.

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

Useful articles:

Mysqladmin is a client for performing administrative operations!

Leave a comment!

 

Comments 

 
-1 #1 2012-04-02 06:26
Sounds good...
Quote
 

Add comment


Security code
Refresh

mysql tools

How create data source in Visual Studio with data from My SQL tables with FK. more...

'mysql.proc' doesn't exist fixed problem

_

Table 'mysql.proc' doesn't exist. Fix error 1146 Table doesn't exist here...