MySQL 命令行工具mycli

MyCli 简介:

MyCli 是一个 MySQL 命令行工具,支持自动补全和语法高亮。也可用于 MariaDB 和 Percona。
参考地址如下:
 https://github.com/dbcli/mycli
 http://www.cnblogs.com/Jing-420-/p/5883595.html

pgcli 是一个postgresql的命令行工具,支持自动补全和语法高亮
官网地址:
 https://www.pgcli.com/
 https://github.com/dbcli/pgcli

mycli 使用 prompt_toolkit 编写
当你输入 SQL 关键字,数据库的表格和列时,自动补全。
语法高亮使用 Pygments.
智能补全(默认启用),会提示文本感应的(context-sensitive)补全。
SELECT * FROM <tab> 只显示表格名称
SELECT * FROM users WHERE <tab> 只显示列名称。
配置文件在第一次启动时,自动创建在 ~/.myclirc
漂亮的打印表格数据。

演示环境:

[root@mgr04 ~]# cat /etc/redhat-release
CentOS Linux release 7.2.1511 (Core)
MySQL5.7.24 二进制安装

安装mycli命令:

首先安装python-pip
使用yum安装python-pip时找不到该软件包,所以我们先安装拓展源。
sudo yum -y install epel-release
然后安装python-pip
sudo yum -y install python-pip python-devel
清除一下cache
sudo yum clean all
现在安装完python-pip了,可以安装mycli了。
sudo pip install mycli

[root@mgr04 ~]# pip install mycli

Collecting mycli
  Downloading https://files.pythonhosted.org/packages/f9/8a/9f96f12b482ff2e1bbb1d4252e14b4a24ed37daa5b1dcd14c3d990eca9dd/mycli-1.19.0-py2.py3-none-any.whl (56kB)
    100% |████████████████████████████████| 61kB 288kB/s 
Collecting Pygments>=1.6 (from mycli)
  Downloading https://files.pythonhosted.org/packages/6e/00/c5cb5fc7c047da4af049005d0146b3a961b1a25d9cefbbe24bf0882a11ad/Pygments-2.4.0-py2.py3-none-any.whl (882kB)
    100% |████████████████████████████████| 890kB 438kB/s 
Collecting cli-helpers[styles]>=1.0.1 (from mycli)
  Downloading https://files.pythonhosted.org/packages/e6/6a/5e0e8011a9ae7807de2ea455da57cec381d04f8219f9a2a8f3b171ac3885/cli_helpers-1.2.0.tar.gz
Collecting prompt-toolkit>=2.0.6 (from mycli)
  Downloading https://files.pythonhosted.org/packages/c8/ab/10d2c114828bd20ca0d757acee37ab5a7bae588139739b24d6cd3f45f8de/prompt_toolkit-2.0.9-py2-none-any.whl (337kB)
    100% |████████████████████████████████| 337kB 410kB/s 
Collecting click>=4.1 (from mycli)
  Downloading https://files.pythonhosted.org/packages/fa/37/45185cb5abbc30d7257104c434fe0b07e5a195a6847506c074527aa599ec/Click-7.0-py2.py3-none-any.whl (81kB)
    100% |████████████████████████████████| 81kB 1.8MB/s 
Collecting configobj>=5.0.5 (from mycli)
  Downloading https://files.pythonhosted.org/packages/64/61/079eb60459c44929e684fa7d9e2fdca403f67d64dd9dbac27296be2e0fab/configobj-5.0.6.tar.gz
Collecting PyMySQL>=0.9.2 (from mycli)
  Downloading https://files.pythonhosted.org/packages/ed/39/15045ae46f2a123019aa968dfcba0396c161c20f855f11dea6796bcaae95/PyMySQL-0.9.3-py2.py3-none-any.whl (47kB)
    100% |████████████████████████████████| 51kB 3.4MB/s 
Collecting cryptography>=1.0.0 (from mycli)
  Downloading https://files.pythonhosted.org/packages/c3/c1/cf8665c955c9393e9ff0872ba6cd3dc6f46ef915e94afcf6e0410508ca69/cryptography-2.6.1-cp27-cp27mu-manylinux1_x86_64.whl (2.3MB)
    100% |████████████████████████████████| 2.3MB 287kB/s 
Collecting sqlparse<0.3.0,>=0.2.2 (from mycli)
  Downloading https://files.pythonhosted.org/packages/65/85/20bdd72f4537cf2c4d5d005368d502b2f464ede22982e724a82c86268eda/sqlparse-0.2.4-py2.py3-none-any.whl
Collecting tabulate[widechars]>=0.8.2 (from cli-helpers[styles]>=1.0.1->mycli)
  Downloading https://files.pythonhosted.org/packages/c2/fd/202954b3f0eb896c53b7b6f07390851b1fd2ca84aa95880d7ae4f434c4ac/tabulate-0.8.3.tar.gz (46kB)
    100% |████████████████████████████████| 51kB 3.1MB/s 
Collecting terminaltables>=3.0.0 (from cli-helpers[styles]>=1.0.1->mycli)
  Downloading https://files.pythonhosted.org/packages/9b/c4/4a21174f32f8a7e1104798c445dacdc1d4df86f2f26722767034e4de4bff/terminaltables-3.1.0.tar.gz
Collecting backports.csv>=1.0.0 (from cli-helpers[styles]>=1.0.1->mycli)
  Downloading https://files.pythonhosted.org/packages/8e/26/a6bd68f13e0f38fbb643d6e497fc3462be83a0b6c4d43425c78bb51a7291/backports.csv-1.0.7-py2.py3-none-any.whl
Collecting wcwidth (from prompt-toolkit>=2.0.6->mycli)
  Downloading https://files.pythonhosted.org/packages/7e/9f/526a6947247599b084ee5232e4f9190a38f398d7300d866af3ab571a5bfe/wcwidth-0.1.7-py2.py3-none-any.whl
Requirement already satisfied (use --upgrade to upgrade): six>=1.9.0 in /usr/lib/python2.7/site-packages (from prompt-toolkit>=2.0.6->mycli)
Collecting cffi!=1.11.3,>=1.8 (from cryptography>=1.0.0->mycli)
  Downloading https://files.pythonhosted.org/packages/8d/e9/0c8afd1579e5cf7bc0f06fbcd7cdb954cbc0baadd505973949a99337da1c/cffi-1.12.3-cp27-cp27mu-manylinux1_x86_64.whl (415kB)
    100% |████████████████████████████████| 419kB 990kB/s 
Requirement already satisfied (use --upgrade to upgrade): ipaddress; python_version < "3" in /usr/lib/python2.7/site-packages (from cryptography>=1.0.0->mycli)
Collecting asn1crypto>=0.21.0 (from cryptography>=1.0.0->mycli)
  Downloading https://files.pythonhosted.org/packages/ea/cd/35485615f45f30a510576f1a56d1e0a7ad7bd8ab5ed7cdc600ef7cd06222/asn1crypto-0.24.0-py2.py3-none-any.whl (101kB)
    100% |████████████████████████████████| 102kB 1.7MB/s 
Collecting enum34; python_version < "3" (from cryptography>=1.0.0->mycli)
  Downloading https://files.pythonhosted.org/packages/c5/db/e56e6b4bbac7c4a06de1c50de6fe1ef3810018ae11732a50f15f62c7d050/enum34-1.1.6-py2-none-any.whl
Collecting pycparser (from cffi!=1.11.3,>=1.8->cryptography>=1.0.0->mycli)
  Downloading https://files.pythonhosted.org/packages/68/9e/49196946aee219aead1290e00d1e7fdeab8567783e83e1b9ab5585e6206a/pycparser-2.19.tar.gz (158kB)
    100% |████████████████████████████████| 163kB 1.3MB/s 
Installing collected packages: Pygments, configobj, wcwidth, tabulate, terminaltables, backports.csv, cli-helpers, prompt-toolkit, click, PyMySQL, pycparser, cffi, asn1crypto, enum34, cryptography, sqlparse, mycli
  Found existing installation: configobj 4.7.2
    DEPRECATION: Uninstalling a distutils installed project (configobj) has been deprecated and will be removed in a future version. This is due to the fact that uninstalling a distutils project will only partially uninstall the project.
    Uninstalling configobj-4.7.2:
      Successfully uninstalled configobj-4.7.2
  Running setup.py install for configobj ... done
  Running setup.py install for tabulate ... done
  Running setup.py install for terminaltables ... done
  Running setup.py install for cli-helpers ... done
  Running setup.py install for pycparser ... done
Successfully installed PyMySQL-0.9.3 Pygments-2.4.0 asn1crypto-0.24.0 backports.csv-1.0.7 cffi-1.12.3 cli-helpers-1.2.0 click-7.0 configobj-5.0.6 cryptography-2.6.1 enum34-1.1.6 mycli-1.19.0 prompt-toolkit-2.0.9 pycparser-2.19 sqlparse-0.2.4 tabulate-0.8.3 terminaltables-3.1.0 wcwidth-0.1.7
You are using pip version 8.1.2, however version 19.1.1 is available.
You should consider upgrading via the 'pip install --upgrade pip' command.
[root@mgr04 ~]# 
  • 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.
  • 51.
  • 52.
  • 53.
  • 54.
  • 55.
  • 56.
  • 57.
  • 58.
  • 59.
  • 60.

显示如下表示mycl客户单命令安装成功:

[root@mgr04 ~]# 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
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.

[root@mgr04 ~]# cat .my.cnf
[mysql]
user=root
password=‘654321’
socket=/tmp/mysql.sock

root@localhost [(none)]>select user,host from mysql.user;
±--------------±----------+
| user | host |
±--------------±----------+
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
±--------------±----------+
6 rows in set (0.01 sec)

[root@mgr04 ~]# mycli
(1130, u"Host ‘::1’ is not allowed to connect to this MySQL server")

直接mycli登录报错,应该是没有给数据库授权登录账户

进行登录账户授权如下:

root@localhost [(none)]>grant all on *.* to root@'127.0.0.1' identified by '654321' with grant option;
Query OK, 0 rows affected, 1 warning (0.02 sec)

root@localhost [(none)]>\q
  • 1.
  • 2.
  • 3.
  • 4.

演示4种mycli登录mysql的方式:

第一种:

[root@mgr04 ~]# mycli -h127.0.0.1 -uroot -p'654321' -P3306 test01
mysql 5.7.24-log
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 - Anonymous
mysql root@127.0.0.1:test01> show databases;                                                                                                                                                                                    
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test01             |
| test02             |
+--------------------+
6 rows in set
Time: 0.025s
mysql root@127.0.0.1:test01> 
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.

第二种:

[root@mgr04 ~]# mycli -p'654321' mysql://root@127.0.0.1:3306/test01
mysql 5.7.24-log
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 - Zhongyang Guan
mysql root@127.0.0.1:test01> show tables;                                                                                                                                                                                       
+------------------+
| Tables_in_test01 |
+------------------+
| tutorials_tbl    |
+------------------+
1 row in set
Time: 0.026s
mysql root@127.0.0.1:test01>  
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.

第三种:

[root@mgr04 ~]# mycli  mysql://root:654321@127.0.0.1:3306/test01
mysql 5.7.24-log
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 - Magnus udd
mysql root@127.0.0.1:test01> \q                                                                                                                                                                                                 
Goodbye!
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.

第四种:

[root@mgr04 ~]#  mycli -h127.0.0.1 -uroot -p'654321'
mysql 5.7.24-log
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 - Ryan Smith
mysql root@127.0.0.1:(none)>  
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.

简单安装登录演示完成

posted @ 2019-05-12 21:47  勤奋的蓝猫  阅读(14)  评论(0编辑  收藏  举报  来源