TAC MetaServlet database X to Y migration examples

You can use MetaServet to migrate a TAC database from one database to another. The examples in this article use the following conventions:

 

TAC URL

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

 

MySQL

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

 

MSSQL2017

user : SA
password : MSSQLpass2017
database : MSSQL_721
database server : mssql2017.test.fr
jdbc:jtds:sqlserver://mssql2017.test.fr:1433/MSSQL_721_DEST

 

Tomcat endorsed folder

Store all of the JDBC drivers you're using in this folder. You need to restart Tomcat if you add a driver.

# pwd
/opt/Talend-7.2.1/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 string and control characters

In the JDBC string (between ' (simple quote)), if there are special characters in the JDBC connection string, they must be escaped.

 

For example, on Linux:

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

needs to be written as:

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

The behavior is similar when using a semicolon ( ; ) or other special characters.

 

Migration limitation

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 TAC database.

 

MYSQL to MYSQL

mysql> drop database mysql_721_dest;
Query OK, 12 rows affected (0.10 sec)

mysql> create database mysql_721_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-7.2.1/tac/apache-tomcat/webapps/org.talend.administrator/WEB-INF/classes/MetaServletCaller.sh --tac-url http://tac721.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_721?useSSL=false\&allowPublicKeyRetrieval=true'","sourceUser":"mysql8","targetPasswd":"mysqlpass","targetUrl":"'jdbc:mysql://mysql8.test.fr:3306/mysql_721_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_721?useSSL=false&allowPublicKeyRetrieval=true",
"sourceUser": "mysql8",
"targetPasswd": "mysqlpass",
"targetUrl": "jdbc:mysql://mysql8.test.fr:3306/mysql_721_dest?useSSL=false&allowPublicKeyRetrieval=true",
"targetUser": "mysql8"
}
-> Complete request: http://tac721.test.fr:8081/org.talend.administrator//metaServlet?eyJhY3Rpb25OYW1lIjoibWlncmF0ZURhdGF...
{"executionTime":{"millis":20052,"seconds":20},"returnCode":0}

 

MYSQL to MSSQL

In this case, the migration limitations apply. That is, both databases are named dbo.

 

To create a destination db and schema:

[dbuser@mssql2017 ~]$ /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P MSSQLpass2017
1> CREATE DATABASE dbo;
2> go 

# /opt/Talend-7.2.1/tac/apache-tomcat/webapps/org.talend.administrator/WEB-INF/classes/MetaServletCaller.sh --tac-url http://tac721.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://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/dbo?useSSL=false&allowPublicKeyRetrieval=true",
"sourceUser": "mysql8",
"targetPasswd": "MSSQLpass2017",
"targetUrl": "jdbc:jtds:sqlserver://mssql2017.test.fr:1433/dbo",
"targetUser": "SA"
}
-> Complete request: http://tac721.test.fr:8081/org.talend.administrator//metaServlet?eyJhY3Rpb25OYW1lIjoibWlncmF0ZURhdGF...
{"executionTime":{"millis":20062,"seconds":20},"returnCode":0}

 

MSSQL to MYSQL

# /opt/Talend-7.2.1/tac/apache-tomcat/webapps/org.talend.administrator/WEB-INF/classes/MetaServletCaller.sh --tac-url http://tac721.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://tac721.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://tac721.test.fr:8081/org.talend.administrator//metaServlet?eyJhY3Rpb25OYW1lIjoibWlncmF0ZURhdGF...
{"executionTime":{"millis":28108,"seconds":28},"returnCode":0}
Version history
Revision #:
12 of 12
Last update:
‎02-11-2020 06:35 AM
Updated by:
 
Contributors
Comments
Community Manager

This article will be merged into the Talend Help Center per DOCT-13236.