Migrating database X to database Y - 8.0

Talend Installation Guide

Version
8.0
Language
English
Operating system
Windows
Subscription type
Subscription
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 ESB
Talend Identity and Access Management
Talend Installer
Talend JobServer
Talend LogServer
Talend MDM Server
Talend MDM Web UI
Talend Runtime
Talend SAP RFC Server
Talend Studio
Content
Installation and Upgrade
Last publication date
2022-10-30

If you want to migrate from one database to another, for example from H2 to MySQL, you need to use the MetaServlet command called migrateDatabase.

As the source database is updated during the migration process, it is mandatory to back it up before migrating it.

The MetaServlet application is located in <TomcatPath>\webapps\<TalendAdministrationCenter>\WEB-INF\classes folder.

To display the help of this command (with related parameters), you need to enter the following in the MetaServlet application:

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

For more information on the MetaServlet application, see Non-GUI operation in metaServlet.

Warning: When migrating to PostgreSQL or MSSQL/SQLServer, the database and schema name must match the one of the source database.

See below an example of migration between H2 and MySQL databases.

To be able to use this command, you need to put it on one single line first.

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\<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'}"
Warning: Simple quotes in the sourceURL and targetURL parameter values are required on Windows.

Use case: Migrating database X to database Y using MetaServlet

The examples use the following conventions:

Talend Administration Center URL

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 folder

You can store all the JDBC drivers you're using in the following folder: tac\apache-tomcat\endorsed or, depending on Tomcat, <TomcatPath>\webapps\org.talend.administrator\WEB-INF\lib.

Restart Tomcat if you add a driver.

Tip: In the JDBC string, special characters that are between the simple quotes ( ' ) must be escaped. The behavior is similar when using a semicolon ( ; ) or other special characters.

For example, on Linux:

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

It needs to be written as:

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

Migration from MySQL to MySQL

Use the following command:

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)
# Talend\tac\apache-tomcat\webapps\org.talend.administrator\WEB-INF\classes\MetaServletCaller.bat 
--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}
            

Migration from MySQL to MSSQL

If you're migrating to MSSQL/SQLServer, the source database and destination database name must be dbo. The dbo source database needs to be the active Talend Administration Center database.

Use the following command to create a destination database and schema:

\mssql-tools\bin\sqlcmd -S localhost -U SA -P MSSQLpass2017
1> CREATE DATABASE dbo;
2> go 
# Talend\tac\apache-tomcat\webapps\org.talend.administrator\WEB-INF\classes\MetaServletCaller.bat 
--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}

Migrating from MSSQL to MySQL

Use the following command:

# Talend\tac\apache-tomcat\webapps\org.talend.administrator\WEB-INF\classes\MetaServletCaller.bat 
--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}