Using CONNECT to access remote MariaDB or MySQL tables

Last updated on 18 Settembre 2021

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.

Genoa Porto Antico is a great connection example. Source: Wikimedia Commons

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, with Ubuntu the package to install is mariadb-plugin-connect . Probably the package will only place the proper file in the plugin directory, so you will still need to run the above statement.

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.

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?

A proposito di Federico Razzoli

Federico is Vettabase Ltd director, and he's an expert database consultant specialised in the MariaDB and MySQL ecosystems.

2 Replies to “Using CONNECT to access remote MariaDB or MySQL tables”

  1. Thanks for your notes on this quick setup.

    Perhaps it should be noted that the user “connect_se” should also be granted File privilege on the mariadb_source? It hasn’t worked for me with Select privilege only; showed an an error message asking explicity for the file privilege.

    On the other hand, I was wondering whether SSL connection is possible for such a setup?

    I have tried running the mariadb_source server once with “require_secure_transport=on” and later with “Require SSL” for the “connect_se” user. But both failed with access denied error message. Any ideas about why that happens? or is SSL simply not supported by the connect storage engine?

    Thanks again!
    Ramy

    • Hi Ramy, thank you for your comment!

      I think you mean that the FILE permission is necessary on mariadb-connect? It is possible, I will test it and if confirmed I’ll fix the article. Thanks for pointing this out.

      I never tried to use SSL with CONNECT. I don’t know if it is supported. I don’t think I’ll have time to spend on it soon-ish, but if you manage to use SSL please let me know.

      Cheers,
      Federico

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *

*