連線mysql
mysql -u algorithm -h xxx -P 11234 -pxxx
新增索引
alter table wiki_update_info add index idx_url(url);
檢視阻塞程序
select * from information_schema.innodb_trx;
常用語句
select t1.post_uuid,t1.url from wiki_analysis_info as t1 full join wiki_link_info as t2 on t1.post_uuid=t2.post_uuid where t2.anchor_text="國際匯率";
update mysql.user SET File_priv = 'Y' WHERE user='algorithm' AND host='mysql11234w.shjt2.yun.qianxin-inc.cn';
CREATE TABLE IF NOT EXISTS algorithm.product_info_post_url (id BIGINT AUTO_INCREMENT,post_uuid CHAR(36) COMMENT '對應s3上的response的物件的post_uuid',url TEXT(1024) COMMENT 'url',url_info_json TEXT(65535) comment 'url的附加資訊，如從哪個url對應的頁面得到本連結，class，content，text',url_md5 CHAR(32) COMMENT 'url的md5加密id',post_time DATETIME COMMENT '向爬蟲提交url的時間',PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
轉表
mysqldump --single-transaction -u algorithm -h mysql11234w.shjt2.yun.qianxin-inc.cn -P 11234 -pxxx --default-character-set=utf8 algorithm product_info_analysis_info > product_info_analysis_info.sql
mysql -u product_info -h mysql11234w.shjt2.yun.qianxin-inc.cn -P 11234 -D product_info -pxxx < product_info_analysis_info.sql
匯出檔案
echo 'select * from xuanji_clean_product_info2 where vendor_source="bing"' | mysql -u product_info -h mysql11234w.shjt2.yun.qianxin-inc.cn -P 11234 -D product_info -pxxx > /home/wangwei22/bing.xls
批次解壓
for i in `find . -name "*.zip" -type f`; do unzip -o $i -d ../zipped ; done