MariaDB ColumnStore System Usage

You are viewing an old version of this article. View the current version here.

Once the MariaDB ColumnStore system installation has been completed, these are some of the basic commands to access the MariaDB ColumnStore Admin and MySQL Console

Non-root user MariaDB ColumnStore Admin console

The MariaDB ColumnStore Admin console that is run from the command line console as root user can be setup to be run for a non-root user, if you choose to do so.

This would require a change in the /etc/sudoers file.

After this line:

# %wheel	ALL=(ALL)	NOPASSWD: ALL

Add, where 'user' is the non-root username:

user	  ALL=(ALL)       NOPASSWD: /usr/local/mariadb/columnstore/bin/mcsadmin

To run the command, you would need to enter the full path or you can setup the alias's by entering the following command:

.  /usr/local/mariadb/columnstore/bin/columnstoreAlias

Now you can run both console commands for a non-root user account: 'mcsmysql' and 'mcsadmin'

MariaDB ColumnStore aliases

To configure the MariaDB ColumnStore Aliases, run the following:

. /usr/local/mariadb/columnstore/bin/columnstoreAlias

Note: This script will also be added to the /root/.bash_profile. The following alias will be created after running the Alias:

mcsmysql = /usr/local/mariadb/columnstore/mysql/bin/mysql 
  --defaults-file=/usr/local/mariadb/columnstore/mysql/my.cnf 
  -u root

mcsmysql: Launches the MariaDB ColumnStore MySQL Console

mcsadmin = /usr/local/mariadb/columnstore/bin/mcsadmin

mcsadmin: Launches the MariaDB ColumnStore Admin Console

mcsadmin

The MariaDB ColumnStore Management Console allows you to configure, monitor, and manage the MariaDB ColumnStore system and servers. For more detailed information, see MariaDB ColumnStore Administrative Console

These 2 examples will provide a full command list

mcsadmin help

Or

mcsadmin
mcsadmin> help

To get a verbose help on a specific command, enter:

mcsadmin help getsysteminfo

NOTE: You can also short-cut commands, both of these do the same command:

mcsadmin getsysteminfo
mcsadmin getsystemi

Check system status

mcsadmin getsystemstatus

getsystemstatus Tue Jan 12 08:07:02 2012
System mymcs1
System and Module statuses
Component    Status                    Last Status Change
------------ ----------------------   ------------------------
System       ACTIVE                    Mon Jan 11 17:54:46 2012
Module pm1   ACTIVE                    Mon Jan 11 17:54:45 2012

Check system info, which provides process status

mcsadmin getsysteminfo

Stopping the system

Stopping the system stops the application processes. The platform process that supports the Management Console and System Alarms remain active. If you want to stop the system or server and immediately start the processes again (typically if the system or a server hangs), you can restart the system or servers as shown in the section “Restarting the system”.

To stop the system:

  • From the MariaDB ColumnStore Console, type stopSystem
  • Press y
mcsadmin stopSystem y

Starting the system or modules

You can start the system or module application processes with the following commands:

To start the system:

  • From the MariaDB ColumnStore Console, type startSystem
mcsadmin startSystem

Restarting the system

When you want to stop and immediately start application processes, you can perform a restart. You restart the system or modules application processes with the following commands:

To restart the system:

  • From the MariaDB ColumnStore Console, type restartSystem
mcsadmin restartSystem

Shutting down the system

When you perform a shutdown, all MariaDB ColumnStore processes are stopped. This command would be used mainly when performing software upgrades.

To shutdown the system:

  • From the MariaDB ColumnStore Console, type shutdownSystem
  • Press y
mcsadmin shutdownSystem y

Logging into MariaDB ColumnStore MySQL console

a Linux prompt:

mcsmysql [optional database name]

MySQL Root user password

When a MySQL Root user password is configured, do the following to access the MySQL console:

mcsmysql -p'password' [optional database name]

When a MySQL Root user password is configured, a user/password file is required to be set on the module where the where the MySQL console is being executed. This is used by the MariaDB Columnstore software during the system upgrade process and also during the User Module failover process when the MySQL Replication Master needs to be changed. The MariaDB Columnstore software needs to log into the MySQL console to make updates during these processes.

Setup this file in the user account where MariaDB Columnstore was installed, .my.cnf So for root user install, it would be /root/.my.cnf

  [mysqladmin] 
  user            = root
  password        = 'password'

Make sure the permissions are set appropriately:

  # chmod 0600 /root/.my.cnf

Example MariaDB ColumnStore database setup

Once logged into mcsmysql:

create database mcs;
use mcs;
create table idbtest(col1 int, col2 int) engine=columnstore;
show create table idbtest;
insert into idbtest values (1, 2);
insert into idbtest values (3, 4);
select * from idbtest;

Importing data into MariaDB ColumnStore

While MariaDB ColumnStore supports multiple ways to get data into the database (individual insert, batch insert, load data infile and import), the most efficient manner to load significant amounts of data into MariaDB ColumnStore is through the import utility.

  • A delimited import file should be created to match the table that you want to import data to similar to the following. In this example, the file will be saved as idbtest.tbl:
100|1001|
200|2002|
  • Save/place this file in a directory
  • From that same directory, import the rows:
/usr/local/mariadb/columnstore/bin/cpimport mcs idbtest idbtest.tbl

Configuring to use UTF-8 Character sets

MariaDB Columnstore has the ability to support UTF-8 character sets. This profile may be set as a default for the instance or set at the session level.

Please refer to the SQL Syntax Guide for the setting UTF-8 profile at the session level.

To set UTF-8 profile at the instance level, specify the following in my.cnf and the Columnstore.xml files.

my.cnf To configure the MariaDB Locale language, modify the my.cnf file (/usr/local/mariadb/columnstore/mysql):

[client]
default-character-set=utf8
[mysqld]
character-set-server=utf8
collation-server=value
init-connect=’SET NAMES utf8’

where value is a valid unicode character set. Please see the following MariaDB reference of valid values:

https://mariadb.com/kb/en/mariadb/supported-character-sets-and-collations/

If no values are specified for collation-server, the default is xxx_general_ci.

Columnstore.xml

To configure the ColumnStore Locale language, modify the Columnstore.xml files (/usr/local/mariadb/columnstore/etc)

<SystemConfig>
   <SystemLang>value</SystemLang>

where value is a valid locale for the operating system. The recommended setting is en_US.utf8.

Importing UTF-8 data

When using the cpimport utility, the input file must be converted to UTF-8 data. The Linux program iconv is one utility to convert it.

iconv -f ISO-8859-1 -t UTF-8 < input.txt > output.txt

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.