In the world of big data, distributed databases play an important role. As data generated and processed by companies grow exponentially, there is a growing need for scalable and reliable database management solutions. An increasing number of companies are already shifting to distributed databases. OceanBase, a distributed database that offers capabilities in both transactions and analytics, as well as compatibility with MySQL, is rapidly gaining traction among data-driven teams.

Developed by Ant Group, OceanBase has been the driving force behind Alibaba’s Singles Day shopping event for nine consecutive years, managing billions of dollars in transactions annually.

As an enterprise-grade DBMS, OceanBase also provides a community edition that allows developers to experience its full potential without a complicated setup.

In this article, I will walk you through how to install OceanBase on an AWS EC2 instance. This is the first in a series of articles where I demonstrate how to integrate OceanBase into your applications.

Prerequisites

Before we jump into the installation process of OceanBase, we need to make sure that the EC2 instance we spin up in AWS meets the hardware requirements of OceanBase as stated in the documentation.

According to the documentation, a server with at least four cores and 10 GiB of RAM (preferably 16 GiB) is required to install OceanBase. The recommended operating systems are Red Hat Enterprise Linux Server, CentOS Linux, and Anolis OS.

In this walkthrough, I will use the CentOS 8 operating system and the a1.x2large EC2 instance type, which comes with 8 vCPUs and 16 GiB of RAM. Since OceanBase recommends having a disk space of “four times the memory size or more,” I’ll mount a 100-GiB SSD storage volume to the instance.

Once we spun up our EC2 instance and connected to it, we can get started and install OceanBase.

Install OceanBase

The first step is to update the yum package. Run the following bash command

sudo yum update

The easiest way to install OceanBase on a server is to use the official all-in-one installation package, which will install OBD, OceanBase DBMS, OBProxy, obagent, Grafana, and Prometheus on your machine. You can use the all-in-one installation by running the following code:

bash -c "$(curl -s <https://obbusiness-private.oss-cn-shanghai.aliyuncs.com/download-center/opensource/oceanbase-all-in-one/installer.sh>)"

This process may take up to 5 minutes. Once everything is installed, you will see an “Install Finished” screen like this.

Alternatively, you can use Docker to quickly start an OceanBase server with one line of code:

docker run -p 2881:2881 --name obstandalone -d oceanbase/oceanbase-ce

For simplicity, I will be using the demo instance we just installed using the all-in-one package in the remainder of this article.

The next step is to activate the setup environment by typing the following command:

source ~/.oceanbase-all-in-one/bin/env.sh

Then we can run the obd demo command to start demo instances of the services we just installed from the all-in-one package.

obd demo

Please note that the obd demo command is only recommended to be used for demo purposes and should not be used for production.

Heads up: update your ulimits configuration

After running this command you may encounter an error saying “open files must not be less than 20000 (Current value: 1024)” with the error code OBD-1007. According to the OceanBase documentation, this error message means our ulimits configuration hasn’t met the official requirements.

To change the ulimits configuration, we need to edit the /etc/security/limits.conf file. ulimit is a built-in Linux configuration that allows viewing or limiting system resource amounts that individual users consume. For demo purposes, we will increase the limits to a large number or unlimited. But in production, it is recommended that you follow the best practices of setting ulimits.

* soft nofile 655360
* hard nofile 655360
* soft nproc 655360
* hard nproc 655360
* soft core unlimited
* hard core unlimited
* soft stack unlimited
* hard stack unlimited

To make the configuration work, you need to log out of your session and log in again. After that, you can check if the configuration is correct by running the following command:

ulimit -a

If the reconfiguration is successful, you may see the following screen.

Now let’s run the obd demo command again. And bingo, we got a running OceanBase server. The demo command will also provide credentials for each service.

+---------------------------------------------+
|                   observer                  |
+-----------+---------+------+-------+--------+
| ip        | version | port | zone  | status |
+-----------+---------+------+-------+--------+
| 127.0.0.1 | 4.0.0.0 | 2881 | zone1 | ACTIVE |
+-----------+---------+------+-------+--------+
obclient -h127.0.0.1 -P2881 -uroot -Doceanbase -A
+--------------------------------------------------+
|                     obagent                      |
+--------------+-------------+------------+--------+
| ip           | server_port | pprof_port | status |
+--------------+-------------+------------+--------+
| 172.*.*.42 | 8088        | 8089       | active |
+--------------+-------------+------------+--------+
+-----------------------------------------------------+
|                      prometheus                     |
+--------------------------+------+----------+--------+
| url                      | user | password | status |
+--------------------------+------+----------+--------+
| <http://172>.*.*.42:9090 |      |          | active |
+--------------------------+------+----------+--------+
+-------------------------------------------------------------------+
|                              grafana                              |
+--------------------------------------+-------+-----------+--------+
| url                                  | user  | password  | status |
+--------------------------------------+-------+-----------+--------+
| <http://172>.*.*.42:3000/d/oceanbase | admin | oceanbase | active |
+--------------------------------------+-------+-----------+--------+
+---------------------------------------------+
|                   obproxy                   |
+-----------+------+-----------------+--------+
| ip        | port | prometheus_port | status |
+-----------+------+-----------------+--------+
| 127.0.0.1 | 2883 | 2884            | active |
+-----------+------+-----------------+--------+
obclient -h127.0.0.1 -P2883 -uroot -Doceanbase -A
demo running

Connect to your OceanBase client

Now let’s try to connect to our OceanBase instance using OBClient, OceanBase’s official CLI based on the MariaDB CLI, which can be used to connect to OceanBase Server/Proxy.

Since OBClient is already installed by the all-in-one package, you can simply run the following code to connect to it:

obclient -h127.0.0.1 -P2881 -uroot -Doceanbase -A

If you already have experience using the MariaDB CLI or the MySQL CLI, OBClient shouldn’t look alien to you.

In this walkthrough, I will try to create a database about ocean life using OceanBase (pun intended). To get started, I will create a database called MarineLife in our demo OceanBase instance by running the following code:

CREATE DATABASE MarineLife DEFAULT CHARACTER SET UTF8 READ WRITE;

Now run SHOW DATABASES and we will see the newly created database.

Now that we have created a database in OceanBase, we can try inserting some data into the database using SQL.

Running SQL in OceanBase

First, let’s select the newly created MarineLife database in OBClient.

USE MarineLife;

To get started, I want to create a table in the MarineLife database called Species, which has properties like species_name, common_name, habitant, and conservation_status. To do that, we need to create a table and its scheme:

CREATE TABLE Species (
id INT AUTO_INCREMENT PRIMARY KEY,
species_name VARCHAR(255) NOT NULL,
common_name VARCHAR(255),
habitat VARCHAR(255),
conservation_status ENUM('Least Concern', 'Near Threatened', 'Vulnerable', 'Endangered', 'Critically Endangered', 'Extinct in the Wild', 'Extinct')
);

Now let’s populate the table with some dummy data:

INSERT INTO Species (species_name, common_name, habitat, conservation_status)
VALUES ('Carcharodon carcharias', 'Great White Shark', 'Coastal and offshore waters', 'Vulnerable'),
('Delphinus delphis', 'Short-beaked Common Dolphin', 'Open seas', 'Least Concern'),
('Chelonia mydas', 'Green Sea Turtle', 'Tropical and subtropical oceans', 'Endangered'),
('Eubalaena glacialis', 'North Atlantic Right Whale', 'North Atlantic Ocean', 'Critically Endangered');

Now we can check the database table:

SELECT * FROM Species;

And we will see the data we just inserted into the table.

Connect OceanBase from TablePlus

You can use TablePlus to manage your database from your local machine. Before you do that, you need to change your AWS EC2 security group to allow inbound access to the 2881 port, which is the default OceanBase port.

To do that, select the security group used by your EC2 instance and edit the Inbound rules to allow access to the 2881 port.

Please be aware that the above setting is only for demo purposes. In production, you may want to restrict the allowed source to a limited IP range.

In most cases, you don’t want to remotely access your database using the root user. We can create a new user called demo that only has access to the MarineLife database.

To create a new user, we will use the following command:

CREATE USER 'demo'@'localhost' IDENTIFIED BY 'password';

Next, we will grant the user permission to access the MarineLife database:

GRANT ALL PRIVILEGES ON MarineLife.* TO 'demo'@'localhost';

Now we can connect to the OceanBase instance from TablePlus using the demo user credentials. Once connected, we can view and manage the MarineLife database and its tables from a user-friendly graphical interface.

Host: YOUR_EC2_PUBLIC_IP_ADDRESS
Port: 2881
User: demo
Password: password

And I have added a few more rows to the database.

Conclusion

In this article, we have learned how to install OceanBase on an AWS EC2 instance and connect to it using OBClient and TablePlus. We have also created a database with a table and inserted data into it.

OceanBase offers a scalable, dependable, and efficient distributed database management system that is compatible with MySQL, making it an ideal choice for processing vast amounts of data.

In the next article of this series, we will explore more advanced features of OceanBase and how to integrate them into your applications in various languages.