从数据库 X 迁移到数据库 Y - 7.3

Talend安装指南

Version
7.3
Language
中文(简体)
Operating system
Linux
Subscription type
订阅
Product
Talend Big Data
Talend Big Data Platform
Talend Data Fabric
Talend Data Integration
Talend Data Management Platform
Talend Data Services Platform
Talend ESB
Talend MDM Platform
Talend Real-Time Big Data Platform
Module
Talend Activity Monitoring Console
Talend Administration Center
Talend Artifact Repository
Talend CommandLine
Talend Data Preparation
Talend Data Stewardship
Talend DQ Portal
Talend ESB
Talend Identity and Access Management
Talend Installer
Talend JobServer
Talend Log Server
Talend MDM Server
Talend MDM Web UI
Talend Repository Manager
Talend Runtime
Talend SAP RFC Server
Talend Studio
Content
安装和升级
Last publication date
2022-10-30

如果想要从一个数据库迁移到另一个,例如从 H2 迁移到 MySQL,您需要使用名为 migrateDatabase 的 MetaServlet 命令。

由于源数据库会在迁移过程中被更新,必须在迁移之前对其进行备份。

MetaServlet 应用程序位于 <TomcatPath>/webapps/<TalendAdministrationCenter>/WEB-INF/classes 文件夹中。

要显示此命令 (及相关参数) 的帮助,您需要在 MetaServlet 应用程序中输入以下内容:

./MetaServletCaller.sh --tac-url=<yourApplicationURL> -h migrateDatabase

有关 MetaServlet 应用程序的更多信息,请参阅《Talend Administration Center 用户指南》。

警告: 当迁移至 PostgreSQL 或 MSSQL/SQLServer 时,数据库和 schema 名称必须与源数据库之一匹配。

在下面查看 H2 与 MySQL 数据库之间的迁移示例。

为了能够使用此命令,您首先需要将它放在单独一行中。

./MetaServletCaller.sh --tac-url http://localhost:8080/org.talend.administrator --json-params='{"actionName":"migrateDatabase","dbConfigPassword":"admin","mode":"synchronous","sourcePasswd":"tisadmin","sourceUrl":"jdbc:h2:/home/Talend/<version>/tac/apache-tomcat/webapps/org.talend.administrator/WEB-INF/database/talend_administrator","sourceUser":"tisadmin","targetPasswd":"root","targetUrl":"jdbc:mysql://localhost:3306/base","targetUser":"root"}'
警告:
  • 对源/目标数据库 URL 中的特殊字符进行编译。例如,将 & 编译成 %26,将 ; 编译成 %3B
  • *json-params 的参数值放在一对单引号中,例如:
    ./MetaServletCaller.sh --tac-url http://192.168.30.36:8080/org.talend.administrator-7.3.1/ -v --json-params='{"actionName":"migrateDatabase","skipBackup":"true","dbConfigPassword":"admin","mode":"synchronous","sourcePasswd":"root","sourceUrl":"jdbc:mysql://mysql-8:3306/tac?useSSL=false%26serverTimezone=UTC%26allowPublicKeyRetrieval=true","sourceUser":"root","targetPasswd":"Root1234!","targetUrl":"jdbc:sqlserver://mssql-2017:1433%3BdatabaseName=tac","targetUser":"sa"}'

使用案例:使用 MetaServlet 从数据库 X 迁移到数据库 Y

这些示例使用了以下约定:

Talend Administration CenterURL

http://tac.test.fr:8081/org.talend.administrator/
DB config password: admin 

MySQL

user: mysql8
password: mysqlpass
database: mysql
database server: mysql8.test.fr
jdbc:mysql://mysql8.test.fr:3306/mysql_source?useSSL=false&allowPublicKeyRetrieval=true

MSSQL2017

user: SA
password: MSSQLpass2017
database: MSSQL
database server: mssql2017.test.fr
jdbc:sqlserver://mssql2017.test.fr:1433;databaseName=MSSQL_DEST

Tomcat endorsed 文件夹

您可以将正在使用的所有 JDBC 驱动程序存储在以下文件夹中:tac/apache-tomcat/endorsed , 根据 Tomcat 的设置,还可以存储至 <TomcatPath>/webapps/org.talend.administrator/WEB-INF/lib

如果添加了驱动程序,请重新启动 Tomcat。

注: 在 JDBC 字符串中 (' (单引号) 之间) 中,必须对任何特殊字符使用转义处理。使用分号 ( ; ) 或其他特殊字符时的方式与之相似。

例如,在 Linux 上:

'jdbc:mysql://mysql8.test.fr:3306/mysql?useSSL=false&allowPublicKeyRetrieval=true'

需要将其写作:

'jdbc:mysql://mysql8.test.fr:3306/mysql?useSSL=false\&allowPublicKeyRetrieval=true'

从 MySQL 迁移到 MySQL

使用以下命令:

mysql> drop database mysql_dest;
Query OK, 12 rows affected (0.10 sec)
                
mysql> create database mysql_dest;
Query OK, 1 row affected (0.00 sec)
                
mysql> grant ALL PRIVILEGES on *.* to 'mysql8'@'%';
Query OK, 0 rows affected (0.01 sec)
# /opt/Talend/tac/apache-tomcat/webapps/org.talend.administrator/WEB-INF/classes/MetaServletCaller.sh 
--tac-url http://tac.test.fr:8081/org.talend.administrator/ -v 
--json-params='{"actionName":"migrateDatabase","skipBackup":"true","dbConfigPassword":"admin","mode":"synchronous","sourcePasswd":"mysqlpass","sourceUrl":"jdbc:mysql://mysql8.test.fr:3306/mysql?useSSL=false\&allowPublicKeyRetrieval=true","sourceUser":"mysql8","targetPasswd":"mysqlpass","targetUrl":"jdbc:mysql://mysql8.test.fr:3306/mysql_dest?useSSL=false\&allowPublicKeyRetrieval=true","targetUser":"mysql8"}'
                
                
-> URL: http://tac.test.fr:8081/org.talend.administrator/
-> Json parameters:
                
{
"actionName": "migrateDatabase",
"dbConfigPassword": "admin",
"mode": "synchronous",
"skipBackup": "true",
"sourcePasswd": "mysqlpass",
"sourceUrl": "jdbc:mysql://mysql8.test.fr:3306/mysql?useSSL=false&allowPublicKeyRetrieval=true",
"sourceUser": "mysql8",
"targetPasswd": "mysqlpass",
"targetUrl": "jdbc:mysql://mysql8.test.fr:3306/mysql_dest?useSSL=false&allowPublicKeyRetrieval=true",
"targetUser": "mysql8"
}
-> Complete request: http://tac.test.fr:8081/org.talend.administrator//metaServlet?eyJhY3Rpb25OYW1lIjoibWlncmF0ZURhdGF...
{"executionTime":{"millis":20052,"seconds":20},"returnCode":0}
          

从 MySQL 迁移到 MSSQL

如果您正在迁移到 MSSQL/SQLServer,源数据库和目标数据集名称必须为 dbo。dbo 源数据库需要是主动 Talend Administration Center 数据库。

使用以下命令创建目标数据库和 schema:

$ /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P MSSQLpass2017
1> CREATE DATABASE dbo;
2> go   
# /opt/Talend/tac/apache-tomcat/webapps/org.talend.administrator/WEB-INF/classes/MetaServletCaller.sh 
--tac-url http://tac.test.fr:8081/org.talend.administrator/ -v 
--json-params='{"actionName":"migrateDatabase","skipBackup":"true","dbConfigPassword":"admin","mode":"synchronous","sourcePasswd":"mysqlpass","sourceUrl":"jdbc:mysql://mysql8.test.fr:3306/dbo?useSSL=false\&allowPublicKeyRetrieval=true","sourceUser":"mysql8","targetPasswd":"MSSQLpass2017","targetUrl":"jdbc:jtds:sqlserver://mssql2017.test.fr:1433/dbo","targetUser":"SA"}'
            
            
-> URL: http://tac.test.fr:8081/org.talend.administrator/
-> Json parameters:
{
"actionName": "migrateDatabase",
"dbConfigPassword": "admin",
"mode": "synchronous",
"skipBackup": "true",
"sourcePasswd": "mysqlpass",
"sourceUrl": "jdbc:mysql://mysql8.test.fr:3306/dbo?useSSL=false&allowPublicKeyRetrieval=true",
"sourceUser": "mysql8",
"targetPasswd": "MSSQLpass2017",
"targetUrl": "jdbc:jtds:sqlserver://mssql2017.test.fr:1433/dbo",
"targetUser": "SA"
}
-> Complete request: http://tac.test.fr:8081/org.talend.administrator//metaServlet?eyJhY3Rpb25OYW1lIjoibWlncmF0ZURhdGF...
{"executionTime":{"millis":20062,"seconds":20},"returnCode":0}
        

从 MSSQL 迁移到 MySQL

使用以下命令:

# /opt/Talend/tac/apache-tomcat/webapps/org.talend.administrator/WEB-INF/classes/MetaServletCaller.sh 
--tac-url http://tac.test.fr:8081/org.talend.administrator/ -v 
--json-params='{"actionName":"migrateDatabase","skipBackup":"true","dbConfigPassword":"admin","mode":"synchronous","sourcePasswd":"MSSQLpass2017","sourceUrl":"jdbc:jtds:sqlserver://mssql2017.test.fr:1433/mssql_test","sourceUser":"SA","targetPasswd":"mysqlpass","targetUrl":"jdbc:mysql://mysql8.test.fr:3306/mysql_dest?useSSL=false\&allowPublicKeyRetrieval=true","targetUser":"mysql8"}'
                
                
-> URL: http://tac.test.fr:8081/org.talend.administrator/
-> Json parameters:
{
"actionName": "migrateDatabase",
"dbConfigPassword": "admin",
"mode": "synchronous",
"skipBackup": "true",
"sourcePasswd": "MSSQLpass2017",
"sourceUrl": "jdbc:jtds:sqlserver://mssql2017.test.fr:1433/mssql_test",
"sourceUser": "SA",
"targetPasswd": "mysqlpass",
"targetUrl": "jdbc:mysql://mysql8.test.fr:3306/mysql_dest?useSSL=false&allowPublicKeyRetrieval=true",
"targetUser": "mysql8"
}
-> Complete request: http://tac.test.fr:8081/org.talend.administrator//metaServlet?eyJhY3Rpb25OYW1lIjoibWlncmF0ZURhdGF...
{"executionTime":{"millis":28108,"seconds":28},"returnCode":0}