DolphinScheduler2.0版本升级3.0版本方案

1.升级背景

因项目需要使用数据质量模块功能，可以为数仓提供良好的数据质量监控功能。故要对已有2.0版本升级到3.0版本以上，此次选择测试了3.0.1 和 3.1.1 两个版本，对进行同数据等任务调度暂停等操作测试，最后选择3.0.1 版本

原因：

1. 3.1.1 在测试sql任务时 ，同时启动上百sql 任务时，会出现sql 任务报错，导致大量任务无法正常运行，询问社区大佬，这是DS本身bug导致，虽然此现象在3.0.1也有出现，不过出现几率较小。

2. DS3.0.1以上版本zookeeper的依赖版本进行了更新，查看驱动版本是3.8版本。我们生产不打算升级zk,故选择使用3.0.1版本。

此版本测试还是比较稳定的，功能比较完善，满足我们使用需求。

此次升级已经验证可行性，已在生产环境验证上线，对已有的问题，并给出了合理的解决方便，故写此篇文章，供各位同学参考。

2.升级的方案

选定方案：

采用数据库表同步的方式进行任务迁移，前期3.0 版本 和 2.0 版本同时运行，任务再验证没问题后，再逐步停止2.0版本。

原因：直接使用官网提供的升级脚本，无法正常运行，有较多问题，目前我们改造后，升级的数据库信息没问题，运行时数据信息有损坏，导致较多问题，所以为安全稳定，不直接使用官网提方案。

3.升级准备

1.首先对已有数据库进行备份，此项非常重要,

备份原始DS库：
 mysqldump -h ip -P 3306 -u 用户 -p 密码 数据库名  > /opt/new_dolphinscheduler.sql

恢复到新库：
　　mysql -u 用户 –p 密码 数据库名 < 备份文件.sql

2.对已有备份表进行表结构变更文章来源地址https://www.toymoban.com/news/detail-493086.html

####此脚本是官网脚本改的，添加了字段
/*
 * Licensed to the Apache Software Foundation (ASF) under one or more
 * contributor license agreements.  See the NOTICE file distributed with
 * this work for additional information regarding copyright ownership.
 * The ASF licenses this file to You under the Apache License, Version 2.0
 * (the "License"); you may not use this file except in compliance with
 * the License.  You may obtain a copy of the License at
 *
 *    http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
*/

SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

-- uc_dolphin_T_t_ds_alert_R_sign
drop PROCEDURE if EXISTS uc_dolphin_T_t_ds_alert_R_sign;
delimiter d//
CREATE PROCEDURE uc_dolphin_T_t_ds_alert_R_sign()
BEGIN
        IF NOT EXISTS (SELECT 1 FROM information_schema.COLUMNS
            WHERE TABLE_NAME='t_ds_alert'
            AND TABLE_SCHEMA=(SELECT DATABASE())
            AND COLUMN_NAME='sign')
    THEN
ALTER TABLE `t_ds_alert` ADD COLUMN `sign` char(40) NOT NULL DEFAULT '' COMMENT 'sign=sha1(content)' after `id`;
ALTER TABLE `t_ds_alert` ADD INDEX `idx_sign` (`sign`) USING BTREE;
END IF;
END;

d//

delimiter ;
CALL uc_dolphin_T_t_ds_alert_R_sign;
DROP PROCEDURE uc_dolphin_T_t_ds_alert_R_sign;

-- add unique key to t_ds_relation_project_user
drop PROCEDURE if EXISTS add_t_ds_relation_project_user_uk_uniq_uid_pid;
delimiter d//
CREATE PROCEDURE add_t_ds_relation_project_user_uk_uniq_uid_pid()
BEGIN
    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
        WHERE TABLE_NAME='t_ds_relation_project_user'
        AND TABLE_SCHEMA=(SELECT DATABASE())
        AND INDEX_NAME='uniq_uid_pid')
    THEN
ALTER TABLE t_ds_relation_project_user ADD UNIQUE KEY uniq_uid_pid(user_id, project_id);
END IF;
END;

d//

delimiter ;
# CALL add_t_ds_relation_project_user_uk_uniq_uid_pid;
DROP PROCEDURE add_t_ds_relation_project_user_uk_uniq_uid_pid;

-- drop t_ds_relation_project_user key user_id_index
drop PROCEDURE if EXISTS drop_t_ds_relation_project_user_key_user_id_index;
delimiter d//
CREATE PROCEDURE drop_t_ds_relation_project_user_key_user_id_index()
BEGIN
    IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
        WHERE TABLE_NAME='t_ds_relation_project_user'
        AND TABLE_SCHEMA=(SELECT DATABASE())
        AND INDEX_NAME='user_id_index')
    THEN
ALTER TABLE `t_ds_relation_project_user` DROP KEY `user_id_index`;
END IF;
END;
d//
delimiter ;
CALL drop_t_ds_relation_project_user_key_user_id_index;
DROP PROCEDURE drop_t_ds_relation_project_user_key_user_id_index;

-- add unique key to t_ds_project
drop PROCEDURE if EXISTS add_t_ds_project_uk_unique_name;
delimiter d//
CREATE PROCEDURE add_t_ds_project_uk_unique_name()
BEGIN
    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
        WHERE TABLE_NAME='t_ds_project'
        AND TABLE_SCHEMA=(SELECT DATABASE())
        AND INDEX_NAME='unique_name')
    THEN
ALTER TABLE t_ds_project ADD UNIQUE KEY unique_name(name);
END IF;
END;
d//
delimiter ;
CALL add_t_ds_project_uk_unique_name;
DROP PROCEDURE add_t_ds_project_uk_unique_name;

drop PROCEDURE if EXISTS add_t_ds_project_uk_unique_code;
delimiter d//
CREATE PROCEDURE add_t_ds_project_uk_unique_code()
BEGIN
    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
        WHERE TABLE_NAME='t_ds_project'
        AND TABLE_SCHEMA=(SELECT DATABASE())
        AND INDEX_NAME='unique_code')
    THEN
ALTER TABLE t_ds_project ADD UNIQUE KEY unique_code(code);
END IF;
END;
d//
delimiter ;
CALL add_t_ds_project_uk_unique_code;
DROP PROCEDURE add_t_ds_project_uk_unique_code;

-- add unique key to t_ds_queue
drop PROCEDURE if EXISTS add_t_ds_queue_uk_unique_queue_name;
delimiter d//
CREATE PROCEDURE add_t_ds_queue_uk_unique_queue_name()
BEGIN
    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
        WHERE TABLE_NAME='t_ds_queue'
        AND TABLE_SCHEMA=(SELECT DATABASE())
        AND INDEX_NAME='unique_queue_name')
    THEN
ALTER TABLE t_ds_queue ADD UNIQUE KEY unique_queue_name(queue_name);
END IF;
END;
d//
delimiter ;
CALL add_t_ds_queue_uk_unique_queue_name;
DROP PROCEDURE add_t_ds_queue_uk_unique_queue_name;

-- add unique key to t_ds_udfs
drop PROCEDURE if EXISTS add_t_ds_udfs_uk_unique_func_name;
delimiter d//
CREATE PROCEDURE add_t_ds_udfs_uk_unique_func_name()
BEGIN
    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
        WHERE TABLE_NAME='t_ds_udfs'
        AND TABLE_SCHEMA=(SELECT DATABASE())
        AND INDEX_NAME='unique_func_name')
    THEN
ALTER TABLE t_ds_udfs ADD UNIQUE KEY unique_func_name(func_name);
END IF;
END;
d//
delimiter ;
CALL add_t_ds_udfs_uk_unique_func_name;
DROP PROCEDURE add_t_ds_udfs_uk_unique_func_name;

-- add unique key to t_ds_tenant
drop PROCEDURE if EXISTS add_t_ds_tenant_uk_unique_tenant_code;
delimiter d//
CREATE PROCEDURE add_t_ds_tenant_uk_unique_tenant_code()
BEGIN
    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
        WHERE TABLE_NAME='t_ds_tenant'
        AND TABLE_SCHEMA=(SELECT DATABASE())
        AND INDEX_NAME='unique_tenant_code')
    THEN
ALTER TABLE t_ds_tenant ADD UNIQUE KEY unique_tenant_code(tenant_code);
END IF;
END;
d//
delimiter ;
CALL add_t_ds_tenant_uk_unique_tenant_code;
DROP PROCEDURE add_t_ds_tenant_uk_unique_tenant_code;

-- ALTER TABLE `t_ds_task_instance` ADD INDEX `idx_code_version` (`task_code`, `task_definition_version`) USING BTREE;
drop PROCEDURE if EXISTS add_t_ds_task_instance_uk_idx_code_version;
delimiter d//
CREATE PROCEDURE add_t_ds_task_instance_uk_idx_code_version()
BEGIN
    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
        WHERE TABLE_NAME='t_ds_task_instance'
        AND TABLE_SCHEMA=(SELECT DATABASE())
        AND INDEX_NAME='idx_code_version')
    THEN
ALTER TABLE `t_ds_task_instance` ADD INDEX `idx_code_version` (`task_code`, `task_definition_version`) USING BTREE;
END IF;
END;
d//
delimiter ;
CALL add_t_ds_task_instance_uk_idx_code_version;
DROP PROCEDURE add_t_ds_task_instance_uk_idx_code_version;

-- ALTER TABLE `t_ds_task_instance` MODIFY COLUMN `task_params` longtext COMMENT 'job custom parameters' AFTER `app_link`;
drop PROCEDURE if EXISTS modify_t_ds_task_instance_col_task_params;
delimiter d//
CREATE PROCEDURE modify_t_ds_task_instance_col_task_params()
BEGIN
    IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME='t_ds_task_instance'
        AND TABLE_SCHEMA=(SELECT DATABASE())
        AND COLUMN_NAME ='task_params')
    THEN
ALTER TABLE `t_ds_task_instance` MODIFY COLUMN `task_params` longtext COMMENT 'job custom parameters' AFTER `app_link`;
END IF;
END;
d//
delimiter ;
CALL modify_t_ds_task_instance_col_task_params;
DROP PROCEDURE modify_t_ds_task_instance_col_task_params;

-- ALTER TABLE `t_ds_task_instance` ADD COLUMN `task_group_id` int(11) DEFAULT NULL COMMENT 'task group id';
drop PROCEDURE if EXISTS add_t_ds_task_instance_col_task_group_id;
delimiter d//
CREATE PROCEDURE add_t_ds_task_instance_col_task_group_id()
BEGIN
    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME='t_ds_task_instance'
        AND TABLE_SCHEMA=(SELECT DATABASE())
        AND COLUMN_NAME ='task_group_id')
    THEN
ALTER TABLE `t_ds_task_instance` ADD COLUMN `task_group_id` int(11) DEFAULT NULL COMMENT 'task group id' after `var_pool`;
END IF;
END;
d//
delimiter ;
CALL add_t_ds_task_instance_col_task_group_id;
DROP PROCEDURE add_t_ds_task_instance_col_task_group_id;

-- ALTER TABLE `t_ds_process_task_relation` ADD KEY `idx_code` (`project_code`, `process_definition_code`) USING BTREE;
drop PROCEDURE if EXISTS add_t_ds_process_task_relation_key_idx_code;
delimiter d//
CREATE PROCEDURE add_t_ds_process_task_relation_key_idx_code()
BEGIN
    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
        WHERE TABLE_NAME='t_ds_process_task_relation'
        AND TABLE_SCHEMA=(SELECT DATABASE())
        AND INDEX_NAME='idx_code')
    THEN
ALTER TABLE `t_ds_process_task_relation` ADD KEY `idx_code` (`project_code`, `process_definition_code`) USING BTREE;
END IF;
END;
d//
delimiter ;
CALL add_t_ds_process_ta

到了这里，关于DolphinScheduler2.0版本升级3.0版本方案的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！