2021-06-08 mysql8.0中的dblink

mysql8.0涓殑dblink

1. 纭繚target鍒皊ource绔殑鏁版嵁搴撶鍙ｆ槸閫氱殑

2. 鏌ョ湅target绔腑federated鎻掍歡鏄惁瀹夎骞跺惎鍔?mysql8.0榛樿宸插畨瑁咃紝浣嗘湭鍚姩)

mysql> show engines;

鹵-------------------鹵--------鹵---------------------------------------------------------------鹵-------------鹵-----鹵------

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

鹵-------------------鹵--------鹵---------------------------------------------------------------鹵-------------鹵-----鹵------

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

3. 閰嶇疆鍚姩federated鎻掍歡

锛?锛夈€佸湪 /etc/my.cnf涓?[mysqd]涓嬫坊鍔犱笅闈㈠弬鏁幫紝骞? federated

锛?锛夈€侀噸鏂闆惎鍔╰arget绔殑mysql鏈嶅姟

service mysqld restart

4. 鍒涘緩server锛堥』鐭ource绔暟鎹簱淇℃伅锛? CREATE SERVER

servername

FOREIGN DATA WRAPPER mysql OPTIONS ( HOST 鈥榮ource鏈嶅姟鍣↖P鈥? PORT source鏁版嵁搴撶鍙? USER 鈥樼敤鎴峰悕鈥? PASSWORD 鈥樺瘑鐮佲€? DATABASE 鈥樻暟鎹簱鍚嶁€?);

5. 鐧婚檰target椤圭洰鐢ㄦ埛锛屽垱寤簂ink琛? 鑾峰彇source绔叿浣撹〃鐨勮〃缁撴瀯锛屼緥锛歛ttachconfig锛屼慨鏀瑰悗鍒涘緩link琛╝ttachconfig_link:

鍘熸潵鐨勭粨鏋勶細

CREATE TABLE

attachconfig

(

UUID

varchar(150) NOT NULL,

NAME

varchar(150) DEFAULT NULL,

ORDERNUM

int DEFAULT NULL,

TABLE_NAME

varchar(150) DEFAULT NULL,

TABLE_KEY

varchar(150) DEFAULT NULL,

FILE_LENGTH

int DEFAULT NULL,

FILE_NUMBER

int DEFAULT NULL,

FILE_EXT

varchar(765) DEFAULT NULL,

EXTRA_PARA

varchar(1500) DEFAULT NULL,

PRIMARY KEY (

UUID

)

) ENGINE=InnoDB DEFAULT CHARSET=gbk;

淇敼涓猴細
  CREATE TABLE `attachconfig_link` (
  `UUID` varchar(150) NOT NULL,
  `NAME` varchar(150) DEFAULT NULL,
  `ORDERNUM` int DEFAULT NULL,
   `TABLE_NAME` varchar(150) DEFAULT NULL,
   `TABLE_KEY` varchar(150) DEFAULT NULL,
   `FILE_LENGTH` int DEFAULT NULL,
   `FILE_NUMBER` int DEFAULT NULL,
   `FILE_EXT` varchar(765) DEFAULT NULL,
    `EXTRA_PARA` varchar(1500) DEFAULT NULL,
    PRIMARY KEY (`UUID`)
    ) ENGINE=FEDERATED DEFAULT CHARSET=gbk CONNECTION='tcgl/attachconfig'   
    璇存槑锛歵cgl鏄垜鎻愪緵鐨勶紝attachconfig鏄鎴風殑琛ㄥ悕
    涔熷彲浠ヤ笉鍒涘緩server锛岀洿鎺ヨ窡source绔俊鎭細
  CREATE TABLE `attachconfig_link` (
  `UUID` varchar(150) NOT NULL,
  `NAME` varchar(150) DEFAULT NULL,
  `ORDERNUM` int DEFAULT NULL,
 `TABLE_NAME` varchar(150) DEFAULT NULL,
  `TABLE_KEY` varchar(150) DEFAULT NULL,
   `FILE_LENGTH` int DEFAULT NULL,
  `FILE_NUMBER` int DEFAULT NULL,
  `FILE_EXT` varchar(765) DEFAULT NULL,
  `EXTRA_PARA` varchar(1500) DEFAULT NULL,
  PRIMARY KEY (`UUID`)
   ) ENGINE=FEDERATED DEFAULT CHARSET=gbk CONNECTION='mysql://鐢ㄦ埛鍚?瀵嗙爜@source鏈嶅姟鍣↖P:source鏁版嵁搴撶鍙?鏁版嵁搴撳悕绉?琛ㄥ悕
           

6. 鏌ヨ鏁版嵁

select * from attachconfig_link; --缁撴灉涓簊ource鏈嶅姟鍣ㄨ〃鐨勬暟鎹?