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';
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)
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)
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/30345407/viewspace-2096496/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 配置Oracle DBlink連線MySQL庫OracleMySql
- Oracle19c dblink連結mysql8.0OracleMySql
- jmeter連結mysql資料庫JMeterMySql資料庫
- [Oracle-> MySQL] Oracle通過dblink連線MySQL--Oracle 19c連線到MySQL 5.7OracleMySql
- oralce資料庫建立dblink連結資料庫
- SparkR連結mysql資料庫(踩坑)SparkMySql資料庫
- Oracle建立dblink MySQLOracleMySql
- federated MySQL的DBLinkMySql
- mysql 連結很慢MySql
- python連結mysql資料庫詳解PythonMySql資料庫
- 【PHP】Php連結mysqlPHPMySql
- EntityFramework 多資料庫連結,MySql,SqlServer,Oracel等Framework資料庫MySqlServer
- 記一次oracle透過dblink連線mysql實施OracleMySql
- SQL Server建立dblink至MySQLServerMySql
- DBeaver5.0.5連結mysqlMySql
- mysql 外連線總結MySql
- Mysql 左連結問題MySql
- 連線mysql資料庫MySql資料庫
- 使用mysqlclient庫連線mysqlMySqlclient
- 建立一個連結到其他資料庫的DBLINK資料庫
- jsp mysql資料庫+tomact連結起來JSMySql資料庫Mac
- [資料庫]【MySQL】MySQL資料庫規範總結資料庫MySql
- MySQL下的DBlink--FEDERATED引擎MySql
- django | 連線mysql資料庫DjangoMySql資料庫
- 如何連線MySQL資料庫MySql資料庫
- pycharm連線MySQL資料庫PyCharmMySql資料庫
- Mysql資料庫表連線MySql資料庫
- PHP連線MySql資料庫PHPMySql資料庫
- mysql資料庫連線(MySQLdb)MySql資料庫
- Weka 連線MySQL資料庫MySql資料庫
- java連線mysql資料庫JavaMySql資料庫
- Ruby連線MySQL資料庫MySql資料庫
- MySQL資料庫總結MySql資料庫
- MySQL federated引擎試驗(DBLINK的作用)MySql
- mysql 左連線,右連線,內連結,exists等MySql
- mysql--通過cmd連線mysql,並建立資料庫MySql資料庫
- php新浪雲連結mysql與storagePHPMySql
- Mysql開啟本地遠端連結MySql