mysql常用命令

连接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