MySQL分为客户端工具和非客户端工具,也有客户端命令和服务器端的命令。MySQL启动时会依次读取以下四个文件:
<code> /etc/my.cnf /etc/mysql/my.cnf $MYSQL_HOME/my.cnf ∼/.my.cnf </code>
如果有重复选项时,则以最后依次读取到的为准。
客户端工具会读取配置文件中[client] 指定的选项。
MySQL客户端工具:
<code> mysql mysqldump mysqladmin mysqlcheck mysqlimport </code>
MySQL非客户端工具:
<code> myisamchk myisampack </code>
mysql 命令的使用方法,其是在shell命令行中使用的:
<code> --user, (简写为-u)+用户 --host,(简写为-h)+主机地址 --password, (简写为-p)+密码 --port PORT --protocol {tcp|socket|pipe|memory} --database DATABASE,(使用-D指定默认数据库 ,然后使用 select database();查看默认的数据库); </code>
MySQ在本机内以mysql.sock通信,只要指定外网地址,即使用了-h选项,则以tcp/ip通信。
以下为MySQL客户端命令介绍:
<code> mysql> help For information about MySQL products and services, visit: http://www.mysql.com/ For developer information, including the MySQL Reference Manual, visit: http://dev.mysql.com/ To buy MySQL Enterprise support, training, or other products, visit: https://shop.mysql.com/ List of all MySQL commands: Note that all text commands must be first on line and end with ';' ? (\?) Synonym for `help'. clear (\c) Clear the current input statement. connect (\r) Reconnect to the server. Optional arguments are db and host. delimiter (\d) Set statement delimiter. edit (\e) Edit command with $EDITOR. ego (\G) Send command to mysql server, display result vertically. exit (\q) Exit mysql. Same as quit. go (\g) Send command to mysql server. help (\h) Display this help. nopager (\n) Disable pager, print to stdout. notee (\t) Don't write into outfile. pager (\P) Set PAGER [to_pager]. Print the query results via PAGER. print (\p) Print current command. prompt (\R) Change your mysql prompt. quit (\q) Quit mysql. rehash (\#) Rebuild completion hash. source (\.) Execute an SQL script file. Takes a file name as an argument. status (\s) Get status information from the server. system (\!) Execute a system shell command. tee (\T) Set outfile [to_outfile]. Append everything into given outfile. use (\u) Use another database. Takes database name as argument. charset (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets. warnings (\W) Show warnings after every statement. nowarning (\w) Don't show warnings after every statement. For server side help, type 'help contents' </code>
MySQL客户端命令:(不需要语句的结束符)
<code> \c: 提前终止语句执行 \g: 无论语句结束符是什么,直接将此语句送至服务器端执行; \G: 无论语句结束符是什么,直接将此语句送到服务器端执行,而且结果以竖排方式显示; \! COMMAND: 执行shell命令 \W: 语句执行结束后显示警告信息; \#: 对新建的对象,支持补全功能; </code>
MySQL服务器语句:有语句结束符,默认为‘ ; ’可以使用\d 自定义语句结束符。
下面介绍shell命令行的mysqladmin命令:
<code> mysqladmin [options] command [arg] [command [arg]] ... # mysqladmin -uroot -p password 'NEW_PASS' ,下面为重要的可使用选项: create DATABASE drop DATABASE ping processlist status --sleep N:显示频率 --count N: 显示多个状态 extended-status: 显示状态变量 variables: 显示服务器变量 flush-privileges: 让mysqld重读授权表, 等同于reload; flush-status: 重置大多数的服务器状态变量 flush-logs: 二进制和中继日志滚动 flush-hosts: 清除主机的缓存及连接的错误数 refresh: 相当于同时执行flush-hosts和flush-logs shutdown: 关闭mysql服务器进程 version: 服务器版本及当前状态信息; start-slave: 启动复制,启动从服务器复制线程; SQL thread IO thread stop-slave: 关闭复制; [root@www ~]# mysqladmin create feiyu; [root@www ~]# mysqladmin drop feiyu; 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 'feiyu' database [y/N] y Database "feiyu" dropped [root@www ~]# mysqladmin status --sleep 2 --count 2 #隔2秒共显示2次 [root@www ~]# mysqladmin status #mysql服务器运行状态 Uptime: 3590 Threads: 3 Questions: 75 Slow queries: 0 Opens: 39 Flush tables: 1 Open tables: 32 Queries per second avg: 0.020 </code>