Shulou(Shulou.com)06/01 Report--

Foreword:

In practice, we may encounter some tables that need to manipulate other database instances, but do not want the system to connect to multiple libraries. At this point, we need to use the data table mapping. Like DBlink in Oracle, anyone who has used Oracle DBlink database links knows that data can be queried across instances, and similarly, Mysql's own FEDERATED engine helps us solve this problem perfectly. This article introduces the startup and use of the FEDERATED engine.

1. Turn on the FEDERATED engine

If you need to create a FEDERATED engine table, the target instance needs to enable the FEDERATED engine. From MySQL5.5, the default installation of the FEDERATED engine is just not enabled, enter the command line and enter the show engines; FEDERATED line status as NO.

Mysql > show engines +- -+ | Engine | Support | Comment | Transactions | XA | Savepoints | +- -- + | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | | MRG_ MYISAM | YES | Collection of identical MyISAM tables | NO | | CSV | YES | CSV storage engine | NO | | BLACKHOLE | YES | / dev/null storage engine (anything you write to It disappears) | NO | | MyISAM | YES | MyISAM storage engine | NO | | InnoDB | DEFAULT | Supports transactions Row-level locking, and foreign keys | YES | | ARCHIVE | YES | Archive storage engine | NO | | MEMORY | YES | Hash based, stored in memory Useful for temporary tables | NO | | FEDERATED | NO | Federated MySQL storage engine | NULL | +-- -+ 9 rows in set (0.00 sec)

Add a line to the configuration file [mysqld]: federated, then restart the database, and the FEDERATED engine starts.

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 | | MyISAM | YES | MyISAM storage engine | NO | | BLACKHOLE | YES | / dev/null storage engine (anything) | You write to it disappears) | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | | MEMORY | YES | Hash based Stored in memory, useful for temporary tables | NO | | ARCHIVE | YES | Archive storage engine | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking And foreign keys | YES | | FEDERATED | YES | Federated MySQL storage engine | NO | +-- -- + 9 rows in set (0.00 sec) 2. Create a FEDERATED table using CONNECTION

Create a common model of FEDERATED engine tables using CONNECTION:

CREATE TABLE (.) ENGINE = FEDERATED CONNECTION='mysql://username:password@hostname:port/database/tablename'

Simply create a test:

# Source table structure and data mysql > show create table test_table\ Graph * 1. Row * * Table: test_tableCreate Table: CREATE TABLE `test_ Table` (`increment_ id` int (11) NOT NULL AUTO_INCREMENT COMMENT 'self-increasing primary key', `stu_ id`int (11) NOT NULL COMMENT 'student ID' `Name` varchar (20) DEFAULT NULL COMMENT 'student name', `create_ time`timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'creation time', `update_ time`timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'modification time', PRIMARY KEY (`increment_ id`), UNIQUE KEY `uk_stu_ id` (`stu_ id`) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT=' student table'1 row in set (0.00 sec) mysql > select * from test_table +-+ | increment_id | stu_id | stu_name | create_time | update_time | +- -- + | 1 | 1001 | wang | 2019-06-21 10:52:03 | 2019-06-21 10:52:03 | 2 | 1002 | dfsfd | 2019-06- 21 10:52:03 | 2019-06-21 10:52:03 | | 3 | 1003 | fdgfg | 2019-06-21 10:52:03 | 2019-06-21 10:52:03 | | 4 | 1004 | sdfsdf | 2019-06-21 10:52:03 | 2019-06-21 10:52:03 | 5 | 1005 | dsfsdg | 2019-06-21 10:52:03 | 2019-06-21 10:52 : 03 | | 6 | 1006 | fgd | 2019-06-21 10:52:03 | 2019-06-21 10:52:03 | +-+ 6 rows in Set (0.00 sec) # destination create table and query # Note: avoid using @ password mysql > CREATE TABLE `test_ table` (- > `increment_ id`int (11) NOT NULL AUTO_INCREMENT COMMENT 'self-increment primary key' for the source address after ENGINE=FEDERATED CONNECTION -> `update_ id` int (11) NOT NULL COMMENT 'student ID',-> `stu_ name` varchar (20) DEFAULT NULL COMMENT 'student name',-> `create_ time`timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'creation time',-> `update_ time`timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'modification time',-> PRIMARY KEY (`student id`) -> UNIQUE KEY `stu_ id` (`stu_ id`)-> ENGINE=FEDERATED DEFAULT CHARSET=utf8 COMMENT=' student form 'CONNECTION='mysql://root:root@10.50.60.212:3306/source/test_table' Query OK, 0 rows affected (0.01sec) mysql > select * from test_table +-+ | increment_id | stu_id | stu_name | create_time | update_time | +- -- + | 1 | 1001 | wang | 2019-06-21 10:52:03 | 2019-06-21 10:52:03 | 2 | 1002 | dfsfd | 2019-06- 21 10:52:03 | 2019-06-21 10:52:03 | | 3 | 1003 | fdgfg | 2019-06-21 10:52:03 | 2019-06-21 10:52:03 | | 4 | 1004 | sdfsdf | 2019-06-21 10:52:03 | 2019-06-21 10:52:03 | 5 | 1005 | dsfsdg | 2019-06-21 10:52:03 | 2019-06-21 10:52 : 03 | 6 | 1006 | fgd | 2019-06-21 10:52:03 | 2019-06-21 10:52:03 | +-+ 6 rows in set (2019 sec) 3. Create a FEDERATED table using CREATE SERVER

If you want to create multiple FEDERATED tables on the same server, or if you want to simplify the process of creating FEDERATED tables, you can use this CREATE SERVER statement to define server connection parameters so that multiple tables can use the same server.

The format created by CREATE SERVER is:

CREATE SERVER fedlinkFOREIGN DATA WRAPPER mysqlOPTIONS (USER 'fed_user', PASSWORD' 123456, HOST 'remote_host', PORT 3306, DATABASE' federated')

The FEDERATED table can then be created in the following format:

CREATE TABLE (.) ENGINE = FEDERATED CONNECTION='test_link/tablename'

The example demonstrates:

# create a servermysql > CREATE SERVER test_link-> FOREIGN DATA WRAPPER mysql-> OPTIONS on the destination side pointing to the source end (USER 'root', PASSWORD' root',HOST '10.50.60.212 recording Port 3306 database' source') Query OK 1 row affected (0.00 sec) mysql > select * from mysql.servers\ gateway * 1. Row * * Server_name: test_link Host: 10.50.60.212 Db: source Username: root Password: root Port: 3306 Socket: Wrapper: mysql Owner: 1 row in set (0.00 sec) # create FEDERATED table mysql > CREATE TABLE `s1` (- > `increment_ id` int (11) NOT NULL AUTO_INCREMENT COMMENT 'self-increment primary key' -> `update_ id` int (11) NOT NULL COMMENT 'student ID',-> `stu_ name` varchar (20) DEFAULT NULL COMMENT 'student name',-> `create_ time`timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'creation time',-> `update_ time`timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'modification time',-> PRIMARY KEY (`student id`) -> UNIQUE KEY `stu_ id` (`stu_ id`)-> ENGINE=FEDERATED DEFAULT CHARSET=utf8 COMMENT=' student form 'CONNECTION='test_link/s1' Query OK, 0 rows affected (0.01 sec) mysql > CREATE TABLE `s2` (- > `NOT NULL AUTO_INCREMENT COMMENT id` int (11) NOT NULL AUTO_INCREMENT COMMENT 'self-increment primary key',-> `stu_ id` int (11) NOT NULL COMMENT 'student ID',-> `stu_ name` varchar (20) DEFAULT NULL COMMENT 'student name',-> `create_ time`timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'creation time' -> `update_ time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'modification time',-> PRIMARY KEY (`uk_stu_ id`),-> UNIQUE KEY `uk_stu_ id` (`stu_ id`)->) ENGINE=FEDERATED DEFAULT CHARSET=utf8 COMMENT=' student table 'CONNECTION='test_link/s2' Query OK, 0 rows affected (0.01sec) 4.FEDERATED usage summary

Based on the MySQL5.7.23 version, the author has experimented with a variety of DDL and DML on the source side and the target side, which are briefly summarized as follows. Interested students can have a try.

The target-side table structure can be different from the source-side structure. It is recommended that the source-side DDL statement is consistent with the source-side structure. The source-side DDL statement changes the table structure. The source-side DML statement will not change. The source-side query will synchronize the source-side drop table. The target-side structure is still there but cannot query the target side can not execute the DDL statement. The source-side execution DML statement source-side data will also change the destination-side truncate table. The source-side table data will also be emptied. The table has no impact on source-side 5.FEDERATED engine best practices

Currently, FEDERATED engine is not widely used. If there is indeed a need for cross-instance access, it is recommended to standardize it. Personal summary of best practices is as follows:

The source side specifically creates users with read-only permissions for use by the target side. It is recommended to create a FEDERATED table in CREATE SERVER mode on the target side. FEDERATED tables should not be too many, so you should pay special attention to migration. The target side should only be used for queries, and it is forbidden to change the FEDERATED table on the target side. It is recommended that the table name and structure of the destination side be the same as that of the source side. After the table structure of the source side changes, the target side should delete and rebuild in time.

Reference:

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

Https://dev.mysql.com/doc/refman/5.7/en/create-server.html

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.