mycli-强大的MySQL命令行客户端

mycli官网 (opens new window) https://www.mycli.net/ (opens new window)

mycli演示

# 安装

# Python Package 
pip install mycli

# Debian/Ubuntu Package
sudo apt-get update
sudo apt-get install mycli

# Fedora
sudo dnf install mycli

# RHEL, Centos:
sudo yum install python-pip python-devel

# macOS
brew install mycli

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

Windows安装：https://www.codewall.co.uk/installing-using-mycli-on-windows/ (opens new window)

# 使用方法

# Usage
$ mycli --help
Usage: mycli [OPTIONS] [DATABASE]

  A MySQL terminal client with auto-completion and syntax highlighting.

  Examples:
    - mycli my_database
    - mycli -u my_user -h my_host.com my_database
    - mycli mysql://my_user@my_host.com:3306/my_database

Options:
  -h, --host TEXT               Host address of the database.
  -P, --port INTEGER            Port number to use for connection. Honors
                                $MYSQL_TCP_PORT.
  -u, --user TEXT               User name to connect to the database.
  -S, --socket TEXT             The socket file to use for connection.
  -p, --password TEXT           Password to connect to the database.
  --pass TEXT                   Password to connect to the database.
  --ssl-ca PATH                 CA file in PEM format.
  --ssl-capath TEXT             CA directory.
  --ssl-cert PATH               X509 cert in PEM format.
  --ssl-key PATH                X509 key in PEM format.
  --ssl-cipher TEXT             SSL cipher to use.
  --ssl-verify-server-cert      Verify server's "Common Name" in its cert
                                against hostname used when connecting. This
                                option is disabled by default.
  -V, --version                 Output mycli's version.
  -v, --verbose                 Verbose output.
  -D, --database TEXT           Database to use.
  -d, --dsn TEXT                Use DSN configured into the [alias_dsn]
                                section of myclirc file.
  --list-dsn                    list of DSN configured into the [alias_dsn]
                                section of myclirc file.
  -R, --prompt TEXT             Prompt format (Default: "\t \u@\h:\d> ").
  -l, --logfile FILENAME        Log every query and its results to a file.
  --defaults-group-suffix TEXT  Read MySQL config groups with the specified
                                suffix.
  --defaults-file PATH          Only read MySQL options from the given file.
  --myclirc PATH                Location of myclirc file.
  --auto-vertical-output        Automatically switch to vertical output mode
                                if the result is wider than the terminal
                                width.
  -t, --table                   Display batch output in table format.
  --csv                         Display batch output in CSV format.
  --warn / --no-warn            Warn before running a destructive query.
  --local-infile BOOLEAN        Enable/disable LOAD DATA LOCAL INFILE.
  --login-path TEXT             Read this path from the login file.
  -e, --execute TEXT            Execute command and quit.
  --help

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

示例

# 连接本地mysql
mysql -hlocalhost -P3306 -uroot -p1234

1
2
3

配置alias

# 编辑配置文件
vim ~/.myclirc

1
2

找到alias_dsn配置项，配置alias

[alias_dsn]
# example_dsn = mysql://[user[:password]@][host][:port][/dbname]
local_root = mysql://root:1234@localhost

local_user = mysql://user:1234@localhost

1
2
3
4
5

使用alias连接

$ mycli -d local_root
mysql 8.0.16
mycli 1.19.0
Chat: https://gitter.im/dbcli/mycli
Mail: https://groups.google.com/forum/#!forum/mycli-users
Home: http://mycli.net
Thanks to the contributor - John Sterling
mysql root@localhost:(none)>

# nopager查询结果不分页
mysql root@localhost:(none)> nopager;
Pager disabled.
Time: 0.000s

# 结果使用less分页查看
mysql root@localhost:(none)> pager less;
PAGER set to less.
Time: 0.000s

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

.myclirc其他配置项：https://www.mycli.net/config (opens new window)

# 使用SSH tunnel连接远程MySQL

很多情况下，我们需要连接到线上远程服务器的MySQL但是本机无法直接连接，可以借助线上可以连接MySQL的服务器做端口映射，使本机可以连到远程MySQL。

官方示例

# 设置远程服务器与本地端口映射
ssh remote -L 3306:localhost:3306 -f sleep 60
mycli

1
2
3

如果每次连接都要这么操作就太麻烦了，可以写成一个脚本来处理

#! /bin/bash
# online_mysql.sh

# 端口映射服务器
remote="user@remote-host"
# 远程MySQL地址
myhost="remote-mysql-host"
# 远程MySQL连接端口
myport=3306
# MySQL用户名
myuser="my-user"
# MySQL密码
mypwd="pwd"

function connect() {
    port=$1
    echo "agent port: $port"
    if check_port $port ;then
        # 端口被占用了则尝试端口+1再重试
        connect `expr $port + 1`
    else 
        ssh $remote -L $port:$myhost:$myport -fN 
        echo "mycli mysql://$myuser:$mypwd@localhost:$port"
        mycli mysql://$myuser:$mypwd@localhost:$port

        pid=`ps aux | grep $port | grep 'ssh' | awk '{print $2}'`
        kill $pid
        echo "kill $pid"
    fi    
    echo "exit"
}

# 检查端口是否被占用
check_port() {
    netstat -an | grep "$1" | grep LISTEN
}

# 默认从1024端口开始作为本地映射端口
connect 1024

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

执行脚本连接远程MySQL

sh online_mysql.sh

#mysql

← mysql常用命令整理 redis key-value 存储结构及扩容→

mycli-强大的MySQL命令行客户端

# 安装

# 使用方法

# 使用SSH tunnel连接远程MySQL

# 更多功能