In practice, we may encounter tables that need to operate on other database instances, but do not want the system to connect to multiple libraries. At this point, we need to use data table mapping. Like DBlink in Oracle, anyone who has used Oracle DBlink database links knows that they can query data across instances. Similarly, Mysql's own FEDERATED engine perfectly solves this problem. This article introduces the start-up and use of FEDERATED engine.
1. Open FEDERATED Engine
If you need to create a FEDERATED engine table, the target instance will turn on the FEDERATED engine. Since MySQL 5.5, the default installation of the FEDERATED engine has not been enabled. Enter show engines on the command line; FEDERATED line status is NO.
mysql> show engines; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | 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 | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | ARCHIVE | YES | Archive storage engine | 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 | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 9 rows in set (0.00 sec)
Add a line in the configuration file [mysqld]: federated, then restart the database, and the FEDERATED engine is turned on.
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 | | PERFORMANCE_SCHEMA | YES | Performance Schema | 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 | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | FEDERATED | YES | Federated MySQL storage engine | NO | NO | NO | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 9 rows in set (0.00 sec)
2. Create FEDERATED tables using CONNECTION
Use CONNECTION to create a general model for FEDERATED engine tables:
CREATE TABLE (......) ENGINE =FEDERATED CONNECTION='mysql://username:password@hostname:port/database/tablename'
Simply create tests:
# Source Table Structure and Data mysql> show create table test_table\G *************************** 1. row *************************** Table: test_table Create 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 number', `stu_name` varchar(20) DEFAULT NULL COMMENT 'Name of student', `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) # Target end-building table and query # Note that after ENGINE=FEDERATED CONNECTION, avoid using passwords with @ for source addresses mysql> 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 number', -> `stu_name` varchar(20) DEFAULT NULL COMMENT 'Name of student', -> `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=FEDERATED DEFAULT CHARSET=utf8 COMMENT='Student table' CONNECTION='mysql://root:root@10.50.60.212:3306/source/test_table'; Query OK, 0 rows affected (0.01 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)
3. Create FEDERATED tables using CREATE SERVER
If you want to create multiple FEDERATED tables on the same server, or you want to simplify the process of creating FEDERATED tables, you can use the 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 fedlink FOREIGN DATA WRAPPER mysql OPTIONS (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'
Example demo:
# Target side creates server pointing to source side mysql> CREATE SERVER test_link -> FOREIGN DATA WRAPPER mysql -> OPTIONS (USER 'root', PASSWORD 'root',HOST '10.50.60.212',PORT 3306,DATABASE 'source'); Query OK, 1 row affected (0.00 sec) mysql> select * from mysql.servers\G *************************** 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) # Target side creates FEDERATED table mysql> CREATE TABLE `s1` ( -> `increment_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Self-increasing primary key', -> `stu_id` int(11) NOT NULL COMMENT 'Student number', -> `stu_name` varchar(20) DEFAULT NULL COMMENT 'Name of student', -> `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=FEDERATED DEFAULT CHARSET=utf8 COMMENT='Student table' CONNECTION='test_link/s1'; Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE `s2` ( -> `increment_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Self-increasing primary key', -> `stu_id` int(11) NOT NULL COMMENT 'Student number', -> `stu_name` varchar(20) DEFAULT NULL COMMENT 'Name of student', -> `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=FEDERATED DEFAULT CHARSET=utf8 COMMENT='Student table' CONNECTION='test_link/s2'; Query OK, 0 rows affected (0.01 sec)
4. Summary of FEDERATED
Based on MySQL version 5.7.23, the author has experimented with a variety of DDL and DML at the source and target end, which can be summarized as follows. Interested students can try it.
- Target end-build table structure can be different from source-end recommendation and consistent with source-end structure
- Source DDL(CREATE, ALTER, DROP, etc.) Statement Change Table Structure Target End will not change
- Source-side DML (SELECT, UPDATE, INSERT, DELETE, etc.) statements target-side queries will be synchronized
- The target structure of the source drop table is still in existence but cannot be queried
- The target end cannot execute DDL statements
- The source data of the DML statement executed by the target end will also change.
- Target truncate table source table data will also be cleared
- Target drop table has no effect on source
5.FEDERATED Engine Best Practices
At present, FEDERATED engine is not widely used. If there is indeed a need for cross-instance access, it is recommended to do a good job of standardization. I summarize the best practices as follows:
- The source side specifically creates a user with read-only permissions for use by the target side.
- The target side suggests creating FEDERATED tables in CREATE SERVER mode.
- FEDERATED tables should not be too many, so special attention should be paid to migration.
- The target end should only be used for queries, forbidding changes to the FEDERATED table at the target end.
- It is recommended that the name and structure of the target end table be consistent with that of the source end.
- The target end should delete and rebuild in time after the source end table structure changes.