1

0.整理账号密码
1.绿洲工具熟悉
2.慢SQL优化
3.数据库结构设计规范
4.了解业务表以及关联性，数据流向
5.表变更通知到下游BI部门
6.数据库权限、安全把控
7.数据安全、数据流程
8.工具平台优化、流程优化。


今天的任务：
1.排查MySQL同步到MaxCompute任务部分表同步异常问题。
2.分库分表查询方案，调研查询工具，进行中。






select * from information_schema.`PROCESSLIST` where command !='Sleep' AND db !='null'
select * from information_schema.`INNODB_TRX`

mysqldump --login-path=  --single-transaction --set-gtid-purged=OFF   db_name   table_name  > /data/backup/`date +%Y%m%d'.'%k%M%S`-table_name.sql
ossutil64 sync -f /data/backup  oss://bbj-dbbackup/dbbak/man_backup

update mysql.user set authentication_string=password('Bbinfo198..,') where user='root' ;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'Bbinfo198..,' WITH GRANT OPTION;
flush privileges;

select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,EXTRA from information_schema.columns where  COLUMN_NAME  ='gmt_modified' AND EXTRA !='on update CURRENT_TIMESTAMP'
alter table  t_charging_order_product_0000 modify `gmt_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间';

select concat('alter table ',table_schema,'.', table_name ," modify `gmt_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间' ")
from information_schema.tables 
where table_name like 't_charging_order_product_%';

select  concat(table_schema,'.', table_name)
from information_schema.tables 
where table_name like 't_charging_order_0%';

-- mongo
-- 生产
mongo  bbj-vpc-prd-primary.mongodb.rds.aliyuncs.com:3717/admin  -ubbj_dba -p  
bbj_dba_36c3f2

use admin
db.createUser( {user: "bbj_dba",pwd: "bbj_dba_36c3f2",roles: [ { role: "root", db: "admin" } ] });

use tech_ants
db.createUser( {user: "tech_ants_prd",pwd: "tech_ants_prd_e66436",roles:[ { role: "readWrite", db: "tech_ants" } ]});

tech_ants_prd

mongos> use tech_ants
switched to db tech_ants
mongos> show users
{
   "_id" : "tech_ants.tech_ants_prd",
   "user" : "tech_ants_prd",
   "db" : "tech_ants",
   "roles" : [
       {
           "role" : "udf_readWrite",
           "db" : "tech_ants"
       }
   ]
}
{
   "_id" : "tech_ants.tech_ants_prd_readonly",
   "user" : "tech_ants_prd_readonly",
   "db" : "tech_ants",
   "roles" : [
       {
           "role" : "read",
           "db" : "tech_ants"
       }
   ]
}
 
/alidata1/python_scripts/luzhen_export_copy.py  

double(10,4)  
decimal(20,4)

-- 姓名、手机号、身份证号加密
SELECT  (
case IFNULL(CHAR_LENGTH(name),'') 
when '' then name
else CONCAT(left(name, 1),'**',right(name, 1))
end
) as name,(
case IFNULL(CHAR_LENGTH(phone),0) 
when 0 then phone 
when 11 then CONCAT(left(phone, 4),'****',right(phone, 3)) 
else CONCAT(left(phone, 1),'*********',right(phone, 1))  
end
) as phone, (
case IFNULL(CHAR_LENGTH(id_card),0) 
when 0 then id_card 
when 15 then CONCAT(left(id_card, 4),'*******',right(id_card, 4)) 
when 18 then CONCAT(left(id_card, 4),'**********',right(id_card, 4)) 
else CONCAT(left(id_card, 1),'*************',right(id_card, 1)) 
end
) as id_card

-- Hologres 修改表字段的默认值
ALTER TABLE <schema_name>.<table_name> ALTER COLUMN <column> SET DEFAULT <expression>;


ldap://10.168.35.53:389

CN=sql,OU=ServiceAccount,DC=test,DC=bbinsure,DC=local
omIrK#19oR

CN=梁国军,OU=运维保障部,OU=百保君,DC=test,DC=bbinsure,DC=local
P@ssw0rd456


INSERT INTO `data_masking_columns` 
(`column_id`,`rule_type`,`active`,`table_schema`,`table_name`,`column_name`,`column_comment`,`create_time`,`sys_time`,`instance_id`) VALUES 
(null,2,1,'tech_ants_user_00','','','','2022-01-24 17:23:33.393247','2022-01-24 17:23:33.393277',8);

select 
case instance_id  when 3 then '百宝' when 6 then 'BI'  when 8 then '盎司'  else '其他' end as '数据库实例',
table_schema,table_name,column_name,column_comment 
from data_masking_columns
order by 1,2,3,4
    
CREATE TABLE `tech_ants_im_00`.`t_im_date_export_msg` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `file_url` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT '下载文件地址',
  `file_name` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT '下载文件名',
  `creator` varchar(50) CHARACTER SET utf8 NOT NULL DEFAULT 'system' COMMENT '创建人',
  `modifier` varchar(50) CHARACTER SET utf8 NOT NULL DEFAULT 'system' COMMENT '更新人',
  `gmt_created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `gmt_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  `is_deleted` char(1) CHARACTER SET utf8 DEFAULT 'N' COMMENT '是否删除，N 正常 ，Y 删除',
  `type` int(2) DEFAULT NULL COMMENT '1',
  `status` char(1) CHARACTER SET utf8 DEFAULT NULL COMMENT '1: 上传完成，0：上传中',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='数据导出';


archery 查询
select * from  workflow_audit where workflow_title='会话子用户id清理'    
select * from  sql_workflow  where id =  23
update sql_workflow set status='workflow_finish'  where id =  23
update sql_workflow set run_date_end='2022-03-28 15:41:00.000000'   where id =  23