Preface:

There is a Federated storage engine in MySQL. Data tables using this engine do not store any data locally, but all data are stored on remote servers.

1. See if Federated storage engine is supported

MariaDB [(none)]> show engines;
| FEDERATED          | YES     | FederatedX pluggable storage engine                                        | YES          | NO   | YES        |
#The first YES indicates that it supports federated storage engine
#If not, modify the MySQL startup file my.cnf, and add federated=1 at the last line of the file
#Then restart MySQL

2. Create a database named bd for local use

MariaDB [(none)]> create database bd;
#Create a database named bd for use as a local database

3. Create a database named yc for remote use

MariaDB [(none)]> create database yc;
#Create a database named yc to use as remote storage data

4. Configure yc database

Create a data table

MariaDB [(none)]> use yc;          #Using yc database
MariaDB [yc]> create table yc_fed
    -> (
    -> id int auto_increment not null,
    -> c1 varchar(10) not null default '',
    -> c2 char(10) not null default '',
    -> primary key(id)
    -> )
    -> engine=innodb             #Specify that the storage engine of the table is innodb, and the default is innodb
    -> ; 

Insert data

MariaDB [yc]> insert into yc_fed(c1,c2)  #Insert three rows of simple data for the table
    -> values
    -> ('a','b'),
    -> ('c','d'),
    -> ('e','f');
MariaDB [yc]> select * from yc_fed;     #View all data in the table
+----+----+----+
| id | c1 | c2 |
+----+----+----+
|  1 | a  | b  |
|  2 | c  | d  |
|  3 | e  | f  |
+----+----+----+

Create users and give permissions to tables in this library

MariaDB [yc]> grant select,update,insert,delete on yc.yc_fed to vincen@'127.0.0.1' identified by '123';
#In IP 127.0.0.1 (local), create a user named vincen with password 123 and permission of select, update, insert and delete for yc feed table of yc library

5. Configure bd database

Create a data structure and data table of YC feed

MariaDB [yc]> use bd;               #Using bd database
MariaDB [bd]> create table bd_fed   #Create a table named BD ABCD fed. Note that the data structure should be consistent with YC ABCD fed
    -> (
    -> id int(11) not null auto_increment,
    -> c1 varchar(10) not null default '',
    -> c2 char(10) not null default '',
    -> primary key(id)
    -> )
    -> engine=federated connection='mysql://vincen:123@127.0.0.1:3306/yc/yc_fed'
#Specify that the storage engine of this table is Federated, use vincen user, password 123, and connect yc Chu fed data table in yc database locally
    -> ;

6. Query data on BD ﹣ fed table

MariaDB [bd]> select * from bd_fed;   #To view the data of bd UU fed table of bd Library
+----+----+----+
| id | c1 | c2 |
+----+----+----+
|  1 | a  | b  |
|  2 | c  | d  |
|  3 | e  | f  |
+----+----+----+
3 rows in set (0.02 sec)
#No data was inserted after the table was created
#There are three data records, which are the same as the data in yc feed table in yc library

7. Delete one row of data in BD fed table

MariaDB [bd]> delete from bd_fed where id = 2;  #Delete the data with id=2 in BD UU fed table 
MariaDB [bd]> select * from bd_fed;        #View the BD? Fed table after deletion
+----+----+----+
| id | c1 | c2 |
+----+----+----+
|  1 | a  | b  |
|  3 | e  | f  |
+----+----+----+
#You can see that the data row with id 2 has been deleted

8. Go back to the YC feed table to view the data

MariaDB [bd]> use yc;                 #Using yc database
MariaDB [yc]> select * from yc_fed;   #View YC feed table
+----+----+----+
| id | c1 | c2 |
+----+----+----+
|  1 | a  | b  |
|  3 | e  | f  |
+----+----+----+
#Compared with the data inserted at the beginning, the table now has less data with id 2