Technology Sharing | Best Practice of mysql Federated Engine

Author: Ma Wenbin

MySQL OCP certification, PostgresSQL PGCA certification, good at backup and recovery of open source database related products such as MySQL, PostgreSQL, dble, read-write separation, SQL tuning, monitoring operation and maintenance, high-availability architecture design, etc. Currently working in Moon House (China) Co., Ltd.

Source of this article: original submission

* Produced by the Aikesheng open source community, original content is not allowed to be used without authorization, please contact the editor and indicate the source for reprinting.

1. Background

• The local MySQL database wants to access the data in the remote MySQL database table, but does not want to use data synchronization to achieve it, so consider the FEDERATED storage engine to achieve it.
• To allow this storage engine, use --with-federated-storage-engine to configure when building MySQL.

2. FEDERATED engine architecture:

When the client issues an SQL statement referencing the FEDERATED table, the information flow between the local server (where the SQL statement is executed) and the remote server (where the data is actually stored) is as follows:

1. The storage engine will traverse each column of the FEDERATED table and construct an appropriate SQL statement that references the remote table.
2. The statement is sent to the remote server using the MySQL client API.
3. The remote server processes the statement, and the local server retrieves any results (the number of rows affected or the result set) produced by the statement.
4. If the statement produces a result set, each column will be 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.

• The architecture diagram is as follows:

3. Related features

• Supported syntax:

1. Internally, the implementation uses SELECT, INSERT, UPDATE, and DELETE, but not HANDLER.
2. The FEDERATED storage engine supports SELECT, INSERT, UPDATE, DELETE, TRUNCATE TABLE, and indexes. In addition, it does not support ALTER TABLE, DROP TABLE or any data definition language statements that directly affect the structure of the table. The current implementation does not use prepared statements.
3. FEDERATED accepts the INSERT ... ON DUPLICATE KEY UPDATE statement, but if a duplicate key conflict occurs, the statement will fail with an error.

• Unsupported syntax:

1. FEDERATED performs batch insert processing to send multiple rows to the remote table in batches, thereby improving performance. In addition, if the remote table is transactional, it can make the remote storage engine execute the statement rollback correctly when an error occurs. This feature has the following limitations:

1) The inserted size cannot exceed the maximum packet size between servers. If the insert exceeds this size, it will be divided into multiple packets, and rollback problems may occur.

2) INSERT ... ON DUPLICATE KEY UPDATE will not be processed in batches.

1. If the remote table has changed, there is no way for the FEDERATED engine to know. The reason for this is that the table must work like a data file, except for the database system, no other files can be written. If any changes occur to the remote database, it may destroy the integrity of the data in the local table.
2. When using the CONNECTION character string, you cannot use the "@" character in the password. You can work around this limitation by using the CREATE SERVER statement to create a server connection.
3. The insert_id and timestamp options will not be propagated to the data provider.
4. DROP TABLE any statement issued against the FEDERATED table only deletes the local table, not the remote table.
5. FEDERATED tables are not suitable for query caching.
6. FEDERATED tables do not support user-defined partitions.

4. Environment

Two mysql-5.7.26 instances

ip	port	Remarks
192.168.234.204	3306	server-1
192.168.234.204	3310	server-2

5. Engine Settings

• Open the Federated storage engine in the configuration file

配置文件中添加 federated 引擎就可以，两个实例都要添加
vim /etc/my.cnf
[mysqld]
federated

• Check whether Federated is turned on [Support status NO in FEDERATED indicates that the engine is not 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)


 FEDERATED          | YES  说明已经开启了FEDERATED 引擎

6. Deployment

• There is a database db1 on server-1, and a database db2 on server-2. The data table link remote_tb1 of table tb1 on database db1 of server-1 must be established on database db2 of server-2, and the connection is made by ordinary user test .
• Establish test database database and table on server-1

create database db1;
use db1;
create table tb1(
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT
)ENGINE=INNODB;

插入数据：
insert into tb1 select NULL;
insert into tb1 select NULL;
insert into tb1 select NULL;
查看数据
mysql> select * from tb1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

• Create a test account on server-1

create user 'test'@'192.168.%' IDENTIFIED BY '123456';
grant select,update,insert,delete on db1.* to 'test'@'192.168.%';
flush privileges;

• Create remote access databases and tables on server-2

create database db2;
use db2;
create table remote_tb1(
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT
)ENGINE=FEDERATED
CONNECTION='mysql://test:123456@192.168.234.204:3306/db1/tb1';

create table tb2(
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
name varchar(20)
)ENGINE=INNODB;

插入数据：
insert into tb2(name) select 'a';
insert into tb2(name) select 'b';
insert into tb2(name) select 'c';

mysql> select * from db2.tb2;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
+----+------+
3 rows in set (0.00 sec)

• Query in server-2 whether there is data in db1.tb1 in server-1

mysql> select * from db2.remote_tb1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)
说明是关联上了，

测试下join：
select t2.* from db2.remote_tb1 as t1 join 
db2.tb2 as t2 on t1.id=t2.id 
where t2.name='c';

mysql> select t2.* from db2.remote_tb1 as t1 join
    -> db2.tb2 as t2 on t1.id=t2.id
    -> where t2.name='c';
+----+------+
| id | name |
+----+------+
|  3 | c    |
+----+------+
1 row in set (0.00 sec)
说明本地表和远程表关联也是可以的。

• Test the DML under server-2 operation and observe whether the binlog will write GTID transactions

mysql> delete from db2.remote_tb1 where id =3;
Query OK, 1 row affected (0.00 sec)

mysql> select * from db2.remote_tb1;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

• /usr/local/mysql-5.7.26/bin/mysqlbinlog -vvv mysql-bin.000884 |grep -C 10 remote_tb1

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 40057515
#210415 14:25:53 server id 2342042  end_log_pos 40057586 CRC32 0x82abe215     Query    thread_id=53    exec_time=0    error_code=0
SET TIMESTAMP=1618467953/*!*/;
SET @@session.sql_mode=1411383296/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=45/*!*/;
BEGIN
/*!*/;
# at 40057586
#210415 14:25:53 server id 2342042  end_log_pos 40057638 CRC32 0xddbc9dfc     Table_map: `db2`.`remote_tb1` mapped to number 416
# at 40057638
#210415 14:25:53 server id 2342042  end_log_pos 40057678 CRC32 0x5c28d0d0     Delete_rows: table id 416 flags: STMT_END_F

BINLOG '
cdx3YBOavCMANAAAACY7YwIAAKABAAAAAAEAA2RiMgAKcmVtb3RlX3RiMQABAwAA/J283Q==
cdx3YCCavCMAKAAAAE47YwIAAKABAAAAAAEAAgAB//4DAAAA0NAoXA==
'/*!*/;
### DELETE FROM `db2`.`remote_tb1`
### WHERE
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
# at 40057678
#210415 14:25:53 server id 2342042  end_log_pos 40057750 CRC32 0xb37fe7b3     Query    thread_id=53    exec_time=0    error_code=0
SET TIMESTAMP=1618467953/*!*/;
COMMIT
/*!*/;

从 binlog SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; 是可以看出，链接端操作DML是把GTID 事务隐藏了。

• server-2 The server observes remote_tb2 related files

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

[root@EOS_DB04 db2]# pwd
/mysqlData/3310_data/data/db2
[root@EOS_DB04 db2]# ll
total 128
-rw-r----- 1 mysql mysql    67 Apr 15 14:11 db.opt
-rw-r----- 1 mysql mysql  8556 Apr 15 14:11 remote_tb1.frm
-rw-r----- 1 mysql mysql  8586 Apr 15 14:18 tb2.frm
-rw-r----- 1 mysql mysql 98304 Apr 15 14:18 tb2.ibd

to sum up

Compared with other data synchronization products, this kind of link establishment cross-ip cross-database query will be lighter and easier to build.