安装和使用Inforbright

Inforbright简介

Infobright是一款基于知识网格的列式数据库，对大批量数据（百万、千万、亿级）的查询性能非常高，据说比MyISAM、InnoDB等普通MySQL引擎快5~60倍，可存储TB级体积的数据，存储数据高压缩比可达到40:1。基于列式存储，无需索引、无需分区。快速响应复制的聚合查询，非常适合分析性的SQL，如SUM、AVG、COUNT、GROUP BY 等。

Infobright的使用场景

1、大数据量的分析应用。如：网页/在线分析、移动端数据分析、营销分析、广告定位、客户行为分析等。
2、日志/事件管理系统。电信详单分析和报告、系统/网络 安全认证记录。
3、数据集市。企事业单位特定数据仓库、为中小企业提供数据仓库。
4、嵌入式分析。为独立软件供应商/ SaaS供应商提供嵌入式分析应用

Infobright的限制

1、不支持数据更新：社区版Infobright只能使用“LOAD DATA INFILE”的方式导入数据，不支持INSERT、UPDATE、DELETE。
2、不支持高并发：只能支持10多个并发查询，用于企业高层决策和产品定向已经足够了。

Infobright安装

下载相应的Windows版本安装：https://www.infobright.org/index.php/Download/ICE

安装成功后如图：

打开Infobright Commond Line Client,如图

设置密码之后只能通过命令行窗口进入Infobright的bin目录执mysql，可以打开Infobright的命令行窗口：

但是设置密码之后会有一系列的权限问题，解决方法先保留。

创建数据库(无密码)

创建数据库并指定字符集：

CREATE DATABASE mytest;

结果

mysql> CREATE DATABASE mytest;Query OK, 1 row affected (0.00 sec)mysql> use mytest;Database changedmysql>

创建表

从现有的MySQL表脚本修改

现有的MySQL建表脚本(这里以我的reviews表为例)

CREATE TABLE `reviews` (    `id` VARCHAR(32) NOT NULL,    `review` VARCHAR(10000) NULL DEFAULT NULL,    `reviewer` VARCHAR(100) NULL DEFAULT NULL,    `date` DATE NOT NULL DEFAULT '0000-00-00',    `helpful_count` INT(11) NULL DEFAULT NULL,    `starts` FLOAT NULL DEFAULT NULL,    PRIMARY KEY (`id`),    FULLTEXT INDEX `fulltext_reviews` (`review`, `reviewer`))ENGINE=MyISAM;

修改为适应Infobright的建表脚本如下：

CREATE TABLE `reviews` (    `id` CHAR(32) NOT NULL,    `review` VARCHAR(10000) NULL DEFAULT NULL,    `reviewer` VARCHAR(100) NULL DEFAULT NULL,    `date` DATE NOT NULL DEFAULT '0000-00-00',    `helpful_count` INT(11) NULL DEFAULT NULL,    `starts` FLOAT NULL DEFAULT NULL)ENGINE=brighthouse;

对比一下，ID是定长的，干脆改成CHAR了。
去掉PRIMARY KEY、FULLTEXT INDEX等索引，因为Infobright是无需索引的。
把ENGINE修改为brighthouse。
执行脚本:

导入CSV数据

使用命令先设置以CSV格式导入（安装的是社区版（ICE）,只能支持CSV）

 set @bh_dataformat = 'txt_variable';


如果是企业版（IEE），需要支持多种格式如binary、MySQL，可以参考
{Infobright_home}/Data_Loading_Guide.pdf

使用MySQL客户端工具将现有的reviews表数据导出为reviews.csv,使用命令导入到Infobright的mytest.reviews表中

load data infile 'd:\\reviews.csv' into table reviews fields terminated by ',' optionally enclosed by  '"' lines terminated by '\n';

Infobright的默认端口是：5029

---

附加

mysql设置编码命令

SET character_set_client = utf8;SET character_set_connection = utf8;SET character_set_database = utf8;SET character_set_results = utf8;SET character_set_server = utf8;SET collation_connection = utf8_bin;SET collation_database = utf8_bin;SET collation_server = utf8_bin;

my.ini中配置默认编码

default-character-set=utf8

---

Ubuntu安装Infobright

下载https://www.infobright.org/index.php/download/ICE/infobright-4.0.7-0-x86_64-ice.deb

进入下载目录执行如下命令：

dpkg -i infobright-4.0.7-0-x86_64-ice.deb 

运行结果

Selecting previously unselected package infobright.(Reading database ... 184850 files and directories currently installed.)Preparing to unpack infobright-4.0.7-0-x86_64-ice.deb ...Installing infobright 4.0.7-0 (x86_64)The installer will generate /tmp/ib4.0.7-0-install.log install trace log.Unpacking infobright (4.0.7-0) ...Setting up infobright (4.0.7-0) ...Creating/Updating datadir and cachedirCreating user mysql and group mysqlInstalling default databasesInstalling MySQL system tables...OKFilling help tables...OKTo start mysqld at boot time you have to copysupport-files/mysql.server to the right place for your systemPLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !To do so, start the server, then issue the following commands:/usr/local/infobright-4.0.7-x86_64/bin/mysqladmin -u root password 'new-password'/usr/local/infobright-4.0.7-x86_64/bin/mysqladmin -u root -h ubuntu password 'new-password'Alternatively you can run:/usr/local/infobright-4.0.7-x86_64/bin/mysql_secure_installationwhich will also give you the option of removing the testdatabases and anonymous user created by default.  This isstrongly recommended for production servers.See the manual for more instructions.You can start the MySQL daemon with:cd /usr/local/infobright-4.0.7-x86_64 ; /usr/local/infobright-4.0.7-x86_64/bin/mysqld_safe &You can test the MySQL daemon with mysql-test-run.plcd /usr/local/infobright-4.0.7-x86_64/mysql-test ; perl mysql-test-run.plPlease report any problems with the /usr/local/infobright-4.0.7-x86_64/scripts/mysqlbug script!The latest information about MySQL is available at http://www.mysql.com/Support MySQL by buying support/licenses from http://shop.mysql.com/System Physical memory: 3935(MB)Infobright optimal ServerMainHeapSize is set to 1300(MB)Infobright optimal LoaderMainHeapSize is set to 400(MB)Infobright server installed into folder /usr/local/infobrightInstallation log file /tmp/ib4.0.7-0-install.log--------------------------------------To activate infobright server, please run ./postconfig.sh script from /usr/local/infobright-4.0.7-x86_64.Example command: cd /usr/local/infobright-4.0.7-x86_64; ./postconfig.shroot@ubuntu:/opt# sh /usr/local/infobright-4.0.7-x86_64/postconfig.shInfobright post configuration--------------------------------------Please run this script from inside the installed folder.--------------------------------------root@ubuntu:/opt# sh /usr/local/infobright-4.0.7-x86_64/postconfig.sh  Infobright post configuration--------------------------------------Please run this script from inside the installed folder.--------------------------------------

激活

进入/usr/local/infobright-4.0.7-x86_64目录执行命令：

./postconfig.sh

运行结果：

Infobright post configuration--------------------------------------Infobright server activated.--------------------------------------Register your copy of ICE and receive a free copy of the User Manual (a $50 value) as well as a copy of the Bloor Research Spotlight Report "What's Cool About Columns" which explains the differences and benefits of a columnar versus row database.Registration will require opening an HTTP connection to Infobright, do you wish to register now? [Y/N]: yDo you want to tell us a bit about yourself? We will try to send your information to our server. [Y/N]: yFirst Name: gionee   /*这里要求你输入*/Last Name: gionee    /*这里要求你输入*/Company: gionee      /*这里要求你输入*/Job Title: gionee    /*这里要求你输入*/City: shenzhen       /*这里要求你输入*/State/Province: gd   /*这里要求你输入*/Country: China       /*这里要求你输入*/Email address: [e-mail] /*这里要求你输入*/Phone number:  /*这里要求你输入*/Industry:1. Aerospace and Defense2. Automotive and Transportation3. Charity, Non-profit Organization4. Chemicals5. Computer Hardware6. Computer Software and Services7. Consumer Products8. Diversified Services (Accounting, Legal)9. Education10. Electronics11. Energy, Utilities, Oil and Gas12. Engineering, Construction, Materials13. Financial Services, Banking, Insurance14. Food and Beverages (Consumables)15. Government16. Healthcare17. Leisure, Entertainment, Restaurants18. Manufacturing19. Media20. Metals and Mining21. Online advertising and Marketing22. Pharmaceuticals and Medicine23. Retail24. Telecommunications25. Transportation26. OtherEnter number here[1-26]: 18218089328Please give a valid number for industry[1-26]: 1What kind of project do you intend to use ICE for?:1. Enterprise Data Warehouse2. Department or Application Data Mart3. Log Analytics4. Online Analytics5. Telecom Analytics6. Embedded Analytic Database7. OtherEnter number here[1-7]: 1 /*这里要求你输入*/We successfully submitted your information to our server. Thank you for registration.

启动infobright

/etc/init.d/mysqld-ib start

日志：

Starting MySQLerror: list of process IDs must follow pUsage: ps [options] Try 'ps --help <simple|list|output|threads|misc|all>'  or 'ps --help <s|l|o|t|m|a>' for additional help text.For more details see ps(1).. * 

查看启动状态

/etc/init.d/mysqld-ib status

 * MySQL running (10837)

设置root密码

usr/local/infobright-4.0.7-x86_64/bin/mysqladmin -u root password 'root'  

登录
执行命令：

/usr/local/infobright-4.0.7-x86_64/bin# mysql-ib -u root -p

结果

root@ubuntu:/usr/local/infobright-4.0.7-x86_64/bin# mysql-ib -u root -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 4Server version: 5.1.40 build number (revision)=IB_4.0.7_r16961_17249(ice) (static)Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

设置远程登录权限以及常规操作

执行命令

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;

结果

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;Query OK, 0 rows affected (0.00 sec)mysql> FLUSH PRIVILEGES;Query OK, 0 rows affected (0.00 sec)mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || BH_RSI_Repository  || mysql              || sys_infobright     || test               |+--------------------+5 rows in set (0.00 sec)