MySQL Storage engine-Federated Best practice

1. Background

The local MySQL database must access the data in the tables of the remote MySQL database through the FEDERATED storage engine.

* somewhat similar to database links (DBLINK) in Oracle. To allow this storage engine, use-- with-federated-storage-engine to configure when building MySQL.

* when creating a FEDERATED table, the server creates a table definition file in the database directory. The file starts with the name of the table and has a .frm extension.

* No other files were created because the actual data is on a remote database.

two。 Related characteristics

* allow local access to data from tables in a remote MySQL database

* No data files are stored locally

* only MySQL access to MySQL is supported

* access to heterogeneous databases is not supported

* MySQL does not enable Federated storage engine by default

3. Environment

Two MySQL 5.7Instanc

ServerA 3306

ServerB 3307

[root@MySQL] # mysql-S / tmp/mysql.sock1-p123456mysql: [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 7Server version: 5.7.18 MySQL Community Server (GPL) Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.mysql > select version (); +-+ | version () | +-+ | 5.7.18 | +-+ 1 row in set (0.00 sec) [root@MySQL] # mysql-S / tmp/mysql.sock2-p123456mysql: [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 6Server version: 5.7.18 MySQL Community Server (GPL) Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.mysql > select version (); +-+ | version () | +-+ | 5.7.18 | +-+ 1 row in set (0.00 sec)

4. Federated storage engine settings

* check whether Federated is enabled. [Support status NO in FEDERATED indicates that the engine is not enabled]

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 | | InnoDB | DEFAULT | Supports transactions Row-level locking And foreign keys | YES | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | | 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.01 sec)

* configuration file specifies to enable Federated storage engine [/ etc/my.cnf]

[mysqld] federated

* restart MySQL to check again [the status of Support in FEDERATED as YES indicates that the engine is started successfully]

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 | | InnoDB | DEFAULT | Supports transactions Row-level locking And foreign keys | YES | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | | ARCHIVE | YES | Archive storage engine | NO | | MEMORY | | YES | Hash based | Stored in memory Useful for temporary tables | NO | | FEDERATED | YES | Federated MySQL storage engine | NO | +- -+ 9 rows in set (0.00 sec)

5. Deployment

* there is a database dbtestA on ServerA and a database dbtestB on ServerB. To establish a data table link remote_tabletestA of the table tabletestA on the database dbtestA of ServerA on the database dbtestB of ServerB, connect through the test of ordinary users.

* ServerA creates a database

Mysql > create database dbtestA;Query OK, 1 row affected (0.02 sec)

* ServerA creates tabletestA table in dbtestA database

Mysql > create table tabletestA (id INT PRIMARY KEY NOT NULL AUTO_INCREMENT) ENGINE=INNODB;Query OK, 0 rows affected (0.11 sec)

* create ordinary users in ServerA and authorize permissions related to dbtestA database

Mysql > create user 'test'@'127.0.0.1' IDENTIFIED BY' 123456 question OK, 0 rows affected (0.00 sec) mysql > grant select on dbtestA.* to 'test'@'127.0.0.1';Query OK, 0 rows affected (0.00 sec)

* create database dbtestB in ServerB

Mysql > create database dbtestB;Query OK, 1 row affected (0.00 sec)

* ServerB creates a linked table remote_tabletestA in dbtestB, using ordinary user test

Mysql > use dbtestB;Database changedmysql > create table remote_tabletestA (- > id INT PRIMARY KEY NOT NULL AUTO_INCREMENT->) ENGINE=FEDERATED-> CONNECTION='mysql://test:123456@127.0.0.1:3306/dbtestA/tabletestA';Query OK, 0 rows affected (0.09 sec)

* insert data into the tableA table of the ServerA dbtestA library

Database changedmysql > use dbtestA;Database changedmysql > insert into tabletestA select NULL;Query OK, 1 row affected Records: 1 Duplicates: 0 Warnings: 0mysql > insert into tabletestA select NULL;Query OK, 1 row affected (0.03 sec) Records: 1 Duplicates: 0 Warnings: 0mysql > insert into tabletestA select NULL;Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0mysql > select * from tabletestA;+----+ | id | +-- + | 1 | 2 | 3 | +-+ 3 rows in set (0.01sec)

* ServerB dbtestB library link table remote_tabletestA view

Mysql > use dbtestB;Database changedmysql > select * from remote_tabletestA;+----+ | id | +-- + | 1 | | 2 | | 3 | +-+ 3 rows in set (0.01sec)

* View linked table remote_tablestestA related files in ServerB server

.frm table definition file [Federated link library does not generate data files locally]

[root@MySQL] # ll / data/mysql_data2/dbtestB/total 16 root@MySQL-1 mysql mysql 65 Jun 25 10:40 db.opt-rw-r- 1 mysql mysql 8556 Jun 25 10:42 remote_tabletestA.frm

6. Summary

In order to demand-driven technology, there is no difference in technology itself, only in business.