mysql dblink_mysql dblink 链接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       引擎已经启用。

源端：

目标的要dblink源端的表结构

show create table  F_ORDERINFO_DETAIL\G;

*************************** 1. row ***************************

Table: F_ORDERINFO_DETAIL

Create Table: CREATE TABLE `F_ORDERINFO_DETAIL` (

`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=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

mysql> select count(*) from  F_ORDERINFO_DETAIL;

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

| count(*) |

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

|     2900 |

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

目标端：

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地址:端口/库名称/表名称';

mysql> select count(*) from  F_ORDERINFO_DETAIL;

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

| count(*) |

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

|     2900 |

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

源端：

mysql> insert into F_ORDERINFO_DETAIL select * from F_ORDERINFO_DETAIL;

Query OK, 2900 rows affected (0.28 sec)

Records: 2900  Duplicates: 0  Warnings: 0

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from F_ORDERINFO_DETAIL;

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

| count(*) |

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

|     5800 |

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

1 row in set (0.00 sec)

目标端：

mysql> select count(*) from t;

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

| count(*) |

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

|     5800 |

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

1 row in set (0.00 sec)