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