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

Talend Real-Time Big Data Platform 安装与升级指南 (Windows)

EnrichVersion
7.1
EnrichProdName
Talend Real-Time Big Data Platform
task
数据治理
EnrichPlatform
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 Repository Manager
Talend Runtime
Talend SAP RFC Server
Talend Studio

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

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

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

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

MetaServletCaller.bat --tac-url=<yourApplicationURL> -h migrateDatabase

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

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

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

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

MetaServletCaller.bat --tac-url http://localhost:8080/org.talend.administrator --json-params='{"actionName":"migrateDatabase","dbConfigPassword":"admin","mode":"synchronous","sourcePasswd":"tisadmin","sourceUrl":"'jdbc:h2:C:/Talend/6.4.1/tac/apache-tomcat-8.0.20/webapps/org.talend.administrator/WEB-INF/database/talend_administrator'","sourceUser":"tisadmin","targetPasswd":"root","targetUrl":"'jdbc:mysql://localhost:3306/base'","targetUser":"root"}'
警告: sourceURLtargetURL 参数值中的引号在 Windows 上是必需的。

使用案例:使用 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:jtds:sqlserver://mssql2017.test.fr:1433/MSSQL_DEST

Tomcat endorsed 文件夹

在 Tomcat endorsed 文件夹中,您可以存储所有正在使用的 JDBC 驱动程序。如果添加了驱动程序,请重新启动 Tomcat。

# pwd
/opt/Talend/tac/apache-tomcat/endorsed
                
# ls -al
total 2588
drwxr-xr-x 2 root root 67 Dec 14 16:02 .
drwxr-xr-x 16 usertest 1001 300 Dec 14 14:11 ..
-rw-r--r-- 1 root root 317816 Jun 8 2013 jtds-1.3.1.jar
-rw-r--r-- 1 root root 2330539 Sep 8 02:09 mysql-connector-java-8.0.18.jar
注: 在 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://tac721.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:

[dbuser@mssql2017 ~]$ /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}