智家迁移上云

发表于 | 更新于 | 阅读次数：

海知友

源信息 目标信息
实例 10.159.44.168 prd-paas-mysql-18
123.103.113.6 rm-2ze4fd3f195vg25a3
端口 3306 3306
账号 sre / xx rds / xx
迁移对象 vipcode、vipwechat
数据量 150GB / 43GB 
1
select `t`.`id` AS `id`,`t`.`orderNo` AS `orderNo`,`t`.`createTime` AS `createTime`,`t`.`flowStatus` AS `flowStatus`,`t`.`mobile` AS `mobile`,`t`.`userId` AS `userId`,`t`.`userName` AS `userName`,`t`.`recognitionFlag` AS `recognitionFlag`,`t`.`recognitionCondition` AS `recognitionCondition`,`t`.`domainName` AS `domainName`,`t`.`appointType` AS `appointType`,`t`.`productType` AS `productType`,`t`.`userSource` AS `userSource`,`t`.`appointTime` AS `appointTime`,`t`.`productCategory` AS `productCategory`,`t`.`rightStatus` AS `rightStatus`,`t`.`buyMore` AS `buyMore`,`t`.`orderStartTime` AS `orderStartTime`,`t`.`oldfornewdbId` AS `oldfornewdbId`,`t`.`industry` AS `industry`,`t`.`buyIntention` AS `buyIntention`,`t`.`yearBrand` AS `yearBrand`,`t`.`hmcid` AS `hmcid`,`t`.`shopId` AS `shopId`,`t`.`mayEditCoupleOrderId` AS `mayEditCoupleOrderId`,`t`.`coupleSponsor` AS `coupleSponsor` from (select `a`.`id` AS `id`,`a`.`orderNo` AS `orderNo`,ifnull(`a`.`createTime`,`a`.`appointTime`) AS `createTime`,`a`.`flowStatus` AS `flowStatus`,`a`.`mobile` AS `mobile`,`a`.`userId` AS `userId`,`a`.`userName` AS `userName`,`a`.`recognitionFlag` AS `recognitionFlag`,`a`.`recognitionCondition` AS `recognitionCondition`,`a`.`domainName` AS `domainName`,`a`.`appointType` AS `appointType`,`a`.`productType` AS `productType`,`a`.`userSource` AS `userSource`,`a`.`appointTime` AS `appointTime`,`a`.`productCategory` AS `productCategory`,`a`.`rightStatus` AS `rightStatus`,0 AS `buyMore`,NULL AS `orderStartTime`,`a`.`oldfornewdbId` AS `oldfornewdbId`,`a`.`industry` AS `industry`,`c`.`buyIntention` AS `buyIntention`,`c`.`yearBrand` AS `yearBrand`,`a`.`hmcid` AS `hmcid`,NULL AS `shopId`,NULL AS `mayEditCoupleOrderId`,NULL AS `coupleSponsor` from (`vipcode`.`code_workflow` `a` left join `vipcode`.`code_oldfornewdb_count` `c` on(`a`.`oldfornewdbId` = `c`.`id`)) where `a`.`flowStatus` in ('0','6','7','8') and `a`.`isDelete` = 0 union all select `a`.`id` AS `id`,`a`.`orderNo` AS `orderNo`,`b`.`createTime` AS `createTime`,`a`.`flowStatus` AS `flowStatus`,`a`.`mobile` AS `mobile`,`a`.`userId` AS `userId`,`a`.`userName` AS `userName`,`a`.`recognitionFlag` AS `recognitionFlag`,`a`.`recognitionCondition` AS `recognitionCondition`,`a`.`domainName` AS `domainName`,`a`.`appointType` AS `appointType`,`a`.`productType` AS `productType`,`a`.`userSource` AS `userSource`,`a`.`appointTime` AS `appointTime`,`a`.`productCategory` AS `productCategory`,`a`.`rightStatus` AS `rightStatus`,`b`.`buyMore` AS `buyMore`,`b`.`createTime` AS `orderStartTime`,`a`.`oldfornewdbId` AS `oldfornewdbId`,`a`.`industry` AS `industry`,NULL AS `buyIntention`,NULL AS `yearBrand`,`a`.`hmcid` AS `hmcid`,`b`.`shopId` AS `shopId`,`b`.`mayEditCoupleOrderId` AS `mayEditCoupleOrderId`,`b`.`coupleSponsor` AS `coupleSponsor` from (`vipcode`.`code_workflow` `a` join `vipcode`.`code_portalorder` `b`) where `a`.`id` = `b`.`workFlowId` and `b`.`status` = 1 and `b`.`buyMore` = 1 and `a`.`isDelete` = 0 group by `a`.`id`) `t` order by `t`.`createTime` desc
1
2
TABLE_SCHEMA: vipcode
TABLE_NAME: listworkflow

会员中心

源信息 目标信息
实例 10.159.36.193 prd-paas-mysql-14
123.103.10.20 rm-2zeb87aj0804q44fi
端口 3307 3306
账号 sre / xx rds / xx
迁移对象 dbvipcenter
数据量 357GB

Functions：fn_getSplitSum

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE FUNCTION `dbvipcenter`.`fn_getSplitSum`(f_string varchar(100)) RETURNS int(11)
BEGIN
    DECLARE i int(11);
    DECLARE total int(11);
    DECLARE result int(11);
    DECLARE resulttemp text;
	  SET i=1;
		SET result = 0;
    set total = 1+(LENGTH(f_string)-LENGTH(replace(f_string,',','')));

    IF(f_string is NULL or LENGTH(f_string)=0) THEN
        RETURN 0;
    ELSE
        WHILE i<=total DO
                set resulttemp = reverse(substring_index(reverse(substring_index(f_string,',',i)),',',1));
				set result = result + CAST(resulttemp as int);
                set i = i+1;
        END WHILE;
    END IF;
    RETURN result;
END

Views：vip_order_right_info

源信息 目标信息
实例 10.159.36.192 prd-paas-mysql-16
123.103.113.99 rm-2zehekw2nfux23a7b
端口 3306 3306
账号 sre / xx rds / xx
迁移对象 dbvipcentertask
数据量 128GB
源信息 目标信息
实例 10.159.36.194 prd-paas-mysql-16
123.103.113.102 rm-2zehekw2nfux23a7b
端口 3308 3306
账号 sre / xx rds / xx
迁移对象 dbvipcenterdata
数据量 54GB 
1
2
3
4
5
报错：Specified key was too long; max key length is 767 bytes
措施：启用 innodb_large_prefix

show variables like 'binlog_format'; 
show variables like 'binlog_row_image';
1
2
3
4
5
6
SELECT table_schema, CONCAT(
TRUNCATE(SUM(data_length)/1024/1024/1024,2),'GB') AS data_size, CONCAT(
TRUNCATE(SUM(index_length)/1024/1024/1024,2),'GB') AS index_size
FROM information_schema.tables
GROUP BY table_schema
ORDER BY SUM(data_length) DESC;