mysql客户端的使用方法

mysql客户端是最常用的管理数据库的工具。当然除了这个，还有一些图形化的工具，比如navicat等，他们本身都非常好用。但还是建议熟悉mysql客户端，因为图形化的工具离开了自己熟悉的环境，不一定有，这时候就只能依赖mysql客户端这个黑乎乎的东西了。

一、登录服务器方法

# mysql -uroot -p -h 127.0.0.1
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 8.0.17-debug Source distribution

Copyright (c) 2000, 2019, 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> 

以下方式都可以登录到test库中，数据库名的位置非常灵活

# mysql -utest -p test
# mysql test -utest -p 
# mysql -utest test -p 
# mysql -utest test  -p  -h localhost
# mysql -utest   -p  -h localhost test

参数类型	可选项语法格式	默认值
Hostname	-h hostname <P> --host = hos tname	localhost
Username	-U username Your <P>--user = username	login name
Password	-P <P>--password	None

• MySQL对于127.0.0.1和localhost表现不同

1. 依照惯例,它们特殊对待主机名localhost, 这时它们会尝试使用一个Unix domain socket文件来连接本地服务器。
2. 要强制使用TCP/IP连接到本地服务器,那就使用IP地址127.0.0.1而不是主机名localhost。
3. 可选的,也可以通过指定--protocol=tcp选项来强制使用TCP/IP进行连接

以上可以通过tcpdump抓包验证。localhost是不能通过tcpdump -i lo port 3306 抓到包的，而127.0.0.1可以

不论查看权限还是删除用户，都需要带登录主机选项。原因在于MySQL判断的时候是加上该字符串一起的，即root与root@localhost这两个在mysql中含义是不一样的。

• socket登录

1. Unix domain Socket的路径名常常变化,通常情况为/tmp/mysql.sock
2. 为显式指定套接字文件路径名,使用-s file_ name或--socket= file_ name选项

# mysql -S /data/mysql8017/datadir/mysql.sock 

二、mysql客户端参数

Usage: mysql [OPTIONS] [database]   //命令方式
 -?, --help          //显示帮助信息并退出
 -I, --help          //显示帮助信息并退出
 --auto-rehash       //自动补全功能
 -A, --no-auto-rehash  //默认状态是没有自动补全功能的。
 -B, --batch         //sql不使用历史文件，禁用交互
 (Enables --silent)
 --character-sets-dir=name   //字体集的安装目录                   
 --default-character-set=name    //设置数据库的默认字符集
 -C, --compress      //在客户端和服务器端传递信息时使用压缩
 -#, --debug[=#]     //bug调用功能
 -D, --database=name //使用哪个数据库
 --delimiter=name    //mysql默认命令结束符是分号，下面有例子
 -e, --execute=name  //执行mysql的sql语句
 -E, --vertical      //垂直打印查询输出
 -f, --force         //如果有错误跳过去，继续执行下面的
 -G, --named-commands
 /*Enable named commands. Named commands mean this program's
 internal commands; see mysql> help . When enabled, the
 named commands can be used from any line of the query,
 otherwise only from the first line, before an enter.
 Disable with --disable-named-commands. This option is
 disabled by default.*/
 -g, --no-named-commands
 /*Named commands are disabled. Use \* form only, or use
 named commands only in the beginning of a line ending
 with a semicolon (;) Since version 10.9 the client now
 starts with this option ENABLED by default! Disable with
 '-G'. Long format commands still work from the first
 line. WARNING: option deprecated; use
 --disable-named-commands instead.*/
 -i, --ignore-spaces //忽视函数名后面的空格.
 --local-infile      //启动/禁用 LOAD DATA LOCAL INFILE.
 -b, --no-beep       //sql错误时，禁止嘟的一声
 -h, --host=name     //设置连接的服务器名或者Ip
 -H, --html          //以html的方式输出
 -X, --xml           //以xml的方式输出
 --line-numbers      //显示错误的行号
 -L, --skip-line-numbers  //忽略错误的行号
 -n, --unbuffered    //每执行一次sql后，刷新缓存
 --column-names      //查寻时显示列信息，默认是加上的
 -N, --skip-column-names  //不显示列信息
 -O, --set-variable=name  //设置变量用法是--set-variable=var_name=var_value
 --sigint-ignore     //忽视SIGINT符号(登录退出时Control-C的结果)
 -o, --one-database  //忽视除了为命令行中命名的默认数据库的语句。可以帮跳过日志中的其它数据库的更新。
 --pager[=name]      //使用分页器来显示查询输出，这个要在linux可以用more,less等。
 --no-pager          //不使用分页器来显示查询输出。
 -p, --password[=name] //输入密码
 -P, --port=#        //设置端口
 --prompt=name       //设置mysql提示符
 --protocol=name     //使用什么协议
 -q, --quick         //不缓存查询的结果，顺序打印每一行。如果输出被挂起，服务器会慢下来，mysql不使用历史文件。
 -r, --raw           //写列的值而不转义转换。通常结合--batch选项使用。
 --reconnect         //如果与服务器之间的连接断开，自动尝试重新连接。禁止重新连接，使用--disable-reconnect。
 -s, --silent        //一行一行输出，中间有tab分隔
 -S, --socket=name   //连接服务器的sockey文件
 --ssl               //激活ssl连接，不激活--skip-ssl
 --ssl-ca=name       //CA证书
 --ssl-capath=name   //CA路径
 --ssl-cert=name     //X509 证书
 --ssl-cipher=name   //SSL cipher to use (implies --ssl).
 --ssl-key=name      //X509 密钥名
 --ssl-verify-server-cert //连接时审核服务器的证书
 -t, --table         //以表格的形势输出
 --tee=name          //将输出拷贝添加到给定的文件中，禁时用--disable-tee
 --no-tee            //根--disable-tee功能一样
 -u, --user=name     //用户名
 -U, --safe-updates  //Only allow UPDATE and DELETE that uses keys.
 -U, --i-am-a-dummy  //Synonym for option --safe-updates, -U.
 -v, --verbose       //输出mysql执行的语句
 -V, --version       //版本信息
 -w, --wait          //服务器down后，等待到重起的时间
 --connect_timeout=# //连接前要等待的时间
 --max_allowed_packet=# //服务器接收／发送包的最大长度
 --net_buffer_length=# //TCP / IP和套接字通信缓冲区大小。
 --select_limit=#    //使用--safe-updates时SELECT语句的自动限制
 --max_join_size=#   //使用--safe-updates时联接中的行的自动限制
 --secure-auth       //拒绝用(pre-4.1.1)的方式连接到数据库
 --server-arg=name   //Send embedded server this as a parameter.
 --show-warnings     //显示警告
 
 --column-type-info  #结果集返回时，同时显示字段的类型等相关信息

-C, --compress  #在客户端和服务器端传递信息时使用压缩

-D, --database=name  #使用那个数据库

-e, --execute=name  #执行 MySQL 的 SQL 语句

三、定制mysql客户端

在my.cnf中，参数是分区块的，比如服务器的参数放在[mysqld]下面，而客户端的参数放在[mysql]下。那么放在[mysql]下的参数，有哪些呢？各是做什么用的？

用户名和密码参数

[mysql]
host = localhost
user = cbuser
password = cbpasswd

如果写在my.cnf中，下次登录则只需要输入mysql即可，连用户名和密码都不需要了。但这可能存在一些安全问题

分页与过滤

• 分页
  默认的情况下，mysql是不分页的，也就是如果查询到一个表有数千行数据，会一次性显示完全。如果想要类似于linux中的more或者less的效果，可以在my.cnf中如下配置

pager="/usr/bin/more -100"
#pager="/usr/bin/less -E"
#pager=grep sync;   //后续执行命令时，后面相当于都加上了  |grep sync;
#nopager;

也可以直接在mysql命令行中如下使用

mysql> select * from test ;\P /usr/bin/more

• 过滤
  同样，可以在my.cnf中加上grep选项，就可以grep特定字符串了。不过该功能不是很实用

提示符

使用mysql，有时候很羡慕mariadb的客户端，因为mariadb可以在提示符显示当前数据库，而不必时常去select database()来查看自己所处的数据库。其实mysql也可以做到，只需要稍微配置一下即可。有三种方法：

1. 在操作系统命令行中输入

# mysql --prompt '\u@mysql \r:\m:\s->'
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 31
Server version: 8.0.17-debug Source distribution

Copyright (c) 2000, 2019, 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.

root@mysql 06:36:43->

2. 在mysql命令行中输入

mysql> prompt \u@mysql \r:\m:\s->
PROMPT set to '\u@mysql \r:\m:\s->'
root@mysql 06:40:42->

3. 在my.cnf中配置

[mysql]
prompt=\\u@username \\r:\\m:\\s

不过一般我喜欢将提示符设置成

prompt=\\u@\d \\R:\\m >\\_

这样可以看到当前用户，所处数据库，以及执行命令的时间。如下：

root@test 06:42 > select * from test limit 1;

这种方法是长效的，不会随着退出mysql客户端而失效。

4. 提示符参数介绍

参数	说明
\c	执行的sql的序号
\D	当前日期的详细值
\d	当前所处数据库
\h	服务器主机
\l	当前分隔符
\m	当前时间的分钟
\n	换行符
\O	当前月份的三字母表示法 (Jan, Feb, …)
\o	当前月份的数字表示
\P	am/pm
\p	当前TCP/IP端口或者socket文件
\R	当前小时，24小时制
\r	当前小时，12小时制
\S	分号
\s	当前时间的秒数
\t	制表符
\U	包含了主机的用户名
\u	用户名
\v	服务器版本
\w	当前星期，三字符表示
\Y	当前年，4位数
\y	当前年，2位数
_	空格
\	空格（斜线后有一个空格）
'	单引号
"	双引号
\	转义后的右斜线

四、mysql客户端使用方法

本部分很多来自mysql cookbook

查看登录选项

 # mysql --print-defaults
 或者
 # my_print_defaults client mysql 
 注意：这就是执行命令，不需要加其他的参数（比如-u -p之类）

取消尚未完成的sql

select test from \c

改变显示格式

select * from test \G // 这时候不需要加分号，否则\G会单独作为一个语句执行

sql输入操作技巧

;           //分号
\g          //结束
\c          //取消
Ctrl + A    //行首
Ctrl + E    //行尾
>
->
\P          //pager分页
\P /usr/bin/less
\n          //nopager

文件中执行语句

# mysql -utest -p123456 test < filename.sql
> source '$file_path'
# cat file.sql | mysql test
# mysql -utest -p123456 test -e "select * from test;"    //执行单行sql
# mysql -utest -p123456 test < infile.sql > outfile.txt  //执行命令到文件中

输出控制

替换输出的tab成其他字符

% mysql -utest-p123456 test < infile.sql | sed -e "s/\t/:/g"                                  
% mysql -utest-p123456 test < infile.sql | sed -e "s/\t/:/g"

以html格式输出

% mysql -utest-p123456 test -e "select * from url"  -H  > 1.txt                              
% mysql -utest-p123456 test -e "select * from url"  --html  > 1.txt

以xml格式输出 -X也可以放前面

% mysql -utest-p123456 test -e "select * from url"  -X  > 1.txt
% mysql -utest-p123456 test -e "select * from url"  --xml  > 1.txt

跳过头部

% mysql -utest-p123456 test -e "select * from url" --skip-column-names                
% mysql -utest-p123456 test -e "select * from url" -ss

控制垂直输出（三者效果一样）

mysql > select * from test \G                                               
% mysql -utest-p123456 test -e "select * from url" -E                                                  
% mysql -utest-p123456 test -e "select * from url" --vertical

控制信息详细程度，详细输出

% mysql -utest-p123456 test -e "select * from url" -v                                                  
% mysql -utest-p123456 test -e "select * from url" -vv

记录交互式mysql

mysql > \T temp.out                                                                
mysql > \t

历史命令

类似于linux的历史命令记录在.bash_history中，mysql的历史命令记录在~/.mysql_history中