15個實用的管理mysql的mysqladmin命令

15 Practical Usages of Mysqladmin Command For Administering MySQL Server 

  

by Ramesh Natarajan on January 14, 2009

---

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?

# 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

• 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 was 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 | +----+------+-----------+----+---------+------+-------+------------------+

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

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;

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

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

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

# 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

# mysqladmin  -u root -ptmppassword pro stat ver

# mysqladmin  -u root -ptmppassword pro stat ver