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 %11dn",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 |
+-----+------+-----------+----+---------+------+-------+------------------+
当然还有更多更多的功能， 这里就不再一一列举了，把一些常用的东西记上， 以供查询。