mysql往infobright中按天导数据脚本

一） infobright安装文档：
1)下载infobright并解压到home下
wget http://ftp2.ie.netbsd.org/mirrors/download.sourceforge.net/pub/sourceforge/i/project/in/infobright/ICE%20v3.3.1%20for%20Linux/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 /home/infobright

2)安装infobright，使用3307端口
cd /home/infobright
./install-infobright.sh –datadir=/home/infobright/data –cachedir=/home/infobright/cache –config=/home/infobright/my.cnf –port=3307 –socket=/tmp/mysql3307.sock –user=mysql –group=mysql

3)启动infobright
/home/infobright/bin/mysqld –defaults-file=/home/infobright/my.cnf –basedir=/home/infobright –datadir=/home/infobright/data –user=mysql –log-error=/home/infobright/data/bh.err –pid-file=/home/infobright/data/localhost.pid –socket=/tmp/mysql3307.sock –port=3307

二）crontab按天执行，从mysql中往infobright中导入数据脚本：
#!/bin/sh
enddate=`date +%m_%d`

/home/mysql/bin/mysql -u admin -p111 –default-character-set=utf8 -S /tmp/mysql.sock -D analytic -e “select * from visitlog where time > date_add(curdate(), interval -1 day) into outfile ‘/home/csv/visitlog_${enddate}.csv’ FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘/”‘ ESCAPED BY ‘///’ LINES TERMINATED BY ‘/n’;”

/home/infobright/bin/mysql -u admin -p111 –default-character-set=utf8 -S /tmp/mysql3307.sock -D analytic –skip-column-names -e “LOAD DATA INFILE ‘/home/csv/visitlog_${enddate}.csv’ INTO TABLE visitlog FIELDS TERMINATED BY ‘,’ ESCAPED BY ‘///’ LINES TERMINATED BY ‘/n’;”

转载 http://www.hellophp.cn/archives/289

--------------------------------------

日志导入数据库步骤

zcat api20110505.gz |grep -Ev "#|bot"|awk '{gsub(//[/,"");gsub(//]/,"");gsub(//"/,"");gsub(/'"'"'/,"");print 0 " "$1" " $4" " $5" " $6" " $7" "$8" "$9" "$10" "$11" "$12$13$14$15$16$17$18$19$20$21$22$23$24$25$26$27$28$29$30$31$32$33$34$35$36$37$38$39$40$41$42$43$44$45$46$47}'>nginx_log.txt
/usr/local/mysql/bin/mysql -u root -p123456 -S /tmp/mysql.sock -D weblog -e "LOAD DATA INFILE '/home/log/nginx_log.txt' INTO TABLE nginx_log FIELDS TERMINATED BY ' ' ESCAPED BY '///' LINES TERMINATED BY '/n';"
/usr/local/mysql/bin/mysql -u root -p123456 -S /tmp/mysql.sock -D weblog -e "update nginx_log set addtime=STR_TO_DATE(addtime,'%d/%b/%Y:%T');"
/usr/local/mysql/bin/mysql -u root -p123456 -S /tmp/mysql.sock -D weblog -e "select * from nginx_log into outfile '/data/mysql/test.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '/"' ESCAPED BY '///' LINES TERMINATED BY '/n';"
/usr/local/infobright/bin/mysql -S /tmp/mysql3307.sock -D weblog --skip-column-names -e "LOAD DATA INFILE '/data/mysql/test.csv' INTO TABLE maptitle_log FIELDS TERMINATED BY ',' ESCAPED BY '///' LINES TERMINATED BY '/n';"

zcat api20110505.gz |grep -Ev "#|bot"|awk '{gsub(//[/,"");gsub(//]/,"");gsub(//"/,"");gsub(/'"'"'/,"");print 0 " "$1" " $4" " $5" " $6" " $7" "$8" "$9" "$10" "$11" "$12$13$14$15$16$17$18$19$20$21$22$23$24$25$26$27$28$29$30$31$32$33$34$35$36$37$38$39$40$41$42$43$44$45$46$47}'>nginx_log.txt
 /usr/local/mysql/bin/mysql -u root -p123456 -S /tmp/mysql.sock -D weblog -e "LOAD DATA INFILE '/home/log/nginx_log.txt' INTO TABLE nginx_log FIELDS TERMINATED BY ' ' ESCAPED BY '///' LINES TERMINATED BY '/n';"
/usr/local/mysql/bin/mysql -u root -p123456 -S /tmp/mysql.sock -D weblog -e "update nginx_log set addtime=STR_TO_DATE(addtime,'%d/%b/%Y:%T');"
/usr/local/mysql/bin/mysql -u root -p123456 -S /tmp/mysql.sock -D weblog -e "select * from nginx_log into outfile '/data/mysql/test.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '/"'  ESCAPED BY '///' LINES TERMINATED BY '/n';"
/usr/local/infobright/bin/mysql -S /tmp/mysql3307.sock -D weblog --skip-column-names -e "LOAD DATA INFILE '/data/mysql/test.csv' INTO TABLE maptitle_log FIELDS TERMINATED BY ',' ESCAPED BY '///' LINES TERMINATED BY '/n';"

(责任编辑：IT)

搜索

热门标签:

mysql往infobright中按天导数据脚本