ORACLE、MySQL、PostgreSQL 客户端 sqlplus,mysql,psql 常用命令对比(一)

写在前面的话:当前根据 墨天轮中国数据库排行 https://www.modb.pro/dbRank 来看,已经有 292 个数据库了,国产数据库遍地开花,如果只掌握一种数据库貌似有点落伍了。

大体梳理了下,目前拥有的数据库中级级别(Professional)的证书有 ORACLE 11g OCP(2010)、MySQL 5.7(2019)/8.0(2023) OCP、Tidb PCTP(2022)、HCIP-GaussDB-OLTP(2022);初级级别的有:巨杉数据库openGaussMogDBOceanBaseGoldenDB 总共 9 种数据库了,有云数据库、分布式、集中式、开源数据库等,实际上这些数据库大多都是 paper 能力,目前实际能够支持的也就是 ORACLE 和 MySQL,目前准备实际学习下 PostgreSQL,发现数据库多了,一些命令容易混,还发现 PostgreSQL 的一些操作跟 ORACLE 和 MySQL 有一些不太一样的地方、感觉不太习惯的地方,于是就萌生了总结下这三种数据库的一些对比,先从客户端的常用命令开始吧。

手里正好有一台 ORACLE Cloud 2c12g 的 arm 服务器,看了下目前 MySQL 和 PostgreSQL 的客户端都已经支持了 arm 架构了,ORACLE 目前没用找到 arm 架构的客户端,但是 ORACLE 数据库目前是支持 arm 架构了,不过需要 oel 8.4 及以后的版本,目前我的 arm 服务器上安装的是 oel7 的 os,由于 OCI 不支持更换操作系统,所以本次测试 ORACLE 是在 x86 服务器上测试,MySQL 和 PostgreSQL 是在 arm 架构的服务器上测试。

安装客户端工具

目前三种数据库的客户端工具都支持 rpm 安装

ORACLE 数据库客户端 sqlplus 安装

下载软件包:https://yum.oracle.com/repo/OracleLinux/OL7/oracle/instantclient/x86_64/index.html

1
2
wget https://download.oracle.com/otn_software/linux/instantclient/2112000/oracle-instantclient-basic-21.12.0.0.0-1.x86_64.rpm
wget https://download.oracle.com/otn_software/linux/instantclient/2112000/oracle-instantclient-sqlplus-21.12.0.0.0-1.x86_64.rpm

oracle-instantclient-sqlplus 依赖 oracle-instantclient-basic,下载两个软件包,然后通过 yum install *.rpm 安装即可。

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
[root@tcloud sqlplus]# yum install *.rpm
Loaded plugins: fastestmirror, langpacks
Examining oracle-instantclient-basic-21.12.0.0.0-1.x86_64.rpm: oracle-instantclient-basic-21.12.0.0.0-1.x86_64
Marking oracle-instantclient-basic-21.12.0.0.0-1.x86_64.rpm to be installed
Examining oracle-instantclient-sqlplus-21.12.0.0.0-1.x86_64.rpm: oracle-instantclient-sqlplus-21.12.0.0.0-1.x86_64
Marking oracle-instantclient-sqlplus-21.12.0.0.0-1.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package oracle-instantclient-basic.x86_64 0:21.12.0.0.0-1 will be installed
---> Package oracle-instantclient-sqlplus.x86_64 0:21.12.0.0.0-1 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=============================================================================================================
Package Arch Version Repository Size
=============================================================================================================
Installing:
oracle-instantclient-basic x86_64 21.12.0.0.0-1 /oracle-instantclient-basic-21.12.0.0.0-1.x86_64 238 M
oracle-instantclient-sqlplus x86_64 21.12.0.0.0-1 /oracle-instantclient-sqlplus-21.12.0.0.0-1.x86_64 3.2 M

Transaction Summary
=============================================================================================================
Install 2 Packages

Total size: 241 M
Installed size: 241 M
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : oracle-instantclient-basic-21.12.0.0.0-1.x86_64 1/2
Installing : oracle-instantclient-sqlplus-21.12.0.0.0-1.x86_64 2/2
Verifying : oracle-instantclient-basic-21.12.0.0.0-1.x86_64 1/2
Verifying : oracle-instantclient-sqlplus-21.12.0.0.0-1.x86_64 2/2

Installed:
oracle-instantclient-basic.x86_64 0:21.12.0.0.0-1 oracle-instantclient-sqlplus.x86_64 0:21.12.0.0.0-1

Complete!
[root@tcloud sqlplus]#
[root@tcloud sqlplus]# sqlplus -V

SQL*Plus: Release 21.0.0.0.0 - Production
Version 21.12.0.0.0

MySQL 数据库客户端 mysql 安装

rpm 包下载地址:https://downloads.mysql.com/archives/community/

本次 MySQL 是以 arm 的主机进行演示的,当只下载 mysql-community-client-8.0.33-1.el7.aarch64.rpm 的时候会提示需要依赖,本次测试安装需要 mysql-community-client-pluginsmysql-community-libsmysql-community-common 这三个依赖。下载之后通过 yum install *.rpm 安装即可。

1
2
3
4
wget https://cdn.mysql.com/archives/mysql-8.0/mysql-community-client-plugins-8.0.33-1.el7.aarch64.rpm
wget https://cdn.mysql.com/archives/mysql-8.0/mysql-community-client-8.0.33-1.el7.aarch64.rpm
wget https://cdn.mysql.com/archives/mysql-8.0/mysql-community-libs-8.0.33-1.el7.aarch64.rpm
wget https://cdn.mysql.com/archives/mysql-8.0/mysql-community-common-8.0.33-1.el7.aarch64.rpm
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
61
62
[root@instance-20211219-1950 mysqlcli]# yum install *.rpm
Loaded plugins: langpacks, ulninfo
Examining mysql-community-client-8.0.33-1.el7.aarch64.rpm: mysql-community-client-8.0.33-1.el7.aarch64
Marking mysql-community-client-8.0.33-1.el7.aarch64.rpm to be installed
Examining mysql-community-client-plugins-8.0.33-1.el7.aarch64.rpm: mysql-community-client-plugins-8.0.33-1.el7.aarch64
Marking mysql-community-client-plugins-8.0.33-1.el7.aarch64.rpm to be installed
Examining mysql-community-common-8.0.33-1.el7.aarch64.rpm: mysql-community-common-8.0.33-1.el7.aarch64
Marking mysql-community-common-8.0.33-1.el7.aarch64.rpm to be installed
Examining mysql-community-libs-8.0.33-1.el7.aarch64.rpm: mysql-community-libs-8.0.33-1.el7.aarch64
Marking mysql-community-libs-8.0.33-1.el7.aarch64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package mysql-community-client.aarch64 0:8.0.33-1.el7 will be installed
---> Package mysql-community-client-plugins.aarch64 0:8.0.33-1.el7 will be installed
---> Package mysql-community-common.aarch64 0:8.0.33-1.el7 will be installed
---> Package mysql-community-libs.aarch64 0:8.0.33-1.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=============================================================================================================
Package Arch Version Repository Size
=============================================================================================================
Installing:
mysql-community-client aarch64 8.0.33-1.el7 /mysql-community-client-8.0.33-1.el7.aarch64 79 M
mysql-community-client-plugins aarch64 8.0.33-1.el7 /mysql-community-client-plugins-8.0.33-1.el7.aarch64 20 M
mysql-community-common aarch64 8.0.33-1.el7 /mysql-community-common-8.0.33-1.el7.aarch64 10 M
mysql-community-libs aarch64 8.0.33-1.el7 /mysql-community-libs-8.0.33-1.el7.aarch64 7.5 M

Transaction Summary
=============================================================================================================
Install 4 Packages

Total size: 117 M
Installed size: 117 M
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : mysql-community-client-plugins-8.0.33-1.el7.aarch64 1/4
Installing : mysql-community-common-8.0.33-1.el7.aarch64 2/4
Installing : mysql-community-libs-8.0.33-1.el7.aarch64 3/4
Installing : mysql-community-client-8.0.33-1.el7.aarch64 4/4
Verifying : mysql-community-client-plugins-8.0.33-1.el7.aarch64 1/4
Verifying : mysql-community-client-8.0.33-1.el7.aarch64 2/4
Verifying : mysql-community-common-8.0.33-1.el7.aarch64 3/4
Verifying : mysql-community-libs-8.0.33-1.el7.aarch64 4/4

Installed:
mysql-community-client.aarch64 0:8.0.33-1.el7 mysql-community-client-plugins.aarch64 0:8.0.33-1.el7 mysql-community-common.aarch64 0:8.0.33-1.el7
mysql-community-libs.aarch64 0:8.0.33-1.el7

Complete!
[root@instance-20211219-1950 mysqlcli]# ll
total 21900
-rw-r--r-- 1 root root 16516556 Mar 17 2023 mysql-community-client-8.0.33-1.el7.aarch64.rpm
-rw-r--r-- 1 root root 3670704 Mar 17 2023 mysql-community-client-plugins-8.0.33-1.el7.aarch64.rpm
-rw-r--r-- 1 root root 680276 Mar 17 2023 mysql-community-common-8.0.33-1.el7.aarch64.rpm
-rw-r--r-- 1 root root 1544900 Mar 17 2023 mysql-community-libs-8.0.33-1.el7.aarch64.rpm
[root@instance-20211219-1950 mysqlcli]#
1
2
[root@instance-20211219-1950 mysqlcli]# mysql -V
mysql Ver 8.0.33 for Linux on aarch64 (MySQL Community Server - GPL)

PostgreSQL 数据库客户端 psql 安装

官方手册:https://www.postgresql.org/download/linux/redhat/

在我写本文章的时候2023/12/19 ,通过官方的 PostgreSQL Yum Repository 安装命令是无法正常安装的。

1
2
3
4
5
6
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-aarch64/pgdg-redhat-repo-latest.noarch.rpm
sudo yum install -y postgresql16-server # 安装数据库
sudo yum install -y postgresql-client
sudo /usr/pgsql-16/bin/postgresql-16-setup initdb
sudo systemctl enable postgresql-16
sudo systemctl start postgresql-16

报错如下:

1
2
3
4
yum install postgresql16-server
Loaded plugins: langpacks, ulninfo
https://download.postgresql.org/pub/repos/yum/common/redhat/rhel-7Server-aarch64/repodata/repomd.xml: [Errno 14] HTTPS Error 404 - Not Found
Trying other mirror.

也就是 https://download.postgresql.org/pub/repos/yum/common/redhat/rhel-7Server-aarch64/repodata/repomd.xml: [Errno 14] HTTPS Error 404 - Not Found 这个网页404了。

还是采用rpm 软件包的方式安装:Direct RPM download direct download

我的操作系统是RHEL / CentOS 7 - aarch64https://download.postgresql.org/pub/repos/yum/common/redhat/rhel-7-aarch64/

1
2
wget https://download.postgresql.org/pub/repos/yum/15/redhat/rhel-7-aarch64/postgresql15-15.5-1PGDG.rhel7.aarch64.rpm
wget https://download.postgresql.org/pub/repos/yum/15/redhat/rhel-7-aarch64/postgresql15-libs-15.5-1PGDG.rhel7.aarch64.rpm

yum 安装的时候需要依赖 postgresql15-libs ,两个rpm都下载,然后通过 yum install 安装

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
61
62
63
[root@instance-20211219-1950 psql]# yum install *.rpm
Loaded plugins: langpacks, ulninfo
Examining postgresql15-15.5-1PGDG.rhel7.aarch64.rpm: postgresql15-15.5-1PGDG.rhel7.aarch64
Marking postgresql15-15.5-1PGDG.rhel7.aarch64.rpm to be installed
Examining postgresql15-libs-15.5-1PGDG.rhel7.aarch64.rpm: postgresql15-libs-15.5-1PGDG.rhel7.aarch64
Marking postgresql15-libs-15.5-1PGDG.rhel7.aarch64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package postgresql15.aarch64 0:15.5-1PGDG.rhel7 will be installed
---> Package postgresql15-libs.aarch64 0:15.5-1PGDG.rhel7 will be installed
--> Finished Dependency Resolution
epel/aarch64/metalink | 7.6 kB 00:00:00
ol7_UEKR6/aarch64 | 3.0 kB 00:00:00
ol7_addons/aarch64 | 3.0 kB 00:00:00
ol7_developer_EPEL/aarch64 | 3.6 kB 00:00:00
ol7_ksplice/aarch64 | 3.0 kB 00:00:00
ol7_latest/aarch64 | 3.6 kB 00:00:00
ol7_oci_included/aarch64 | 2.9 kB 00:00:00
ol7_optional_latest/aarch64 | 3.0 kB 00:00:00
ol7_software_collections/aarch64 | 3.0 kB 00:00:00
https://download.postgresql.org/pub/repos/yum/common/redhat/rhel-7Server-aarch64/repodata/repomd.xml: [Errno 14] HTTPS Error 404 - Not Found
Trying other mirror.
https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7Server-aarch64/repodata/repomd.xml: [Errno 14] HTTPS Error 404 - Not Found
Trying other mirror.
https://download.postgresql.org/pub/repos/yum/12/redhat/rhel-7Server-aarch64/repodata/repomd.xml: [Errno 14] HTTPS Error 404 - Not Found
Trying other mirror.
https://download.postgresql.org/pub/repos/yum/13/redhat/rhel-7Server-aarch64/repodata/repomd.xml: [Errno 14] HTTPS Error 404 - Not Found
Trying other mirror.
https://download.postgresql.org/pub/repos/yum/14/redhat/rhel-7Server-aarch64/repodata/repomd.xml: [Errno 14] HTTPS Error 404 - Not Found
Trying other mirror.
https://download.postgresql.org/pub/repos/yum/15/redhat/rhel-7Server-aarch64/repodata/repomd.xml: [Errno 14] HTTPS Error 404 - Not Found
Trying other mirror.

Dependencies Resolved

=============================================================================================================
Package Arch Version Repository Size
=============================================================================================================
Installing:
postgresql15 aarch64 15.5-1PGDG.rhel7 /postgresql15-15.5-1PGDG.rhel7.aarch64 9.2 M
postgresql15-libs aarch64 15.5-1PGDG.rhel7 /postgresql15-libs-15.5-1PGDG.rhel7.aarch64 1.2 M

Transaction Summary
=============================================================================================================
Install 2 Packages

Total size: 10 M
Installed size: 10 M
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : postgresql15-libs-15.5-1PGDG.rhel7.aarch64 1/2
Installing : postgresql15-15.5-1PGDG.rhel7.aarch64 2/2
Verifying : postgresql15-15.5-1PGDG.rhel7.aarch64 1/2
Verifying : postgresql15-libs-15.5-1PGDG.rhel7.aarch64 2/2

Installed:
postgresql15.aarch64 0:15.5-1PGDG.rhel7 postgresql15-libs.aarch64 0:15.5-1PGDG.rhel7

Complete!

这里仍然报 repomd.xml: [Errno 14] HTTPS Error 404 - Not Found 但是不影响安装,由于这里需要的rpm包都已经本地下载完成。

1
2
[root@instance-20211219-1950 psql]# psql -V
psql (PostgreSQL) 15.5

当然上面只是为了对比而安装,通常情况下我们都已经安装了数据库,这些客户端工具都随着数据库都自动安装了。

总结:三种数据库安装客户端工具都支持通过 rpm 软件包进行安装,需要注意的是rpm包需要依赖。

下面对三种数据库的客户端工具常用操作进行对比。

连接到数据库

连接远程数据库

Oracle sqlplus 客户端连接

1
sqlplus [username][/password]/@[hostname]:[port]/[DB service name] [AS SYSDBA]
  • 普通用户连接数据库(不使用 as sysdba)
1
sqlplus liups/liups@liups.com:1521/ora19cl

上面是用户 liups 使用密码 liups 连接到 IP 为 liups.com 端口为 1521,服务名为 ora19cl 的 oracle 数据库,如果不写端口号默认是1521,写端口号的方式是 hostname:port

  • 特权用户连接数据库(使用 as sysdba)
1
sqlplus sys/password123@liups.com:1521/ora19cl as sysdba

注意⚠️:

特权用户必须使用 as sysdba 或者 as sysoper选项,否则报 ERROR:ORA-28009: connection as SYS should be as SYSDBA or SYSOPER

以上是使用 [hostname]:[port]/[DB service name] 的方式进行连接,ORACLE 还支持通过 tnsname的方式进行连接。

比如tnsnames.ora 里新增如下 tnsname:dbo19c_high

1
2
cat tnsnames.ora
dbo19c_high = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.ap-tokyo-1.oraclecloud.com))(connect_data=(service_name=aad4ubqywguykly_dbo19c_high.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))
1
2
sqlplus liups/liups@dbo19c_high
sqlplus sys/password123@dbo19c_high as sysdba

ORACLE 可以直接输入sqlplus 后面不带任何参数,然后通过交互的形式输入用户、密码

1
2
3
4
5
6
7
8
9
10
[oracle@liups:/home/oracle]$ sqlplus
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Dec 20 20:49:17 2023
Version 19.18.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Enter user-name: a<---交互式输入
Enter password: <---交互式输入
Last Successful login time: Tue Dec 19 2023 22:29:35 +08:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.18.0.0.0

MySQL mysql 客户端连接

语法:

1
mysql -u username -p -h hostname -P port dbname

具体参数如下:

1
2
3
4
5
6
7
8
9
-u, --user=name     User for login if not current user.
-p, --password[=name]
Password to use when connecting to server. If password is
not given it's asked from the tty.
-P, --port=# Port number to use for connection or 0 for default to, in
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/services, built-in default (3306).
-h, --host=name Connect to host.
-D, --database=name Database to use.
1
mysql -u liups -pPassword123 -h liups.com -P 3308 liupsdb

用户liups 使用密码 Password123 连接到 IP 为 liups.com 端口为 3308 的MySQL 的 liupsdb数据库。指定端口需要通过 -P 指定,默认是 3306

liupsdb 是 要连接的 database,可以通过-D 参数,也可以省略 -D,也就是如果在命令行里没有任何参数的字符串会认为是要连接的database

PostgreSQL psql 客户端连接

1
psql -U username -d database_name -h 
1
2
3
4
5
6
-h, --host=HOSTNAME      database server host or socket directory (default: "local socket")
-p, --port=PORT database server port (default: "5432")
-U, --username=USERNAME database user name (default: "opc")
-w, --no-password never prompt for password
-W, --password force password prompt (should happen automatically)
-d, --dbname=DBNAME database name to connect to (default: "root")

可以看到 :

MySQL 需要通过 -u 指定用户名,-p 指定密码,-h 指定 hostname或者ip-P 指定端口,默认是3306

PostgreSQL 需要通过 -U 指定用户名,-h 指定 hostname或者ip-p 指定端口,默认是 5432

可以看到 MySQL 和 PostgreSQL 的区别:

指定用户名 :MySQL 是通过 -u 参数而 PostgreSQL 是通过 -U 参数,一个是小写,一个是大写;

指定端口 :MySQL 是通过 -P 参数,PostgreSQL 是通过 -p 参数,一个是大写,一个是小写;

指定服务器:MySQL 和 PostgreSQL 两者都是通过 -h 指定服务器地址(ip或者/域名/hostanme);

指定密码: MySQL 是通过 -p 参数,后面可以直接跟密码也可以不跟,不跟密码的话,需要交互式输入密码,但是 PostgreSQL 的密码是通过-W 参数强制提示输入密码,但是他后面不能跟密码,需要交互式输入(即使没有密码)。

指定数据库:MySQL 是通过 -D 参数,PostgreSQL 是通过 `-d 参数,一个是大写,一个是小写,都可以省略,也就是不带参数的字符串为要连接的数据库。

但是 PostgreSQL 除了通过上面的方式连接数据库之外,还支持以下方式连接数据库:

1
psql 'postgresql://liups:password@liups.com:5434/liupsdb'
1
2
3
4
5
[root@liups tmp]# psql 'postgresql://liups:password@liups.com:5434/liupsdb'
psql (15.5)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, compression: off)
Type "help" for help.
liupsdb=>
1
psql 'postgresql://liups:Password123@liups.com:5434/liupsdb'

说明:

postgresql :协议名称;liups: 用户名;Password123 :密码;liups.com : 服务器地址;5434端口;liupsdb: 连接的数据库名称

以上是,用户liups 使用密码 Password123 连接到 IP 为 liups.com 端口为 5434 的 PostgreSQL 的liupsdb数据库。

如果想用psql直接连接数据库,需要通过设置postgres用户的环境变量来实现:

1
2
3
4
5
export PGHOME=/usr/pgsql-13
export PGUSER=postgres
export PGPORT=5434
export PGHOST=localhost
export PATH=$PGHOME/bin:$PATH:$HOME/bin

连接本地数据库

ORACLE sqlplus 客户端连接

如果不输入@及后面的IP信息,默认就是登录本地数据库,登录本地数据库需要设置 ORACLE_SID 环境变量

1
2
export ORACLE_SID=mesdb
sqlplus liups/liups

这是使用用户名为liups 密码为 liups登录到本地 mesdb 数据库实例

特权用户登录:

1
2
export ORACLE_SID=mesdb
sqlplus / as sysdba

特权用户登录,不需要输入用户名、密码,需要当前的用户属于 oracle 软件安装的用户,通常是 oracle。

注意⚠️:实际上是本地特权用户登录是:用户名和密码可以随便输入,但是它默认仍然是以sys as sysdba 登录。

1
2
3
4
5
6
7
8
9
10
11
12
13
[oracle@liups:/home/oracle]$ sqlplus a/a as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Dec 20 21:14:57 2023
Version 19.18.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.18.0.0.0
sys@ORA19CL 21:14:57> show user
USER is "SYS"
sys@ORA19CL 21:26:13> select username from dba_users where username='A';
USERNAME
--------------------
A

在这里我们可以看到是通过a/a 也就是输入用户名 a 密码也是a进行 as sysdba 登录的,但是show user 看到的仍然是sys用户,即使数据库中有a这个用户也不影响。

MySQL mysql 客户端连接

语法:

1
mysql -u username -p [-h 127.0.0.1/localhost]-P port dbname [-S socket]

MySQL 连接到本地数据库有3种方式:

  1. 通过 Unix Socket 连接:

    1
    mysql -u your_username -p

    这会默认使用 Unix Socket 连接到本地 MySQL 服务器。

    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
    [root@liups ~]#  mysql -umes -pmesdb2023
    mysql: [Warning] Using a password on the command line interface can be insecure.
    Welcome to the MySQL monitor. Commands end with ; or \g.
    Your MySQL connection id is 23
    Server version: 8.0.35 MySQL Community Server - GPL

    Copyright (c) 2000, 2023, Oracle and/or its affiliates.

    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> status
    --------------
    mysql Ver 8.0.35 for Linux on x86_64 (MySQL Community Server - GPL)

    Connection id: 23
    Current database:
    Current user: mes@localhost
    SSL: Not in use
    Current pager: stdout
    Using outfile: ''
    Using delimiter: ;
    Server version: 8.0.35 MySQL Community Server - GPL
    Protocol version: 10
    Connection: Localhost via UNIX socket
    Server characterset: utf8mb4
    Db characterset: utf8mb4
    Client characterset: utf8mb4
    Conn. characterset: utf8mb4
    UNIX socket: /tmp/mysql.sock
    Binary data as: Hexadecimal
    Uptime: 1 hour 22 min 33 sec

    Threads: 2 Questions: 138 Slow queries: 0 Opens: 230 Flush tables: 3 Open tables: 149 Queries per second avg: 0.027
    --------------

    通过 status 查看,Connection: Localhost via UNIX socketUNIX socket: /tmp/mysql.sock

    或者使用 localhost:

    1
    mysql -h localhost -u your_username -p
  2. 通过 TCP/IP 连接到本地 IP 地址:

    1
    mysql -h 127.0.0.1 -u your_username -p

    这会通过 TCP/IP 连接到本地 MySQL 服务器,其中 127.0.0.1 可以换成本地实际的ip地址。

    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
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    [root@liups ~]#  mysql -umes -pmesdb2023 -h127.0.0.1
    mysql: [Warning] Using a password on the command line interface can be insecure.
    Welcome to the MySQL monitor. Commands end with ; or \g.
    Your MySQL connection id is 24
    Server version: 8.0.35 MySQL Community Server - GPL

    Copyright (c) 2000, 2023, Oracle and/or its affiliates.

    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> status
    --------------
    mysql Ver 8.0.35 for Linux on x86_64 (MySQL Community Server - GPL)

    Connection id: 24
    Current database:
    Current user: mes@localhost
    SSL: Cipher in use is TLS_AES_256_GCM_SHA384
    Current pager: stdout
    Using outfile: ''
    Using delimiter: ;
    Server version: 8.0.35 MySQL Community Server - GPL
    Protocol version: 10
    Connection: 127.0.0.1 via TCP/IP
    Server characterset: utf8mb4
    Db characterset: utf8mb4
    Client characterset: utf8mb4
    Conn. characterset: utf8mb4
    TCP port: 3306
    Binary data as: Hexadecimal
    Uptime: 1 hour 23 min 59 sec

    Threads: 2 Questions: 143 Slow queries: 0 Opens: 230 Flush tables: 3 Open tables: 149 Queries per second avg: 0.028
    --------------
    [root@liups ~]# mysql -umes -pmesdb2023 -hlocalhost
    mysql: [Warning] Using a password on the command line interface can be insecure.
    Welcome to the MySQL monitor. Commands end with ; or \g.
    Your MySQL connection id is 25
    Server version: 8.0.35 MySQL Community Server - GPL

    Copyright (c) 2000, 2023, Oracle and/or its affiliates.

    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> status
    --------------
    mysql Ver 8.0.35 for Linux on x86_64 (MySQL Community Server - GPL)

    Connection id: 25
    Current database:
    Current user: mes@localhost
    SSL: Not in use
    Current pager: stdout
    Using outfile: ''
    Using delimiter: ;
    Server version: 8.0.35 MySQL Community Server - GPL
    Protocol version: 10
    Connection: Localhost via UNIX socket
    Server characterset: utf8mb4
    Db characterset: utf8mb4
    Client characterset: utf8mb4
    Conn. characterset: utf8mb4
    UNIX socket: /tmp/mysql.sock
    Binary data as: Hexadecimal
    Uptime: 1 hour 24 min 15 sec

    Threads: 2 Questions: 148 Slow queries: 0 Opens: 230 Flush tables: 3 Open tables: 149 Queries per second avg: 0.029
    --------------

    注意⚠️:1、-h 127.0.0.1 和-hlocalhost的区别,登录之后可以通过 status 命令查看连接状态:

    -h 127.0.0.1 的连接方式是通过 Connection: 127.0.0.1 via TCP/IP 也就是 TCP/IP的方式,但是

    -h localhost的连接方式是通过 Connection: Localhost via UNIX socket 也就是 UNIX socket的方式,这是有区别的。

    2、指定 Socket 文件路径:

    如果 MySQL 服务器的 Socket 文件不在默认位置,需要通过 --socket (-S)选项指定路径:

    1
    mysql -u your_username -p --socket=/path/to/mysql.sock
  3. 通过命名管道连接(仅在 Windows 上有效):

    1
    mysql --protocol=pipe -u your_username -p

    这会通过命名管道连接到本地 MySQL 服务器。

    这里没有 windows 环境,没有时间测试。

PostgreSQL psql 客户端连接

PostgreSQL psql 客户端连接本地数据库通常使用安装 PostgreSQL 数据库的操作系统用户通常是 postgres,直接通过psql 就可以直接通过socket的方式登录

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
[root@liups ~]# ps -ef |grep postgres
postgres 1074 1 0 20:11 ? 00:00:00 /usr/pgsql-15/bin/postmaster -D /var/lib/pgsql/15/data/
[root@liups ~]# su - postgres
Last login: Tue Jan 16 22:09:20 CST 2024 on pts/0
-bash-4.2$ psql
psql (15.5)
Type "help" for help.

postgres=# select current_user;
current_user
--------------
postgres
(1 row)
postgres-# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".
postgres-#

通过 \conninfo 检查是 via socket in "/var/run/postgresql" 也就是通过 socket 的方式登录的。

可以看到直接就可以登录,不需要密码。

1
2
3
4
5
6
Connection options:
-h, --host=HOSTNAME database server host or socket directory (default: "local socket")
-p, --port=PORT database server port (default: "5432")
-U, --username=USERNAME database user name (default: "postgres")
-w, --no-password never prompt for password
-W, --password force password prompt (should happen automatically)

可以看到 -h 可以指定 ip/主机名称或者 socket directory。

1
2
3
psql -U abc -h127.0.0.1 
psql -U abc -hlocalhost
psql -h /var/run/postgresql/

注意⚠️:1、-h 默认是 local socket,- U 默认是 postgres实际上经过测试它默认是 当前os的系统用户。

2、psql -h 可以写 socket 的目录,这与MySQL 是有区别的,MySQL 是通过 --socket (-S) 来指定socket,且是完整的socket的文件目录。

总结:

1、安装:

ORACLE、MySQL、PostgreSQL 客户端工具 sqlplus,mysql,psql 都支持通过 rpm安装,且都有依赖, 并不是一个rpm包可以解决。

2、连接远程数据库:

  1. MySQL、PostgreSQL 都支持通过-h指定数据库地址,ORACLE 是通过 @ 来指定的,PostgreSQL 也支持通过 @ 来指定
  2. ORACLE 支持通过 特殊的tnsnames 来登录

3、连接本地数据库:

  1. ORACLE、PostgreSQL 都跟操作系统的用户有关,使用安装数据库的操作系统用户,可以直接通过免密码登录,MySQL 跟操作系统的用户没有关系,不支持直接免密码登录(如果需要免密登录,需要特殊配置,后面详讲)。
  2. MySQL、PostgreSQL 都支持通过 Linux的 socket 方式登录,ORACLE 没有 socket 的方式登录。