1、mysql导出数据且SCP到ob数据库上
mysql> show tables; +------------------------+ | Tables_in_psd_schedule | +------------------------+ | pf_td_ext_data_dict | | pf_td_simple_triggers | | pf_td_triggers | +------------------------+ 3 rows in set (0.09 sec)
mysql> select count(1) from pf_td_ext_data_dict; +----------+ | count(1) | +----------+ | 23 | +----------+ 1 row in set (0.35 sec)
mysql> select count(1) from pf_td_simple_triggers; +----------+ | count(1) | +----------+ | 685 | +----------+ 1 row in set (0.48 sec)
mysql> select count(1) from pf_td_triggers; +----------+ | count(1) | +----------+ | 843 | +----------+ 1 row in set (0.93 sec) [root@mongdb2 ~]# mysqldump -uroot -p psd_schedule>schedule.sql Enter password: Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. [root@mongdb2 ~]# more psd_schedule.sql
scp schedule.sql admin@xxx:/u01 |
2、Oceanbase数据库导入到test租户TEST数据库中
obclient [test]> source /u01/psd_schedule.sql Query OK, 0 rows affected (0.009 sec)
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near '--' at line 1 Query OK, 0 rows affected (0.009 sec)
Query OK, 0 rows affected (0.013 sec)
Query OK, 0 rows affected (0.009 sec)
Query OK, 0 rows affected (0.011 sec)
Query OK, 0 rows affected (0.010 sec)
Query OK, 0 rows affected (0.010 sec)
Query OK, 0 rows affected (0.010 sec)
Query OK, 0 rows affected (0.010 sec)
Query OK, 0 rows affected (0.001 sec)
Query OK, 0 rows affected (0.009 sec)
Query OK, 0 rows affected (0.001 sec)
Query OK, 0 rows affected (0.009 sec)
Query OK, 0 rows affected (0.002 sec)
ERROR 1193 (HY000): Unknown system variable 'SQL_LOG_BIN' ERROR 1193 (HY000): Unknown system variable 'sql_log_bin' ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near '--' at line 1 Query OK, 0 rows affected (0.010 sec)
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near '--' at line 1 ERROR 1193 (HY000): Unknown system variable 'gtid_purged' ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near '--' at line 1 Query OK, 0 rows affected (0.004 sec)
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near '--' at line 1 Query OK, 0 rows affected, 1 warning (0.039 sec)
Query OK, 0 rows affected (0.007 sec)
Query OK, 0 rows affected (0.003 sec)
Query OK, 0 rows affected, 1 warning (0.996 sec)
Query OK, 0 rows affected (0.011 sec)
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near '--' at line 1 Query OK, 0 rows affected (0.010 sec)
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near '--' at line 1 Query OK, 0 rows affected (0.006 sec)
Query OK, 0 rows affected (0.013 sec)
Query OK, 23 rows affected (1.543 sec) Records: 23 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.017 sec)
Query OK, 0 rows affected (0.011 sec)
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near '--' at line 1 Query OK, 0 rows affected (0.013 sec)
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near '--' at line 1 Query OK, 0 rows affected, 1 warning (0.029 sec)
Query OK, 0 rows affected (0.011 sec)
Query OK, 0 rows affected (0.010 sec)
Query OK, 0 rows affected, 1 warning (0.218 sec)
Query OK, 0 rows affected (0.022 sec)
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near '--' at line 1 Query OK, 0 rows affected (0.007 sec)
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near '--' at line 1 Query OK, 0 rows affected (0.011 sec)
Query OK, 0 rows affected (0.014 sec)
Query OK, 685 rows affected (0.157 sec) Records: 685 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.004 sec)
Query OK, 0 rows affected (0.006 sec)
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near '--' at line 1 Query OK, 0 rows affected (0.022 sec)
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near '--' at line 1 Query OK, 0 rows affected, 1 warning (0.007 sec)
Query OK, 0 rows affected (0.002 sec)
Query OK, 0 rows affected (0.009 sec)
Query OK, 0 rows affected, 1 warning (0.674 sec)
Query OK, 0 rows affected (0.007 sec)
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near '--' at line 1 Query OK, 0 rows affected (0.007 sec)
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near '--' at line 1 Query OK, 0 rows affected (0.008 sec)
Query OK, 0 rows affected (0.003 sec)
Query OK, 843 rows affected (1.353 sec) Records: 843 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.015 sec)
Query OK, 0 rows affected (0.009 sec)
ERROR 1193 (HY000): Unknown system variable 'sql_log_bin' Query OK, 0 rows affected (0.010 sec)
Query OK, 0 rows affected (0.011 sec)
Query OK, 0 rows affected (0.023 sec)
Query OK, 0 rows affected (0.006 sec)
Query OK, 0 rows affected (0.011 sec)
Query OK, 0 rows affected (0.006 sec)
Query OK, 0 rows affected (0.010 sec)
Query OK, 0 rows affected (0.009 sec)
Query OK, 0 rows affected (0.014 sec)
obclient [test]> show tables; +-----------------------+ | Tables_in_test | +-----------------------+ | pf_td_ext_data_dict | | pf_td_simple_triggers | | pf_td_triggers | +-----------------------+ 3 rows in set (0.017 sec)
obclient [test]> select count(1) from pf_td_ext_data_dict; +----------+ | count(1) | +----------+ | 23 | +----------+ 1 row in set (0.009 sec)
obclient [test]> select count(1) from pf_td_simple_triggers; +----------+ | count(1) | +----------+ | 685 | +----------+ 1 row in set (0.011 sec)
obclient [test]> select count(1) from pf_td_triggers; +----------+ | count(1) | +----------+ | 843 | +----------+ 1 row in set (0.019 sec) |
有些兼容性的报错，数据全部导入完成。
3、部署DATAX软件
下载地址：http://datax-opensource.oss-cn-hangzhou.aliyuncs.com/datax.tar.gz
解压软件： tar -xvf datax.tar.gz -C /tmp 生存配置样例： python ./datax/bin/datax.py -r mysqlreader -w oceanbasev10writer > rds2ob1.json |
修改rds2ob1.json文件
{ "job": { "setting": { "speed": { "channel":4 }, "errorLimit": { "record": 1 } }, "content": [ { "reader": { "name": "mysqlreader", "parameter": { "username": "root", "password": "123456", "column": ["*"], "connection": [ { "jdbcUrl": [ "jdbc:mysql://xxxx:3306/psd_schedule?useUnicode=true&characterEncoding=utf8&autoReconnect=true&serverTimezone=Asia/Shanghai" ], "table": ["pf_td_triggers"] } ] } }, "writer": { "name": "oceanbasev10writer", "parameter": { "writeMode": "replace", "username": "root", "password": "xxxx", "column": ["*"], "batchSize": 1024, "connection": [ { "jdbcUrl": "||_dsc_ob10_dsc_||obtest:test||_dsc_ob10_dsc_||jdbc:oceanbase://xxxx:2883/trade?useLocalSessionState=true&allowBatch=true&allowMultiQueries=true&rewriteBatchedStatements=true", "table": ["pf_td_triggers"] } ] } } } ] } } |
标红部分obtest是集群名，test是租户名。trade是DB名字
执行离线同步运行 python ./datax/bin/datax.py rds2ob1.json 即可不落地文件，同步数据到OceanBase中。结果如下：
核实OCEANBASE test租户trade数据库中的数据是否同步：
[admin@ob ~]$ obclient -h172.22.150.7 -P2883 -p -A -c -Dtest -uroot@test Enter password: Welcome to the OceanBase. Commands end with ; or \g. Your OceanBase connection id is 33 Server version: OceanBase 3.1.0 (r-) (Built May 30 2021 11:21:29)
Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient [test]> obclient [test]> obclient [test]> show databases; +--------------------+ | Database | +--------------------+ | oceanbase | | information_schema | | mysql | | test | | trade | +--------------------+ 5 rows in set (0.013 sec)
obclient [test]> use trade; Database changed obclient [trade]> show tables; +-----------------+ | Tables_in_trade | +-----------------+ | emp | | pf_td_triggers | +-----------------+ 2 rows in set (0.012 sec)
obclient [trade]> select count(1) from pf_td_triggers; +----------+ | count(1) | +----------+ | 843 | +----------+ 1 row in set (0.026 sec) |
