为了顺应当前形势和更好的发展,黑基网已于9月19日正式更名为【安基网】,域名更换为www.safebase.cn,请卸载旧的APP并安装新的APP,给您带来不便,敬请理解!谢谢

黑基Web安全攻防班
安基网 首页 IT技术 电脑技术 查看内容

MySQL Infobright 数据仓库快速安装笔记

2011-12-29 17:38| 投稿: computer

摘要: Infobright是一个与MySQL集成的开源数据仓库(Data Warehouse)软件,可作为MySQL的一个存储引擎来使用,SELECT查询与普通MySQL无区别。  一、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多个并发查询   二、Infobright 安装与基本用法:  1、下载安装社区版Infobright二进制Linux版本,端口3307 ulimit -SHn 65535mkdir -p /data0/mysql/3307/usr/sbin/groupadd mysql/usr/sbin/useradd -g mysql mysqlcd /usr/local  ①、64位系统: wget http://www.infobright.org/downloads/ice/infobright-3.3.1-x86_64-ice.tar.gztar zxvf infobright-3.3.1-x86_64-ice.tar.gzmv infobright-3.3.1-x86_64 infobright  ②、32位系统: wget http://www.infobright.org/downloads/ice/infobright-3.3.1-i686-ice.tar.gztar zxvf infobright-3.3.1-i686-ice.tar.gzmv 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   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   4、创建管理MySQL数据库的shell脚本: vi /data0/mysql/3307/mysql  输入以下内容(这里的用户名admin和密码12345678接下来的步骤会创建): #!/bin/shmysql_port=3307mysql_username="admin"mysql_password="12345678"function_start_mysql(){    printf "Starting MySQL... "    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... "    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... "    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_mysqlelif [ "$1" = "stop" ]; then    function_stop_mysqlelif [ "$1" = "restart" ]; thenfunction_restart_mysqlelif [ "$1" = "kill" ]; thenfunction_kill_mysqlelse    printf "Usage: /data0/mysql/${mysql_port}/mysql {start|stop|restart|kill} "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';   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 ' ';"   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;  ②、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;  注: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 ' ';"   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)  ②、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)   13、(可选)停止MySQL/Infobright: /data0/mysql/3307/mysql stop

小编推荐:欲学习电脑技术、系统维护、网络管理、编程开发和安全攻防等高端IT技术,请 点击这里 注册黑基账号,公开课频道价值万元IT培训教程免费学,让您少走弯路、事半功倍,好工作升职加薪!



免责声明:本文由投稿者转载自互联网,版权归原作者所有,文中所述不代表本站观点,若有侵权或转载等不当之处请联系我们处理,让我们一起为维护良好的互联网秩序而努力!联系方式见网站首页右下角。


鲜花

握手

雷人

路过

鸡蛋

相关阅读

最新评论

最新

返回顶部