官方文档https://mariadb.com/kb/en/library/mariadb-columnstore/
推荐使用最新1.2.x最新版本。
先决条件
yum -y install boost
yum -y install expect perl perl-DBI openssl zlib file sudo libaio rsync snappy net-tools numactl-libs nmap
安装1.0的jemalloc，可下载rpm包如jemalloc-3.6.0-1.el7.x86_64.rpm。
安装(单机)
推荐下载linux二进制版本https://downloads.mariadb.com/ColumnStore/1.2.2/centos/x86_64/7/mariadb-columnstore-1.2.2-1-centos7.x86_64.bin.tar.gz，更加透明。
直接运行自带shell命令/usr/local/mariadb/columnstore/bin/quick_installer_single_server.sh即可完成单机版本安装与启动。
使用这种模式安装的columnstore并没有独立UM模块，而是作为原生进程存在(否则、至少需要2台服务器)。
安装完成后，建议执行命令/usr/local/mariadb/columnstore/bin/columnstoreAlias为columnstore的两个主要客户端：SQL客户端以及mcsadmin(MariaDB ColumnStore MySQL Console)创建别名：
其中：
#和非columnstore引擎的mysql是一样的
mcsmysql = /usr/local/mariadb/columnstore/mysql/bin/mysql--defaults-file=/usr/local/mariadb/columnstore/mysql/my.cnf-u root #默认密码为空
mcsadmin= /usr/lodcal/mariadb/columnstore/bin/mcsadmin
然后就可以检查节点状态了(节点状态异常是可用来查看)，如下所示：
[root@oel-12c ~]#mcsadmin getsysteminfo
getsysteminfo Wed Mar 13 15:52:54 2019System columnstore-1System and Module statuses
Component Status Last Status Change------------ -------------------------- ------------------------System ACTIVE Tue Mar12 13:35:07 2019Module pm1 ACTIVE Tue Mar12 13:34:59 2019MariaDB ColumnStoreProcessstatusesProcess Module Status Last Status Change ProcessID------------------ ------ --------------- ------------------------ ----------ProcessMonitor pm1 ACTIVE Tue Mar12 13:34:23 2019 1281ProcessManager pm1 ACTIVE Tue Mar12 13:34:29 2019 1838DBRMControllerNode pm1 ACTIVE Tue Mar12 13:34:39 2019 2428ServerMonitor pm1 ACTIVE Tue Mar12 13:34:42 2019 2447DBRMWorkerNode pm1 ACTIVE Tue Mar12 13:34:42 2019 2465PrimProc pm1 ACTIVE Tue Mar12 13:34:45 2019 2544ExeMgr pm1 ACTIVE Tue Mar12 13:34:50 2019 2628WriteEngineServer pm1 ACTIVE Tue Mar12 13:34:54 2019 2685DDLProc pm1 ACTIVE Tue Mar12 13:34:58 2019 2756DMLProc pm1 ACTIVE Tue Mar12 13:35:06 2019 2827mysqld pm1 ACTIVE Tue Mar12 13:34:36 2019 2260Active Alarm Counts: Critical= 0, Major = 0, Minor = 0, Warning = 0, Info = 0[root@oel-12c ~]#mcsadmin getsystemstatus
getsystemstatus Wed Mar 13 15:53:05 2019System columnstore-1System and Module statuses
Component Status Last Status Change------------ -------------------------- ------------------------System ACTIVE Tue Mar12 13:35:07 2019Module pm1 ACTIVE Tue Mar12 13:34:59 2019
查看后台进程状态
查看所有支持的命令：
[root@oel-12c ~]# mcsadmin help
help Wed Mar13 15:52:43 2019List of commands:
Note: the command must be the first entry entered on the command line
Command Description------------------------------ --------------------------------------------------------
?Get help on the Console Commands
addDbroot Add DBRoot Disk storage to the MariaDB Columnstore System
addModule Add a Module within the MariaDB Columnstore System
alterSystem-disableModule Disable a Module and Alter the MariaDB Columnstore System
alterSystem-enableModule Enable a Module and Alter the MariaDB Columnstore System
assignDbrootPmConfig Assign unassigned DBroots to Performance Module
assignElasticIPAddress Assign Amazon Elastic IP Address to a module
disableLog Disable the levels of process and debug logging
disableMySQLReplication Disable MySQL Replication functionality on the system
查看命令的详细参数：
[root@oel-12c ~]# mcsadmin help getsysteminfo
help Wed Mar13 15:53:23 2019Command: getSystemInfo
Description: Get the Over-all System Statuses
Get the System, Module, Process, and Active Alarm Statuses
Arguments: None
进入mcsadmin控制台：
[root@oel-12c ~]# mcsadmin
MariaDB ColumnStore Admin Console
enter'help' forlist of commands
enter'exit'to exit the MariaDB ColumnStore Command Console
use up/down arrows to recall commands
Active Alarm Counts: Critical= 0, Major = 0, Minor = 0, Warning = 0, Info = 0Critical Active Alarms:
进入SQL控制台(密码默认空)：
[root@oel-12c ~]# mcsmysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connectionid is 11Server version:10.3.11-MariaDB-log Columnstore 1.2.2-1Copyright (c)2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type'help;' or '\h' for help. Type '\c' to clearthe current input statement.
MariaDB [(none)]>
修改密码、远程登录(同普通mysql)，如下：
MariaDB [(none)]> create user root@'%' identified by "123456";
Query OK,0 rows affected (0.001sec)
MariaDB [(none)]> grant all on *.* to root@'%';
Query OK,0 rows affected (0.000sec)
MariaDB [(none)]>exit
Bye
[root@oel-12c ~]# lsof -i:3306 # 端口，同普通mysqlCOMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld2260 mysql 26u IPv6 29128 0t0 TCP *:mysql (LISTEN)
[root@oel-12c ~]#
使用IDE访问：
所以，columnstore仅仅只是一个引擎，上层架构相同。下层分布式，和mysql ndb cluster的架构是一样的。
停止和启动
系统停止
完全停止columnstore(包括管理控制台和告警服务，停止后一个进程都没了)：
[root@oel-12c ~]# ps axu | grepcolumnstore
root1276 0.0 0.0 115256 3096 ? S 3月12 0:00 /bin/bash /usr/local/mariadb/columnstore/bin/run.sh -l /tmp/columnstore_tmp_files /usr/local/mariadb/columnstore/bin/ProcMon
root1281 0.2 0.4 1647100 24996 ? Sl 3月12 2:43 /usr/local/mariadb/columnstore/bin/ProcMon
root1960 0.0 0.0 115600 3448 ? S 3月12 0:00 /bin/sh /oradata/mariadb/columnstore/mysql//bin/mysqld_safe --datadir=/oradata/mariadb/columnstore/mysql/db --pid-file=/oradata/mariadb/columnstore/mysql/db/oel-12c.pid --ledir=/oradata/mariadb/columnstore/mysql//bin
mysql 2260 0.0 3.1 2325368 183900 ? Sl 3月12 0:15 /oradata/mariadb/columnstore/mysql//bin/mysqld --basedir=/oradata/mariadb/columnstore/mysql/ --datadir=/oradata/mariadb/columnstore/mysql/db --plugin-dir=/oradata/mariadb/columnstore/mysql/lib/plugin --user=mysql --log-error=/oradata/mariadb/columnstore/mysql/db/oel-12c.err --pid-file=/oradata/mariadb/columnstore/mysql/db/oel-12c.pid --socket=/oradata/mariadb/columnstore/mysql/lib/mysql/mysql.sock --port=3306
root 2428 0.0 0.3 753836 18404 ? Sl 3月12 0:03 /usr/local/mariadb/columnstore/bin/controllernode fg
root2447 0.1 0.3 459460 18312 ? Sl 3月12 1:01 /usr/local/mariadb/columnstore/bin/ServerMonitor
root2465 0.0 0.4 539900 26444 ? Sl 3月12 0:02 /usr/local/mariadb/columnstore/bin/workernode DBRM_Worker1 fg
root8744 0.0 0.0 112692 2360 pts/0 S+ 16:07 0:00 grep --color=auto columnstore
[root@oel-12c ~]# mcsadmin shutdownSystem y
shutdownsystem Wed Mar13 16:07:12 2019This command stops the processing of applications on all Modules within the MariaDB ColumnStore System
Checkingforactive transactions
Stopping System...
Successful stop of System
Shutting Down System...
Successful shutdown of System
[root@oel-12c ~]# ps axu | grepcolumnstore
root9315 0.0 0.0 112688 2212 pts/0 S+ 16:07 0:00 grep --color=auto columnstore
启动系统(异常时可用来启动)：
[root@oel-12c ~]# mcsadmin startSystem
startsystem Wed Mar13 16:09:55 2019startSystem command,'columnstore'service is down, sending command to
start the'columnstore'service on all modules
System being started, pleasewait........
Successful start of System
[root@oel-12c ~]# ps axu | grepcolumnstore
root9515 0.0 0.0 113136 2596 pts/0 S 16:09 0:00 /bin/bash /usr/local/mariadb/columnstore/bin/run.sh -l /tmp/columnstore_tmp_files /usr/local/mariadb/columnstore/bin/ProcMon
root9516 19.8 0.4 1417724 24776 pts/0 Sl 16:09 0:13 /usr/local/mariadb/columnstore/bin/ProcMon
root9806 0.0 0.0 113272 3148 pts/0 S 16:10 0:00 /bin/sh /oradata/mariadb/columnstore/mysql//bin/mysqld_safe --datadir=/oradata/mariadb/columnstore/mysql/db --pid-file=/oradata/mariadb/columnstore/mysql/db/oel-12c.pid --ledir=/oradata/mariadb/columnstore/mysql//bin
mysql 9993 0.4 3.0 2349356 178932 pts/0 Sl 16:10 0:00 /oradata/mariadb/columnstore/mysql//bin/mysqld --basedir=/oradata/mariadb/columnstore/mysql/ --datadir=/oradata/mariadb/columnstore/mysql/db --plugin-dir=/oradata/mariadb/columnstore/mysql/lib/plugin --user=mysql --log-error=/oradata/mariadb/columnstore/mysql/db/oel-12c.err --pid-file=/oradata/mariadb/columnstore/mysql/db/oel-12c.pid --socket=/oradata/mariadb/columnstore/mysql/lib/mysql/mysql.sock --port=3306
root 10117 0.0 0.3 618660 18528 pts/0 Sl 16:10 0:00 /usr/local/mariadb/columnstore/bin/controllernode fg
root10137 0.1 0.3 467324 18512 pts/0 Sl 16:10 0:00 /usr/local/mariadb/columnstore/bin/ServerMonitor
root10200 0.0 0.4 550140 26552 pts/0 Sl 16:10 0:00 /usr/local/mariadb/columnstore/bin/workernode DBRM_Worker1 fg
root10813 0.0 0.0 112688 2360 pts/0 S+ 16:11 0:00 grep --color=auto columnstore
停止应用(跟rac不停止cluster类似，相当于重启库本身)：
[root@oel-12c ~]# mcsadmin stopSystem y #此时客户端发送请求，将收到 [Err] 1815 - Internal error: The system is not yet ready to accept queries
stopsystem Wed Mar13 16:12:36 2019This command stops the processing of applications on all Modules within the MariaDB ColumnStore System
Checkingforactive transactions
System being stopped now...
Successful stop of System
[root@oel-12c ~]# ps axu | grepcolumnstore
root9515 0.0 0.0 113136 2596 pts/0 S 16:09 0:00 /bin/bash /usr/local/mariadb/columnstore/bin/run.sh -l /tmp/columnstore_tmp_files /usr/local/mariadb/columnstore/bin/ProcMon
root9516 7.9 0.4 1794652 25128 pts/0 Sl 16:09 0:14 /usr/local/mariadb/columnstore/bin/ProcMon
root11942 0.0 0.0 112688 2284 pts/0 S+ 16:12 0:00 grep --color=auto columnstore
最近测试发现个额外现象。mcsadmin startSystem后，重新连接报"[Err] 1815 - Internal error: fatal error running mysql_real_connect() in libmysql_client lib (1045) (Access denied for user 'root'@'localhost' (using password: NO))"，各种进程、模块状态都是正常的。
常规的SQL以及DDL、建库等和普通mysql一样，唯一的差别是引擎要声明为columnstore，如下：
create databasemcs;usemcs;create table idbtest(col1 int, col2 int) engine=columnstore;
showcreate tableidbtest;insert into idbtest values (1, 2);insert into idbtest values (3, 4);select * from idbtest;
设置utf-8
vim /usr/local/mariadb/columnstore/mysql/my.cnf
[client]
default-character-set=utf8
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
init-connect=’SET NAMES utf8’
设置columnstore本身的本地语言(建议不要直接修改配置文件/usr/local/mariadb/columnstore/etc/Columnstore.xml)，改了后要通过mcsadmin stopSystem/startSystem使之生效：
/usr/local/mariadb/columnstore/bin/setConfig SystemConfig SystemLang en_US.utf8
将文件转换为utf-8格式：
iconv -f ISO-8859-1 -t UTF-8 < input.txt > output.txt