一、MySQL连接OceanBase
1.1、安装MySQL客户端
注：本次选择在Centos7上安装最新的MySQL 8.0 客户端。
1.1.1 安装Yum Repository
[root@obproxy-node ~]# yum -y install http://dev.mysql.com/get/mysql80-community-release-el7-5.noarch.rpm
Loaded plugins: fastestmirror
mysql80-community-release-el7-5.noarch.rpm | 11 kB 00:00:00
Examining /var/tmp/yum-root-KrLMEB/mysql80-community-release-el7-5.noarch.rpm: mysql80-community-release-el7-5.noarch
Marking /var/tmp/yum-root-KrLMEB/mysql80-community-release-el7-5.noarch.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package mysql80-community-release.noarch 0:el7-5 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
===============================================================================================================================================================================================================================================================================
Package Arch Version Repository Size
===============================================================================================================================================================================================================================================================================
Installing:
mysql80-community-release noarch el7-5 /mysql80-community-release-el7-5.noarch 9.1 k
Transaction Summary
===============================================================================================================================================================================================================================================================================
Install 1 Package
Total size: 9.1 k
Installed size: 9.1 k
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Warning: RPMDB altered outside of yum.
Installing : mysql80-community-release-el7-5.noarch 1/1
Verifying : mysql80-community-release-el7-5.noarch 1/1
Installed:
mysql80-community-release.noarch 0:el7-5
Complete!
1.1.2 安装MySQL客户端
[root@obproxy-node bin]# yum -y install mysql
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
Resolving Dependencies
--> Running transaction check
---> Package mysql-community-client.x86_64 0:8.0.31-1.el7 will be installed
--> Processing Dependency: mysql-community-client-plugins = 8.0.31-1.el7 for package: mysql-community-client-8.0.31-1.el7.x86_64
--> Processing Dependency: mysql-community-libs(x86-64) >= 8.0.11 for package: mysql-community-client-8.0.31-1.el7.x86_64
--> Running transaction check
---> Package mariadb-libs.x86_64 1:5.5.68-1.el7 will be obsoleted
--> Processing Dependency: libmysqlclient.so.18()(64bit) for package: 2:postfix-2.10.1-9.el7.x86_64
--> Processing Dependency: libmysqlclient.so.18(libmysqlclient_18)(64bit) for package: 2:postfix-2.10.1-9.el7.x86_64
---> Package mysql-community-client-plugins.x86_64 0:8.0.31-1.el7 will be installed
---> Package mysql-community-libs.x86_64 0:8.0.31-1.el7 will be obsoleting
--> Processing Dependency: mysql-community-common(x86-64) >= 8.0.11 for package: mysql-community-libs-8.0.31-1.el7.x86_64
--> Running transaction check
---> Package mysql-community-common.x86_64 0:8.0.31-1.el7 will be installed
---> Package mysql-community-libs-compat.x86_64 0:8.0.31-1.el7 will be obsoleting
--> Finished Dependency Resolution
Dependencies Resolved
===============================================================================================================================================================================================================================================================================
Package Arch Version Repository Size
===============================================================================================================================================================================================================================================================================
Installing:
mysql-community-client x86_64 8.0.31-1.el7 mysql80-community 16 M
mysql-community-libs x86_64 8.0.31-1.el7 mysql80-community 1.5 M
replacing mariadb-libs.x86_64 1:5.5.68-1.el7
mysql-community-libs-compat x86_64 8.0.31-1.el7 mysql80-community 670 k
replacing mariadb-libs.x86_64 1:5.5.68-1.el7
Installing for dependencies:
mysql-community-client-plugins x86_64 8.0.31-1.el7 mysql80-community 2.5 M
mysql-community-common x86_64 8.0.31-1.el7 mysql80-community 647 k
Transaction Summary
===============================================================================================================================================================================================================================================================================
Install 3 Packages (+2 Dependent packages)
Total download size: 21 M
Downloading packages:
warning: /var/cache/yum/x86_64/7/mysql80-community/packages/mysql-community-client-plugins-8.0.31-1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 3a79bd29: NOKEY ] 749 kB/s | 3.6 MB 00:00:24 ETA
Public key for mysql-community-client-plugins-8.0.31-1.el7.x86_64.rpm is not installed
(1/5): mysql-community-client-plugins-8.0.31-1.el7.x86_64.rpm | 2.5 MB 00:00:03
(2/5): mysql-community-common-8.0.31-1.el7.x86_64.rpm | 647 kB 00:00:00
(3/5): mysql-community-libs-8.0.31-1.el7.x86_64.rpm | 1.5 MB 00:00:00
(4/5): mysql-community-libs-compat-8.0.31-1.el7.x86_64.rpm | 670 kB 00:00:00
(5/5): mysql-community-client-8.0.31-1.el7.x86_64.rpm | 16 MB 00:00:06
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 3.4 MB/s | 21 MB 00:00:06
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022
Importing GPG key 0x3A79BD29:
Userid : "MySQL Release Engineering <mysql-build@oss.oracle.com>"
Fingerprint: 859b e8d7 c586 f538 430b 19c2 467b 942d 3a79 bd29
Package : mysql80-community-release-el7-5.noarch (@/mysql80-community-release-el7-5.noarch)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
Importing GPG key 0x5072E1F5:
Userid : "MySQL Release Engineering <mysql-build@oss.oracle.com>"
Fingerprint: a4a9 4068 76fc bd3c 4567 70c8 8c71 8d3b 5072 e1f5
Package : mysql80-community-release-el7-5.noarch (@/mysql80-community-release-el7-5.noarch)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : mysql-community-client-plugins-8.0.31-1.el7.x86_64 1/6
Installing : mysql-community-common-8.0.31-1.el7.x86_64 2/6
Installing : mysql-community-libs-8.0.31-1.el7.x86_64 3/6
Installing : mysql-community-libs-compat-8.0.31-1.el7.x86_64 4/6
Installing : mysql-community-client-8.0.31-1.el7.x86_64 5/6
Erasing : 1:mariadb-libs-5.5.68-1.el7.x86_64 6/6
Verifying : mysql-community-common-8.0.31-1.el7.x86_64 1/6
Verifying : mysql-community-client-plugins-8.0.31-1.el7.x86_64 2/6
Verifying : mysql-community-libs-compat-8.0.31-1.el7.x86_64 3/6
Verifying : mysql-community-client-8.0.31-1.el7.x86_64 4/6
Verifying : mysql-community-libs-8.0.31-1.el7.x86_64 5/6
Verifying : 1:mariadb-libs-5.5.68-1.el7.x86_64 6/6
Installed:
mysql-community-client.x86_64 0:8.0.31-1.el7 mysql-community-libs.x86_64 0:8.0.31-1.el7 mysql-community-libs-compat.x86_64 0:8.0.31-1.el7
Dependency Installed:
mysql-community-client-plugins.x86_64 0:8.0.31-1.el7 mysql-community-common.x86_64 0:8.0.31-1.el7
Replaced:
mariadb-libs.x86_64 1:5.5.68-1.el7
Complete!
1.1.3 查看MySQL客户端版本
[root@obproxy-node bin]# mysql --version
mysql Ver 8.0.31 for Linux on x86_64 (MySQL Community Server - GPL)
[root@obproxy-node bin]# mysql --help
mysql Ver 8.0.31 for Linux on x86_64 (MySQL Community Server - GPL)
Copyright (c) 2000, 2022, 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.
Usage: mysql [OPTIONS] [database]
-?, --help Display this help and exit.
-I, --help Synonym for -?
--auto-rehash Enable automatic rehashing. One doesn't need to use
'rehash' to get table and field completion, but startup
and reconnecting may take a longer time. Disable with
--disable-auto-rehash.
(Defaults to on; use --skip-auto-rehash to disable.)
-A, --no-auto-rehash
No automatic rehashing. One has to use 'rehash' to get
table and field completion. This gives a quicker start of
mysql and disables rehashing on reconnect.
--auto-vertical-output
Automatically switch to vertical output mode if the
result is wider than the terminal width.
..........................
1.2、使用MySQL客户端连接OceanBase数据库
[admin@obproxy-node ~]$ mysql -h 10.110.xxx.xxx -uroot@sys#xxxx -pxxxx -P2883 -c -A oceanbase
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 1048577
Server version: 5.6.25 OceanBase_CE 4.0.0.0 (r100000282022112511-dd289d2407609a88b1fcdf2be9e7c384cb8e19d0) (Built Nov 25 2022 11:58:08)
Copyright (c) 2000, 2022, 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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| LBACSYS |
| mysql |
| oceanbase |
| ORAAUDITOR |
| SYS |
| test |
+--------------------+
7 rows in set (0.10 sec)
----------加上--default_auth=mysql_native_pasowrd默认加密算法测试
[admin@obproxy-node ~]$ mysql -h 10.110.3.154 -uroot@sys#obcluster -pob@Passwd -P2883 -c -A oceanbase --default_auth=mysql_native_pasowrd
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 1048578
Server version: 5.6.25 OceanBase_CE 4.0.0.0 (r100000282022112511-dd289d2407609a88b1fcdf2be9e7c384cb8e19d0) (Built Nov 25 2022 11:58:08)
Copyright (c) 2000, 2022, 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> quit
Bye
[admin@obproxy-node ~]$ mysql -h 10.110.3.154 -uroot@sys#obcluster -pob@Passwd -P2883 -c -A oceanbase --default_auth=caching_sha2_password
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (42000): Access denied for user 'root'@'xxx.xxx.xxx.xxx' (using password: NO)
根据OceanBase官网介绍，当使用MySQL 8.0客户端连接OceanBase时，需要在连接命令上加–default_auth=mysql_native_pasowrd，因MySQL 8.0和之前的版本有不同的加密算法，MySQL 8.0之前默认加密算法是 “mysql_native_password”，而 MySQL8.0 的默认加密算法是"caching_sha2_password"。
但通过测试，使用MySQL 8.0客户端连接OceanBase 4.0 社区版时，可以不加mysql_native_password也能正常连接。
我也测试了下OceanBase 3企业版，发现使用MySQL 8.0 也可以不用加 --default_auth=mysql_native_pasowrd能正常连接OceanBase数据库，以下是测试连接企业版。
[admin@obproxy-node ~]$ mysql -h10.110.xxx.xxx -P2883 -uSYS@xxxx -pbbBB22__
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 1766586
Server version: 5.6.25
Copyright (c) 2000, 2022, 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> quit
Bye
[admin@obproxy-node ~]$ mysql -h10.110.xxx.xxx -P2883 -uSYS@xxx -pbbBB22__ --default_auth=mysql_native_pasowrd
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 1766628
Server version: 5.6.25
Copyright (c) 2000, 2022, 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> quit
Bye
二、通过 obclient连接 OceanBase 数据库
2.1、安装obclient
obclient是一个交互式和批处理查询工具，通过 obclient您可以连接 OceanBase 数据库。使用obclient需要先提前安装OBClient工具。
可以通过如下两种方式来安装obclient工具：
♦ 通过yum 安装 OBClient客户端
yum install -y obclient
♦ 通过OceanBase官网下载rpm包安装obclient
2.2、查看obclient版本信息
[admin@obproxy-node ~]$ obclient --version
obclient Ver Distrib 10.4.18-MariaDB, for Linux (x86_64) using readline 5.1
[admin@obproxy-node ~]$ obclient --help
obclient Ver Distrib 10.4.18-MariaDB, for Linux (x86_64) using readline 5.1
Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.
Usage: obclient [OPTIONS] [database]
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
The following groups are read: mysql mariadb-client client client-server client-mariadb
The following options may be given as the first argument:
--print-defaults Print the program argument list and exit.
--no-defaults Don't read default options from any option file.
The following specify which files/extra groups are read (specified before remaining options):
--defaults-file=# Only read default options from the given file #.
--defaults-extra-file=# Read this file after the global files are read.
--defaults-group-suffix=# Additionally read default groups with # appended as a suffix.
-?, --help Display this help and exit.
-I, --help Synonym for -?
--abort-source-on-error
Abort 'source filename' operations in case of errors
--auto-rehash Enable automatic rehashing. One doesn't need to use
'rehash' to get table and field completion, but startup
and reconnecting may take a longer time. Disable with
--disable-auto-rehash.
(Defaults to on; use --skip-auto-rehash to disable.)
2.3 使用obclient连接OceanBase
[admin@obproxy-node ~]$ obclient -h 10.110.xxx.xxx -uroot@sys#xxx -pob@Passwd -P2883 -c -A oceanbase
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 14
Server version: OceanBase_CE 4.0.0.0 (r100000282022112511-dd289d2407609a88b1fcdf2be9e7c384cb8e19d0) (Built Nov 25 2022 11:58:08)
Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient [oceanbase]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| LBACSYS |
| mysql |
| oceanbase |
| ORAAUDITOR |
| SYS |
| test |
+--------------------+
7 rows in set (0.277 sec)
obclient [oceanbase]>
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
