公有云的 com 环境的数据迁移至 公有云的 csp 环境(程序版本不一致，且不允许升级至同一个版本)的实现流程
1、现阶段，公有云中，存在 2 个环境。分别为 com 环境 与 csp 环境。com 环境的容器，如图1
图1
2、csp 环境的容器。在 csp 环境中，已经将 com 环境的 2 个容器合并为一个容器。且新增了一个新的容器：stat。如图2
图2
3、现在的需求是，csp 环境中的数据仅保留一小部分，然后将 com 环境的数据部分迁移至 csp 环境中。最大的难点在于 com 环境的程序版本与 csp 环境不一致。csp 环境的程序版本更新。查看两个版本间的数据库迁移文件的 Git 日志。大约间隔了1年半的时间。如图3
图3
4、查看数据库迁移表中的记录数，com 环境的迁移记录数为 123 条，csp 环境的迁移记录数为 137 条。可以简单地理解为多执行了 14 条 SQL 语句。如图4
图4
5、最为理想的迁移步骤，应该是先保证两个环境的数据库结构一致，这样的话，编写一个命令行脚本文件，同时连接 2 个数据库，将 com 环境的数据全部迁移至 csp 环境中的。
6、现在不能够做到两个环境的数据库结构一致。那么只能够逐一分析多出的 14 个迁移文件的 SQL 内容。如图5
图5
7、查看 第 1 个迁移文件，主要是修改表的结构，或者调整字段的类型与默认值，或者添加新的字段。其他迁移文件大体类似。
<?php
use yii\db\Migration;
/**
* Class m200409_063336_plan_tonglian
*/
class m200409_063336_plan_tonglian extends Migration
{
/**
* {@inheritdoc}
*/
public function safeUp()
{
$this->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、将 com 环境的数据部分迁移至 csp 环境中，一般是指将某个租户下的数据全部迁移。因此，需要迁移脚本支持参数：租户ID。
9、将 com 环境的数据库与 csp 环境的数据库皆导入至本地环境。分别对应数据库：webtv_pcs_api_migrate、webtv_pcs_api。如图6
图6
10、尝试先迁移第一张表：pa_plan。查看表中的数据。肉眼可见表结构已经发生了很大的变化。具体的变化情况通过查看 14 个迁移文件具体分析。如图7
图7
11、编辑迁移脚本命令，/console/controllers/Migrate20211110Controller.php 。当顺序地执行多个相关的语句时， 你或许需要将它们包在一个事务中来保证数据库的完整性和一致性。 如果这些语句中的任何一个失败了， 数据库将回滚到这些语句执行前的状态。主键 ID 也需要迁移，因为一般来说，在迁移之前，是会清空 csp 环境中的所有数据的。即使保留，也仅会保留一少部分。只能够寄希望于主键 ID 不会冲突。打印 $comPlans
<?php
/**
* Created by PhpStorm.
* User: Qiang Wang
* Date: 2021/11/10
* Time: 10:58
*/
namespace console\controllers;
use Yii;
use yii\console\Controller;
use yii\console\ExitCode;
use yii\db\Connection;
use yii\db\Exception;
use yii\helpers\ArrayHelper;
/**
* 数据库迁移(20211110)(迁移 com 环境的数据至 csp 环境)
*
* @author Qiang Wang <shuijingwanwq@163.com>
* @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] => <p>我来了 2019</p>
[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] => <p>新建选题</p>
[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、删除 webtv_pcs_api.plan 表中的所有数据。执行迁移命令。如图8
图8
PS E:\wwwroot\pcs-api-develop> ./yii migrate20211110/com-to-csp ef8992cc47f80c66fd79063b7a449ab6 PS E:\wwwroot\pcs-api-develop>
13、查看 webtv_pcs_api.plan 表中的数据，迁移成功，符合预期。如图9
图9
14、最终实现的迁移文件如下
<?php
/**
* Created by PhpStorm.
* User: Qiang Wang
* Date: 2021/11/10
* Time: 10:58
*/
namespace console\controllers;
use Yii;
use yii\console\Controller;
use yii\console\ExitCode;
use yii\db\Connection;
use yii\db\Exception;
/**
* 数据库迁移(20211110)(迁移 com 环境的数据至 csp 环境)
*
* @author Qiang Wang <shuijingwanwq@163.com>
* @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、执行数据迁移命令，连续执行了 4 个租户的数据迁移。如图9
图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、查看 csp 环境的数据库，数据迁移成功。如图10
图10
17、之前在制定数据库设计规范时，就要求每个字段必须有默认值，除非是像 text 这种类型的字段，无法设置默认值。至于后续迁移过程中，如果发生主键ID冲突的情况。可能需要将这个租户下的数据的主键ID统一增加某个值了。然后就涉及至关联表中的关联主键ID同步增加的问题。
近期评论