利用MySQL或obclient客户端毗连OceanBase数据库
==========================================现供职于某上市互联网公司担当DBA
Oracle & PG ACE称号，
拥有 Oracle OCM、AWS、以及部分国产数据库等产品认证。
喜好技术分享，热爱交友，也热爱健身。
2019年加入墨天轮，目前已发表了一百多篇原创文章，曾多次获评"月度墨力之星"。
OceanBase、PG中国分会、IvorySQL等南京地域组织者，也是TeckTalk技术交流社区首创人。
个人微信公众号： 尚雷的驿站
可微信公众号搜索 ： 尚雷的驿站
==========================================
一、MySQL毗连OceanBase
1.1、安装MySQL客户端
注：本次选择在Centos7上安装最新的MySQL 8.0 客户端。
1.1.1 安装Yum Repository
# 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 kB00: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
===============================================================================================================================================================================================================================================================================
Install1 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客户端
# 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
replacingmariadb-libs.x86_64 1:5.5.68-1.el7
mysql-community-libs-compat x86_64 8.0.31-1.el7 mysql80-community 670 k
replacingmariadb-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
===============================================================================================================================================================================================================================================================================
Install3 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 MB00: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 MB00:00:03
(2/5): mysql-community-common-8.0.31-1.el7.x86_64.rpm | 647 kB00:00:00
(3/5): mysql-community-libs-8.0.31-1.el7.x86_64.rpm | 1.5 MB00:00:00
(4/5): mysql-community-libs-compat-8.0.31-1.el7.x86_64.rpm | 670 kB00:00:00
(5/5): mysql-community-client-8.0.31-1.el7.x86_64.rpm |16 MB00:00:06
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 3.4 MB/s |21 MB00: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客户端版本
# mysql --version
mysqlVer 8.0.31 for Linux on x86_64 (MySQL Community Server - GPL)
# mysql --help
mysqlVer 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
-?, --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数据库
$ mysql -h 10.110.xxx.xxx -uroot@sys#xxxx -pxxxx -P2883 -c -A oceanbase
mysql: 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默认加密算法测试
$ mysql -h 10.110.3.154 -uroot@sys#obcluster -pob@Passwd -P2883 -c -A oceanbase --default_auth=mysql_native_pasowrd
mysql: 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
$ mysql -h 10.110.3.154 -uroot@sys#obcluster -pob@Passwd -P2883 -c -A oceanbase --default_auth=caching_sha2_password
mysql: 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数据库，以下是测试毗连企业版。
$ mysql -h10.110.xxx.xxx -P2883 -uSYS@xxxx -pbbBB22__
mysql: 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
$ mysql -h10.110.xxx.xxx -P2883 -uSYS@xxx -pbbBB22__ --default_auth=mysql_native_pasowrd
mysql: 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版本信息
$ obclient --version
obclientVerDistrib 10.4.18-MariaDB, for Linux (x86_64) using readline 5.1
$ obclient --help
obclientVerDistrib 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
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
$ 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 > show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| LBACSYS |
| mysql |
| oceanbase |
| ORAAUDITOR |
| SYS |
| test |
+--------------------+
7 rows in set (0.277 sec)
obclient >
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]