MariaDB 10.7 Column Store Engine on Ubuntu 20.4 – Community Edition

MariaDB 10.7 Column Store Engine on Ubuntu 20.4 – Community Edition

MariaDB 10.7 Column Store Engine on Ubuntu 20.4 – Community Edition

Step 1. Virtual Environment Setup using either VMWare Viewer or Oracle VM Box

Download VMWare Player – Evaluation Learing Purpose
https://www.vmware.com/products/workstation-player/workstation-player-evaluation.html

Step 2. Download the respective VM Image
https://www.linuxvmimages.com/images/ubuntu-2004/#ubuntu-20044

Download VM Image for Ubuntu 20.4
03/22/2022 01:11 PM 2,599,524,771 Ubuntu_20.04.4_VM.7z

Uncompress the above file

03/08/2022 03:46 AM 8,607,367,168 Ubuntu_20.04.4_VM_LinuxVMImages.COM.vmdk
03/08/2022 03:46 AM 3,071 Ubuntu_20.04.4_VM_LinuxVMImages.COM.vmx

Step 3. Import VM Images in VMware Workstation (VMDK File)
Step 3.1: Go to the downloads directory of your system (default one) or to the location where you have saved the VM image.
Step 3.2: Extract the compressed file using the 7-Zip.
Optionally, move the extracted folder to where you have your other VMs.
Step 3.3: Open VMware Workstation and go to File » Open.
Import Virtual Appliance
Step 3.4: Browse to the location where you have extracted the VM image. Select the image and then click on Open.
Select VM Image
Step 3.5: In a second or two, you should see a new Virtual Machine in the VMware Workstation.
Virtual Machine
Step 3.6: If required, you can change the virtual machine’s resources like CPU, Memory, Network, and other settings by Edit virtual machine settings.
Edit Virtual Machine Settings
Step 3.7: You can now power on the virtual machine to start working on it.
Power On Virtual Machine
Step 3.8: Click on I Copied It.
Copy Virtual Machine

Step 4. Download the MariaDB respective version with ColumnStore

MariaDB 10.7 With ColumnStore – Download and Install – With Internet

Step 4.1: Start the New VM with any Name
ubuntu/ubuntu
Network Setting change to Bridged so that we can have the IP address same as our desktop to SCP

Step 4.2: Create a mysql user (from ubuntu)
sudo adduser mysql

Step 4.3: Give Sudo access to mysql user (from ubuntu)
sudo usermod -aG sudo mysql

Logout and login confirm
sudo whoami
(should return root)

Step 4.4: Download the MariaDB Community Server with ColumnStore

sudo mkdir /db_dumps
sudo chown mysql:mysql /db_dumps

D:\Data-Thanam\Download>scp mariadb-10.7.3-ubuntu-focal-amd64-debs.tar mysql@192.168.3.24:/db_dumps

Step 4.5: Install the MariaDB with ColumnStore
cd /db_dumps
tar -xf mariadb-10.7.3-ubuntu-focal-amd64-debs.tar
cd mariadb-10.7.3-ubuntu-focal-amd64-debs/
sudo ./setup_repository

sudo su –

apt-get update && apt-get install mariadb-server mariadb-backup libmariadb3 mariadb-client mariadb-plugin-columnstore

Step 6: Check and Confirm Columstore

SELECT PLUGIN_NAME, PLUGIN_STATUS FROM information_schema.PLUGINS WHERE PLUGIN_LIBRARY LIKE ‘ha_columnstore%’;

CREATE DATABASE IF NOT EXISTS test;

CREATE TABLE IF NOT EXISTS test.contacts (first_name VARCHAR(50),last_name VARCHAR(50),email VARCHAR(100)) ENGINE=ColumnStore;

INSERT INTO test.contacts VALUES (“Walker”, “Percy”, “w.percy@example.com”), (“Flannery”, “O’Connor”, “flan@example.edu”);

select * from test.contacts;

show create table test.contacts\G;

Step 7. Confirm the MariaDB and ColumnStore Services Stop and Start

MariaDB Service Stop and Start
systemctl stop mariadb
systemctl start mariadb
systemctl restart mariadb

Note: When MariaDB service is stopped both of them are not available

ColumnStore Service Stop and Start

systemctl stop mariadb-columnstore
systemctl start mariadb-columnstore
systemctl restart mariadb-columnstore

Note: When ColumnStore service is stopped DB is available but the Table and schemas related with ColumnStore engine may not be accessible.

MariaDB [test]> select * from contacts;
ERROR 1815 (HY000): Internal error: DBRM is not responding. Cannot accept queries
MariaDB [test]>