基于MySQL和Infobright的数据仓库技术

用户设置
ulimit -SHn 65535
mkdir -p /home/mysql/infobright
/usr/sbin/groupadd mysql
/usr/sbin/useradd -g mysql mysql
cd /usr/local/soft/

下载解压
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

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

安装步骤
cd infobright
./install-infobright.sh --datadir=/home/mysql/infobright/data --cachedir=/home/mysql/infobright/cache --config=/home/mysql/infobright/my.cnf --port=3307 --socket=/tmp/mysqlinfobright.sock --user=mysql --group=mysql

内存限制
vi /home/mysql/infobright/data/brighthouse.ini

启动与停止
cd /usr/local/infobright/; ./bin/mysqld_safe --defaults-file=/home/mysql/infobright/my.cnf 2>&1 > /dev/null &
cd /usr/local/infobright/; ./bin/mysqladmin -uroot -p -S /tmp/mysqlinfobright.sock shutdown
/usr/local/infobright/bin/mysql -S /tmp/mysqlinfobright.sock

测试导出
/usr/local/mysql/bin/mysql -uroot -p -Dt2011 -e "select username,user_id,regtime into outfile "/home/test.csv" FIELDS TERMINATED BY "," ENCLOSED BY """ ESCAPED BY "\" LINES TERMINATED BY " " from users limit 300;"

测试导入
/usr/local/infobright/bin/mysql -S /tmp/mysqlinfobright.sock -uroot -p -Dt2011_logs --skip-column-names -e "LOAD DATA INFILE "/home/test.csv" INTO TABLE access_log FIELDS TERMINATED BY "," ESCAPED BY "\" LINES TERMINATED BY " ";"

设置权限
/usr/local/mysql/bin/mysql -uroot
/usr/local/infobright/bin/mysql -S /tmp/mysqlinfobright.sock
GRANT ALL PRIVILEGES ON *.* TO "addcn"@"localhost" IDENTIFIED BY "123456";
SET PASSWORD FOR "addcn"@"localhost" = old_password（"addcn2011"）;
FLUSH PRIVILEGES;

GRANT ALL PRIVILEGES ON *.* TO "addcn"@"%" IDENTIFIED BY "123456";
SET PASSWORD FOR "addcn"@"%" = old_password（"addcn2011"）;

/usr/local/infobright/bin/mysql -S /tmp/mysqlinfobright.sock -uaddcn -paddcn2011 -Dt2011_logs --skip-column-names -e "LOAD DATA INFILE "/home/test.csv" INTO TABLE access_log FIELDS TERMINATED BY "," ESCAPED BY "\" LINES TERMINATED BY " ";"

其它数据
CREATE DATABASE `t2011_logs` ;
CREATE TABLE `access_log` （
`sid` varchar（50） NOT NULL,
`user_id` int（11） NOT NULL,
`timestamp` int（11） NOT NULL
） ENGINE=BRIGHTHOUSE DEFAULT CHARSET=latin1;

1,1001,1282815666
2,1002,1282815668
3,1003,1282815675
LOAD DATA INFILE "/home/test.csv" INTO TABLE `access_log` FIELDS TERMINATED BY "," ESCAPED BY "" LINES TERMINATED BY " ";

select * from access_log;

压缩比例：
1,660,546数据，1498MB后为56179KB
1 533 952 / 56 179 = 27.3047224

内容超出字段类型：
ERROR 2 （HY000） at line 1: Wrong data or column definition. Row: 3, field: 16.CentOS编译安装MongoDB提高MySQL导入导出速度的方法相关资讯 MySQL基础教程

MySQL基础教程：关于varchar（N）（01月22日）
MySQL SELECT同时UPDATE同一张表（02/19/2013 07:20:18）
Linux修改MySQL最大并发连接数（02/15/2013 15:37:21）

高性能MySQL（第3版）中文PDF带目（10/26/2014 10:03:50）
如何在MySQL中的获取IP地址的网段（02/18/2013 12:23:33）
C++和C#访问MySQL的简单代码示例（12/21/2012 09:04:10）

本文评论查看全部评论（0）