数据同步工具
方案：
1.mq -- insert update 哪些地方涉及到 都得处理/ 一张表两个队列/ /专家库要写一张表至少两个监听/ 数据库变更我也得变更
2.dblink
show engines;
开启federated引擎没有则安装插件
CREATE TABLE school (
`id` varchar(10) NOT NULL DEFAULT '' COMMENT '主键id',
`school_name` varchar(10) DEFAULT NULL COMMENT '学校名称',
PRIMARY KEY (`id`)
) ENGINE=FEDERATED DEFAULT CHARSET=utf8
COMMENT='学校表-远程表' CONNECTION='mysql://root:12345678@localhost:3306/test/school';
-- root:root 远程数据库的账号和密码
-- localhost:3306 远程数据库的ip和端口
-- b 远程数据库的名称
-- school 远程数据库的表名称
3.datax 阿里巴巴离线数据同步
{
"job": {
"content": [
{
"reader": {
"name": "mysqlreader",
"parameter": {
"username": "root",
"password": "root",
"column": ["*"],
"splitPk": "id",
"connection": [
{
"table": [
"stcsm_user_unit"
],
"jdbcUrl": [
"jdbc:mysql://139.196.142.114:3306/stcsm_user?useUnicode=true&characterEncoding=utf8"
]
}
]
}
},
"writer": {
"name": "mysqlwriter",
"parameter": {
"writeMode": "insert",
"username": "root",
"password": "root",
"column": [
"*"
],
"session": [
"set session sql_mode='ANSI'"
],
"preSql": [
"truncate stcsm_user_unit"
],
"connection": [
{
"jdbcUrl": "jdbc:mysql://139.196.142.114:3306/stcsm_expert_apply?useUnicode=true&characterEncoding=utf8",
"table": [
"stcsm_user_unit"
]
}
]
}
}
}
],
"setting": {
"speed": {
"channel": 5
}
}
}
}
CREATE TABLE school (
`id` varchar(10) NOT NULL DEFAULT '' COMMENT '主键id',
`school_name` varchar(10) DEFAULT NULL COMMENT '学校名称',
PRIMARY KEY (`id`)
) ENGINE=FEDERATED DEFAULT CHARSET=utf8
COMMENT='学校表-远程表' CONNECTION='mysql://root:12345678@localhost:3306/test/school';
-- root:root 远程数据库的账号和密码
-- localhost:3306 远程数据库的ip和端口
-- b 远程数据库的名称
-- school 远程数据库的表名称
DBLink
MAC 开启FEDERATED引擎
第一步先关闭mysql
sudo su
sudo /usr/local/mysql/support-files/mysql.server start --federated&
mysql -uroot -p12345678
show engines;
DataX 的用法
TRUNCATE TABLE Customer;
2. 解压、修改目录权限
tar zxvf datax.tar.gz
sudo chmod -R 755 datax
3. 执行同步job
python datax.py ../job/job.json
开启FEDERATED引擎 【MySQL】跨库join
dblink
public class TestDatax {
public static void main(String[] args) {
try {
WebLogs.info("start");
String windowcmd = "cmd /c python datax.py D:\\Software\\install\\Environment\\DataX\\datax\\job\\mysql2mysql.json";
WebLogs.info(windowcmd);
//.exec("你的命令",null,new File("datax安装路径"));
Process pr = Runtime.getRuntime().exec(windowcmd,null,new File("D:\\Software\\install\\Environment\\DataX\\datax\\bin"));
BufferedReader in = new BufferedReader(new InputStreamReader(pr.getInputStream()));
String line = null;
while ((line = in.readLine()) != null) {
WebLogs.info(line);
}
in.close();
pr.waitFor();
WebLogs.info("end");
} catch (Exception e) {
e.printStackTrace();
}
}
}
{
"job": {
"content": [
{
"reader": {
"name": "mysqlreader",
"parameter": {
"column": ["id","name"],
"connection": [
{
"jdbcUrl": ["jdbc:mysql://localhost:3306/test1?useUnicode=true&characterEncoding=utf8&yearIsDateType=false&zeroDateTimeBehavior=convertToNull&tinyInt1isBit=false&rewriteBatchedStatements=true"],
"table": ["student"]
}
],
"password": "****",
"username": "****",
}
},
"writer": {
"name": "mysqlwriter",
"parameter": {
"column": ["id","name"],
"connection": [
{
"jdbcUrl": "jdbc:mysql://localhost/test",
"table": ["student"]
}
],
"password": "****",
"username": "****",
}
}
}
],
"setting": {
"speed": {
"channel": 1
}
}
}
}
{
"job": {
"content": [
{
"reader": {
"name": "mysqlreader",
"parameter": {
"column": ["id", "name"],
"connection": [
{
"jdbcUrl": ["jdbc:mysql://localhost:3306/hzc?useUnicode=true&characterEncoding=UTF-8"],
"table": ["book"]
}
],
"password": "12345678",
"username": "root",
"where": "",
"session": ["set names utf8mb4"]
}
},
"writer": {
"name": "mysqlwriter",
"parameter": {
"column": ["id", "name"],
"connection": [
{
"jdbcUrl": "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8",
"table": ["book"]
}
],
"password": "12345678",
"username": "root",
"where": "",
"session": ["set names utf8mb4"]
}
}
}
],
"setting": {
"speed": {
"channel": "5"
}
}
}
}
小蚊子大人
