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.
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
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
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!
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 world.country LOCK = EXCLUSIVE , DROP COLUMN Region , DROP COLUMN SurfaceArea ;
The operation should be very fast, but expect a short-living exclusive lock.
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.
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
MariaDB [world]> SELECT TABLE_NAME FROM remote_tables WHERE TABLE_SCHEMA = 'world'; +------------+ | TABLE_NAME | +------------+ | country | +------------+ 1 row in set (0.004 sec)
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.