Infobright则是开源数据仓库(DataWarehouse)解决方案，基于列存储的存储引擎，可实现海量数据存储，且拥有较高的数据压缩比例，压缩比一版在10:1,40:1甚至更大，取决于数据重复度、数据类型和数据在磁盘的分布状态。能达到高速的数据装载性能：多种数据导入方式，语法简单，并拥有优化的统计算法(sum/group/count…)：在动态数据手动分布的情况下，需要设计静态表，涉及额外的工作，修改代码等。Infobright在导入时则不涉及到类似的问题
Infobright在应用中的确优势突出：例如，没有复杂的数据仓库模型设计要求(比如星状模型、雪花模型)，应用Infobright可节约设计开销，不需要物化视图、数据分区、索引建立，节省存储资源;高压缩比率通常是10:1，某些应用可能达到40:1;集成利用广泛;和众多的BI套件相容，比如Pentaho、Cognos、Jaspersof
降低运维成本;随着数据库的逐渐增大，查询和装载性能持续保持稳定;实施和管理简单，需要极少的管理;是第一个商业支持的开源仓储分析数据库，也是Oracle/MySQL 官方推荐的仓储集成架构。
Infobright应用案例
Infobright适用场景包括：大数据量的分析应用、网页/在线分析、移动分析(话单分析)、客户行为分析(超市购买热销商品统计等)、分析营销和广告、日志/事件管理系统、电信详单分析和报告、系统/网络 安全认证记录(登陆记录、事件记录) 、数据集市、企事业单位特定数据仓库、为中小企业提供数据仓库、嵌入式分析、为独立软件供应商/ SaaS供应商提供嵌入式分析应用 ……
Centos6.4 X86_64
infobright-4.0.7需要boost_1_42以上的版本，所以先安装boost，编译安装时间较长
cd /home/data/
wget http://downloads.sourceforge.net/project/boost/boost/1.43.0/boost_1_43_0.tar.gz
tar -xzf boost_1_43_0.tar.gz
cd boost_1_43_0
./bootstrap.sh --prefix=/usr/local/boost
yum install python-devel
./bjam install
export BOOST_ROOT=/usr/local/boost
echo "/usr/local/boost/lib" > /etc/ld.so.conf.d/boost-x86_64.conf
ldconfig
cd ..
安装 infobright-4.0.7
wget http://www.infobright.org/downloads/ice/infobright-4.0.7-0-src-ice.tar.gz
tar -xzf infobright-4.0.7-0-src-ice.tar.gz
cd infobright-4.0.7
make PREFIX=/usr/local/infobright EDITION=community release
make PREFIX=/usr/local/infobright EDITION=community install-release
mkdir -p /usr/local/infobright/conf /usr/local/infobright/logs /data/infobright/data
chown -R mysql.mysql /usr/local/infobright/conf /usr/local/infobright/logs /data/infobright/data
cp src/build/pkgmt/my-ib.cnf /usr/local/infobright/conf/my-ib.cnf
/usr/local/infobright/bin/mysql_install_db --basedir=/usr/local/infobright --datadir=/data/infobright/data --user=mysql
vim /usr/local/infobright/conf/my-ib.cnf
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
|
grep
-
v
^\
# /usr/local/infobright/conf/my-ib.cnf|sed '/^$/d'
[client]
port= 3307
socket=
/tmp/mysql-ib
.sock
loose-
local
-infile=1
[mysqld]
basedir =
/usr/local/infobright
datadir =
/data/infobright/data
log-error =
/usr/local/infobright/var/bh
.err
log-output = FILE
port= 3307
socket=
/tmp/mysql-ib
.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 500M
table_cache = 16
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 8M
net_buffer_length = 8K
thread_cache_size = 32
thread_stack = 512K
query_cache_size = 8M
query_cache_type=0
thread_concurrency = 8
server-
id
=1
default-storage-engine=brighthouse
collation_server=latin1_bin
character_set_server=latin1
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 32M
sort_buffer_size = 32M
read_buffer = 1M
write_buffer = 1M
[myisamchk]
key_buffer = 32M
sort_buffer_size = 32M
read_buffer = 1M
write_buffer = 1M
[mysqlhotcopy]
interactive-timeout
[mysql-
test
]
host=
local
user=root
port=5029
database=dk
testcase-timeout=3600
|
mkdir /usr/local/infobright/var
/usr/local/infobright/bin/mysqld_safe --defaults-file=/usr/local/infobright/conf/my-ib.cnf --user=mysql > /dev/null 2>&1 &
ps aux|grep mysql
root 809 0.0 0.1 106064 1492 pts/0 T 10:05 0:00 /bin/sh /usr/local/infobright/bin/mysqld_safe --defaults-file=/usr/local/infobright/conf/my-ib.cnf --user=mysql
mysql 938 0.0 3.1 849624 32048 pts/0 Sl 10:05 0:00 /usr/local/infobright/libexec/mysqld --defaults-file=/usr/local/infobright/conf/my-ib.cnf --basedir=/usr/local/infobright --datadir=/data/infobright/data --user=mysql --log-error=/usr/local/infobright/var/bh.err --pid-file=/data/infobright/data/localhost.localdomain.pid --socket=/tmp/mysql-ib.sock --port=3307
/usr/local/infobright/bin/mysqladmin -u root password "123456"
/usr/local/infobright/bin/mysql -uroot -p 123456
|
1
2
3
4
5
6
7
8
9
10
11
|
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| BH_RSI_Repository |
| mysql |
| sys_infobright |
|
test
|
+--------------------+
5 rows
in
set
(0.00 sec)
|
启动 /usr/local/infobright/bin/mysqld_safe --defaults-file=/usr/local/infobright/conf/my-ib.cnf --user=mysql > /dev/null 2>&1 &
关闭 /usr/local/infobright/bin/mysqladmin -uroot -p shutdown
在mysql导出数据的语句
/usr/local/mysql/bin/mysql -u"root" -p"*" -e "select * from database_name.table_name into outfile '/tmp/table_name.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\\\' LINES TERMINATED BY '\n';"
在infobright服务器导入数据(当然需要先建库和表)
|
1
2
3
4
5
6
7
|
CREATE TABLE `table_name` (
`
id
` int(30) NOT NULL COMMENT
'日志表'
,
`uid` int(20) DEFAULT NULL,
`user` varchar(60) NOT NULL COMMENT
'用户名'
,
`ip` varchar(60) DEFAULT NULL COMMENT
'登录IP'
,
`
date
` int(10) NOT NULL COMMENT
'登录时间'
) ENGINE=BRIGHTHOUSE;
|
/usr/local/infobright/bin/mysql -u"root" -p"*" -S /tmp/mysql-ib.sock -D database_name --skip-column-names -e "LOAD DATA INFILE '/tmp/table_name.csv' INTO TABLE table_name character set utf8 FIELDS TERMINATED BY ',' ESCAPED BY '\\\' LINES TERMINATED BY '\n';"
