创建DBLink----Oracle和mysql

Oracle：

DROP PUBLIC DATABASE LINK NM_HN;

CREATE PUBLIC DATABASE LINK NM_HN
CONNECT TO XZXT
IDENTIFIED BY <PWD>
USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP) (HOST=10.184.17.3) (PORT=1521) )(CONNECT_DATA=(SERVICE_NAME=XZXT1)))';

 

mysql:

1、查看target端是否安装了FEDERATED存储引擎

mysql> show engines ;

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

| Engine | Support | Comment | Transactions | XA | Savepoints |

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

| CSV | YES | CSV storage engine | NO | NO | NO |

| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |

| MyISAM | YES | MyISAM storage engine | NO | NO | NO |

| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |

| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |

| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |

| ARCHIVE | YES | Archive storage engine | NO | NO | NO |

| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |

| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

注意：如果没有安装FEDERATED 引擎 执行install plugin federated soname 'ha_federated.so';

这里已经安装好了，只是没有启用

2、将federated添加到my.cnf 重启数据库

vi /etc/my.cnf

[mysqld]

federated

service mysql restart

mysql> show engines;

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

| Engine | Support | Comment | Transactions | XA | Savepoints |

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |

| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |

| CSV | YES | CSV storage engine | NO | NO | NO |

| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |

| MyISAM | YES | MyISAM storage engine | NO | NO | NO |

| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |

| ARCHIVE | YES | Archive storage engine | NO | NO | NO |

| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |

| FEDERATED | YES | Federated MySQL storage engine | NO | NO | NO |

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

这里FEDERATED 引擎已经启用。

在目标数据库中，建立同步传输

目标端：

CREATE TABLE `t` (

`SYSTEM_TYPE` varchar(100) DEFAULT NULL,

`ORDER_DATE` int(6) DEFAULT NULL,

`CUSTOMER_ID` varchar(40) DEFAULT NULL,

`UBI_UID` varchar(40) DEFAULT NULL,

`FOI_ORDERTIME` varchar(100) DEFAULT NULL,

`FOI_ORDERNO` varchar(40) DEFAULT NULL,

`FOI_KEY` varchar(2500) DEFAULT NULL,

`FOI_KEYTYPE` varchar(40) DEFAULT NULL,

`CODENAME` varchar(100) DEFAULT NULL,

`ORDER_STATUS` varchar(20) DEFAULT NULL,

`FOI_ORDERTYPE` varchar(60) DEFAULT NULL,

`QYZM` int(11) DEFAULT NULL,

`GDCZ` int(11) DEFAULT NULL,

`GLRY` int(11) DEFAULT NULL,

`QYDWTZ` int(11) DEFAULT NULL,

`FRDWTZ` int(11) DEFAULT NULL,

`FRZWRZ` int(11) DEFAULT NULL,

`CXFR` int(11) DEFAULT NULL,

`CXGD` int(11) DEFAULT NULL,

`CXGG` int(11) DEFAULT NULL,

`ORDER_TYPE` varchar(20) DEFAULT NULL,

`DESC_INFO2` varchar(50) DEFAULT NULL

) ENGINE=federated connection = 'mysql://root:123456@192.168.1.5:3306/czb/F_ORDERINFO_DETAIL';

• 注意：源端表结构 engine=federated connection = 'mysql://用户:密码@IP地址:端口/库名称/表名称';
• 注意：数据库密码中不要有'@'、':'、'/'、‘.’等特殊字符