How to use MySQL FEDERATED engine ?

For a long time, I never encountered any system using FEDERATED tables. But recently, there seems to be more interest for FEDERATED tables. Is this related to the increase of microservices?

In a microservices architecture, services are typically designed to be small, loosely coupled, and independently deployable. Each service may have its own database, which can make it difficult to access and integrate data from multiple services.

The FEDERATED engine can help to solve this problem by providing a way to create virtual tables that access data from remote MySQL servers. This allows you to integrate data from different services into a single database, without requiring complex replication schemes or manual data management.

Let’s have a look at how to use the FEDERATED storage engine with MySQL 8.0.

Architecture

We have 2 MySQL Servers:

• production
• sales

The production server has a table with all the products their cost (purchase price), the price we sell them and the quantity in stock. It also contains a table for the suppliers.

The sales server has tables for the orders, the items that are parts of the orders and the customers.

In the production server, we also have one view that gives access to only the data from production we want to share with sales.

And in the sales server, we have a table product that is a federated table pointing the the view in the production server:

Tables in Production

This is the definition of the tables in the production server:

product

CREATE TABLE `product` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `supplier_id` int unsigned DEFAULT NULL,
  `purchase_cost` decimal(8,2) DEFAULT NULL,
  `price` decimal(8,2) DEFAULT NULL,
  `quantity` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `supplier_id` (`supplier_id`),
  CONSTRAINT `product_ibfk_1` FOREIGN KEY (`supplier_id`) REFERENCES `supplier` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

supplier

CREATE TABLE `supplier` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

product_view

This is not a table, this is a view.

CREATE ALGORITHM=UNDEFINED DEFINER=`msandbox`@`localhost` 
       SQL SECURITY DEFINER VIEW `product_view` AS 
select `product`.`id` AS `id`,`product`.`name` AS `name`, 
       `product`.`price` AS `price`,
       if((`product`.`quantity` > 0),'yes','no') AS `stock` 
from `product`

And the exact column types in the view:

desc product_view;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int unsigned | NO   |     | 0       |       |
| name  | varchar(20)  | YES  |     | NULL    |       |
| price | decimal(8,2) | YES  |     | NULL    |       |
| stock | varchar(3)   | NO   |     |         |       |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

This means that from the production server, we only want to share the id, name, price and if we have some stock or not, but not the real value. And this is the info we will make available using the FEDERATED table. So a FEDERATED table can point to a remote table but also to a view.

Tables in Sales

customer

CREATE TABLE `customer` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

orders

CREATE TABLE `orders` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` int unsigned DEFAULT NULL,
  `date` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `status` enum('pending','confirmed','shipped','delivered','paid') DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `customer_id` (`customer_id`),
  CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

order_lines

CREATE TABLE `order_lines` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `order_id` int unsigned NOT NULL,
  `product_id` int unsigned NOT NULL,
  `price` decimal(8,2) NOT NULL,
  `quantity` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `order_id` (`order_id`),
  CONSTRAINT `order_lines_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

product

CREATE TABLE `product` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `price` decimal(8,2) DEFAULT NULL,
  `stock` varchar(3) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=FEDERATED DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  CONNECTION='remote/product_view'

The product table above is using FEDERATED as Storage Engine. The column definition is coming from the DESC command’s output on the “parent/remote” table.

We have to create the remote server.

Remote Access

To setup the remote access from the sales server to the production server for the specific view (product_view), we need first to create a dedicated user on the production server:

create user remote_user identified by 'remoteme';
grant select on production.product_view to remote_user;

This mean that the user remote_user will be able to connect to the production server using the password remoteme and will be able to only use SELECT in the product_view table.

On the sales server we need to create a SERVER to use with the FEDERATED table. We already called it remote in the table definition’s CONNECTION parameter:

create server 'remote' foreign data wrapper mysql 
 options(user 'remote_user',
         host '192.168.1.56', port 3306, password 'remoteme', 
         database 'production');

Test

Now, let’s populate the tables in production and see the content of the product and product_view:

select * from product;
+----+------------------+-------------+---------------+-------+----------+
| id | name             | supplier_id | purchase_cost | price | quantity |
+----+------------------+-------------+---------------+-------+----------+
|  1 | dolphin keychain |           1 |          0.50 |  1.50 |       50 |
|  2 | dolphin sickers  |           1 |          0.20 |  1.00 |       10 |
|  3 | dolphin pens     |           2 |          1.00 |  2.00 |       10 |
+----+------------------+-------------+---------------+-------+----------+
3 rows in set (0.00 sec)

select * from product_view;
+----+------------------+-------+-------+
| id | name             | price | stock |
+----+------------------+-------+-------+
|  1 | dolphin keychain |  1.50 | yes   |
|  2 | dolphin sickers  |  1.00 | yes   |
|  3 | dolphin pens     |  2.00 | yes   |
+----+------------------+-------+-------+
3 rows in set (0.00 sec)

Now we can also try to see the content in the product FEDERATED table in the sales server:

select * from product;
+----+------------------+-------+-------+
| id | name             | price | stock |
+----+------------------+-------+-------+
|  1 | dolphin keychain |  1.50 | yes   |
|  2 | dolphin sickers  |  1.00 | yes   |
|  3 | dolphin pens     |  2.00 | yes   |
+----+------------------+-------+-------+

As you can see we have access to the data stored in the production sever and as we didn’t want to share all details from the original product table, we used the view as remote source of the FEDERATED table.

We can create an order like this for example:

start transaction;
Query OK, 0 rows affected (0.00 sec)

insert into orders(customer_id, status) values (1,'pending');
Query OK, 1 row affected (0.00 sec)

select * from orders;
+----+-------------+---------------------+---------+
| id | customer_id | date                | status  |
+----+-------------+---------------------+---------+
|  1 |           1 | 2023-03-07 13:04:05 | pending |
+----+-------------+---------------------+---------+
1 row in set (0.00 sec)

select last_insert_id() into @order_id;
Query OK, 1 row affected (0.00 sec)

insert into order_lines (order_id, product_id, price, quantity) 
 values (@order_id,1,(select price from product where id=1),2);
Query OK, 1 row affected (0.00 sec)

insert into order_lines (order_id, product_id, price, quantity) 
 values (@order_id,2,(select price from product where id=2),5);
Query OK, 1 row affected (0.01 sec)

commit;
Query OK, 0 rows affected (0.02 sec)

select * from order_lines;
+----+----------+------------+-------+----------+
| id | order_id | product_id | price | quantity |
+----+----------+------------+-------+----------+
|  1 |        1 |          1 |  1.50 |        2 |
|  2 |        1 |          2 |  1.00 |        5 |
+----+----------+------------+-------+----------+
2 rows in set (0.00 sec)

select o.id, c.name as 'customer name', date, status, sum(price*quantity) tot 
  from orders o 
  join order_lines on order_lines.order_id=o.id 
  join customer c on c.id=customer_id group by o.id;
+----+---------------+---------------------+---------+------+
| id | customer name | date                | status  | tot  |
+----+---------------+---------------------+---------+------+
|  1 | Lenka corp    | 2023-03-07 13:04:05 | pending | 8.00 |
+----+---------------+---------------------+---------+------+

Conclusion

As you can see it’s very easy to create some link between MySQL Servers using the FEDERATED engine. This is especially useful when you need access to a limited amount of data from another server and you don’t want to duplicate the data using a replication based solution.

I see new opportunities for the FEDERATED engine in microservices architectures.

The manual has an entire section dedicated to the FEDERATED engine: https://dev.mysql.com/doc/refman/8.0/en/federated-storage-engine.html.

Enjoy MySQL !