MySQL Infobright 数据仓库快速安装笔记[原创]
浏览:2632次 出处信息
一、Infobright的基本特征:
优点:
查询性能高:百万、千万、亿级记录数条件下,同等的SELECT查询语句,速度比MyISAM、InnoDB等普通的MySQL存储引擎快5~60倍
存储数据量大:TB级数据大小,几十亿条记录
高压缩比:在我们的项目中为18:1,极大地节省了数据存储空间
基于列存储:无需建索引,无需分区
适合复杂的分析性SQL查询:SUM, COUNT, AVG, GROUP BY
限制:
不支持数据更新:社区版Infobright只能使用“LOAD DATA INFILE”的方式导入数据,不支持INSERT、UPDATE、DELETE
不支持高并发:只能支持10多个并发查询
二、Infobright 安装与基本用法:
1、下载安装社区版Infobright二进制Linux版本,端口3307
ulimit -SHn 65535
mkdir -p /data0/mysql/3307
/usr/sbin/groupadd mysql
/usr/sbin/useradd -g mysql mysql
cd /usr/local
mkdir -p /data0/mysql/3307
/usr/sbin/groupadd mysql
/usr/sbin/useradd -g mysql mysql
cd /usr/local
①、64位系统:
wget http://www.infobright.org/downloads/ice/infobright-3.3.1-x86_64-ice.tar.gz
tar zxvf infobright-3.3.1-x86_64-ice.tar.gz
mv infobright-3.3.1-x86_64 infobright
tar zxvf infobright-3.3.1-x86_64-ice.tar.gz
mv infobright-3.3.1-x86_64 infobright
②、32位系统:
wget http://www.infobright.org/downloads/ice/infobright-3.3.1-i686-ice.tar.gz
tar zxvf infobright-3.3.1-i686-ice.tar.gz
mv infobright-3.3.1-i686 infobright
tar zxvf infobright-3.3.1-i686-ice.tar.gz
mv infobright-3.3.1-i686 infobright
cd infobright
./install-infobright.sh --datadir=/data0/mysql/3307/data --cachedir=/data0/mysql/3307/cache --config=/data0/mysql/3307/my.cnf --port=3307 --socket=/tmp/mysql3307.sock --user=mysql --group=mysql
./install-infobright.sh --datadir=/data0/mysql/3307/data --cachedir=/data0/mysql/3307/cache --config=/data0/mysql/3307/my.cnf --port=3307 --socket=/tmp/mysql3307.sock --user=mysql --group=mysql
2、开始安装,提示以下信息:
Infobright installation script is running...
Checking system configuration...
Infobright license agreement...
System tool 'Less' - a text file viewer will be used to display license agreement.
Please only use up/down arrow keys for scrolling license text and press Q when finished reading.
Press R -Read license agreement, N -Exit the installation [R/N]:
选择R,空格翻页到页尾,看到以下提示时,选择Q继续安装:
END OF TERMS AND CONDITIONS
============ Press Q to continue installation ==========
(END)
接下来会显示以下信息,选择Y同意:
Press Y -I agree, Any other key -I do not agree [Y/*]:
这时,会提示是否在线注册,选择N不注册:
Installation has been made for system user root and mysql.
Please see README or User guide for instructions related to start/stop the Infobright server and connect to it.
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]:
3、修改Infobright内存使用限制
vi /data0/mysql/3307/data/brighthouse.ini
根据自身的物理内存大小修改ServerMainHeapSize、ServerCompressedHeapSize、LoaderMainHeapSize的值,有参考:
############ Critical Memory Settings ############
# System Memory Server Main Heap Size Server Compressed Heap Size Loader Main Heap Size
# 32GB 24000 4000 800
# 16GB 10000 1000 800
# 8GB 4000 500 800
# 4GB 1300 400 400
# 2GB 600 250 320
# System Memory Server Main Heap Size Server Compressed Heap Size Loader Main Heap Size
# 32GB 24000 4000 800
# 16GB 10000 1000 800
# 8GB 4000 500 800
# 4GB 1300 400 400
# 2GB 600 250 320
4、创建管理MySQL数据库的shell脚本:
vi /data0/mysql/3307/mysql
输入以下内容(这里的用户名admin和密码12345678接下来的步骤会创建):
#!/bin/sh
mysql_port=3307
mysql_username="admin"
mysql_password="12345678"
function_start_mysql()
{
printf "Starting MySQL...\n"
cd /usr/local/infobright/ && /bin/sh ./bin/mysqld_safe --defaults-file=/data0/mysql/${mysql_port}/my.cnf 2>&1 > /dev/null &
}
function_stop_mysql()
{
printf "Stoping MySQL...\n"
cd /usr/local/infobright/ && ./bin/mysqladmin -u ${mysql_username} -p${mysql_password} -S /tmp/mysql${mysql_port}.sock shutdown
}
function_restart_mysql()
{
printf "Restarting MySQL...\n"
function_stop_mysql
sleep 5
function_start_mysql
}
function_kill_mysql()
{
kill -9 $(ps -ef | grep 'bin/mysqld_safe' | grep ${mysql_port} | awk '{printf $2}')
kill -9 $(ps -ef | grep 'libexec/mysqld' | grep ${mysql_port} | awk '{printf $2}')
}
if [ "$1" = "start" ]; then
function_start_mysql
elif [ "$1" = "stop" ]; then
function_stop_mysql
elif [ "$1" = "restart" ]; then
function_restart_mysql
elif [ "$1" = "kill" ]; then
function_kill_mysql
else
printf "Usage: /data0/mysql/${mysql_port}/mysql {start|stop|restart|kill}\n"
fi
mysql_port=3307
mysql_username="admin"
mysql_password="12345678"
function_start_mysql()
{
printf "Starting MySQL...\n"
cd /usr/local/infobright/ && /bin/sh ./bin/mysqld_safe --defaults-file=/data0/mysql/${mysql_port}/my.cnf 2>&1 > /dev/null &
}
function_stop_mysql()
{
printf "Stoping MySQL...\n"
cd /usr/local/infobright/ && ./bin/mysqladmin -u ${mysql_username} -p${mysql_password} -S /tmp/mysql${mysql_port}.sock shutdown
}
function_restart_mysql()
{
printf "Restarting MySQL...\n"
function_stop_mysql
sleep 5
function_start_mysql
}
function_kill_mysql()
{
kill -9 $(ps -ef | grep 'bin/mysqld_safe' | grep ${mysql_port} | awk '{printf $2}')
kill -9 $(ps -ef | grep 'libexec/mysqld' | grep ${mysql_port} | awk '{printf $2}')
}
if [ "$1" = "start" ]; then
function_start_mysql
elif [ "$1" = "stop" ]; then
function_stop_mysql
elif [ "$1" = "restart" ]; then
function_restart_mysql
elif [ "$1" = "kill" ]; then
function_kill_mysql
else
printf "Usage: /data0/mysql/${mysql_port}/mysql {start|stop|restart|kill}\n"
fi
5、赋予shell脚本可执行权限:
chmod +x /data0/mysql/3307/mysql
6、启动MySQL/Infobright:
/data0/mysql/3307/mysql start
7、通过命令行登录管理MySQL服务器(提示输入密码时直接回车):
/usr/local/infobright/bin/mysql -u root -p -S /tmp/mysql3307.sock
8、输入以下SQL语句,创建一个具有root权限的用户(admin)和密码(12345678):
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' IDENTIFIED BY '12345678';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'127.0.0.1' IDENTIFIED BY '12345678';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'127.0.0.1' IDENTIFIED BY '12345678';
9、示例:从普通的MySQL数据库(假设MySQL安装路径为/usr/local/webserver/mysql)导出数据到csv文件:
/usr/local/webserver/mysql/bin/mysql -S /tmp/mysql3306.sock -D tongji_logs -e "select * from log_visits_2010_05_10 into outfile '/data0/test.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\\\' LINES TERMINATED BY '\n';"
10、示例:普通MySQL和Infobright建表对比
①、普通MySQL的InnoDB存储引擎建表:
CREATE TABLE IF NOT EXISTS `log_visits_2010_05_12` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`cate_id` int(11) NOT NULL,
`site_id` int(11) unsigned NOT NULL,
`visitor_localtime` char(8) NOT NULL,
`visitor_idcookie` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `cate_site_id` (`cate_id`,`site_id`),
KEY `visitor_localtime` (`visitor_localtime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
`id` int(11) NOT NULL AUTO_INCREMENT,
`cate_id` int(11) NOT NULL,
`site_id` int(11) unsigned NOT NULL,
`visitor_localtime` char(8) NOT NULL,
`visitor_idcookie` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `cate_site_id` (`cate_id`,`site_id`),
KEY `visitor_localtime` (`visitor_localtime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
②、Infobright的BRIGHTHOUSE存储引擎建表:
CREATE TABLE IF NOT EXISTS `log_visits` (
`id` int(11) NOT NULL,
`cate_id` int(11) NOT NULL,
`site_id` int(11) NOT NULL,
`visitor_localtime` char(8) NOT NULL,
`visitor_idcookie` varchar(255) NOT NULL,
) ENGINE=BRIGHTHOUSE DEFAULT CHARSET=utf8;
`id` int(11) NOT NULL,
`cate_id` int(11) NOT NULL,
`site_id` int(11) NOT NULL,
`visitor_localtime` char(8) NOT NULL,
`visitor_idcookie` varchar(255) NOT NULL,
) ENGINE=BRIGHTHOUSE DEFAULT CHARSET=utf8;
注:BRIGHTHOUSE存储引擎建表时不能有AUTO_INCREMENT自增、unsigned无符号、unique唯一、主键PRIMARY KEY、索引KEY。
11、示例:从csv文件导入数据到Infobright数据仓库:
/usr/local/infobright/bin/mysql -S /tmp/mysql3307.sock -D dw --skip-column-names -e "LOAD DATA INFILE '/data0/test.csv' INTO TABLE log_visits_2010_04_13 FIELDS TERMINATED BY ',' ESCAPED BY '\\\' LINES TERMINATED BY '\n';"
12、示例:普通MySQL和Infobright查询速度对比(共220多万条记录):
①、普通MySQL的InnoDB存储引擎(已建索引):
mysql> SELECT config_browser_name, count(*) AS total FROM `browser_info` GROUP BY config_browser_name order by total DESC;
+---------------------+---------+
| config_browser_name | total |
+---------------------+---------+
| IE | 2204016 |
| CH | 20650 |
| FF | 10475 |
| MO | 6147 |
| OT | 1631 |
| OP | 1282 |
| SF | 797 |
| KM | 5 |
| KO | 2 |
+---------------------+---------+
9 rows in set (1 min 28.13 sec)
+---------------------+---------+
| config_browser_name | total |
+---------------------+---------+
| IE | 2204016 |
| CH | 20650 |
| FF | 10475 |
| MO | 6147 |
| OT | 1631 |
| OP | 1282 |
| SF | 797 |
| KM | 5 |
| KO | 2 |
+---------------------+---------+
9 rows in set (1 min 28.13 sec)
②、Infobright的BRIGHTHOUSE存储引擎:
mysql> SELECT config_browser_name, count(*) AS total FROM `browser_info` GROUP BY config_browser_name order by total DESC;
+---------------------+---------+
| config_browser_name | total |
+---------------------+---------+
| IE | 2204016 |
| CH | 20650 |
| FF | 10475 |
| MO | 6147 |
| OT | 1631 |
| OP | 1282 |
| SF | 797 |
| KM | 5 |
| KO | 2 |
+---------------------+---------+
9 rows in set (0.84 sec)
+---------------------+---------+
| config_browser_name | total |
+---------------------+---------+
| IE | 2204016 |
| CH | 20650 |
| FF | 10475 |
| MO | 6147 |
| OT | 1631 |
| OP | 1282 |
| SF | 797 |
| KM | 5 |
| KO | 2 |
+---------------------+---------+
9 rows in set (0.84 sec)
13、(可选)停止MySQL/Infobright:
/data0/mysql/3307/mysql stop
建议继续学习:
- Hadoop超级安装手册 (阅读:3979)
- 列式数据仓库引擎之Infobright (阅读:3547)
- Ubuntu下Postgresql-8.4安装及配置 (阅读:3341)
- 详细步骤:在64位Linux上安装Memcached (阅读:3046)
- 在 Dell PowerEdge 1950 上安装 Linux 2.6.32-rc8 内核的问题与解决 (阅读:3033)
- Infobright的架构 (阅读:2947)
- ubuntu 9.10快速安装nginx+php环境手记 (阅读:2641)
- mysql 的模块不能安装的解决方法 (阅读:2592)
- Squid的Linux下安装配置笔记(上) (阅读:2357)
- Linux服务器基本安装 (阅读:2311)
QQ技术交流群:445447336,欢迎加入!
扫一扫订阅我的微信号:IT技术博客大学习
扫一扫订阅我的微信号:IT技术博客大学习
<< 前一篇:DBA工作初体验之心惊胆战
后一篇:WordPress数据字典 >>
文章信息
- 作者:张宴 来源: 回忆未来
- 标签: Infobright 安装
- 发布时间:2010-05-20 13:25:52
建议继续学习
近3天十大热文
- [54] IOS安全–浅谈关于IOS加固的几种方法
- [52] android 开发入门
- [52] 如何拿下简短的域名
- [51] 图书馆的世界纪录
- [49] Oracle MTS模式下 进程地址与会话信
- [49] Go Reflect 性能
- [47] 【社会化设计】自我(self)部分――欢迎区
- [46] 读书笔记-壹百度:百度十年千倍的29条法则
- [36] 程序员技术练级攻略
- [29] 视觉调整-设计师 vs. 逻辑