- 当前位置：首页 >系统运维 >15个常用的MySQL使用管理命令示例
15个常用的MySQL使用管理命令示例
发布时间：2025-11-04 00:16:28 来源：云智核 作者：系统运维
-
复制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> How to check whether MySQL Server is up and running? # MySQLadmin -u root -p ping Enter password: MySQLd is alive3. How do I find out what version of MySQL I am running? Apart from giving the ‘Server version’, this command also displays the current status of the MySQL 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.254. 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.25The status command displays the following information: 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 waas 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 | +----+------+-----------+----+---------+------+-------+------------------+You can use this command effectively to debug any performance issue and identify the query that is causing problems, by running the command automatically every 1 second as shown below. # 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? # 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 tables in a database, total number of columns, row, column types, indexes etc., use the MySQLshow command that we discussed in our previous articles. 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;Refresh command will flush all the tables and close/open log files. # MySQLadmin -u root -ptmppassword refresh11. What is the safe method to shutdown the MySQL server? # MySQLadmin -u root -ptmppassword shutdown # MySQL -u root -ptmppassword ERROR 2002 (HY000): Cant connect to local MySQL server through socket /var/lib/MySQL/MySQL.sockNote: 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-threadsflush-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? First identify the hanging MySQL client process using the processlist command. # 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 | +----+------+-----------+----+---------+------+-------+------------------+Now, use the kill command and pass the process_id as shown below. To kill multiple process you can pass comma separated process id’s. # 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 TO15. How to combine multiple MySQLadmin commands together? In the example below, you can combine process-list, status and version command to get all the output together as shown below. # 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 secYou can also use the short form as shown below:
# MySQLadmin -u root -ptmppassword pro stat verUse the option -h,
- Copyright © 2025 Powered by 15个常用的MySQL使用管理命令示例,云智核 滇ICP备2023006006号-28sitemap