You are here
马哥 32_04 _mysql系列之四mysql客户端工具的使用 有大用
mysql
--user, -u
--host, -h
--password, -p
--port
--protocol
--database DATABASE,-D (连接数据库后,直接设定默认数据库)
其它选项
--compress 语句先压缩,再发送(或再返回),节约带宽
--ssl-ca ca证书文件
--ssl-capath 多个ca证书的目录
--ssl-cert 自己的证书
--ssl-cipher 加密算法列表
--ssl-key 自己的密钥文件
--ssl-verify-server-cert 验证服务器端证书的
(以后主从复制的时候,要用到ssl证书)
[root@mail mydb]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.28-log Source distribution
Copyright (c) 2000, 2012, 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.
mysql> select database(); #没有默认数据库
+------------+
| database() |
+------------+
| NULL |
+------------+
1 row in set (0.00 sec)
mysql>
mysql> use mydb; #此时是使用这个为默认数据库
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| mydb |
+------------+
1 row in set (0.00 sec)
mysql>
[root@mail mydb]# mysql -D mydb
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.28-log Source distribution
Copyright (c) 2000, 2012, 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.
mysql>
mysql> select database(); #默认为 mydb 了
+------------+
| database() |
+------------+
| mydb |
+------------+
1 row in set (0.00 sec)
mysql>
mysql>
交互式模式
批处理模式(mysql脚本模式)
(比如 mysql < init.sql ?)
[root@mail mydb]# vim test.sql
create database testdb;
create table testdb.tb1(id int,name char(20));
[root@mail ~]# pwd
/root
[root@mail ~]# vim test.sql
create database testdb;
create table testdb.tb1(id int,name char(20));
[root@mail mydb]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.5.28-log Source distribution
Copyright (c) 2000, 2012, 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.
mysql> \h
For information about MySQL products and services, visit:
For developer information, including the MySQL Reference Manual, visit:
To buy MySQL Enterprise support, training, or other products, visit:
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'
mysql>
mysql> \. /root/test.sql ( \. 相当于 source )
Query OK, 1 row affected (0.00 sec)
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
| testdb |
+--------------------+
6 rows in set (0.00 sec)
mysql>
mysql> drop database testdb;
Query OK, 1 row affected (0.06 sec)
mysql>
mysql> show databases; # testdb 没了
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql>
mysql> quit
Bye
[root@mail ~]#
[root@mail ~]# mysql < test.sql # 同样创建了数据库和表
[root@mail ~]#
[root@mail ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.5.28-log Source distribution
Copyright (c) 2000, 2012, 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.
mysql> show databases; # 看到了 testdb
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
| testdb |
+--------------------+
6 rows in set (0.00 sec)
mysql>
mysql> use testdb
Database changed
mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| tb1 |
+------------------+
1 row in set (0.00 sec)
mysql>
mysql> delimiter // # 表示使用双斜线作为语句结束符
mysql> show databases
-> ;
-> // # 使用双斜线才算语句的结束
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
| testdb |
+--------------------+
6 rows in set (0.00 sec)
mysql> show databases // # 使用双斜线才是语句的结束
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
| testdb |
+--------------------+
6 rows in set (0.00 sec)
mysql> \d ; #结束符换回分号了
mysql> show databases // #此时不能结束
-> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '//' at line 1
mysql> show databases ; #此时才算结束
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
| testdb |
+--------------------+
6 rows in set (0.00 sec)
mysql> status # 客户端命令
--------------
mysql Ver 14.14 Distrib 5.5.28, for Linux (i686) using readline 5.1
Connection id: 8
Current database: testdb
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.5.28-log Source distribution
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /usr/local/mysql/mysql.sock
Uptime: 1 hour 10 min 35 sec
Threads: 1 Questions: 45 Slow queries: 0 Opens: 37 Flush tables: 1 Open tables: 27 Queries per second avg: 0.010
--------------
mysql> select user,host,fr\c #\c 中止语句的执行,,相当于 shell 中 ctrl+c快捷键
mysql> select user,host from user;\c # 如果 \c 前面有分号(语句结束符),仍然会执行,
+------+-----------------+
| user | host |
+------+-----------------+
| root | 127.0.0.1 |
| root | localhost |
| root | mail.magedu.com |
+------+-----------------+
3 rows in set (0.01 sec)
mysql> \r (或 connect 重新连接到数据库)(\r 就是 reconnect 的简写)
Connection id: 9
Current database: mysql
mysql> \d // # 这里指定语句结束符,
mysql> select * from user; # 这里忘记了语句结束符
-> .
->\c
mysql> select * from user\g # 小写g 一定会执行
mysql 命令两类
客户端命令:不是发送到服务器端执行后的结果,而是与服务器端交互获取到的数据
(客户端命令 有 use , \c (clear) 提请中止语句的执行,
\g 无论语句结束符是什么,直接将此语句送到服务器端执行
\G 无论语句结束符是什么,直接将此语句送到服务器端执行,而且结果以竖排方式显示 )
服务器端命令: 服务器端进行执行,然后返回结果;有语句结束符,默认为 分号(;)
\d ( 或 delimiter ): 定义语句结束符
\p (print)显示当前正在执行的命令
\q (quit) 退出
\. (source) 引入这个文件并执行
\s (status) 从服务器返回状态信息
\! (system) 执行shell命令
\W (warnings) 语句执行结束后显示警告信息
\w (nowarning) 语句执行结束后不显示警告信息
\R (prompt) 修改mysql的提示符 > (修改小于号),没有多大意义
\# 对新建的对象,支持补全功能
[root@mail ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.5.28-log Source distribution
Copyright (c) 2000, 2012, 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.
mysql> \p
--------------
--------------
mysql> \s
--------------
mysql Ver 14.14 Distrib 5.5.28, for Linux (i686) using readline 5.1
Connection id: 12
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.5.28-log Source distribution
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /usr/local/mysql/mysql.sock
Uptime: 5 days 16 hours 31 min 19 sec
Threads: 2 Questions: 63 Slow queries: 0 Opens: 37 Flush tables: 1 Open tables: 27 Queries per second avg: 0.000
--------------
mysql>
mysql>
mysql> \! ls /root #执行shell命令
1.sh mbox
anaconda-ks.cfg mhash-0.9.2-6.el5.remi.i386.rpm
apr-1.4.6 mhash-devel-0.9.2-6.el5.remi.i386.rpm
apr-1.4.6.tar.bz2 mysql-5.5.28
apr-util-1.4.1 mysql-5.5.28-linux2.6-i686.tar.gz
apr-util-1.4.1.tar.bz2 mysql-5.5.28.tar.gz
mysql>
/*
sdfsdferwrw #多行注释
*/
mysql>新语句
->语句未结束
'>单引号未结束
">双引号未结束
`>反引号未结束
/*> 多行注释未结束
mysql
命令补全:
名称补全 (当然需要数据库名,表名,字段名载入内存,会导致延迟,会很慢;至少连接数据库时会很慢)
禁用自动补全功能,下面选项三个都能禁用
-A
--no-auto-rehash
--disable-auto-rehash
对新项目启用,自动补全功能,下面两个都能启用
\#
rehash
[root@mail ~]# mysql # mysql默认给我们做了 -A 选项
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.5.28-log Source distribution
Copyright (c) 2000, 2012, 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.
mysql> use mysql
Database changed
mysql> rehash # 此时可以自动补全了
mysql>
mysql 显示格式
--html 或 -H:显示为 html 格式
--xml 或 -X: 显示为 xml格式
[root@mail ~]# mysql --html
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.5.28-log Source distribution
Copyright (c) 2000, 2012, 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.
mysql> use mysql;
Database changed
mysql> select User,Host from user; #html格式的
<TABLE BORDER=1><TR><TH>User</TH><TH>Host</TH></TR><TR><TD>root</TD><TD>127.0.0.1</TD></TR><TR><TD>root</TD><TD>localhost</TD></TR><TR><TD>root</TD><TD>mail.magedu.com</TD></TR></TABLE>3 rows in set (0.04 sec)
mysql>
服务器端命令获取帮助:
help COMMAND (KEYWORD) (服务器端的命令的帮助命令)
[root@mail ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.5.28-log Source distribution
Copyright (c) 2000, 2012, 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.
mysql> help select; #获取 select 帮助命令
Name: 'SELECT'
Description:
Syntax:
SELECT
[ALL | DISTINCT | DISTINCTROW ]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr [, select_expr ...]
[FROM table_references
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[PROCEDURE procedure_name(argument_list)]
[INTO OUTFILE 'file_name'
[CHARACTER SET charset_name]
export_options
| INTO DUMPFILE 'file_name'
| INTO var_name [, var_name]]
[FOR UPDATE | LOCK IN SHARE MODE]]
SELECT is used to retrieve rows selected from one or more tables, and
can include UNION statements and subqueries. See [HELP UNION], and
http://dev.mysql.com/doc/refman/5.5/en/subqueries.html.
The most commonly used clauses of SELECT statements are these:
o Each select_expr indicates a column that you want to retrieve. There
must be at least one select_expr.
o table_references indicates the table or tables from which to retrieve
rows. Its syntax is described in [HELP JOIN].
o The WHERE clause, if given, indicates the condition or conditions
that rows must satisfy to be selected. where_condition is an
expression that evaluates to true for each row to be selected. The
statement selects all rows if there is no WHERE clause.
In the WHERE expression, you can use any of the functions and
operators that MySQL supports, except for aggregate (summary)
functions. See
http://dev.mysql.com/doc/refman/5.5/en/expressions.html, and
http://dev.mysql.com/doc/refman/5.5/en/functions.html.
SELECT can also be used to retrieve rows computed without reference to
any table.
URL: http://dev.mysql.com/doc/refman/5.5/en/select.html #帮助网址
mysql>
mysql> help create index # 创建 index 命令帮助
Name: 'CREATE INDEX'
Description:
Syntax:
CREATE [ONLINE|OFFLINE] [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[index_type]
ON tbl_name (index_col_name,...)
[index_option] ...
index_col_name:
col_name [(length)] [ASC | DESC]
index_type:
USING {BTREE | HASH}
index_option:
KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_name
| COMMENT 'string'
CREATE INDEX is mapped to an ALTER TABLE statement to create indexes.
See [HELP ALTER TABLE]. CREATE INDEX cannot be used to create a PRIMARY
KEY; use ALTER TABLE instead. For more information about indexes, see
http://dev.mysql.com/doc/refman/5.5/en/mysql-indexes.html.
URL: http://dev.mysql.com/doc/refman/5.5/en/create-index.html #帮助网址
mysql>
mysqladmin 也是一个客户端命令
mysqladmin [options] command [arg] [command [arg]] ...
# mysqladmin -uroot -p password 'NEW_PASS' #-uroot -p 是选项 password就是一个子命令, 'NEW_PASS'是子命令的参数
mysqladmin 的子命令
create database:创建数据库的
drop database:删除数据库的
ping :看mysql服务器是否在线
processlist : 进程列表
status : 状态信息; 状态变量,统计工作当中的统计数据的
--sleep N: 多少秒显示一次(显示频率)
--count N: 总共显示多少次
extended-status : 扩展的状态 显示状态变量及其值 (检查故障等用到它)
variables : 显示服务器变量;用来定义服务器的工作属性
flush-privileges: 让mysqld重读授权表 (刷新授权表)
flush-tables : 关闭所有已打开的表
flush-threads: 重置线程池(线程缓存) (把空闲线程清除掉)
flush-status: 重置绝大多数的服务器状态变量 我们不般不会随便flush
flush-logs: 做二进制和中继日志滚动的,(对于错误日志不滚动,只是关闭再打开)
flush-hosts: 登录失败次数太多被阻止,用这个命令重置清空下阻止的主机,主机就可以再次登录尝试了
kill : 杀死内部线程的(线程挂起,无法终止,就kill 杀死它)
reload : 相当于 flush-privileges
refresh : 相当于 flush-hosts 加上 flush-logs
shutdown : 关闭mysql服务器进程的
version: 显示mysql版本号及相关的状态信息的
start slave:启动从服务器的复制线程 (启动复制功能)
(一般是两个,SQL thread,IO thread)
stop slave:停止从服务器的复制线程 (停止复制功能)
mysqladmin # 也可以直接读取家目录下的 .my.cnf 文件里的配置选项 ( 即 /root/.my.cnf )
[root@mail ~]# mysqladmin create hellodb
[root@mail ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.5.28-log Source distribution
Copyright (c) 2000, 2012, 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.
mysql> show databases; # 可以看到 hellodb
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mydb |
| mysql |
| performance_schema |
| test |
| testdb |
+--------------------+
7 rows in set (0.00 sec)
mysql>
[root@mail ~]# mysqladmin ping #看mysql服务器是否在线
mysqld is alive
[root@mail ~]#
[root@mail ~]# mysqladmin -u root -p -h 192.168.0.103 ping
Enter password:
mysqladmin: connect to server at '192.168.0.103' failed
error: 'Can't connect to MySQL server on '192.168.0.103' (111)'
Check that mysqld is running on 192.168.0.103 and that the port is 3306.
You can check this by doing 'telnet 192.168.0.103 3306'
[root@mail ~]# mysqladmin -u root -p -h 192.168.0.103 --port 3307 ping
Enter password:
mysqladmin: connect to server at '192.168.0.103' failed
error: 'Host '192.168.0.75' is not allowed to connect to this MySQL server'
[root@mail ~]# iptables -F
[root@mail ~]# mysqladmin -u root -p -h 192.168.0.103 --port 3307 ping
Enter password:
mysqladmin: connect to server at '192.168.0.103' failed
error: 'Host '192.168.0.75' is not allowed to connect to this MySQL server'
[root@mail ~]#
[root@mail ~]# mysqladmin processlist # 进程列表
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+----+---------+------+-------+------------------+
| 19 | root | localhost | | Query | 0 | | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+
[root@mail ~]#
[root@mail ~]# mysqladmin status
Uptime: 505707 Threads: 1 Questions: 112 Slow queries: 0 Opens: 51 Flush tables: 1 Open tables: 41 Queries per second avg: 0.000
#Uptime 启动时间
#Threads 线程数
#Questions 己经完成的查询树
#Slow queries 慢查询的次数
#Opens 打开的文件数
#Flush tables # flush 的表数
#Open tables 打开的表数
#Queries per second avg 平均每秒钟的查询数(应该是每次查询花费的秒数?)
[root@mail ~]#
[root@mail ~]# mysqladmin status --sleep 2 # 每2秒显示一次
Uptime: 506072 Threads: 1 Questions: 113 Slow queries: 0 Opens: 51 Flush tables: 1 Open tables: 41 Queries per second avg: 0.000
Uptime: 506074 Threads: 1 Questions: 113 Slow queries: 0 Opens: 51 Flush tables: 1 Open tables: 41 Queries per second avg: 0.000
Uptime: 506076 Threads: 1 Questions: 113 Slow queries: 0 Opens: 51 Flush tables: 1 Open tables: 41 Queries per second avg: 0.000
[root@mail ~]# mysqladmin status --sleep 2 --count 2 # 每2秒显示一次,共显示2次
Uptime: 506097 Threads: 1 Questions: 114 Slow queries: 0 Opens: 51 Flush tables: 1 Open tables: 41 Queries per second avg: 0.000
Uptime: 506099 Threads: 1 Questions: 114 Slow queries: 0 Opens: 51 Flush tables: 1 Open tables: 41 Queries per second avg: 0.000
[root@mail ~]#
[root@mail ~]# mysqladmin extended-status # mysql 的状态变量
[root@mail ~]# mysqladmin variables; # 服务器变量
[root@mail ~]# mysqladmin shutdown #关闭mysql服务器
[root@mail ~]# netstat -tnlp | grep 3306 # 此时没有3306端口了
[root@mail ~]#
[root@mail ~]# service mysqld start
Starting MySQL... [确定]
[root@mail ~]#
[root@mail ~]# mysqladmin version # 显示mysql版本号及相关的状态信息的
mysqladmin Ver 8.42 Distrib 5.5.28, for Linux on i686
Copyright (c) 2000, 2012, 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.28-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /usr/local/mysql/mysql.sock
Uptime: 2 min 2 sec
Threads: 1 Questions: 1 Slow queries: 0 Opens: 33 Flush tables: 1 Open tables: 26 Queries per second avg: 0.008
[root@mail ~]#
mysql客户端命令还有:
mysqldump 备份工具(导出工具)
mysqlimport (导入工具)
mysqlcheck
开发视角:
数据类型
约束
创建数据库,表,索引,视图
select 语句