Vertica9.1.1数据库3节点集群部署
- 主机环境准备
| IP | 主机名 | 服务器配置 | Swap 分区 | 文件系统 | 磁盘大小 | 挂载点 | OS |
|---|---|---|---|---|---|---|---|
| 192.168.81.65 | vertica01 | 4核8G内存 | 4G | ext4 | 120G | /opt | Centos7.6 |
| 192.168.81.66 | vertica02 | 4核8G内存 | 4G | ext4 | 120G | /opt | Centos7.6 |
| 192.168.81.67 | vertica03 | 4核8G内存 | 4G | ext4 | 120G | /opt | Centos7.6 |
- 安装前准备
2.1 修改主机名和/ect/hosts文件并关闭selinux
centos7修改主机名的正确方法(三个节点都操作)
[root@vertica01 opt]#hostnamectl set-hostname vertica01
三个节点的/etc/hosts文件中都添加
192.168.81.65 vertica01
192.168.81.66 vertica02
192.168.81.67 vertica03
关闭 SELinux
编辑 SELinux 配置文件 vi /etc/selinux/config，将 SELINUX 的值设为 disabled，如下：
SELINUX=disabled;
修改该文件可使重启系统后不启动 SELinux。关闭当前已开启的 SELinux 使用如下命令：
setenforce 0
查看SELinux状态：
1、/usr/sbin/sestatus -v ##如果SELinux status参数为enabled即为开启状态
SELinux status: enabled
2、getenforce ##也可以用这个命令检查
关闭SELinux：
1、临时关闭（不用重启机器）：
setenforce 0 ##设置SELinux 成为permissive模式
##setenforce 1 设置SELinux 成为enforcing模式
2、修改配置文件需要重启机器：
修改/etc/selinux/config 文件
将SELINUX=enforcing改为SELINUX=disabled
三个节点都重启生效，后续操作无特殊说明均为三个节点都执行
reboot
2.2 手工添加swap分区
检查系统是否有至少2G的swap分区
df –Th
没有则手工添加
dd if=/dev/zero of=/home/swap bs=1024 count=2048000
mkswap /home/swap
swapon /home/swap
echo “/home/swap swap swap defaults 0 0” >> /etc/fstab
验证是否添加成功
free –m
2.3 关闭防火墙
systemctl stop firewalld.service
systemctl disable firewalld.service
firewall-cmd --state
2.4 关闭非必要的服务
systemctl disable avahi-daemon.service
systemctl disable avahi-dnsconfd.service
systemctl disable conman.service
systemctl disable bluetooth.service
systemctl disable cpuspeed.service
systemctl disable setroubleshoot.service
2.5 修改系统参数/etc/sysctl.conf
修改/etc/sysctl.conf文件增加以下参数
kernel.pid_max = 524288
vm.max_map_count = 16527169
vm.dirty_background_ratio = 1
vm.dirty_ratio = 2
kernel.hung_task_panic = 0
生效
sysctl -p
2.6 修改磁盘预读参数/etc/rc.local
修改/etc/rc.local文件增加以下内容
#echo deadline > /sys/block/vda/queue/scheduler
/sbin/blockdev --setra 8192 /dev/vda
echo always > /sys/kernel/mm/transparent_hugepage/enabled
echo always > /sys/kernel/mm/transparent_hugepage/defrag
echo 0 > /proc/sys/vm/swappiness
生效
source /etc/rc.local
2.7 设置语言和时区/etc/profile
修改/etc/profile文件增加以下内容：
export LANG=“en_US.UTF-8”
export TZ=“Asia/Shanghai”
生效
source /etc/profile
2.8 配置本地yum源安装支撑工具
上传centos7.6安装介质CentOS-7-x86_64-DVD-1810.iso到/opt目录
挂载iso文件
mount /opt/*.iso /mnt –r
备份其他源到bak目录
mkdir /etc/yum.repos.d/bak
mv /etc/yum.repos.d/*.repo /etc/yum.repos.d/bak
创建/etc/yum.repos.d/base.repo新增如下内容
[base]
name=base
baseurl=file:///mnt/
gpgcheck=0
enabled=1
清理yum缓存，查看本地源是否配置成功
yum clean all
yum list
安装支撑工具
yum -y install mcelog
yum -y install sysstat
yum -y install dialog
2.9 建立多机互信
创建ssh配置文件
所有节点（执行）
ssh-keygen -t rsa
一路默认回车
该命令会生成密钥文件和私钥文件id_rsa,id_rsa.pub
将vh001服务器上的id_rsa.pub合并到vertica02和vertica03的~/.ssh/authorized_keys文件
ssh-copy-id -i /root/.ssh/id_rsa.pub vertica01
ssh-copy-id -i /root/.ssh/id_rsa.pub vertica02
ssh-copy-id -i /root/.ssh/id_rsa.pub vertica03
在vh002和vh003上分别同样做如上操作
节点2
ssh-copy-id -i /root/.ssh/id_rsa.pub vertica01
ssh-copy-id -i /root/.ssh/id_rsa.pub vertica02
ssh-copy-id -i /root/.ssh/id_rsa.pub vertica03
节点3
ssh-copy-id -i /root/.ssh/id_rsa.pub vertica01
ssh-copy-id -i /root/.ssh/id_rsa.pub vertica02
ssh-copy-id -i /root/.ssh/id_rsa.pub vertica03
测试免密码登录
ssh vertica02
exit
2.10 配置时钟同步
yum install ntp
设置vertica01服务器做内部时钟服务器
vh001修改/etc/ntp.conf文件，添加
server 127.127.1.0
启动服务
systemctl start ntpd.service
systemctl enable ntpd.service
vertica02服务器优先与vertica01服务器同步，修改/etc/ntp.conf启动ntpd服务
/etc/ntp.conf增加以下内容：
server vertica01 prefer
启动服务
systemctl start ntpd.service
systemctl enable ntpd.service
vertica03服务器优先与vertica01服务器同步，其次与vertica02同步，修改/etc/ntp.conf启动ntpd服务
/etc/ntp.conf增加以下内容：
server vertica01 prefer
server vertica02
启动服务
systemctl start ntpd.service
systemctl enable ntpd.service
检查时钟同步结果
ntpdate -u vertica01
- 安装vertica
3.1 使用root用户安装vertica rpm包
将vertica-9.1.1-0.x86_64.RHEL6.rpm上传到vertica01节点/opt目录
升级安装rmp包
rpm -Uvh vertica-9.1.1-3.x86_64.RHEL6.rpm
3.2 使用root用户安装vertica数据库
/opt/vertica/sbin/install_vertica --hosts vertica01,vertica02,vertica03 --rpm vertica-9.1.1-3.x86_64.RHEL6.rpm
屏幕输出如下：
无FAIL字眼则为安装成功。
如果提示有WARN或FATAL，按照提示解决
/opt/vertica/sbin/install_vertica --host vertica01,vertica02,vertica03 --rpm vertica-9.1.1-3.x86_64.RHEL6.rpm --failure-threshold NONE
[root@vertica01 opt]# /opt/vertica/sbin/install_vertica --host vertica01,vertica02,vertica03 --rpm vertica-9.1.1-3.x86_64.RHEL6.rpm
Vertica Analytic Database 9.1.1-0 Installation Tool
Validating options…
Mapping hostnames in --hosts (-s) to addresses…
vertica01 => 192.168.81.65
vertica02 => 192.168.81.66
vertica03 => 192.168.81.67
Starting installation tasks.
Getting system information for cluster (this may take a while)…
Default shell on nodes:
192.168.81.67 /bin/bash
192.168.81.66 /bin/bash
192.168.81.65 /bin/bash
Validating software versions (rpm or deb)…
Beginning new cluster creation…
successfully backed up admintools.conf on 192.168.81.65
Installing software (rpm or deb)…
Installing rpm on 2 hosts…
installing node… 192.168.81.67
installing node… 192.168.81.66
Creating or validating DB Admin user/group…
Password for new dbadmin user (empty = disabled)
Successful on hosts (3): 192.168.81.67 192.168.81.66 192.168.81.65
Provided DB Admin account details: user = dbadmin, group = verticadba, home = /home/dbadmin
Creating group... Adding group
Validating group... Okay
Creating user... Adding user, Setting credentials
Validating user... Okay
Validating node and cluster prerequisites…
System prerequisites passed. Threshold = WARN
Establishing DB Admin SSH connectivity…
Installing/Repairing SSH keys for dbadmin
Setting up each node and modifying cluster…
Creating Vertica Data Directory…
Updating agent…
Creating node node0001 definition for host 192.168.81.65
… Done
Creating node node0002 definition for host 192.168.81.66
… Done
Creating node node0003 definition for host 192.168.81.67
… Done
Sending new cluster configuration to all nodes…
Starting agent…
Completing installation…
Running upgrade logic
No spread upgrade required: /opt/vertica/config/vspread.conf not found on any node
Installation complete.
Please evaluate your hardware using Vertica’s validation tools:
https://my.vertica.com/docs/9.1.x/HTML/index.htm#cshid=VALSCRIPT
To create a database:
-
Logout and login as dbadmin. (see note below)
-
Run /opt/vertica/bin/adminTools as dbadmin
-
Select Create Database from the Configuration Menu
Note: Installation may have made configuration changes to dbadmin
that do not take effect until the next session (logout and login).
To add or remove hosts, select Cluster Management from the Advanced Menu.
[root@vertica01 opt]#
初始化数据库
[root@localhost ~]# su - dbadmin
[dbadmin@localhost ~]$ cd /opt/vertica/bin/
[dbadmin@localhost bin]$ ./admintools
1、提示输入License File Path时，因为用的是社区版license，不用填，直接回车，
2、Accept EULA
3、Configuration Menu
4、Create Database
5、输入Database Name: dbtest
6、输入密码和确认密码
7、选择主机IP，选择所有的主机IP
8、确认数据实例安装目录，不用改，直接用默认的，/opt/dbadmin
9、会出现一个k-safe的提示，这是只有一个节点导致的，直接忽略，继续下一步
10、安装成功
*** Creating database: dbtest ***
Creating database dbtest
Starting bootstrap node v_dbtest_node0001 (192.168.81.65)
Starting nodes:
v_dbtest_node0001 (192.168.81.65)
Starting Vertica on all nodes. Please wait, databases with a large catalog may take a while to initialize.
Node Status: v_dbtest_node0001: (DOWN)
Node Status: v_dbtest_node0001: (DOWN)
Node Status: v_dbtest_node0001: (DOWN)
Node Status: v_dbtest_node0001: (DOWN)
Node Status: v_dbtest_node0001: (UP)
Automatically installing extension packages
Package: place
Success: package place successfully installed
Package: ParquetExport
Success: package ParquetExport successfully installed
Package: logsearch
Success: package logsearch successfully installed
Package: flextable
Success: package flextable successfully installed
Package: MachineLearning
Success: package MachineLearning successfully installed
Package: kafka
Success: package kafka successfully installed
Package: AWS
Success: package AWS successfully installed
Package: txtindex
Success: package txtindex successfully installed
Package: voltagesecure
Success: package voltagesecure successfully installed
Package: approximate
Success: package approximate successfully installed
21、测试连接
[dbadmin@localhost bin]$ vsql -h 127.0.0.1 -d dbtest -U dbadmin
User name: dbadmin
Password: ******
Welcome to vsql, the Vertica Analytic Database interactive terminal.
Type: \h or ? for help with vsql commands
\g or terminate with semicolon to execute query
\q to quit
dbtest=>
测试成功！
22、查询和设置k-safe参数
dbtest=> SELECT current_fault_tolerance FROM system;
寻找当ksafe等于1时候相关的节点
dbtest=> SELECT * FROM v_monitor.critical_nodes;
设置不同ksafe值对节点的要求
K-level Number of Nodes Required
1 3+
2 5+
K 2K+1
一般k最大为2
通常情况下设置ksafe值为1，如下：
dbtest=> SELECT MARK_DESIGN_KSAFE(1);
如果要带上密码，可以用“-w”，如下：
vsql -h 127.0.0.1 -d dbtest -U dbadmin -w XXXXXX
---------------------------------------------------------------
社区版vertica默认只允许3节点和处理1TB的原始数据(raw data)。
可以通过“select get_compliance_status();”命令来查看当前的使用情况：
3.3 安装MC管理工具
上传vertica-console-9.1.1-3.x86_64.RHEL6.rpm到vertica01节点/opt目录进行安装
rpm -Uvh vertica-console-9.1.1-3.x86_64.RHEL6.rpm
安装日志如下：
[root@vertica01 opt]# rpm -Uvh vertica-console-9.1.1-3.x86_64.RHEL6.rpm
Preparing… ################################# [100%]
[preinstall] Starting installation…
Updating / installing…
1:vertica-console-9.1.1-3 ################################# [100%]
[postinstall] copy vertica-consoled
[postinstall] configure the daemon service
Cleaning up temp folder…
Starting the vertica management console…
Vertica Console: 2021-09-08 10:18:07.358:INFO:cv.Startup:Attempting to load properties from /opt/vconsole/config/console.properties
2021-09-08 10:18:07.359:INFO:cv.Startup:Starting Server…
2021-09-08 10:18:07.439:INFO:cv.Startup:starting monitor thread
2021-09-08 10:18:07.444:INFO:oejs.Server:jetty-7.x.y-SNAPSHOT
2021-09-08 10:18:07.476:INFO:oejw.WebInfConfiguration:Extract jar:file:/opt/vconsole/lib/webui.war!/ to /opt/vconsole/temp/webapp
2021-09-08 10:18:13.342:INFO:/webui:Set web app root system property: ‘webapp.root’ = [/opt/vconsole/temp/webapp]
2021-09-08 10:18:13.369:INFO:/webui:Initializing log4j from [classpath:log4j.xml]
2021-09-08 10:18:13.392:INFO:/webui:Initializing Spring root WebApplicationContext
---- Upgrading /opt/vconsole/config/console.properties ----
Please open the Vertica Management Console at https://vertica01:5450/webui
2021-09-08 10:18:41.844:INFO:oejsh.ContextHandler:started o.e.j.w.WebAppContext{/webui,file:/opt/vconsole/temp/webapp/},file:/opt/vconsole/lib/webui.war
2021-09-08 10:18:41.912:INFO:/webui:Initializing Spring FrameworkServlet ‘appServlet’
2021-09-08 10:18:44.695:INFO:oejdp.ScanningAppProvider:Deployment monitor /opt/vconsole/webapps at interval 2
2021-09-08 10:18:44.759:INFO:oejhs.SslContextFactory:Enabled Protocols [SSLv2Hello, TLSv1, TLSv1.1, TLSv1.2] of [SSLv2Hello, SSLv3, TLSv1, TLSv1.1, TLSv1.2]
2021-09-08 10:18:44.780:INFO:oejs.AbstractConnector:Started SslSelectChannelConnector@0.0.0.0:5450 STARTING
start OK
[postinstall] Changing permissions of /opt/vconsole [ OK ]
[root@vertica01 opt]#
打开 https://192.168.81.65:5450/webui 进行验证配置
- 配置vertica集群数据库
4.1 使用MC配置vertica - 创建集群
- 在集群上创建数据库
导入已有vertica集群数据库
1、Import Vertica Database Cluster
2、弹窗输入主节点IP地址
3、找到apikeys.dat文件中的apikey（/opt/vertica/config/apikeys.dat ） 输入apkid
4、输入管理用户和密码 dbadmin dbadmin
4.2 使用vsql操作vertica
[root@vertica01 opt]# su - dbadmin
[dbadmin@vertica01 ~]$ vsql
Password:
Welcome to vsql, the Vertica Analytic Database interactive terminal.
Type: \h or ? for help with vsql commands
\g or terminate with semicolon to execute query
\q to quit
dbadmin=> \h
See https://my.vertica.com/documentation/vertica/9.1.x for information on available commands.
General
\c[onnect] [DBNAME|- [USER]]
connect to new database (currently "dbadmin")
\cd [DIR] change the current working directory
\q quit vsql
\set [NAME [VALUE]]
set internal variable, or list all if no parameters
\timing [on|off]
toggle timing of commands, or explicitly turn it on or off (currently off)
\unset NAME unset (delete) internal variable
! [COMMAND] execute command in shell or start interactive shell
\password [USER]
change user's password
Query Buffer
\e [FILE] edit the query buffer (or file) with external editor
\g send query buffer to server
\g FILE send query buffer to server and results to file
\g | COMMAND send query buffer to server and pipe results to command
\p show the contents of the query buffer
\r reset (clear) the query buffer
\s [FILE] display history or save it to file
\w FILE write query buffer to file
Input/Output
\echo [STRING] write string to standard output
\i FILE execute commands from file
\o FILE send all query results to file
\o | COMMAND pipe all query results to command
\o close query-results file or pipe
\qecho [STRING]
write string to query output stream (see \o)
Informational
\d [PATTERN] describe tables (list tables if no argument is supplied)
PATTERN may include system schema name, e.g. v_catalog.*
\df [PATTERN] list functions
\dj [PATTERN] list projections
\dn [PATTERN] list schemas
\dp [PATTERN] list table access privileges
\ds [PATTERN] list sequences
\dS [PATTERN] list system tables. PATTERN may include system schema name
such as v_catalog, v_monitor, or v_internal.
Example: v_catalog.a*
\dt [PATTERN] list tables
\dtv [PATTERN] list tables and views
\dT [PATTERN] list data types
\du [PATTERN] list users
\dv [PATTERN] list views
\l list all databases
\z [PATTERN] list table access privileges (same as \dp)
Formatting
\a toggle between unaligned and aligned output mode
\b toggle beep on command completion
\C [STRING] set table title, or unset if none
\f [STRING] show or set field separator for unaligned query output
\H toggle HTML output mode (currently off)
\pset NAME [VALUE]
set table output option
(NAME := {format|border|expanded|fieldsep|footer|null|
recordsep|trailingrecordsep|tuples_only|title|tableattr|pager})
\t show only rows (currently off)
\T [STRING] set HTML
