Using CONNECT to access remote MariaDB or MySQL tables

CONNECT is a powerful MariaDB storage engine designed to access remote data sources, using them as if they were local relational tables. We can query these tables and write data into them. While CONNECT supports a huge range of different data sources, the most common is probably remote databases.

In this article we’ll explore the simplest case: mapping a local table to a table located on a remote MariaDB or MySQL server. We’ll also see some good practice and practical advice. For more examples of what we can do with CONNECT, check my recent talk at MariaDB Server Fest 2020: Playing with the CONNECT storage engine.

Installing CONNECT

What we need here is two MariaDB servers: a source server containing the data we want to access; and a local server that we’ll use to query the source data. We’ll need to install CONNECT on the local server.

To save time I’ll use two Docker containers for this. I’m not suggesting to do the same in production. I will call the containers mariadb-source and mariadb-connect.

To install CONNECT, we can use a simple SQL statement:

INSTALL SONAME 'ha_connect';

If this statement fails, this means that the file ha_connect.so is not in the plugin directory (plugin_dir). In this case we probably installed MariaDB from a package provided by our Linux distribution, so we’ll have to find the package containing CONNECT. For example, to install CONNECT on Ubuntu:

apt-get install mariadb-plugin-connect

CONNECT can access remote tables from MariaDB, MySQL and Percona Server, but it can only be installed on MariaDB.

Account and privileges

On the source server, we need to create a user to allow CONNECT to establish a connection. For example:

CREATE USER `connect_se`@`mariadb-connect`
    IDENTIFIED BY 'secret';
GRANT SELECT
    ON world.country
    TO `connect_se`@`mariadb-connect`;
FLUSH PRIVILEGES;

We specified a hostname from which the connection can be done. This may not be important for testing, but in production it is a great idea. Sometimes the connection may come from multiple hosts (like replicas, or nodes of a cluster) and we may not want to create an account for each host. But if we use significant hostnames, we should be able to use a pattern, like cluster1-%. If we don’t use hostnames, we can use the pattern to force at least the connection to come from a certain subnet mask, for example 120.200.24.%.

In this case we only assign the SELECT privilege, so the CONNECT table will be read-only. We should be a bit paranoid with permissions when it comes to allow to access data via another MariaDB server.

Creating the CONNECT table

Now we need to create the CONNECT table on mariadb-connect. We’ll use the MYSQL table type. It is used to read and write data using the MySQL native protocol, which is almost the same the MariaDB protocol.

CREATE OR REPLACE TABLE world.country
    ENGINE = CONNECT
    TABLE_TYPE = MYSQL
    CONNECTION= 'mysql://connect_se:secret@mariadb-source/world/country'
;

Thanks to a MariaDB feature called table discovery, we didn’t have to specify the column names and definitions!

Let’s quickly test the CONNECT table…

MariaDB [world]> SELECT DISTINCT continent FROM country;
+---------------+
| continent     |
+---------------+
| North America |
| Asia          |
| Africa        |
| Europe        |
| South America |
| Oceania       |
| Antarctica    |
+---------------+
7 rows in set (0.004 sec)

Brilliant, it works!

Security notes

If the password is wrong the CREATE TABLE will fail with an error.

The password is readable by anyone who can run SHOW CREATE TABLE on the new table. This statement can be ran by whoever has the SELECT privilege for the table. Some recommendations:

• The readers of the CONNECT table will be able to read these credentials. Don’t give this account any privileges that the readers of the CONNECT table shouldn’t have.
• Don’t use the same password for other accounts.
• Again, specify a hostname or at least a pattern for this account.
• Again, don’t grant unnecessary permissions to this account.
• The connection to the remote database is insecure.

Exposing different columns

If we use table discovery, we expose all the columns from the original table, with the same names. However, we may want to exclude some columns, or add some virtual columns that are not present in the source table.

In this case, we could use SHOW CREATE TABLE to get the source table definition, and specify the same columns (except the ones we want to exclude) for the CONNECT table.

A simpler and less error-prone alternative is to create the table as showed above and then use ALTER TABLE:

ALTER TABLE world.country
      LOCK = EXCLUSIVE
    , DROP COLUMN Region
    , DROP COLUMN SurfaceArea
;

The operation should be very fast, but expect a short-living exclusive lock.

Views

It’s worth mentioning that it is quite possible to create a CONNECT table that connects to a remote view. Table discovery will still work properly.

Metadata

It is useful to be able to query metadata on the remote server, to get information about tables and columns that we can access. We can use the tables from the information_schema system database for this:

CREATE OR REPLACE TABLE world.remote_tables
    ENGINE CONNECT,
    TABLE_TYPE MYSQL,
    CONNECTION 'mysql://connect_se:secret@mariadb-source/information_schema/TABLES',
    COMMENT 'Tables in mariadb-source'
;

We don’t have to grant privileges on this table. When we query it, we will only see the tables on which we have some permissions, because this is how information_schema works:

MariaDB [world]> SELECT TABLE_NAME FROM remote_tables WHERE TABLE_SCHEMA = 'world';
+------------+
| TABLE_NAME |
+------------+
| country    |
+------------+
1 row in set (0.004 sec)

Conclusions

We discussed how to create CONNECT tables to access remote tables from other MariaDB or MySQL servers. We mentioned some good practice to use, especially from security standpoint.

Federico Razzoli

Did you like this article?