When you migrate MySQL data to OceanBase Database, if the source and destination databases cannot connect to the DataX server at the same time, you can export the MySQL data as CSV files and then import the CSV files into the OceanBase database as described in the previous sections. If the source and destination databases can connect to the DataX server at the same time, you can use DataX to migrate the MySQL data to the OceanBase database.
Synchronize MySQL data to OceanBase Database
The following example shows the content of the configuration file:
{
"job": {
"setting": {
"speed": {
"channel": 4
},
"errorLimit": {
"record": 0,
"percentage": 0.1
}
},
"content": [
{
"reader": {
"name": "mysqlreader",
"parameter": {
"username": "tpcc",
"password": "********",
"column": [
"*"
],
"connection": [
{
"table": [
"bmsql_oorder"
],
"jdbcUrl": ["jdbc:mysql://127.0.0.1:3306/tpccdb?useUnicode=true&characterEncoding=utf8"]
}
]
}
},
"writer": {
"name": "oceanbasev10writer",
"parameter": {
"obWriteMode": "insert",
"column": [
"*"
],
"preSql": [
"truncate table bmsql_oorder"
],
"connection": [
{
"jdbcUrl": "||_dsc_ob10_dsc_||obdemo:oboracle||_dsc_ob10_dsc_||jdbc:oceanbase://127.0.0.1:2883/tpcc?useLocalSessionState=true&allowBatch=true&allowMultiQueries=true&rewriteBatchedStatements=true",
"table": [
"bmsql_oorder"
]
}
],
"username": "tpcc",
"password":"********",
"writerThreadCount":10,
"batchSize": 1000,
"memstoreThreshold": "0.9"
}
}
}
]
}
}
Handle common errors
SSL-related errors on MySQL
Symptom
Mon Dec 13 15:44:13 CST 2021 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verificationSolution
Disable SSL in jdbcurl, for example:
"jdbcUrl": ["jdbc:mysql://127.0.0.1:3306/tpccdb?useUnicode=true&characterEncoding=utf8"] # Change the preceding setting to: "jdbcUrl": ["jdbc:mysql://127.0.0.1:3306/tpccdb?useUnicode=true&characterEncoding=utf8&useSSL=false"]
Import failed due to foreign keys
When a table with a foreign key is being truncated, an error occurs. You can use delete from table instead. You can also change the settings to set foreign_key_checks='off' in the PREPARE SQL statement to avoid the error.
Transaction timed out during data import
If the error occurs, you can add the timeout parameter in the PREPARE SQL statement, for example, set ob_trx_timeout=1000000000.
oceanbasev10reader and oceanbasev10writer plug-ins cannot be found
This error is caused when the read and write plug-ins of OceanBase Database are absent from the default binary DataX package. You need to compile and install the plug-ins, create the corresponding directories, and copy the JSON files to the directories. The paths and content of the files are as follows:
target/datax/datax/plugin/reader/oceanbasev10reader/plugin_job_template.json
target/datax/datax/plugin/writer/oceanbasev10writer/plugin_job_template.json
{
"job": {
"content": [
{
"reader": {
"name": "oceanbasev10reader",
"parameter": {
"column": [
"*"
],
"connection": [
{
"jdbcUrl": [
"||_dsc_ob10_dsc_||clusterName:tenantName||_dsc_ob10_dsc_||jdbc:oceanbase://obproxyIp:port/dbname"
],
"table": [
"tabName"
]
}
],
"password": "",
"readBatchSize": 100000,
"username": "",
"weakRead": false
}
},
"writer": {
"name": "oceanbasev10writer",
"parameter": {
"column": [
"*"
],
"connection": [
{
"jdbcUrl": "||_dsc_ob10_dsc_||clusterName:tenantName||_dsc_ob10_dsc_||jdbc:oceanbase://obproxyIp:port/dbname?yearIsDateType=false&ZeroDateTimeBehavior=convertToNull&tinyIntlisBit=false&rewriteBatchedStatements=true",
"table": [
"tabName"
]
}
],
"obWriteMode": "insert",
"password": "",
"preSql": [
""
],
"username": ""
}
}
}
],
"setting": {
"speed": {
"channel": ""
}
}
}
}
Failed to load the MysqlReader and TxtFileWriter plug-ins
Solution:
Delete all folders and files that start with ._ in the plugin[reader,writer] directory.
rm -rf ._*