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

This article mainly explains "how to achieve dblink function through mysql's federated plug-in". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to achieve dblink function through mysql's federated plug-in".

Db1:172.26.99.157 3306 (Source Library)

Db2:172.26.99.157 3310 (calling the source library through dblink)

[root@node7 lepus] # mm

Mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 357

Server version: 5.7.29-log MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

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 |

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

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

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

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

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

+-- +

9 rows in set (0.00 sec)

Mysql > install plugin federated soname 'ha_federated.so'

ERROR 1125 (HY000): Function 'federated' already exists

Mysql > exit

Bye

Restart the database

# mysqladmin-uroot-P3310-p-h 127.0.0.1 shutdown

# mysqld-defaults-file=/mysql/data/my.cnf-user=mysql-datadir=/mysql/data/3310-basedir=/mysql/app/mysql-pid-file=/mysql/data/mysql3310.pid-socket=/mysql/data/mysql3310.sock-port=3310 &

Add parameters to the my.cnf:

Federated

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 |

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

| | MyISAM | YES | MyISAM storage engine | 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 | |

+-- +

9 rows in set (0.00 sec)

@ 3306:

# mysql-uroot-P3306-h227.0.0.1-p

Mysql > create database testdb

Mysql > use testdb

Mysql > CREATE TABLE `options` (

-> `name` varchar (50) DEFAULT NULL

-> `value` varchar (255) DEFAULT NULL

-> `substitution` varchar (100) DEFAULT NULL

-> KEY `idx_ name` (`name`) USING BTREE

->) ENGINE=InnoDB DEFAULT CHARSET=utf8

@ 33310:

Mysql > use tianlei

Mysql > CREATE TABLE `options` (

-> `name` varchar (50) DEFAULT NULL

-> `value` varchar (255) DEFAULT NULL

-> `substitution` varchar (100) DEFAULT NULL

-> KEY `idx_ name` (`name`) USING BTREE

->) ENGINE=FEDERATED DEFAULT CHARSET=utf8

-> CONNECTION='mysql://root:root123@172.26.99.157:3306/testdb/options'

Query OK, 0 rows affected (0.01 sec)

Mysql > select * from options

Empty set (0.01sec)

@ 3306:

Mysql >

Mysql > insert into options (name,value) values ('log','Y')

Query OK, 1 row affected (0.00 sec)

Mysql > select * from options

+-+

| | name | value | description | |

+-+

| | log | Y | NULL |

+-+

1 row in set (0.00 sec)

@ 3310:

Mysql > select * from options

+-+

| | name | value | description | |

+-+

| | log | Y | NULL |

+-+

1 row in set (0.00 sec)

Mysql > insert into options (name,value) values ('sql_mode','N')

Query OK, 1 row affected (0.01sec)

Mysql > select * from options

+-+

| | name | value | description | |

+-+

| | log | Y | NULL |

| | sql_mode | N | NULL |

+-+

2 rows in set (0.00 sec)

Mysql > update options set description='abc' where name='log'

Query OK, 1 row affected (0.01sec)

Rows matched: 1 Changed: 1 Warnings: 0

Mysql > delete from options where name = 'sql_mode'

Query OK, 1 row affected (0.01sec)

In addition to directly using the method of connecting strings, you can also create a server first, and then call server when creating a table:

You can also store connection strings using server.

CREATE SERVER dblink

FOREIGN DATA WRAPPER mysql

OPTIONS (USER 'lepus', HOST' 172.26.99.157, PORT 3306, DATABASE 'testdb')

CREATE TABLE `options2` (

`name` varchar (50) DEFAULT NULL

`value` varchar (255) DEFAULT NULL

`substitution` varchar (100) DEFAULT NULL

KEY `idx_ name` (`name`) USING BTREE

) ENGINE=FEDERATED

DEFAULT CHARSET=utf8

CONNECTION='dblink/options'

Mysql > select * from options2

ERROR 1429 (HY000): Unable to connect to foreign data source: Access denied for user 'lepus'@'172.26.99.157' (using password:

Mysql > drop server dblink

Query OK, 1 row affected (0.00 sec)

CREATE SERVER dblink

FOREIGN DATA WRAPPER mysql

OPTIONS (USER 'lepus', HOST' 172.26.99.157, PORT 3306, DATABASE 'testdb',PASSWORD' lepus')

Mysql > CREATE SERVER dblink

-> FOREIGN DATA WRAPPER mysql

-> OPTIONS (USER 'lepus', HOST' 172.26.99.157, PORT 3306, DATABASE 'testdb',PASSWORD' lepus')

Query OK, 1 row affected (0.01sec)

Mysql > select * from options2

+-+

| | name | value | description | |

+-+

| | log | Y | abc |

+-+

1 row in set (0.01 sec)

Mysql > show create table options2\ G

* * 1. Row *

Table: options2

Create Table: CREATE TABLE `options2` (

`name` varchar (50) DEFAULT NULL

`value` varchar (255) DEFAULT NULL

`substitution` varchar (100) DEFAULT NULL

KEY `idx_ name` (`name`) USING BTREE

) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='dblink/options'

1 row in set (0.00 sec)

A detailed description of the federated engine is available in the official documentation of mysql 5.7. the address is:

Https://dev.mysql.com/doc/refman/5.7/en/federated-storage-engine.html

The remote server includes table definitions and data, which can be myisam, innodb, or other engines

The local server only includes the connection string information of the federated engine table and the remote server with the same table structure. Add, delete, modify and search operations need to be sent to the remote server.

The data interaction is as follows:

1. The storage engine looks at each column owned by the FEDERATED table and constructs the appropriate SQL statement that references the remote table

two。 Statement is sent to the remote server using MySQL client API

3. The remote server processes the statement, and the local server retrieves all the results produced by the statement (number of rows or result sets affected)

4. If the statement generates a result set, each column is converted to the internal storage engine format expected by the FEDERATED engine and can be used to display the results to the client that issued the original statement.

Restrictions:

Remote server must be mysql

The remote table pointed to by FEDERATED must exist before the call

Can point to the FEDERATED engine table, be careful not to loop nesting

The FEDERATED engine cannot use indexes, and if the result set is large and the data is stored in memory, it may use a lot of swap or even hang to live.

FEDERATED engine tables support insert, update, delete, select, truncate table and index operations, but not alter table operations

Can accept insert... On deplicate key update, but it does not take effect. If there are duplicates, an error will still be reported.

Transactions are not supported

Batch insertion can be performed, but be careful not to exceed the maximum packet size that can be transmitted by the server

The FEDERATED engine table cannot know the changes in the table on the remote server.

The password cannot include the @ symbol when using a connection string

INSERT_id and timestamp options are not propagated to data provider

Any DROP TABLE statement issued against the FEDERATED table deletes only the local table, not the remote table

Do not use query caching

User-defined partitions are not supported

Thank you for reading, the above is the content of "how to achieve dblink function through mysql's federated plug-in". After the study of this article, I believe you have a deeper understanding of how to achieve dblink function through mysql's federated plug-in, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.