Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how mysql dblink links to the mysql library, many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

1. Check whether the FEDERATED storage engine is installed on the target side

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 | |

+-- +

Note: if the FEDERATED engine is not installed, execute install plugin federated soname 'ha_federated.so'

It's already installed here, but it's not enabled.

2. Add federated to my.cnf and restart the database

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 | |

+-- +

The FEDERATED engine is enabled here.

Source side:

The table structure of the target to dblink the source side

Show create table F_ORDERINFO_DETAIL\ G

* * 1. Row *

Table: F_ORDERINFO_DETAIL

Create Table: CREATE TABLE `Founding ORDERFODETAIL` (

`TYPE` varchar (100) DEFAULT NULL

`DATE` int (6) DEFAULT NULL

`CUSTOMER_ ID`varchar (40) DEFAULT NULL

`UBI_ UID` varchar (40) DEFAULT NULL

`ORDERTIME` varchar (FOI_) 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

`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 |

+-+

Destination side:

CREATE TABLE `t` (

`TYPE` varchar (100) DEFAULT NULL

`DATE` int (6) DEFAULT NULL

`CUSTOMER_ ID`varchar (40) DEFAULT NULL

`UBI_ UID` varchar (40) DEFAULT NULL

`ORDERTIME` varchar (FOI_) 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

`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'

Note: source side table structure engine=federated connection = 'mysql:// user: password @ IP address: Port / library name / table name'

Mysql > select count (*) from F_ORDERINFO_DETAIL

+-+

| | count (*) |

+-+

| | 2900 |

+-+

Source side:

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)

Destination side:

Mysql > select count (*) from t

+-+

| | count (*) |

+-+

| | 5800 |

+-+

1 row in set (0.00 sec)

After reading the above, do you have any further understanding of how mysql dblink links to mysql libraries? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.