Apollo 配置中心 1.0 到 2.0 升级之表数据 ETL

用户1148526

发布于 2023-10-14 09:56:01

1680

发布于 2023-10-14 09:56:01

0. 需求

将已有的 Apollo 1.0 表数据导入 Apollo 2.0 表。目标库已经创建了 Apollo 2.0 的库表结构。2.0 版本比 1.0 版本多四个表： ApolloConfigDB.AccessKey ApolloConfigDB.ServiceRegistry ApolloPortalDB.SPRING_SESSION ApolloPortalDB.SPRING_SESSION_ATTRIBUTES

1. 导出 1.0 版本数据

只导出不带已经逻辑删除的数据。新版本部分表增加了删除时间戳字段，并在该字段上设置了联合其它字段的唯一索引。只导出 IsDeleted=0 的数据，防止插入新版本库时报违反唯一索引约束的错误。

# 导出 apolloconfigdb 库中所有包含 IsDeleted = 0 的表数据
mysqldump -uroot -p123456 -S /data/18251/mysqldata/mysql.sock --set-gtid-purged=off --no-create-db --no-create-info --skip-opt --complete-insert --quick --extended-insert apolloconfigdb app appnamespace audit cluster commit grayreleaserule item namespace namespacelock release releasehistory serverconfig --where="IsDeleted=0" > apolloconfigdb_IsDeleted_0.sql

# 导出 apolloconfigdb 库中不包含 IsDeleted 字段的表数据
mysqldump -uroot -p123456 -S /data/18251/mysqldata/mysql.sock --set-gtid-purged=off --no-create-db --no-create-info --skip-opt --complete-insert --quick --extended-insert apolloconfigdb instance instanceconfig releasemessage > apolloconfigdb_no_IsDeleted.sql

# 导出 apolloportaldb 库中所有包含 IsDeleted = 0 的表数据
mysqldump -uroot -p123456 -S /data/18251/mysqldata/mysql.sock --set-gtid-purged=off --no-create-db --no-create-info --skip-opt --complete-insert --quick --extended-insert apolloportaldb app appnamespace consumer consumerrole consumertoken favorite permission role rolepermission serverconfig userrole --where="IsDeleted=0" > apolloportaldb_IsDeleted_0.sql

# 导出 apolloportaldb 库中不包含 IsDeleted 字段的表数据
mysqldump -uroot -p123456 -S /data/18251/mysqldata/mysql.sock --set-gtid-purged=off --no-create-db --no-create-info --skip-opt --complete-insert --quick --extended-insert apolloportaldb authorities consumeraudit users > apolloportaldb_no_IsDeleted.sql

2. 转换大小写

现实中 1.0 的库表名称可能都为小写，而 2.0 的库表名称可能区分大小写。

# 库名
sed -i 's/USE `apolloconfigdb`/USE `ApolloConfigDB`/g;' apolloconfigdb_IsDeleted_0.sql
sed -i 's/USE `apolloconfigdb`/USE `ApolloConfigDB`/g;' apolloconfigdb_no_IsDeleted.sql

sed -i 's/USE `apolloportaldb`/USE `ApolloPortalDB`/g;' apolloportaldb_IsDeleted_0.sql
sed -i 's/USE `apolloportaldb`/USE `ApolloPortalDB`/g;' apolloportaldb_no_IsDeleted.sql

# 表名
regexp='s/INSERT INTO `app`/INSERT INTO `App`/g;'
regexp=$regexp's/INSERT INTO `appnamespace`/INSERT INTO `AppNamespace`/g;'
regexp=$regexp's/INSERT INTO `audit`/INSERT INTO `Audit`/g;'
regexp=$regexp's/INSERT INTO `cluster`/INSERT INTO `Cluster`/g;'
regexp=$regexp's/INSERT INTO `commit`/INSERT INTO `Commit`/g;'
regexp=$regexp's/INSERT INTO `grayreleaserule`/INSERT INTO `GrayReleaseRule`/g;'
regexp=$regexp's/INSERT INTO `item`/INSERT INTO `Item`/g;'
regexp=$regexp's/INSERT INTO `namespace`/INSERT INTO `Namespace`/g;'
regexp=$regexp's/INSERT INTO `namespacelock`/INSERT INTO `NamespaceLock`/g;'
regexp=$regexp's/INSERT INTO `release`/INSERT INTO `Release`/g;'
regexp=$regexp's/INSERT INTO `releasehistory`/INSERT INTO `ReleaseHistory`/g;'
regexp=$regexp's/INSERT INTO `serverconfig`/INSERT INTO `ServerConfig`/g;'

sed -i "${regexp}" apolloconfigdb_IsDeleted_0.sql

regexp='s/INSERT INTO `instance`/INSERT INTO `Instance`/g;'
regexp=$regexp's/INSERT INTO `instanceconfig`/INSERT INTO `InstanceConfig`/g;'
regexp=$regexp's/INSERT INTO `releasemessage`/INSERT INTO `ReleaseMessage`/g;'

sed -i "${regexp}" apolloconfigdb_no_IsDeleted.sql

regexp='s/INSERT INTO `app`/INSERT INTO `App`/g;'
regexp=$regexp's/INSERT INTO `appnamespace`/INSERT INTO `AppNamespace`/g;'
regexp=$regexp's/INSERT INTO `consumer`/INSERT INTO `Consumer`/g;'
regexp=$regexp's/INSERT INTO `consumerrole`/INSERT INTO `ConsumerRole`/g;'
regexp=$regexp's/INSERT INTO `consumertoken`/INSERT INTO `ConsumerToken`/g;'
regexp=$regexp's/INSERT INTO `favorite`/INSERT INTO `Favorite`/g;'
regexp=$regexp's/INSERT INTO `permission`/INSERT INTO `Permission`/g;'
regexp=$regexp's/INSERT INTO `role`/INSERT INTO `Role`/g;'
regexp=$regexp's/INSERT INTO `rolepermission`/INSERT INTO `RolePermission`/g;'
regexp=$regexp's/INSERT INTO `serverconfig`/INSERT INTO `ServerConfig`/g;'
regexp=$regexp's/INSERT INTO `userrole`/INSERT INTO `UserRole`/g;'

sed -i "${regexp}" apolloportaldb_IsDeleted_0.sql

regexp='s/INSERT INTO `authorities`/INSERT INTO `Authorities`/g;'
regexp=$regexp's/INSERT INTO `consumeraudit`/INSERT INTO `ConsumerAudit`/g;'
regexp=$regexp's/INSERT INTO `users`/INSERT INTO `Users`/g;'

sed -i "${regexp}" apolloportaldb_no_IsDeleted.sql

3. 数据导入 2.0 版本库

mysql -uroot -p123456 -h127.0.0.1 -P3306 -D ApolloPortalDB < /var/lib/mysql/apolloportaldb_no_IsDeleted.sql > apolloportaldb_no_IsDeleted.log 2>&1
mysql -uroot -p123456 -h127.0.0.1 -P3306 -D ApolloConfigDB < /var/lib/mysql/apolloconfigdb_no_IsDeleted.sql > apolloconfigdb_no_IsDeleted.log 2>&1
mysql -uroot -p123456 -h127.0.0.1 -P3306 -D ApolloPortalDB < /var/lib/mysql/apolloportaldb_IsDeleted_0.sql > apolloportaldb_IsDeleted_0.log 2>&1
mysql -uroot -p123456 -h127.0.0.1 -P3306 -D ApolloConfigDB < /var/lib/mysql/apolloconfigdb_IsDeleted_0.sql > apolloconfigdb_IsDeleted_0.log 2>&1

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2023-08-01，如有侵权请联系 cloudcommunity@tencent.com 删除

apollo