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

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

用户设置

ulimit -SHn 65535

mkdir -p /home/mysql/infobright

/usr/sbin/groupadd mysql

/usr/sbin/useradd -g mysql mysql

cd /usr/local/soft/

下载解压

64位系统

wget

tar zxvf infobright-3.3.1-x86_64-ice.tar.gz

mv infobright-3.3.1-x86_64 infobright

32位系统

wget

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 '\n' 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 '\n';"

设置权限

/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 '\n';"

其它数据

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 '\n';

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.

本文原创发布php中文网，转载请注明出处，感谢您的尊重！