mysqldump.txt

重组分区
alter table rpt_fxq_tb_company_ms reorganize partition report into(partition pt{workday}000000 values less than('{workday}999999'), partition report values less than(maxvalue));

备份测试数据源，供测试使用
select concat('mysqldump --set-gtid-purged=OFF -hrm-bp19v63q682asdrja.mysql.rds.aliyuncs.com -utaipingbi_etl -p''Tpstic123456'' -t -w"pt=20190928000000" tpedw '
	-- ,chr(13)
	,group_concat(distinct table_name separator ' ')
	,'| gzip > tpedw-`date +%Y-%m-%d`.sql.gz') exp_data
from information_schema.tables 
where table_schema='tpedw'  
      and (table_name like 'ods_amltp%' or table_name like 'ods_cthx%' or table_name like 'ods_fund%')
order by table_name

备份AML表结构，防止被误删除
select concat('mysqldump --set-gtid-purged=OFF -hrm-bp19v63q682asdrja.mysql.rds.aliyuncs.com -utaipingbi_etl -p''Tpstic123456'' -d tpedw '
	-- ,chr(13)
	,group_concat(distinct table_name separator ' ')
	,'| gzip > tpedw-ddl`date +%Y-%m-%d`.sql.gz') exp_data
from information_schema.tables
where table_schema='tpedw' 
        and (table_name like '%rpt_fxq%' or table_name like '%edw_cust%')
        
还原测试数据到测试库前增加对应分区
select 'alter table', table_name, concat(' add partition (partition pt', dt, '000000 values less than (''', dt, '999999''));') add_part
from information_schema.tables m, (select date_format(date_add(now(), interval -1 day),'%Y%m%d') dt) v
where table_schema = 'tpedw' 
    and (table_name like 'ods_amltp%' or table_name like 'ods_cthx%' or table_name like 'ods_fund%')

AML新数据生成前增加分区
select 'alter table', table_name, concat(' add partition (partition pt', dt, '000000 values less than (''', dt, '999999''));') add_part
from information_schema.tables m, (select date_format(date_add(now(), interval -1 day),'%Y%m%d') dt) v
where table_schema = 'tpedw' 
        and (table_name like '%rpt_fxq%' or table_name like '%edw_cust%')
and (table_name not like 'rpt_fxq_manual_%' and table_name not like 'x%' and table_name not like 's%')

AML新数据生成前增加分区(仅客户主题)
select 'alter table', table_name, concat(' add partition (partition pt', dt, '000000 values less than (''', dt, '999999''));') add_part
from information_schema.tables m, (select date_format(date_add(now(), interval -1 day),'%Y%m%d') dt) v
where table_schema = 'tpedw' 
        and table_name like '%edw_cust%'
        and (table_name not like 'x%' and table_name not like 's%')

mysql -u"tp_admin" -h"rm-j5efs4eax4128t19u.mysql.rds.aliyuncs.com" -p"9Uu@8Af&" -D tpedw <$1 1>${logFileName} 2>${errFileName}

mysqldump  --no-data --set-gtid-purged=OFF -hrm-bp1k9p25tjq1d86i9.mysql.rds.aliyuncs.com -utpadmin_read -pTpstic123456 nvhlclm web_bat_data> web_bat_data.sql

生成表定义:
mysqldump -d -hrm-bp1k9p25tjq1d86i9.mysql.rds.aliyuncs.com -utpadmin_read -p'Tpstic123456' finarp web_fin_rpprmcustomer

cthx: 传统核心
gl: 总账
amltp: 反洗钱(银保监会)

alter table     `tpedw`.`ods_cthx_t_banktransactions` rename to ods_fund_t_banktransactions;


备份AML数据结构
select concat('mysqldump --set-gtid-purged=OFF -hrm-bp19v63q682asdrja.mysql.rds.aliyuncs.com -utaipingbi_etl -p''Tpstic123456'' -d tpedw '
	-- ,chr(13)
	,group_concat(distinct table_name separator ' ')
	,'| gzip > tpedw-ddl`date +%Y-%m-%d`.sql.gz') exp_data
from information_schema.tables 
where table_schema='tpedw'  
      and table_name like 'rpt_fxq_tb%'
order by table_name

mysql --defaults-extra-file=aml.cnf -D<database> < test_db.sql   

/alidata/workspace/aml/conf/aml.cnf
[client]
port = 3306
default-character-set = utf8mb4
host = rm-bp19v63q682asdrja.mysql.rds.aliyuncs.com
user =taipingbi_etl
password =Tpstic123456