The data of the COM environment of the public cloud is migrated to the CSP environment of the public cloud (the program version is inconsistent, and the upgrade to the same version is not allowed)
1. At this stage, there are two environments in the public cloud. They are COM environments and CSP environments, respectively. COM environment container, as shown in Figure 1
Figure 1
2. Containers in the CSP environment. In the CSP environment, the 2 containers of the COM environment have been merged into one container. And a new container has been added: stat. as shown in Figure 2
Figure 2
3. The current requirement is that the data in the CSP environment is only a small part, and then the data part of the COM environment is migrated to the CSP environment. The biggest difficulty is that the program version of the COM environment is inconsistent with the CSP environment. The program version update of the CSP environment. View the git logs of the database migration files between the two versions. About a year and a half. as shown in Figure 3
Figure 3
4. Check the number of records in the database migration table. The number of migration records in the COM environment is 123, and the number of migration records in the CSP environment is 137. It can be simply understood as 14 more SQL statements are executed. as shown in Figure 4
Figure 4
5. The most ideal migration step should be to ensure that the database structure of the two environments is consistent. In this case, write a command-line script file, connect 2 databases at the same time, and migrate all the data of the COM environment to the CSP environment.
6. Now it is impossible to achieve the same database structure of the two environments. Then you can only analyze the SQL content of the extra 14 migration files one by one. as shown in Figure 5
Figure 5
7. View the first migration file, mainly to modify the structure of the table, or adjust the type and default value of the field, or add a new field. Other migration files are generally similar.
alterColumn('{{%plan}}', 'config_column_id', $this->integer()->notNull()->defaultValue(0)->comment('栏目ID'));
$this->addColumn('{{%plan}}', 'is_send_down', $this->smallInteger()->notNull()->defaultValue(0)->comment('是否下发，0：否；1：是')->after('is_united'));
$this->addColumn('{{%plan}}', 'send_down_accepted_group_id', $this->string(32)->notNull()->defaultValue('')->comment('下发的接受租户ID')->after('is_send_down'));
$this->addColumn('{{%plan}}', 'is_report_up', $this->smallInteger()->notNull()->defaultValue(0)->comment('是否上报，0：否；1：是')->after('send_down_accepted_group_id'));
$this->addColumn('{{%plan}}', 'report_up_accepted_group_id', $this->string(32)->notNull()->defaultValue('')->comment('上报的接受租户ID')->after('is_report_up'));
$this->alterColumn('{{%plan}}', 'prev_status', $this->smallInteger()->notNull()->defaultValue(1)->comment('上一状态，0：禁用；1：编辑；2：待审；3：通过 ( 通过审核 / 已接受下发 ) ；4：拒绝；5：指派；6：完成；7：关闭；8：待接受下发'));
$this->alterColumn('{{%plan}}', 'status', $this->smallInteger()->notNull()->defaultValue(1)->comment('状态，0：禁用；1：编辑；2：待审；3：通过 ( 通过审核 / 已接受下发 ) ；4：拒绝；5：指派；6：完成；7：关闭；8：待接受下发'));
$this->alterColumn('{{%plan_group_relation}}', 'config_column_id', $this->integer()->notNull()->defaultValue(0)->comment('栏目ID'));
$this->addColumn('{{%plan_group_relation}}', 'type', $this->smallInteger()->notNull()->defaultValue(1)->comment('类型，1：联合；2：跟进；3：联合与跟进')->after('accepted_user_id'));
}
/**
* {@inheritdoc}
*/
public function safeDown()
{
echo "m200409_063336_plan_tonglian cannot be reverted.\n";
return false;
}
/*
// Use up()/down() to run migration code without a transaction.
public function up()
{
}
public function down()
{
echo "m200409_063336_plan_tonglian cannot be reverted.\n";
return false;
}
*/
}
8. Migrate the data part of the COM environment to the CSP environment, which generally refers to the migration of all the data under a certain tenant. Therefore, the migration script support parameter is required: tenant ID.
9. Import the databases of the COM environment and the databases of the CSP environment to the local environment. Corresponding to the database: WEBTV_PCS_API_MIGRATE, WEBTV_PCS_API. as shown in Figure 6
Figure 6
10. Try to migrate the first table: PA_PLAN. View the data in the table. It is obvious that the structure of the table has undergone great changes. The specific changes are analyzed by looking at the 14 migration files. as shown in Figure 7
Figure 7
11. Edit the migration script command, /console/controllers/migrate20211110controller.php . When multiple related statements are executed sequentially, you may need to wrap them in one transaction to ensure the integrity and consistency of the database. If any of these statements fails, the database will roll back to the state before these statements are executed. The primary key ID also needs to be migrated, because generally speaking, all data in the CSP environment will be cleared before the migration. Even if it is reserved, only a small part will be retained. Only hope that the primary key ID does not conflict. print $complans
* @since 1.0
*/
class Migrate20211110Controller extends Controller
{
/**
* 将数据从 com (Tag：pcs_api_main_1.10.8_base_sp2) 的数据库中迁移至 csp (Tag：pcs_api_1.16.0.1) 的数据库中
*
* @param string $groupId 租户ID
* @return int
* @throws Exception
*/
public function actionComToCsp($groupId)
{
// 创建一个单独的非缓存链接到数据库(com)
$comDb = new Connection([
'dsn' => 'mysql:host=localhost;dbname=webtv_pcs_api_migrate',
'username' => 'mysql',
'password' => 'hqy-webtv',
'tablePrefix' => 'pa_',
'charset' => 'utf8mb4',
]);
$comDb->open();
// 设置 PDO 连接属性 PDO::MYSQL_ATTR_USE_BUFFERED_QUERY 为 false
$comDb->pdo->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
// 创建一个单独的非缓存链接到数据库(csp)
$cspDb = new Connection([
'dsn' => 'mysql:host=localhost;dbname=webtv_pcs_api',
'username' => 'mysql',
'password' => 'hqy-webtv',
'tablePrefix' => 'pa_',
'charset' => 'utf8mb4',
]);
$cspDb->open();
// 设置 PDO 连接属性 PDO::MYSQL_ATTR_USE_BUFFERED_QUERY 为 false
$cspDb->pdo->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
// 选题
$comPlans = $comDb->createCommand('SELECT * FROM {{%plan}} WHERE group_id=:group_id')->bindValue(':group_id', $groupId)->queryAll();
$transaction = $cspDb->beginTransaction();
try {
$cspDb->createCommand()->batchInsert('{{%plan}}', ['id', 'group_id', 'title', 'config_column_id', 'occur_at', 'place', 'create_user_id', 'create_name', 'content', 'ended_at', 'importance', 'emergency_is_open', 'emergency', 'is_auto_task_create', 'keyword', 'opinion', 'material_asset_id', 'prev_status', 'is_united', 'status', 'is_not_isolated', 'is_deleted', 'created_at', 'updated_at', 'deleted_at'], $comPlans)->execute();
// ... executing other SQL statements ...
$transaction->commit();
} catch(\Throwable $e) {
$transaction->rollBack();
throw $e;
}
$comDb->close();
$cspDb->close();
return ExitCode::OK;
}
}
Array
(
[0] => Array
(
[id] => 1
[group_id] => ef8992cc47f80c66fd79063b7a449ab6
[title] => 测试
[config_column_id] => 1
[occur_at] => 1555398969
[place] =>
[create_user_id] => 37842
[create_name] => changjiang
[content] => 我来了 2019
[ended_at] => 1555485373
[importance] => 2
[emergency_is_open] => 0
[emergency] => 3
[is_auto_task_create] => 0
[keyword] =>
[opinion] =>
[material_asset_id] =>
[prev_status] => 2
[is_united] => 0
[status] => 5
[is_not_isolated] => 0
[is_deleted] => 0
[created_at] => 1555398985
[updated_at] => 1555399225
[deleted_at] => 0
)
[1] => Array
(
[id] => 2
[group_id] => ef8992cc47f80c66fd79063b7a449ab6
[title] => 新建选题
[config_column_id] => 1
[occur_at] => 1555426839
[place] =>
[create_user_id] => 37842
[create_name] => changjiang
[content] => 新建选题
[ended_at] => 1555513241
[importance] => 2
[emergency_is_open] => 0
[emergency] => 3
[is_auto_task_create] => 0
[keyword] =>
[opinion] =>
[material_asset_id] =>
[prev_status] => 0
[is_united] => 0
[status] => 2
[is_not_isolated] => 0
[is_deleted] => 0
[created_at] => 1555426860
[updated_at] => 1555426863
[deleted_at] => 0
)
)
12. Delete all the data in the WEBTV_PCS_API.PLAN table. Execute the migration command. as shown in Figure 8
8
PS E:\wwwroot\pcs-api-develop> ./yii migrate20211110/com-to-csp ef8992cc47f80c66fd79063b7a449ab6
PS E:\wwwroot\pcs-api-develop>
13. View the data in the webTV_PCS_API.PLAN table, the migration is successful, in line with expectations. as shown in Figure 9
Figure 9
14. The final migration file is as follows
* @since 1.0
*/
class Migrate20211110Controller extends Controller
{
/**
* 将数据从 com (Tag：pcs_api_main_1.10.8_base_sp2) 的数据库中迁移至 csp (Tag：pcs_api_1.16.0.1) 的数据库中
*
* @param string $groupId 租户ID
* @return int
* @throws Exception
*/
public function actionComToCsp($groupId)
{
// 创建一个单独的非缓存链接到数据库(com)
$comDb = new Connection([
'dsn' => 'mysql:host=localhost;dbname=webtv_pcs_api_migrate',
'username' => 'mysql',
'password' => 'hqy-webtv',
'tablePrefix' => 'pa_',
'charset' => 'utf8mb4',
]);
$comDb->open();
// 设置 PDO 连接属性 PDO::MYSQL_ATTR_USE_BUFFERED_QUERY 为 false
$comDb->pdo->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
// 创建一个单独的非缓存链接到数据库(csp)
$cspDb = new Connection([
'dsn' => 'mysql:host=localhost;dbname=webtv_pcs_api',
'username' => 'mysql',
'password' => 'hqy-webtv',
'tablePrefix' => 'pa_',
'charset' => 'utf8mb4',
]);
$cspDb->open();
// 设置 PDO 连接属性 PDO::MYSQL_ATTR_USE_BUFFERED_QUERY 为 false
$cspDb->pdo->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
$comAssets = $comDb->createCommand('SELECT * FROM {{%asset}} WHERE group_id=:group_id')->bindValue(':group_id', $groupId)->queryAll();
$comConfigColumns = $comDb->createCommand('SELECT * FROM {{%config_column}} WHERE group_id=:group_id')->bindValue(':group_id', $groupId)->queryAll();
$comConfigColumnUsers = $comDb->createCommand('SELECT * FROM {{%config_column_user}} WHERE group_id=:group_id')->bindValue(':group_id', $groupId)->queryAll();
$comConfigGroups = $comDb->createCommand('SELECT * FROM {{%config_group}} WHERE group_id=:group_id')->bindValue(':group_id', $groupId)->queryAll();
$comConfigTasks = $comDb->createCommand('SELECT * FROM {{%config_task}} WHERE group_id=:group_id')->bindValue(':group_id', $groupId)->queryAll();
$comConfigTaskSteps = $comDb->createCommand('SELECT * FROM {{%config_task_step}} WHERE group_id=:group_id')->bindValue(':group_id', $groupId)->queryAll();
$comConfigUsers = $comDb->createCommand('SELECT * FROM {{%config_user}} WHERE group_id=:group_id')->bindValue(':group_id', $groupId)->queryAll();
$comPlans = $comDb->createCommand('SELECT * FROM {{%plan}} WHERE group_id=:group_id')->bindValue(':group_id', $groupId)->queryAll();
$comPlanAttendedUserRelations = $comDb->createCommand('SELECT * FROM {{%plan_attended_user_relation}} WHERE group_id=:group_id')->bindValue(':group_id', $groupId)->queryAll();
$comPlanLogs = $comDb->createCommand('SELECT * FROM {{%plan_log}} WHERE group_id=:group_id')->bindValue(':group_id', $groupId)->queryAll();
$comPlanReviewLogs = $comDb->createCommand('SELECT * FROM {{%plan_review_log}} WHERE group_id=:group_id')->bindValue(':group_id', $groupId)->queryAll();
$comPlanTasks = $comDb->createCommand('SELECT * FROM {{%plan_task}} WHERE group_id=:group_id')->bindValue(':group_id', $groupId)->queryAll();
$comPlanTaskAttendedUserRelations = $comDb->createCommand('SELECT * FROM {{%plan_task_attended_user_relation}} WHERE group_id=:group_id')->bindValue(':group_id', $groupId)->queryAll();
$comPlanTaskLogs = $comDb->createCommand('SELECT * FROM {{%plan_task_log}} WHERE group_id=:group_id')->bindValue(':group_id', $groupId)->queryAll();
$comPlanTaskSteps = $comDb->createCommand('SELECT * FROM {{%plan_task_step}} WHERE group_id=:group_id')->bindValue(':group_id', $groupId)->queryAll();
$comPlanTaskStepApps = $comDb->createCommand('SELECT * FROM {{%plan_task_step_app}} WHERE group_id=:group_id')->bindValue(':group_id', $groupId)->queryAll();
$comResources = $comDb->createCommand('SELECT * FROM {{%resource}} WHERE group_id=:group_id')->bindValue(':group_id', $groupId)->queryAll();
$comUserPlaces = $comDb->createCommand('SELECT * FROM {{%user_place}} WHERE group_id=:group_id')->bindValue(':group_id', $groupId)->queryAll();
$transaction = $cspDb->beginTransaction();
try {
$cspDb->createCommand()->batchInsert('{{%asset}}', ['id', 'group_id', 'type', 'mime_type', 'original_file_name', 'relative_path', 'title', 'caption', 'description', 'create_user_id', 'status', 'is_not_isolated', 'is_deleted', 'created_at', 'updated_at', 'deleted_at'], $comAssets)->execute();
$cspDb->createCommand()->batchInsert('{{%config_column}}', ['id', 'group_id', 'code', 'name', 'status', 'is_not_isolated', 'is_deleted', 'created_at', 'updated_at', 'deleted_at'], $comConfigColumns)->execute();
$cspDb->createCommand()->batchInsert('{{%config_column_user}}', ['id', 'group_id', 'user_id', 'config_column_id', 'login_name', 'user_nick', 'user_mobile', 'role_code', 'status', 'is_not_isolated', 'is_deleted', 'created_at', 'updated_at', 'deleted_at'], $comConfigColumnUsers)->execute();
$cspDb->createCommand()->batchInsert('{{%config_group}}', ['id', 'group_id', 'base_location_name', 'base_location_icon_asset_id', 'base_location_longitude', 'base_location_latitude', 'base_location_place', 'base_location_address', 'gis_name', 'gis_is_connected', 'gis_user_online_expire', 'gis_refresh', 'gis_avatar_display_type', 'gis_reporter_list_display_type', 'plan_emergency_is_open', 'sms_notification_is_open', 'status', 'is_deleted', 'created_at', 'updated_at', 'deleted_at'], $comConfigGroups)->execute();
$cspDb->createCommand()->batchInsert('{{%config_task}}', ['id', 'group_id', 'code', 'name', 'sort_order', 'category', 'is_default', 'parameter', 'status', 'is_deleted', 'created_at', 'updated_at', 'deleted_at'], $comConfigTasks)->execute();
$cspDb->createCommand()->batchInsert('{{%config_task_step}}', ['id', 'group_id', 'config_task_id', 'config_task_code', 'config_step_id', 'step_code', 'step_name', 'sort_order', 'is_default', 'up_status_type', 'status', 'is_deleted', 'created_at', 'updated_at', 'deleted_at'], $comConfigTaskSteps)->execute();
$cspDb->createCommand()->batchInsert('{{%config_user}}', ['id', 'group_id', 'user_id', 'user_nick', 'gis_avatar_own_asset_id', 'gis_avatar_own_checked_asset_id', 'gis_avatar_customize_asset_id', 'gis_avatar_customize_checked_asset_id', 'is_device', 'position_is_open', 'place', 'longitude', 'latitude', 'status', 'is_not_isolated', 'is_deleted', 'created_at', 'updated_at', 'deleted_at'], $comConfigUsers)->execute();
$cspDb->createCommand()->batchInsert('{{%plan}}', ['id', 'group_id', 'title', 'config_column_id', 'occur_at', 'place', 'create_user_id', 'create_name', 'content', 'ended_at', 'importance', 'emergency_is_open', 'emergency', 'is_auto_task_create', 'keyword', 'opinion', 'material_asset_id', 'prev_status', 'is_united', 'status', 'is_not_isolated', 'is_deleted', 'created_at', 'updated_at', 'deleted_at'], $comPlans)->execute();
$cspDb->createCommand()->batchInsert('{{%plan_attended_user_relation}}', ['id', 'group_id', 'config_column_id', 'plan_id', 'relation_user_id', 'role', 'status', 'is_not_isolated', 'is_deleted', 'created_at', 'updated_at', 'deleted_at'], $comPlanAttendedUserRelations)->execute();
$cspDb->createCommand()->batchInsert('{{%plan_log}}', ['id', 'group_id', 'plan_id', 'user_id', 'category', 'action', 'opinion', 'status', 'is_not_isolated', 'is_deleted', 'created_at', 'updated_at', 'deleted_at'], $comPlanLogs)->execute();
$cspDb->createCommand()->batchInsert('{{%plan_review_log}}', ['id', 'group_id', 'plan_id', 'review_user_id', 'review_user_name', 'review_user_nick', 'review_opinion', 'status', 'is_deleted', 'created_at', 'updated_at', 'deleted_at'], $comPlanReviewLogs)->execute();
$cspDb->createCommand()->batchInsert('{{%plan_task}}', ['id', 'group_id', 'config_column_id', 'plan_id', 'sort_order', 'title', 'config_task_id', 'create_user_id', 'create_name', 'exec_user_id', 'exec_name', 'place', 'address', 'task_info', 'task_data', 'occur_at', 'ended_at', 'current_step_id', 'material_asset_id', 'is_united', 'prev_status', 'status', 'is_not_isolated', 'is_deleted', 'created_at', 'updated_at', 'deleted_at'], $comPlanTasks)->execute();
$cspDb->createCommand()->batchInsert('{{%plan_task_attended_user_relation}}', ['id', 'group_id', 'config_column_id', 'plan_id', 'plan_task_id', 'relation_user_id', 'role', 'status', 'is_not_isolated', 'is_deleted', 'created_at', 'updated_at', 'deleted_at'], $comPlanTaskAttendedUserRelations)->execute();
$cspDb->createCommand()->batchInsert('{{%plan_task_log}}', ['id', 'group_id', 'plan_task_id', 'user_id', 'category', 'action', 'opinion', 'status', 'is_not_isolated', 'is_deleted', 'created_at', 'updated_at', 'deleted_at'], $comPlanTaskLogs)->execute();
$cspDb->createCommand()->batchInsert('{{%plan_task_step}}', ['id', 'group_id', 'task_id', 'task_title', 'step_code', 'step_name', 'sort_order', 'updated_name', 'status', 'is_deleted', 'created_at', 'updated_at', 'deleted_at'], $comPlanTaskSteps)->execute();
$cspDb->createCommand()->batchInsert('{{%plan_task_step_app}}', ['id', 'group_id', 'plan_task_step_id', 'step_code', 'step_name', 'app_name', 'status', 'is_deleted', 'created_at', 'updated_at', 'deleted_at'], $comPlanTaskStepApps)->execute();
$cspDb->createCommand()->batchInsert('{{%resource}}', ['id', 'group_id', 'resource_id', 'task_id', 'task_step_id', 'title', 'resource_cover', 'resource_url', 'resource_data', 'media_code', 'source', 'status', 'is_deleted', 'created_at', 'updated_at', 'deleted_at'], $comResources)->execute();
$cspDb->createCommand()->batchInsert('{{%user_place}}', ['id', 'group_id', 'task_id', 'task_step_id', 'source', 'create_user_id', 'create_name', 'longitude', 'latitude', 'place', 'status', 'is_deleted', 'created_at', 'updated_at', 'deleted_at'], $comUserPlaces)->execute();
$transaction->commit();
} catch(\Throwable $e) {
$transaction->rollBack();
throw $e;
}
$comDb->close();
$cspDb->close();
return ExitCode::OK;
}
}
15. Execute the data migration command to execute the data migration of 4 tenants in a row. as shown in Figure 9
Figure 9
PS E:\wwwroot\pcs-api-develop> ./yii migrate20211110/com-to-csp ef8992cc47f80c66fd79063b7a449ab6
PS E:\wwwroot\pcs-api-develop> ./yii migrate20211110/com-to-csp 81e490953501bc5b57b3ac875ae0b9b5
PS E:\wwwroot\pcs-api-develop> ./yii migrate20211110/com-to-csp bc8231038e867f47948074c202e1dc63
PS E:\wwwroot\pcs-api-develop> ./yii migrate20211110/com-to-csp 5cab1bb59ab0cbae48d9be6878e91cc1
PS E:\wwwroot\pcs-api-develop>
16. Check the database of the CSP environment, the data migration is successful. As shown in Figure 10
Figure 10
17. When formulating the database design specification, it is required that each field must have a default value, unless it is a field like text, the default value cannot be set. As for the subsequent migration process, if the primary key ID conflict occurs. It may be necessary to uniformly increase the primary key ID of the data under this tenant to a certain value. Then there is a problem that the associated primary key ID is synchronized to increase in the association table.
