15个mysql使用管理命令
来源:互联网 发布:ubuntu 文件夹权限 编辑:程序博客网 时间:2024/06/17 23:02
In all the 15 mysqladmin command-line examples below, tmppassword is used as the MySQL root user password. Please change this to your MySQL root password.
1. How to change the MySQL root user password?
# mysqladmin -u root -ptmppassword password 'newpassword'# mysql -u root -pnewpasswordWelcome to the MySQL monitor. Commands end with ; or /g.Your MySQL connection id is 8Server 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 pingEnter password:mysqld is alive
3. 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 versionmysqladmin Ver 8.42 Distrib 5.1.25-rc, for redhat-linux-gnu on i686Copyright (C) 2000-2006 MySQL ABThis software comes with ABSOLUTELY NO WARRANTY. This is free software,and you are welcome to modify and redistribute it under the GPL licenseServer version 5.1.25-rc-communityProtocol version 10Connection Localhost via UNIX socketUNIX socket /var/lib/mysql/mysql.sockUptime: 107 days 6 hours 11 min 44 secThreads: 1 Questions: 231976 Slow queries: 0 Opens: 17067Flush tables: 1 Open tables: 64 Queries per second avg: 0.25
4. What is the current status of MySQL server?
# mysqladmin -u root -ptmppassword statusUptime: 9267148Threads: 1 Questions: 231977 Slow queries: 0 Opens: 17067Flush tables: 1 Open tables: 64 Queries per second avg: 0.25
The 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 -ptmppasswordWelcome to the MySQL monitor. Commands end with ; or /g.Your MySQL connection id is 705Server 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 commandthat we discussed in our previous articles.
9. How to Delete/Drop an existing MySQL database?
# mysqladmin -u root -ptmppassword drop testdbDropping 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] yDatabase “testdb” dropped# mysql -u root -ptmppasswordWelcome to the MySQL monitor. Commands end with ; or /g.Your MySQL connection id is 707Server 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 refresh
11. What is the safe method to shutdown the MySQL server?
# mysqladmin -u root -ptmppassword shutdown# mysql -u root -ptmppasswordERROR 2002 (HY000): Can't connect to local MySQL serverthrough 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?
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-slaveSlave stopped# mysqladmin -u root -ptmppassword start-slavemysqladmin: 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?
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: 3135Threads: 1 Questions: 80 Slow queries: 0 Opens: 15 Flush tables: 3Open tables: 0 Queries per second avg: 0.25mysqladmin Ver 8.42 Distrib 5.1.25-rc, for redhat-linux-gnu on i686Copyright (C) 2000-2006 MySQL ABThis software comes with ABSOLUTELY NO WARRANTY. This is free software,and you are welcome to modify and redistribute it under the GPL licenseServer version 5.1.25-rc-communityProtocol version 10Connection Localhost via UNIX socketUNIX socket /var/lib/mysql/mysql.sockUptime: 52 min 15 sec
You can also use the short form as shown below:
# mysqladmin -u root -ptmppassword pro stat ver
Use the option -h, to connect to a remote MySQL server and execute the mysqladmin commands as shown below.
# mysqladmin -h 192.168.1.112 -u root -ptmppassword pro stat ver
- 15个mysql使用管理命令
- 15个mysql使用管理命令
- 15个mysql使用管理命令
- 15个实用的管理mysql的mysqladmin命令
- 15个实用的管理mysql的mysqladmin命令
- 15个用于管理MySQL服务器mysqladmin命令
- 使用systemctl命令管理MySQL服务
- mysql 管理命令
- mysql 用户管理命令
- mysql基本管理命令
- MySQL常用管理命令
- mysql管理日常命令
- MySQL用户管理命令
- mysql常用管理命令
- Mysql 基本管理命令
- MySQL cluster多个管理节点和多个sql节点的配置和相关命令
- MySQL cluster多个管理节点和多个sql节点的配置和相关命令
- MySQL常用用户管理命令
- WebService 发布不成功,显示无法找到该页错误 404的解决方法
- vmware 网络配置实例二 windows host + linux guest
- 2-26
- 以虚线分隔List项目(drawRowBackground应用一例)
- 写着玩的zip解压和压缩zip文件
- 15个mysql使用管理命令
- 归并排序
- 欲为Java技术大牛所需的25个学习要点
- 纸上得来终觉浅,绝知此事要躬行
- 逆向,真是一件苦差事
- DirectDraw程序设计指南
- Flex Tutorial - Image ItemRenderer
- Ext扩展组件介绍之一--Ext.Drawing.Surface绘图组件(vml/svg)
- 首届营养健康教育机构年会召开