MariaDB CONNECT storage engine

Question

I have a MariaDB running on one server that contains a particular table that I want somehow to have access to on another server on the same private network.The second server is also running MariaDB. Can this be achieved with the CONNECT storage engine? If so, can you point out any examples of how to do it? Thank you!

Answer 1

Yes, this is possible:

First install the package. On Redhat and friends, run this command:

sudo yum install MariaDB-connect-engine

Or on Debian/Ubuntu and friends:

sudo apt-get install mariadb-plugin-connect-engine 

(Or should that say 'mariadb-plugin-connect'?)

Then log in to MariaDB and install the plugin:

INSTALL SONAME 'ha_connect';

Now create the CONNECT table locally:

CREATE TABLE my_connect_table (
   col1 type1 -- same columns as in your remote table
   ...
) 
ENGINE=CONNECT TABLE_TYPE=MYSQL dbname=the_db_name tabname=the_table_name 
CONNECTION='mysql://the_username:the_password@the_hostname';  

Now you can SELECT, INSERT, UPDATE, DELETE etc. with that table as you wish. If you DROP this table, then you only DROP the local CONNECT table, not your remote table.

For reference, see: CONNECT MYSQL Table Type: Accessing MySQL/MariaDB Tables

You might also want to consider the MariaDB Spider storage engine. The Connect engine can use any remote database system (Oracle, MSSQL, PostgreSQL, ...) if you install the ODBC driver, or it can even be e.g. a CSV file instead of an actual database system. The Spider engine is only for remote MariaDB databases. However, the advantage of Spider is that it has sharding features (i.e. allows distributing the data in a table across multiple servers) and supports XA transactions.