MYSQLADMIN的使用

MYSQL ADMIN的学习
MYSQLADMIN的功能很多, 下面我们主要看下哪些功能是常用的吧。
1. 修改ROOT密码
[root@mysql_source ~]# mysqladmin -uroot -p password 'xg123'
Enter password: 
[root@mysql_source ~]# mysql -uroot -pxg123
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 352
Server version: 5.5.37-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

(testing)root@localhost [(none)]> 

2. 查看当前版本以及数据库的一个大概运行情况:
[root@mysql_source ~]# mysqladmin -uroot -p version
Enter password: 
mysqladmin  Ver 8.42 Distrib 5.1.61, for unknown-linux-gnu on x86_64
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Server version          5.5.37-log
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /tmp/mysql.sock
Uptime:                 13 hours 47 min 0 sec

Threads: 1  Questions: 4805035  Slow queries: 8  Opens: 384  Flush tables: 1  Open tables: 27  Queries per second avg: 96.836

3. 查看MYSQL当前运行情况
[root@mysql_source ~]# mysqladmin -uroot -p status
Enter password: 
Uptime: 49707  Threads: 1  Questions: 4805037  Slow queries: 8  Opens: 384  Flush tables: 1  Open tables: 27  Queries per second avg: 96.667

4. 查看MYSQL是否存活。
[root@mysql_source ~]# mysqladmin -uroot -p ping
Enter password: 
mysqld is alive

5. 查看MYSQL详细的运行情况
[root@mysql_source ~]# mysqladmin -uroot -p extended-status
Enter password: 
+------------------------------------------+-------------+
| Variable_name                            | Value       |
+------------------------------------------+-------------+
| Aborted_clients                          | 1           |
| Aborted_connects                         | 1           |
| Binlog_cache_disk_use                    | 0           |
| Binlog_cache_use                         | 2986419     |
| Binlog_stmt_cache_disk_use               | 0           |
| Binlog_stmt_cache_use                    | 0           |
| Bytes_received                           | 4704268399  |
| Bytes_sent                               | 4471316943  |
| Com_admin_commands                       | 2           |
......还有很多。

这个参数还可以做很多事情,如:
mysqladmin  -uroot -p  -r -i 1 ext |\
awk -F"|" \
"BEGIN{ count=0; }"\
'{ if($2 ~ /Variable_name/ && ++count == 1){\
    print "----------|---------|--- MySQL Command Status --|----- Innodb row operation ----|-- Buffer Pool Read --";\
    print "---Time---|---QPS---|select insert update delete|  read inserted updated deleted|   logical    physical";\
}\
else if ($2 ~ /Queries/){queries=$3;}\
else if ($2 ~ /Com_select /){com_select=$3;}\
else if ($2 ~ /Com_insert /){com_insert=$3;}\
else if ($2 ~ /Com_update /){com_update=$3;}\
else if ($2 ~ /Com_delete /){com_delete=$3;}\
else if ($2 ~ /Innodb_rows_read/){innodb_rows_read=$3;}\
else if ($2 ~ /Innodb_rows_deleted/){innodb_rows_deleted=$3;}\
else if ($2 ~ /Innodb_rows_inserted/){innodb_rows_inserted=$3;}\
else if ($2 ~ /Innodb_rows_updated/){innodb_rows_updated=$3;}\
else if ($2 ~ /Innodb_buffer_pool_read_requests/){innodb_lor=$3;}\
else if ($2 ~ /Innodb_buffer_pool_reads/){innodb_phr=$3;}\
else if ($2 ~ /Uptime / && count >= 2){\
  printf(" %s |%9d",strftime("%H:%M:%S"),queries);\
  printf("|%6d %6d %6d %6d",com_select,com_insert,com_update,com_delete);\
  printf("|%6d %8d %7d %7d",innodb_rows_read,innodb_rows_inserted,innodb_rows_updated,innodb_rows_deleted);\
  printf("|%10d %11d\n",innodb_lor,innodb_phr);\
}}'

----------|---------|--- MySQL Command Status --|----- Innodb row operation ----|-- Buffer Pool Read --
---Time---|---QPS---|select insert update delete|  read inserted updated deleted|   logical    physical
 10:58:17 |        1|     0      0      0      0|     0        0       0       0|         0           0
 10:58:18 |        1|     0      0      0      0|     0        0       0       0|         0           0
 10:58:19 |        1|     0      0      0      0|     0        0       0       0|         0           0
 10:58:20 |        1|     0      0      0      0|     0        0       0       0|         0           0
 10:58:21 |        1|     0      0      0      0|     0        0       0       0|         0           0
 10:58:22 |        1|     0      0      0      0|     0        0       0       0|         0           0
 10:58:23 |        1|     0      0      0      0|     0        0       0       0|         0           0

 可以看到这么些信息。  还是很详细的吧,可以看到QPS, TPS, 操作行以及BUFFER POOL READ的情况  这段内容出自于orczhou的BLOG。

 6. 查看参数值。
[root@mysql_source ~]# mysqladmin -uroot -p var | grep innodb_log_buffer_size
Enter password: 
| innodb_log_buffer_size                            | 8388608 

7. 查看当前的PROCESS
[root@mysql_source ~]# mysqladmin -uroot -p processlist
Enter password: 
+-----+------+-----------+----+---------+------+-------+------------------+
| Id  | User | Host      | db | Command | Time | State | Info             |
+-----+------+-----------+----+---------+------+-------+------------------+
| 363 | root | localhost |    | Query   | 0    |       | show processlist |

8. 创建数据库
[root@mysql_source ~]# mysqladmin -uroot -p create testdb
Enter password: 
[root@mysql_source ~]# mysql -uroot -p -S /tmp/mysql.sock 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 365
Server version: 5.5.37-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

(testing)root@localhost [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| testdb             |
+--------------------+

9. DROP DATABASE;
[root@mysql_source ~]# mysqladmin -uroot -p drop testdb
Enter password: 
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
[root@mysql_source ~]# mysql -uroot -p -S /tmp/mysql.sock 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 367
Server version: 5.5.37-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

(testing)root@localhost [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.01 sec)

10. 加载和刷新权限表。
[root@mysql_source ~]# mysqladmin -uroot -p reload
Enter password: 
[root@mysql_source ~]# mysqladmin -uroot -p refresh
Enter password: 

11. 关闭数据库
[root@mysql_source ~]# mysqladmin -uroot -p shutdown
Enter password: 

[root@mysql_source ~]# ps -elf | grep mysql
0 S root     23135 23043  0  80   0 - 25822 pipe_w 12:39 pts/2    00:00:00 grep mysql

12. KILL会话
[root@mysql_source ~]# mysqladmin -uroot -p processlist
Enter password: 
+-----+------+-----------+----+---------+------+-------+------------------+
| Id  | User | Host      | db | Command | Time | State | Info             |
+-----+------+-----------+----+---------+------+-------+------------------+
| 370 | root | localhost |    | Sleep   | 37   |       |                  |
| 373 | root | localhost |    | Query   | 0    |       | show processlist |
+-----+------+-----------+----+---------+------+-------+------------------+
[root@mysql_source ~]# mysqladmin -uroot -p kill 370
Enter password: 
[root@mysql_source ~]# mysqladmin -uroot -p processlist
Enter password: 
+-----+------+-----------+----+---------+------+-------+------------------+
| Id  | User | Host      | db | Command | Time | State | Info             |
+-----+------+-----------+----+---------+------+-------+------------------+
| 375 | root | localhost |    | Query   | 0    |       | show processlist |
+-----+------+-----------+----+---------+------+-------+------------------+


当然还有更多更多的功能, 这里就不再一一列举了,把一些常用的东西记上, 以供查询。

  

posted @ 2014-06-11 14:23 SMALL-D 阅读(...) 评论(...)  编辑 收藏