文档章节

15个实用的管理mysql的mysqladmin命令

Moks角木
 Moks角木
发布于 2015/11/24 21:28
字数 1219
阅读 286
收藏 2

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?

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.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

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

+----+------+-----------+----+---------+------+-------+------------------+

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

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 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: 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. 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  -u root -ptmppassword pro stat ver


© 著作权归作者所有

Moks角木
粉丝 31
博文 252
码字总数 171514
作品 0
静安
架构师
私信 提问
MySQL性能调优与架构设计--mysql自带工具使用

1、mysql: 个人的提示符定义: "\u@\h : \d \r:\m:\s> ",显示效果:“sky@localhost : test 04:25:45>” 2、mysqladmin Usage: mysqladmin [OPTIONS] command command ... mysqadmin,顾名......

ivan-Zhao
2015/10/17
36
0
Mysql客户端管理工具mysqladmin用法

Mysqladmin 适合于linux和windows系统 linux下:mysqladmin -u[username] -p[password] status windows下:先在安装目录找到mysqladmin.exe,然后在dos界面下change到这个目录,执行 mysqla...

Junn
2013/07/29
1K
0
MySQL客户端程序及一些使用小工具介绍

下面简单列出了MySQL客户端程序和实用工具: myisampack:压缩MyISAM表以产生更小的只读表的一个工具 mysql:交互式输入SQL语句或从文件以批处理模式执行它们的命令行工具 mysqlaccess:检查...

我就叫你女王吧
2016/05/16
33
0
mysql之编译安装mysql,以及mysql mysqladmin工具的使用说明。

mysql的编译安装不是使用./configure 而是使用cmake,这点大家需要注意: 一:安装好编译环境,三个包组:“Development tools” “Desktop platform Development” “Server platform Devel...

chinahaike
2014/04/09
0
0
MySQL:常见使用问题

1、Linux 上安装MySQL 安装步骤: 1)解压 tar.gz文件 shell> tar -zxvf mysql-5.7.9-linux-glibc2.5-x86_64.tar.gz 2)初始化默认数据库(mysql、performaceschema、sys、informationschema......

胡壮壮
2017/05/02
0
0

没有更多内容

加载失败,请刷新页面

加载更多

mysql-connector-java升级到8.0后保存时间到数据库出现了时差

在一个新项目中用到了新版的mysql jdbc 驱动 <dependency>     <groupId>mysql</groupId>     <artifactId>mysql-connector-java</artifactId>     <version>8.0.18</version> ......

ValSong
今天
5
0
Spring Boot 如何部署到 Linux 中的服务

打包完成后的 Spring Boot 程序如何部署到 Linux 上的服务? 你可以参考官方的有关部署 Spring Boot 为 Linux 服务的文档。 文档链接如下: https://docs.ossez.com/spring-boot-docs/docs/r...

honeymoose
今天
6
0
Spring Boot 2 实战:使用 Spring Boot Admin 监控你的应用

1. 前言 生产上对 Web 应用 的监控是十分必要的。我们可以近乎实时来对应用的健康、性能等其他指标进行监控来及时应对一些突发情况。避免一些故障的发生。对于 Spring Boot 应用来说我们可以...

码农小胖哥
今天
9
0
ZetCode 教程翻译计划正式启动 | ApacheCN

原文:ZetCode 协议:CC BY-NC-SA 4.0 欢迎任何人参与和完善:一个人可以走的很快,但是一群人却可以走的更远。 ApacheCN 学习资源 贡献指南 本项目需要校对,欢迎大家提交 Pull Request。 ...

ApacheCN_飞龙
今天
5
0
CSS定位

CSS定位 relative相对定位 absolute绝对定位 fixed和sticky及zIndex relative相对定位 position特性:css position属性用于指定一个元素在文档中的定位方式。top、right、bottom、left属性则...

studywin
今天
9
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部