Create a link to a remote server and access data using CONNECT SE

Written by Written by Anel Husakovic

In this blog we are going to see how to create a link to a remote server and use it to access multiple tables at once.
In the previous blog we have seen how to establish a remote connection between Docker containers.
The way we did it was to specify the connection string to reference a single table only.
But what if we need more tables, what if need a whole database?

The solution is to link to a remote database with the CREATE SERVER statement.
A link obtained this way can be passed to the CREATE TABLE statement of a storage engine (SE) to make a connection where using the table discovery feature SE will find out about the table fields and create the table.
Let’s see how.

Following the previous blog let’s start the mariadb client in the remote container called mariadb-remote.

$ docker exec -it mariadb-remote mariadb -uanel -panel web_users

We have also created the mariadb-local container; let’s login into the mariadb client, where we have created the database called test.


$ docker exec -it mariadb-local mariadb -ulocal_user -plocal_user_pw test

— Create the server link on a mariadb-local

Note: you will need to have the SUPER, or FEDERATED ADMIN (from MariaDB 10.5.2+) and SELECT privileges.

MariaDB [test]> CREATE SERVER link_remote1
                FOREIGN DATA WRAPPER `mariadb`
                OPTIONS (
                USER 'anel',
                PASSWORD 'anel',
                HOST 'mariadb-remote',
                DATABASE 'web_users'
                );

Note that we specified the database as a parameter to the server link, so we can access all tables from a specific database using the defined link as a connection string.

The result of the previous query is the record in the mysql.servers table.

MariaDB [test]> select * from mysql.servers;
+--------------+----------------+-----------+----------+----------+------+--------+---------+-------+
| Server_name  | Host           | Db        | Username | Password | Port | Socket | Wrapper | Owner |
+--------------+----------------+-----------+----------+----------+------+--------+---------+-------+
| link_remote1 | mariadb-remote | web_users | anel     | anel     |    0 |        | mariadb |       |
+--------------+----------------+-----------+----------+----------+------+--------+---------+-------+
1 row in set (0.000 sec)

To drop the remote server link use the DROP SERVER link_remote1 statement.

Let’s try to create a table using the server link.

MariaDB [test]> CREATE TABLE test.my_local_webusers
ENGINE = CONNECT
TABLE_TYPE=MYSQL
CONNECTION 'link_remote1/webusers';
Query OK, 0 rows affected (0.031 sec)

Note that we could omit the table name webusers from the connection string. In that case we need to specify the name of the remote table we want to create.

MariaDB [test]> CREATE TABLE webusers ENGINE = CONNECT TABLE_TYPE=MYSQL CONNECTION 'link_remote1';

Validate the results

MariaDB [test]> select * from my_local_webusers;
+----+---------------+-----------+-------------------+----------------+---------------+--------------+-----------------+-----------------+-----------------+-----------------------+---------------+-----------------+----------------------------------------+----------------------------------+
| id | name          | username  | email             | address_street | address_suite | address_city | address_zipcode | address_geo_lat | address_geo_lng | phone                 | website       | company_name    | company_catchPhrase                    | company_bs                       |
+----+---------------+-----------+-------------------+----------------+---------------+--------------+-----------------+-----------------+-----------------+-----------------------+---------------+-----------------+----------------------------------------+----------------------------------+
|  1 | Leanne Graham | Bret      | Sincere@april.biz | Kulas Light    | Apt. 556      | Gwenborough  | 92998-3874      | -37.3159        | 81.1496         | 1-770-736-8031 x56442 | hildegard.org | Romaguera-Crona | Multi-layered client-server neural-net | harness real-time e-markets      |
|  2 | Ervin Howell  | Antonette | Shanna@melissa.tv | Victor Plains  | Suite 879     | Wisokyburgh  | 90566-7771      | -43.9509        | -34.4618        | 010-692-6593 x09125   | anastasia.net | Deckow-Crist    | Proactive didactic contingency         | synergize scalable supply-chains |
+----+---------------+-----------+-------------------+----------------+---------------+--------------+-----------------+-----------------+-----------------+-----------------------+---------------+-----------------+----------------------------------------+----------------------------------+
2 rows in set (0.002 sec)

Conclusion and future work

In this blog we learned how to apply the CREATE SERVER statement and access data using CONNECT SE. You can try to do the same with other storage engines, like SPIDER or FEDERATEDX.

Feedback Welcome

If you come across any problems in this feature preview, with the design, or edge cases that don’t work as expected, please let us know with a JIRA bug/feature request in the MDEV project. You are welcome to chat about it on Zulip.