Schedule Meeting

a

Using CONNECT to access remote MariaDB or MySQL tables

by | Sep 23, 2020 | MariaDB Storage Engines, MariaDB

Need Help?  Click Here for Expert Support

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

All content in this blog is distributed under the CreativeCommons Attribution-ShareAlike 4.0 International license. You can use it for your needs and even modify it, but please refer to Vettabase and the author of the original post. Read more about the terms and conditions: https://creativecommons.org/licenses/by-sa/4.0/

About Federico Razzoli
Federico Razzoli is a database professional, with a preference for open source databases, who has been working with DBMSs since year 2000. In the past 20+ years, he served in a number of companies as a DBA, Database Engineer, Database Consultant and Software Developer. In 2016, Federico summarized his extensive experience with MariaDB in the “Mastering MariaDB” book published by Packt. Being an experienced database events speaker, Federico speaks at professional conferences and meetups and conducts database trainings. He is also a supporter and advocate of open source software. As the Director of Vettabase, Federico does business worldwide but prefers to do it from Scotland where he lives.

Recent Posts

Enforcing strong passwords for MariaDB users

Enforcing strong passwords for MariaDB users

MariaDB users normally connect using a password. Weak passwords are a common security problem, especially when passwords are generated by humans. However, MariaDB comes with plugins that help validating passwords to make sure they are strong enough. This article is a...

Validating rows with CHECK constraints in MariaDB

Validating rows with CHECK constraints in MariaDB

Relational databases provide several ways to validate data. CHECK constraints are a powerful tool for in-database data validation. Their impact on performance is minimal, if any. In this article we'll discuss MariaDB support for CHECK constraints. Note that the CHECK...

MariaDB ColumnStore SQL limitations

MariaDB ColumnStore SQL limitations

MariaDB ColumnStore is an extremely fast and scalable solution for analytics built on MariaDB, which Vettabase supports. MariaDB ColumnStore inherits the MariaDB SQL dialect, and many MariaDB features. However, numerous MariaDB features are not available for the...

Services

Need Help?  Click Here for Expert Support

4 Comments

  1. Ramy-Badr Ahmed

    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

    Reply
    • Federico Razzoli

      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

      Reply
  2. prast

    What’s the difference between CONNECT and FEDERATED ?

    Reply
    • Federico Razzoli

      CONNECT can do much more. It can connect to many databases using MySQL/MariaDB protocol, ODBC or JDBC. It can also read/write several types of data files, including custom formats.

      Reply

Submit a Comment

Your email address will not be published. Required fields are marked *