Environmental planning
The environment is like this. There is only one virtual machine configured with 16c80G. It is ready to use docker to simulate four CentOS hosts and build oceanbase 3.1 1 community version of the cluster. Each OBServer needs at least 8G of memory, otherwise it cannot be started.
The specific configuration is as follows:
host name | IP | port | Host mapping port | Zone | effect |
|---|---|---|---|---|---|
lhrob1 | 172.72.8.11 | 2881 | 28811 | zone1 | OB Server1 |
lhrob2 | 172.72.8.12 | 2881 | 28812 | zone2 | OB Server2 |
lhrob3 | 172.72.8.13 | 2881 | 28813 | zone3 | OB Server3 |
lhrobproxy | 172.72.8.14 | 2883 | 28814 | Used for OBD, OBProxy, OBClient, mysql client, time server, etc |
Initialization preparation
Application environment
docker network create --subnet=172.72.8.0/16 lhrob-network docker network inspect lhrob-network docker rm -f lhrob1 lhrob2 lhrob3 lhrobproxy docker run -d --name lhrob1 -h lhrob1 \ --net=lhrob-network --ip 172.72.8.11 \ -p 28811:2881 \ -v /sys/fs/cgroup:/sys/fs/cgroup \ --privileged=true lhrbest/lhrcentos76:8.5 \ /usr/sbin/init docker run -d --name lhrob2 -h lhrob2 \ --net=lhrob-network --ip 172.72.8.12 \ -p 28812:2881 \ -v /sys/fs/cgroup:/sys/fs/cgroup \ --privileged=true lhrbest/lhrcentos76:8.5 \ /usr/sbin/init docker run -d --name lhrob3 -h lhrob3 \ --net=lhrob-network --ip 172.72.8.13 \ -p 28813:2881 \ -v /sys/fs/cgroup:/sys/fs/cgroup \ --privileged=true lhrbest/lhrcentos76:8.5 \ /usr/sbin/init docker run -d --name lhrobproxy -h lhrobproxy \ --net=lhrob-network --ip 172.72.8.14 \ -p 28814:2883 \ -v /sys/fs/cgroup:/sys/fs/cgroup \ --privileged=true lhrbest/lhrcentos76:8.5 \ /usr/sbin/init docker exec -it lhrobproxy bash
Configure clock source
reference resources: https://open.oceanbase.com/docs/community/oceanbase-database/V3.1.1/optional-configuring-clock-sources
If you use a cluster to install OceanBase, you need to ensure the time synchronization of each machine in the cluster. Otherwise, the cluster cannot be started, and an exception will occur when the service is running. If you have configured NTP clock synchronization, you do not need to reconfigure it.
The server time in the OceanBase cluster must be consistent, otherwise the OceanBase cluster will not start and will fail during operation. When the error between the physical machine and the clock server is less than 50ms, the clock can be considered as synchronous state, and the maximum tolerance error of OceanBase cluster cannot exceed 100ms. When it exceeds 100ms, there will be no owner. After clock synchronization is restored. Restart the OceanBase cluster to restore normal operation.
When deploying the OceanBase cluster, the maximum allowable clock deviation of RPC for each OBServer is 100ms.
Here, "172.72.8.14" is taken as the time server, and the other three observers synchronize the time of the machine:
yum install ntp ntpdate -y ntpq -4p ntpstat timedatectl
1. Modify "172.72.8.14" to time server / etc / NTP conf
# For more information about this file, see the man pages # ntp.conf(5), ntp_acc(5), ntp_auth(5), ntp_clock(5), ntp_misc(5), ntp_mon(5). driftfile /var/lib/ntp/drift #Add: log directory logfile /var/log/ntpd.log # Permit time synchronization with our time source, but do not # permit the source to query or modify the service on this system. restrict default nomodify notrap nopeer noquery # Permit all access over the loopback interface. This could # be tightened as well, but to do so would effect some of # the administrative functions. restrict 127.0.0.1 restrict ::1 #New: this line means authorization 172.72 All machines on the 8.0 network segment can query and synchronize time from this machine restrict 172.72.8.0 mask 255.255.255.0 nomodify notrap # Hosts on local network are less restricted. #restrict 192.168.1.0 mask 255.255.255.0 nomodify notrap # Use public servers from the pool.ntp.org project. # Please consider joining the pool (http://www.pool.ntp.org/join.html). #server 0.centos.pool.ntp.org iburst #server 1.centos.pool.ntp.org iburst #server 2.centos.pool.ntp.org iburst #server 3.centos.pool.ntp.org iburst #New: time server list server 0.cn.pool.ntp.org iburst server 1.cn.pool.ntp.org iburst server 2.cn.pool.ntp.org iburst server 3.cn.pool.ntp.org iburst #New: use local time when external time is unavailable server 127.0.0.1 iburst fudge 127.0.0.1 stratum 10 #broadcast 192.168.1.255 autokey # broadcast server #broadcastclient # broadcast client #broadcast 224.0.1.1 autokey # multicast server #multicastclient 224.0.1.1 # multicast client #manycastserver 239.255.254.254 # manycast server #manycastclient 239.255.254.254 autokey # manycast client # Enable public key cryptography. #crypto includefile /etc/ntp/crypto/pw # Key file containing the keys and key identifiers used when operating # with symmetric key cryptography. keys /etc/ntp/keys # Specify the key identifiers which are trusted. #trustedkey 4 8 42 # Specify the key identifier to use with the ntpdc utility. #requestkey 8 # Specify the key identifier to use with the ntpq utility. #controlkey 8 # Enable writing of statistics records. #statistics clockstats cryptostats loopstats peerstats # Disable the monitoring facility to prevent amplification attacks using ntpdc # monlist command when default restrict does not include the noquery flag. See # CVE-2013-5211 for more details. # Note: Monitoring will not be disabled with the limited restriction flag. disable monitor
Configure startup:
systemctl enable ntpd systemctl is-enabled ntpd ntpdate -u 1.cn.pool.ntp.org systemctl restart ntpd [root@lhrobproxy /]# ntpstat synchronised to NTP server (84.16.73.33) at stratum 2 time correct to within 98 ms polling server every 64 s
For other clients, modify "/ etc/ntp.conf", comment the line beginning with server, and add the following line:
server 172.72.8.14 restrict 172.72.8.14 nomodify notrap noquery server 127.0.0.1 fudge 127.0.0.1 stratum 10
Configure startup:
systemctl enable ntpd systemctl restart ntpd
Client configuration auto Sync:
crontab -e * * * * * /usr/sbin/ntpdate -u 172.72.8.14 & > /dev/null
Configure kernel parameters
All 4 nodes are running:
cat >> /etc/security/limits.conf <<"EOF" root soft nofile 655350 root hard nofile 655350 * soft nofile 655350 * hard nofile 655350 * soft stack 20480 * hard stack 20480 * soft nproc 655360 * hard nproc 655360 * soft core unlimited * hard core unlimited EOF echo "fs.aio-max-nr=1048576" >> /etc/sysctl.conf sysctl -p
If it's just a test, you can set only FS aio-max-nr=1048576.
Create user
useradd -U admin -d /home/admin -s /bin/bash echo "admin:lhr" | chpasswd chown -R admin:admin /home/admin echo "admin ALL=(ALL) NOPASSWD: ALL" >> /etc/sudoers
Set password less SSH login
You can use sshusersetup on rac SH quick configuration, running only on lhrobproxy:
sh sshUserSetup.sh -user admin -hosts "lhrob1 lhrob2 lhrob3 lhrobproxy" -advanced exverify -confirm
Install cluster
In lhrobproxy operation:
Install OBD
yum install -y yum-utils yum-config-manager --add-repo https://mirrors.aliyun.com/oceanbase/OceanBase.repo yum install -y ob-deploy
Setting the configuration file for yaml
OBD can automatically create clusters based on this yaml file
cat > /tmp/obd_observer_obproxy.yaml <<"EOF"
## Only need to configure when remote login is required
user:
username: admin #User name, provided that the three nodes are consistent
password: lhr #Password, provided that the three nodes are consistent
key_file: #Key, can be omitted
#################### Here is observer Build parameters ##############################
oceanbase-ce:
servers:
- name: z1 # zone name
# Please don't use hostname, only IP can be supported
ip: 172.72.8.11 # OB1 address
- name: z2
ip: 172.72.8.12 # OB2 address
- name: z3
ip: 172.72.8.13 # OB3 address
global:
mysql_port: 2881 # Database port
rpc_port: 2882 # Protocol port number for remote access
home_path: /home/admin/oceanbase # Software Directory
#data_dir: /data # Data directory
#redo_dir: /redo # redo directory
devname: eth0 # Set the network card of the node to be deployed
memory_limit: 8G
system_memory: 2G # The remaining reserved memory of the system is 2G
lower_case_table_names: 1 # The database is not case sensitive
foreign_key_checks: 0 # DML statements do not check external constraints, and DDL operations are not affected
sys_bkgd_migration_retry_num: 5 # Maximum number of retries when replica migration fails.
stack_size: 512K # Sets the size of the program function call stack. The disk should be 512k aligned. If not, it may fail to start
cpu_count: 16 # cpu 16 core
cache_wash_threshold: 1G # Sets the capacity threshold that triggers cache cleanup. If the memory space is less than the specified value, the memory space will be cleaned up.
__min_full_resource_pool_memory: 1073741824 # By default, the minimum memory specification of ordinary tenants must be greater than or equal to 5GB. Here, it is set to 1G, which means that I can set the minimum tenant memory to 1G
workers_per_cpu_quota: 10 # Sets the number of worker threads allocated to each CPU quota.
schema_history_expire_time: 1d # Metadata history data expiration time.
net_thread_count: 4 # Set the number of network I/O threads, The value of net_thread_count had better be same as cpu's core number.
major_freeze_duty_time: Disable
minor_freeze_times: 10 # How many small merges trigger a global merge.
enable_separate_sys_clog: True # Whether to store the system transaction log separately from the user transaction log.
enable_merge_by_turn: FALSE
datafile_size: 5G
# datafile_disk_percentage: 0.1 # Database system initialization is used to store data. For example, 40 is set here, which means 40%. If my single node is 1TB, about 400 G of space will be occupied immediately
syslog_level: INFO # Log alert level
enable_syslog_recycle: True # Enable the function of reclaiming system logs
max_syslog_file_count: 4 # Number of log files
log_dir_size_threshold: 1G
cluster_id: 1 # Cluster ID
# observer cluster name, consistent with obproxy's cluster_name
appname: lhrob311cluster # The cluster name should be consistent with the corresponding obproxy below
root_password: lhr
proxyro_password: lhr
###Next, set the node information
z1:
zone: zone1 # Set the name of the Zone where the node is located
z2:
zone: zone2
z3:
zone: zone3
########################## Here is obrpoxy Construction parameters of ######################3
obproxy:
servers:
- 127.0.0.1
global:
listen_port: 2883
prometheus_listen_port: 2884
home_path: /home/admin/obproxy
# oceanbase root server list
# format: ip:mysql_port,ip:mysql_port
rs_list: 172.72.8.11:2881;172.72.8.12:2881;172.72.8.13:2881
enable_cluster_checkout: false
EOF
Initialize cluster with OBD
sh /etc/profile.d/obd.sh obd cluster deploy lhrob311cluster -c /tmp/obd_observer_obproxy.yaml -f obd cluster list obd cluster display lhrob311cluster obd cluster start lhrob311cluster -- modify parameters obd cluster edit-config lhrob311cluster
Execution process:
[root@lhrobproxy soft]# obd cluster deploy lhrob311cluster -c /tmp/obd_observer_obproxy.yaml -f Update OceanBase-community-stable-el7 ok Update OceanBase-development-kit-el7 ok Download oceanbase-ce-3.1.1-4.el7.x86_64.rpm (46.21 M): 100% [###############################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################] Time: 0:00:18 2.58 MB/s Package oceanbase-ce-3.1.1 is available. Download obproxy-3.1.0-1.el7.x86_64.rpm (7.70 M): 100% [#####################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################] Time: 0:00:01 4.62 MB/s Package obproxy-3.1.0 is available. install oceanbase-ce-3.1.1 for local ok install obproxy-3.1.0 for local ok +-----------------------------------------------------------------------------+ | Packages | +--------------+---------+---------+------------------------------------------+ | Repository | Version | Release | Md5 | +--------------+---------+---------+------------------------------------------+ | oceanbase-ce | 3.1.1 | 4.el7 | f19f8bfb67723712175fb0dfd60579196b3168f1 | | obproxy | 3.1.0 | 1.el7 | 0b17cf0459a3b53c5a2febb6572894d183154c64 | +--------------+---------+---------+------------------------------------------+ Repository integrity check ok Parameter check ok Open ssh connection ok Remote oceanbase-ce-3.1.1-f19f8bfb67723712175fb0dfd60579196b3168f1 repository install ok Remote oceanbase-ce-3.1.1-f19f8bfb67723712175fb0dfd60579196b3168f1 repository lib check !! [WARN] z1(172.72.8.11) oceanbase-ce-3.1.1-f19f8bfb67723712175fb0dfd60579196b3168f1 require: libmariadb.so.3 [WARN] z2(172.72.8.12) oceanbase-ce-3.1.1-f19f8bfb67723712175fb0dfd60579196b3168f1 require: libmariadb.so.3 [WARN] z3(172.72.8.13) oceanbase-ce-3.1.1-f19f8bfb67723712175fb0dfd60579196b3168f1 require: libmariadb.so.3 Remote obproxy-3.1.0-0b17cf0459a3b53c5a2febb6572894d183154c64 repository install ok Remote obproxy-3.1.0-0b17cf0459a3b53c5a2febb6572894d183154c64 repository lib check ok Try to get lib-repository Download oceanbase-ce-libs-3.1.1-4.el7.x86_64.rpm (155.15 K): 100% [#########################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################] Time: 0:00:00 1.87 MB/s Package oceanbase-ce-libs-3.1.1 is available. install oceanbase-ce-libs-3.1.1 for local ok Use oceanbase-ce-libs-3.1.1-58384f7ab4ee736e9d530f4bdd63c20ced0e7aba for oceanbase-ce-3.1.1-f19f8bfb67723712175fb0dfd60579196b3168f1 Remote oceanbase-ce-libs-3.1.1-58384f7ab4ee736e9d530f4bdd63c20ced0e7aba repository install ok Remote oceanbase-ce-3.1.1-f19f8bfb67723712175fb0dfd60579196b3168f1 repository lib check ok Cluster status check ok Initializes observer work home ok Initializes obproxy work home ok lhrob311cluster deployed [root@lhrobproxy soft]# obd cluster list +------------------------------------------------------------------------+ | Cluster List | +-----------------+------------------------------------+-----------------+ | Name | Configuration Path | Status (Cached) | +-----------------+------------------------------------+-----------------+ | lhrob311cluster | /root/.obd/cluster/lhrob311cluster | deployed | +-----------------+------------------------------------+-----------------+ [root@lhrobproxy soft]# obd cluster start lhrob311cluster Get local repositories and plugins ok Open ssh connection ok Cluster param config check ok Check before start observer ok [WARN] (172.72.8.11) clog and data use the same disk (/) [WARN] (172.72.8.12) clog and data use the same disk (/) [WARN] (172.72.8.13) clog and data use the same disk (/) Check before start obproxy ok Start observer ok observer program health check ok Connect to observer ok Initialize cluster Cluster bootstrap ok Wait for observer init ok +-----------------------------------------------+ | observer | +-------------+---------+------+-------+--------+ | ip | version | port | zone | status | +-------------+---------+------+-------+--------+ | 172.72.8.11 | 3.1.1 | 2881 | zone1 | active | | 172.72.8.12 | 3.1.1 | 2881 | zone2 | active | | 172.72.8.13 | 3.1.1 | 2881 | zone3 | active | +-------------+---------+------+-------+--------+ Start obproxy ok obproxy program health check ok Connect to obproxy ok Initialize cluster +---------------------------------------------+ | obproxy | +-----------+------+-----------------+--------+ | ip | port | prometheus_port | status | +-----------+------+-----------------+--------+ | 127.0.0.1 | 2883 | 2884 | active | +-----------+------+-----------------+--------+ lhrob311cluster running [root@lhrobproxy soft]# obd cluster list +------------------------------------------------------------------------+ | Cluster List | +-----------------+------------------------------------+-----------------+ | Name | Configuration Path | Status (Cached) | +-----------------+------------------------------------+-----------------+ | lhrob311cluster | /root/.obd/cluster/lhrob311cluster | running | +-----------------+------------------------------------+-----------------+ [root@lhrobproxy soft]# [root@lhrobproxy soft]# netstat -tulnp| grep 288 tcp 0 0 0.0.0.0:2883 0.0.0.0:* LISTEN 8048/obproxy tcp 0 0 0.0.0.0:2884 0.0.0.0:* LISTEN 8048/obproxy -- The remaining nodes are similar [root@lhrob1 log]# netstat -tulnp| grep 288 tcp 0 0 0.0.0.0:2881 0.0.0.0:* LISTEN 8244/observer tcp 0 0 0.0.0.0:2882 0.0.0.0:* LISTEN 8244/observer
Configure obproxy
https://open.oceanbase.com/articles/1100243
obproxy communicates with OB cluster by using an internal account proxyro in sys tenant. This account needs to be created.
After obproxy is started, it is used by default root@proxysys Login, password is empty. The password needs to be changed (specified through the proxy parameter obproxy_sys_password).
After obproxy is started, you need to change the password of proxyro (specified through the proxy parameter observer_sys_password) to be consistent with the proxyro password created in the OB cluster before you can link the OB cluster.
mysql -h127.1 -uroot@sys -P2881 -plhr -c -A oceanbase create user proxyro identified by 'lhr'; alter user proxyro identified by 'lhr'; grant select on *.* to proxyro; mysql -h127.1 -uroot@proxysys -P2883 -p alter proxyconfig set obproxy_sys_password='lhr'; alter proxyconfig set observer_sys_password='lhr'; show proxyconfig like '%sys_password%'; mysql -h127.1 -uroot@sys -P2883 -plhr -c -A oceanbase mysql -uroot@sys -plhr -h192.168.66.35 -P28814 select * from oceanbase.__all_server; show full processlist;
Install client
yum install -y obclient mariadb mariadb-libs mariadb-devel
You can query:
mysql -uroot@proxysys#lhrob311cluster -p -h192.168.66.35 -P28814 obclient -h 127.0.0.1 -uroot@proxysys -P2883 obclient -h 127.0.0.1 -uroot@proxysys#lhrob311cluster -P2883 mysql -uroot@sys -plhr -h192.168.66.35 -P28811 mysql -uroot@sys -plhr -h192.168.66.35 -P28814
As follows:
C:\Users\lhrxxt>mysql -uroot@sys -plhr -h192.168.66.35 -P28814
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 92
Server version: 5.6.25 OceanBase 3.1.1 (r4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e) (Built Oct 21 2021 10:33:14)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
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 [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| oceanbase |
| information_schema |
| mysql |
| SYS |
| LBACSYS |
| ORAAUDITOR |
| test |
+--------------------+
7 rows in set (0.64 sec)
MySQL [(none)]> create database lhrdb;
Query OK, 1 row affected (0.62 sec)
MySQL [(none)]> select * from oceanbase.__all_server;
+----------------------------+----------------------------+-------------+----------+----+-------+------------+-----------------+--------+-----------------------+------------------------------------------------------------------------+-----------+--------------------+--------------+----------------+-------------------+
| gmt_create | gmt_modified | svr_ip | svr_port | id | zone | inner_port | with_rootserver | status | block_migrate_in_time | build_version | stop_time | start_service_time | first_sessid | with_partition | last_offline_time |
+----------------------------+----------------------------+-------------+----------+----+-------+------------+-----------------+--------+-----------------------+------------------------------------------------------------------------+-----------+--------------------+--------------+----------------+-------------------+
| 2021-11-03 10:07:29.148223 | 2021-11-03 15:36:29.125523 | 172.72.8.11 | 2882 | 1 | zone1 | 2881 | 0 | active | 0 | 3.1.1_4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e(Oct 21 2021 10:33:14) | 0 | 1635907362335045 | 0 | 1 | 0 |
| 2021-11-03 10:07:28.665950 | 2021-11-03 15:36:29.126133 | 172.72.8.12 | 2882 | 2 | zone2 | 2881 | 0 | active | 0 | 3.1.1_4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e(Oct 21 2021 10:33:14) | 0 | 1635907363335302 | 0 | 1 | 0 |
| 2021-11-03 10:07:28.675445 | 2021-11-03 15:36:29.126735 | 172.72.8.13 | 2882 | 3 | zone3 | 2881 | 1 | active | 0 | 3.1.1_4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e(Oct 21 2021 10:33:14) | 0 | 1635907362334795 | 0 | 1 | 0 |
+----------------------------+----------------------------+-------------+----------+----+-------+------------+-----------------+--------+-----------------------+------------------------------------------------------------------------+-----------+--------------------+--------------+----------------+-------------------+
3 rows in set (0.12 sec)
MySQL [(none)]> select
-> zone,
-> concat(svr_ip, ':', svr_port) as observer,
-> concat(cpu_assigned, ' : ', cpu_total) as cpu_summary,
-> concat(mem_assigned/(1024*1024*1024),' : ', mem_total/(1024*1024*1024)) as mem_summary_gb,
-> concat(disk_assigned/(1024*1024*1024),' : ', disk_total/(1024*1024*1024)) as disk_summary_gb,
-> cast(cpu_weight as decimal(5,2)) as c_weight,
-> cast(memory_weight as decimal(5,2)) as m_weight,
-> cast(disk_weight as decimal(5,2)) as d_weight,
-> unit_num
-> from oceanbase.__all_virtual_server_stat
-> order by zone,observer;
+-------+------------------+-------------+-----------------+-----------------+----------+----------+----------+----------+
| zone | observer | cpu_summary | mem_summary_gb | disk_summary_gb | c_weight | m_weight | d_weight | unit_num |
+-------+------------------+-------------+-----------------+-----------------+----------+----------+----------+----------+
| zone1 | 172.72.8.11:2882 | 2.5 : 10 | 1.5000 : 6.0000 | 2.0000 : 2.0000 | 0.50 | 0.50 | 0.00 | 1 |
| zone2 | 172.72.8.12:2882 | 2.5 : 10 | 1.5000 : 6.0000 | 2.0000 : 2.0000 | 0.50 | 0.50 | 0.00 | 1 |
| zone3 | 172.72.8.13:2882 | 2.5 : 10 | 1.5000 : 6.0000 | 2.0000 : 2.0000 | 0.50 | 0.50 | 0.00 | 1 |
+-------+------------------+-------------+-----------------+-----------------+----------+----------+----------+----------+
3 rows in set (0.40 sec)
The next step is to create a tenant, which will not be demonstrated in detail here.
Patrol script
You can use Mr. Mai's OceanBase patrol script( https://www.xmmup.com/shujukuxunjianjiaoben.html ), the script is not very detailed at present. Of course, it has been improving, as follows:
C:\Users\lhrxxt>mysql -uroot@sys -plhr -h192.168.66.35 -P28814 -f --silent < D:\DB_OceanBase_HC_lhr_v7.0.0.sql > D:\lhr_OceanBase_check.html mysql: [Warning] Using a password on the command line interface can be insecure.
Operation results:
Others are not listed.
Only some results are listed here. For other details, please refer to: https://share.weiyun.com/5lb2U2M
This article ends.