mysqladmin命令详解mysqladmin 管理命令详细应用
分类： MYSQL 2009-10-02 15:18 7061人阅读 评论(0) 收藏 举报
数据库服务器commandvariablescharacteroutput
mysqladmin工具的使用格式：
mysqladmin [option] command [commandoption] command ......
option 选项：
-c number 自动运行次数统计，必须和 -i 一起使用
-i number 间隔多长时间重复执行
每个两秒查看一次服务器的状态，总共重复5次。
./mysqladmin -uroot -p -i 2 -c 5 status
-#,--debug[=name] Output debug log. Oftenthis is 'd:t:o,filename'.
-f, --force Don't ask for confirmation on dropdatabase; with
multiple commands, continue even if an error occurs. 禁用错误，drop 数据库时不提示，执行多条命令时出错继续执行
-C,--compress Use compression inserver/client protocol.
--character-sets-dir=name 字符集所在目录
Directory where character sets are.
--default-character-set=name 设置默认字符集
Set the default character set.
-?, --help Display this help and exit. 显示帮助
-h,--host=name Connect to host. 连接的主机名或iP
-p,--password[=name] 登录密码，如果不写于参数后，则会提示输入
Password to use when connecting to server. If password is
not given it's asked from the tty.
-P, --port=# Port number to use for connection. 指定数据库端口
--protocol=name The protocol of connection(tcp,socket,pipe,memory). 指定连接协议
-r,--relative Show difference betweencurrent and previous values when
used with -i. Currently works only with extended-status. 显示前后变化的值，必须结合- i
./mysqladmin -uroot -p -i 2 -r extended-status
假如此处显示的uptime 将永远是2，因为与之前取的结果比只相差2.
-O, --set-variable=name
Change the value of a variable. Please note that this
option is deprecated; you can set variables directly with
--variable-name=value.修改变量的值，该选项已经不再使用，请使用--variable-name=value 的方式修改变量值
-s, --silent Silently exit if one can't connect toserver.
-S, --socket=name Socket file to use for connection. 指定socket file
-i,--sleep=# Execute commands againand again with a sleep between. 间隔一段时间执行一次
-u,--user=name User for login if notcurrent user.登录数据库用户名
-v,--verbose Write more information. 写更多的信息
-V,--version Output versioninformation and exit. 显示版本
./mysql -uroot -p-V
-E,--vertical Print output vertically.Is similar to --relative, but
prints output vertically.
-w,--wait[=#] Wait and retry ifconnection is down. 如果连接断开，等待w 指定的时间后重试
--connect_timeout=#
--shutdown_timeout=#
1、查看服务器的状况：status
./mysql -uroot -pstatus
显示结果：
Uptime:4883162 Threads: 1 Questions: 86 Slow queries: 0 Opens: 0 Flush tables: 1 Open tables: 18 Queries per second avg: 0.000
2.修改root 密码：
mysqladmin -uroot -poldpassword password 'newpassword'
3.检查mysqlserver是否可用：
mysqladmin -uroot-p ping
显示结果：
mysqld is alive
4.查询服务器的版本
mysqladmin -uroot-p version
5.查看服务器状态的当前值：
mysqladmin -uroot -pextended-status
显示结果：
+-----------------------------------+----------+
| Variable_name | Value |
+-----------------------------------+----------+
| Aborted_clients | 12 |
| Aborted_connects | 300 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 23608 |
| .......
|Threads_created | 3 |
| Threads_running | 1 |
| Uptime | 4884294 |
+-----------------------------------+----------+
6.查询服务器系统变量值：
mysqladmin -uroot -p variables
显示结果：
+---------------------------------+----------------------------------------+
| Variable_name | Value |
+---------------------------------+----------------------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | /usr/local/mysql/ |
....
7.显示服务器所有运行的进程：
mysqladmin -uroot -p processlist
mysqladmin -uroot -p-i 1processlist 每秒刷新一次
显示结果：
+-----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+------+-----------+----+---------+------+-------+------------------+
| 331 | root | localhost | |Query | 0 | | show processlist |
+-----+------+-----------+----+---------+------+-------+------------------+
8.创建数据库
./mysqladmin -uroot -p createdaba-test
验证：
./mysql -uroot -p 登录后：show databases;
9.显示服务器上的所有数据库
./mysqlshow -uroot -p
10.显示数据库daba-test下有些什么表：
./mysqlshow -uroot -p daba-test
11.统计daba-test 下数据库表列的汇总
./mysqlshow -uroot -p daba-test-v
12.统计daba-test 下数据库表的列数和行数
./mysqlshow -uroot -p daba-test-v -v
13. 删除数据库 daba-test
./mysqladmin -uroot -p dropdaba-test
14. 重载权限信息
./mysqladmin -uroot -p reload
15.刷新所有表缓存，并关闭和打开log
./mysqladmin -uroot -p refresh
16.使用安全模式关闭数据库
./mysqladmin -uroot -p shutdown
You can also use “/etc/rc.d/init.d/mysqld stop” to shutdown the server. To startthe server, execute “/etc/rc.d/init.d/mysqlstart”--如果不是以服务来运行则这两条命令无效
17.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 asreload).
flush-status: Clear status variables.
flush-threads: Flush the thread cache.
18 .mysqladmin 执行kill 进程：
./mysqladmin-uroot -p processlist
./mysqladmin-uroot -p kill idnum
19.停止和启动MySQL replication on a slave server
mysqladmin -u root -p stop-slave
mysqladmin -u root -p start-slave
20 .同时执行多个命令
mysqladmin -u root -p processstatus version
