github monitor使用笔记

查看是否还有任务在执行中:
select distinct(status) from monitor_task where id>1; — Status: 0-等待中, 1-运行中, 2-完成

Excel格式化语句:
=”insert into monitor_task (`keywords`, `pages`, `interval`, `status`, `name`, `mail`,`ignore_org`,`ignore_repo`, `match_method`) values (‘”&K3&”‘,5,720,0,'”&N3&”‘,'”&L3&”;”&M3&”‘,”,”,1);”

报表生成语句:
select a.task_id, b.name, count(1) from Leakage a left join monitor_task b on a.task_id = b.id group by a.task_id;

mysql> desc monitor_task;

导出数据到 xls 文件:
echo “select * from github.Leakage where task_id>1″ | mysql -uroot -p > ./export_Leakage.xls

mysql> show variables like ‘%secure%’;
+————————–+———————–+
| Variable_name | Value |
+————————–+———————–+
| require_secure_transport | OFF |
| secure_auth | ON |
| secure_file_priv | /var/lib/mysql-files/ | (mysql限制了导入与导出的目录权限,要在这个目录中进行导入导出操作)
+————————–+———————–+

目标路径: /var/lib/mysql/export_data/export_Leakage.csv
docker exec -it container_id /bin/bash

mysql -uroot -p

select b.name as 业务线, a.keyword as 关键字, a.file_name as 包含关键字的文件名, a.html_url, a.last_modified, a.repo_name, a.repo_url, a.user_name, a.user_url, a.add_time as 扫描时间
from github.Leakage a left join github.monitor_task b on a.task_id = b.id
where a.task_id > 1 into outfile ‘/var/lib/mysql-files/export_Leakage.csv’ fields terminated by ‘,’ optionally enclosed by ‘”‘ escaped by ‘”‘ lines terminated by ‘\n’;

cp /var/lib/mysql-files/export_Leakage.csv /var/lib/mysql/export_data/
导出后中文是乱码,用UE打开可识别乱码, 再使用 excel 新建->数据自文本导入(编码格式选: unicode utf-8)

====================================================
只导出表结构:
mysqldump -uroot -p github | gzip > /var/lib/mysql-files/github.sql.gz

cp /var/lib/mysql-files/github.sql.gz /var/lib/mysql/export_data/

目标文件存放在:
./docker/data/mysql/export_data/

====================================================
导入表数据:

vi ./docker/data/mysql/export_data/ins_temp.sql
cp /var/lib/mysql/export_data/ins_temp.sql /var/lib/mysql-files/

====================================================
找出keywords有更新的项目组:
select b.name, length(b.keywords) from `github`.`monitor_task` a, `github`.`monitor_task_temp` b where a.name=b.name and a.keywordsb.keywords;

更新 keywords:
update `github`.`monitor_task` a, `github`.`monitor_task_temp` b set a.keywords = b.keywords where a.name=b.name and a.keywordsb.keywords;

更新 email:
select a.name, a.mail, b.mail from `github`.`monitor_task` a, `github`.`monitor_task_temp` b where a.name=b.name and a.mailb.mail;
(以前mail字段末尾没有多余的分号,这次更新进入分号)
update `github`.`monitor_task` a, `github`.`monitor_task_temp` b set a.mail = b.mail where a.name=b.name and a.mailb.mail;
====================================================
单独调试 processors.py
docker ps 找到 webserver container_id
docker exec -it container_id /bin/bash
根据代码注释提示: 去掉processors.py源码中的个注释”#”
命令行执行:
PYTHONPATH=. python3 github_monitor/apps/monitor/processors.py