|
公司软件架构调整，把postgresql 数据库转换成mysql.postgresql大小1T左右。
现在记录下迁移转化数据库过程mssql数据库迁移，跟需要注意的事项。
采用的工具：aws 的stc +dms+人工检查
stc:
1、下载aws的stc工具，把postgresql表结构转成mysql.
转换过程中需要注意的地方：
mapping rules转化规则：
schema名称需要注意下，postgresql一般都是public，需要重命名成mysql的schema:
schemas public rename sulu ;
数据类型：
character varying(%) to varchar(%) ;
text to mediumtext ; -- postgresql text类型几乎不限制大小，mysql text有大小现在。需要text类型转成对于的合适的大小类型
boolean to varchar(5) ; -- postgresql 转到mysql后存的是 true/false 字符 ,迁移完后还要进一步处理。
timestamp with time zone to timestamp -- 时间 带时区的，如果有时区区别需要注意，这里同已转成mysql的timestamp
timestamp without time zone to timestamp -- 时间不带时区的.
修改每个表的自增主键 -- postgresql 自增主键用的是seq ,mysql 内部系统维护
id bigint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY
用stc工具把转换的sql导出成mysql 的sql
检查表的索引是否正常转成mysql语法
导出的sql 修改每个时间类型的默认值
create_time timestamp default current_timestamp,
update_time timestamp default current_timestamp,
把修改完的sql,导入到mysql数据库进行表结构初始化。
aws上mysql数据库默认配置 没有开启忽略数据库大小写参数 lower_case_table_names = 1 ;
修改字符集utf8mb4 ，时区等其他其他需要修改的参数
创建DMS任务的时候 ，要选高级 vpc安全组中的其中一个，确保能连上数据源
任务设置 目标表准备模式 不执行任何操作（防止把已建的表结构删除）
转化规则 ：
架构重命名 public => sulu
开始迁移数据
数据迁移完，处理bool类型。
写个存储过程，统一把bool类型存成字符的转成正常的mysql int 存储成 1，0
call pinsert_table_bool(); #存储需要处理的表字段
call p_bool_sql(); #处理bool表数据
# 记录需要处理的bool字段的表
CREATE PROCEDURE `pinsert_table_bool`()
begin
drop table if exists test_bool_table ;
create table if not exists test_bool_table(id int auto_increment,
table_name varchar(64),
column_name varchar(64),
PRIMARY KEY(id)
) engine = innodb DEFAULT CHARSET=utf8mb4;
#获取bool字段
#select concat(' union select ',"'",table_name,"','",column_name,"'") from information_schema.columns where TABLE_SCHEMA='yncash' and column_type = 'varchar(5)' ;
insert into test_bool_table(table_name,column_name)
select "schema_version","success"
union select "t_bank_payment","is_active"
union select "t_bank_repayment","is_active"
union select "t_collection_task_comment","need_remind"
union select "t_contract_template","is_deleted"
union select "t_coupon","used"
union select "t_customer_inbox","is_read"
union select "t_gf_blacklist","is_match"
union select "t_loan_app","is_ios"
union select "t_loan_app","isstopcollecting"
union select "t_region","is_used"
union select "t_review_rule_execution","manual_ignore"
union select "t_review_step_rule_rel","manual_ignore"
union select "t_review_step_task_rel","manual_ignore"
union select "t_review_task_execution","manual_ignore"
union select "t_reward_lending","is_tips"
union select "t_risk_constants","black_city"
union select "t_risk_constants","black_list_company"
union select "t_risk_constants","black_list_person"
union select "t_risk_constants","black_list_person_ktp"
union select "t_risk_constants","black_list_person_mobile"
union select "t_risk_constants","contact_apply_success"
union select "t_risk_constants","is_person_bank_card_black"
union select "t_risk_constants","is_person_ktp_black"
union select "t_risk_constants","is_person_mobile_black"
union select "t_risk_constants","re_apply"
union select "t_risk_rule","android_ignore"
union select "t_risk_rule","ignore_re_apply"
union select "t_risk_rule","ios_ignore"
union select "t_risk_rule_bak20190627","android_ignore"
union select "t_risk_rule_bak20190627","ignore_re_apply"
union select "t_risk_rule_bak20190627","ios_ignore"
union select "t_tongdun_credit_bodyguard","success"
union select "t_tongdun_device_info","allow_mock_location"
union select "t_tongdun_device_info","root"
union select "t_white_risk_rule","android_ignore"
union select "t_white_risk_rule","ignore_re_apply"
union select "t_white_risk_rule","ios_ignore"
union select "v_data_market","black_city"
union select "v_data_market","black_list_company"
union select "v_data_market","black_list_person"
union select "v_data_market","black_list_person_ktp"
union select "v_data_market","black_list_person_mobile"
union select "v_data_market","contact_apply_success"
union select "v_data_market","is_person_bank_card_black"
union select "v_data_market","is_person_ktp_black"
union select "v_data_market","is_person_mobile_black"
union select "v_data_market","re_apply"
union select "x_trace_device_info","is_root";
end
p_bool_sql 生产对应的修改bool类型update,alter语句
CREATE PROCEDURE `p_bool_sql`()
begin
declare m_table_name varchar(64) ;
declare m_column_name varchar(64) ;
declare m_id int ;
declare m_i int ;
declare m_is_nullable varchar(10) ;
declare m_column_default varchar(10) ;
select max(id) into m_id from test_bool_table;
set m_i = 1 ;
set @m_sql = '' ;
while m_i<=m_id do
select table_name,column_name into m_table_name,m_column_name from test_bool_table where id = m_i ;
select is_nullable,COLUMN_DEFAULT into m_is_nullable,m_column_default
from information_schema.columns where table_schema='yncash' and
table_name = m_table_name and COLUMN_NAME = m_column_name ;
set @m_null = if(m_is_nullable='NO',' NOT NULL ',' ');
SET @m_default = case when m_column_default = 'TRUE' then ' default 1 ' when m_column_default = 'false' then ' default 0 ' else ' ' end ;
set @m_sql2=concat(" update ",m_table_name," set ",m_column_name,"=case ",m_column_name," when 'true' then 1 when 'false' then 0 else ",m_column_name," end ;") ;
set @m_sql3=concat(" alter table ",m_table_name," modify column ",m_column_name," tinyint ",@m_null,' ',@m_default,";" ) ;
set m_i = m_i + 1 ;
set @m_sql = concat(@m_sql,@m_sql2,@m_sql3) ;
end WHILE ;
select @m_sql;
end
（编辑：威海站长网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
