MySQL数据仓Inforbright安装

1、Infobright的基本特征

Infobright是一个与MySQL集成的开源数据仓库（Data Warehouse）软件，可作为MySQL的一个存储引擎来使用，SELECT查询与普通MySQL无区别。

Infobright的基本特征：
优点：
查询性能高：百万、千万、亿级记录数条件下，同等的SELECT查询语句，速度比MyISAM、InnoDB等普通的MySQL存储引擎快5～60倍。
存储数据量大：TB级数据大小，几十亿条记录。
高压缩比：在我们的项目中为18:1，极大地节省了数据存储空间。
基于列存储：无需建索引，无需分区。
适合复杂的分析性SQL查询：SUM, COUNT, AVG, GROUP BY。

限制：
不支持数据更新：社区版Infobright只能使用“LOAD DATA INFILE”的方式导入数据，不支持INSERT、UPDATE、DELETE。
不支持高并发：只能支持10多个并发查询。

2、下载并安装infobright

#下载
wget https://wqzimage.oss-cn-beijing.aliyuncs.com/infobright-4.0.7-0-x86_64-ice.rpm

#安装
rpm -ivh infobright-4.0.7-0-x86_64-ice.rpm --prefix=/usr/local


Preparing...             ################################# [100%]
Installing infobright 4.0.7-0 (x86_64)
The installer will generate /tmp/ib4.0.7-0-install.log install trace log.
Updating / installing...
  1:infobright-4.0.7-0        ################################# [100%]
Creating/Updating datadir and cachedir
Creating user mysql and group mysql
Installing default databases
Installing MySQL system tables...
OK
Filling help tables...
OK
 
To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system
 
PLEASE 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 tk01-dba-mysql-7-197 password 'new-password'
 
Alternatively you can run:
/usr/local/infobright-4.0.7-x86_64/bin/mysql_secure_installation
 
which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly 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.pl
cd /usr/local/infobright-4.0.7-x86_64/mysql-test ; perl mysql-test-run.pl
 
Please 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: 15866(MB)
Infobright optimal ServerMainHeapSize is set to 6000(MB)
Infobright optimal LoaderMainHeapSize is set to 800(MB)
Infobright server installed into folder /usr/local/infobright
Installation 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.sh

3、激活infobright server

cd /usr/local/infobright/
[root@aly infobright]# ./postconfig.sh 

#第一次运行的时候，会提醒是否注册，选择N，不注册，此时再次运行这个脚本：
./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]: n
Register now http://www.infobright.org/Downloads/ICE/

#在次运行  以下设置根据自己的需求更改
 ./postconfig.sh 
Infobright post configuration
--------------------------------------
Using postconfig you can: 
--------------------------------------
(1) Move existing data directory to other location,
(2) Move existing cache directory to other location,
(3) Configure server socket,
(4) Configure server port,
(5) Relocate datadir path to an existing data directory.

Please type 'y' for option that you want or press ctrl+c for exit.

Current configuration: 

--------------------------------------
Current config file: [/etc/my-ib.cnf]
Current brighthouse.ini file: [/usr/local/infobright-4.0.7-x86_64/data/brighthouse.ini]
Current datadir: [/usr/local/infobright-4.0.7-x86_64/data]
Current CacheFolder in brighthouse.ini file: [/usr/local/infobright-4.0.7-x86_64/cache]
Current socket: [/tmp/mysql-ib.sock] 
Current port: [5029]
--------------------------------------
#是否更改data储存目录
(1) Do you want to copy current datadir [/usr/local/infobright-4.0.7-x86_64/data] to a new location? [y/n]:y
#新的储存目录地址
Give new datadir path (e.g. /opt/datadirnewpath/data):/data/infobright/data
#是否更改连接的sock文件
(2) Option to change CacheFolder is disabled when option 1 is chosen!
(3) Do you want to change current socket [/tmp/mysql-ib.sock]? [y/n]:n
#是否更改端口
(4) Do you want to change current port [5029]? [y/n]:n
(5) Relocation is disabled when options 1-4 are chosen!

--------------------------------------
Datadir(/usr/local/infobright-4.0.7-x86_64/data) is going to be copied to /data/infobright/data
--------------------------------------
#是否将数据存储文件复制到指定位置
Please confirm to proceed? [y/n]:y
Copying /usr/local/infobright-4.0.7-x86_64/data to /data/infobright/data ...is done.
You can now remove/backup your old /usr/local/infobright-4.0.7-x86_64/data ...
Done!

4、检查数据存储文件

#infobright 数据存储目录
cd /data/infobright/data/
[root@k8s-master data]# pwd
/data/infobright/data
[root@k8s-master data]# ll
total 20
-rw-rw---- 1 mysql mysql    0 Jun 21 14:52 bh.err
-rw-r--r-- 1 mysql mysql 1898 Jun 21 14:52 brighthouse.ini
-rw-r--r-- 1 mysql mysql    8 Jun 21 14:52 ib_data_version
drwxr-xr-x 2 mysql mysql 4096 Jun 21 14:52 mysql
drwxr-xr-x 2 mysql mysql 4096 Jun 21 14:52 sys_infobright
drwxr-xr-x 2 mysql mysql 4096 Jun 21 14:52 test

5、启动服务

#启动
/etc/init.d/mysqld-ib start
Starting MySQL. SUCCESS! 
#关闭
/etc/init.d/mysqld-ib stop
#检查端口是否启动
ps -ef|grep 5029

6、创建root用户的密码

 cd /usr/local/infobright/bin/
[root@k8s-master bin]# ./mysqladmin -uroot password 'pwd@123'
Warning: ./mysqladmin: unknown variable 'loose-local-infile=1'

#如果不设置进入infobright的用户名和密码，则可以直接使用命令mysql-ib进入上述界面。可以看到，默认的存储引擎是brighthouse。到这里，infobright的安装过程算是全部完成了。

7、连接数据库测试

[root@k8s-master ~]# mysql-ib -uroot -ppwd@123
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server 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.

mysql> 

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| BH_RSI_Repository  |
| mysql              |
| sys_infobright     |
| test               |
+--------------------+
5 rows in set (0.00 sec)

8、导入数据

#社区版的目前只能通过load data的方法进行数据导入，语法如下：
LOAD DATA [LOW_PRIORITY| CONCURRENT] [LOCAL] INFILE 'file_name.txt'  
[REPLACE | IGNORE]  
INTO TABLE tbl_name  
[FIELDS    
  [TERMINATED BY 'string']    
  [[OPTIONALLY] ENCLOSED BY 'char']    
  [ESCAPED BY 'char' ]  
] 
[LINES    
[STARTING BY 'string']    
[TERMINATED BY 'string']  
]  
[IGNORE number LINES]  [(col_name_or_user_var,...)]  [SET col_name = expr,...)]

9、infobright配置文件

#infobright配置文件 所在位置
/etc/init.d/mysqld-ib
#默认是以mysql用户启动的
#可以修改 启动用户 加载存储数据db的配置

10、lb储存配置

ll /data/infobright/data/brighthouse.ini