0

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!

  • Unless you need "access" in the same SQL query (eg, via JOIN), simply have two regular connections, one to each server. – Rick James Jul 14 '19 at 18:12
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.

  • Thank you! What do you think of the performance? Nothing is cached locally, I guess. – Martin Dimitrov Jul 15 '19 at 8:36
  • BTW, what is the difference between CONNECT and SPIDER storage engine? Thanks. – Martin Dimitrov Jul 15 '19 at 9:02
  • @MartinDimitrov I don't have much experience with the performance of this. If you compare it with having two DB connections (one for the local and one for the remote DB), you are adding another network hop with this solution. But if you have to e.g. JOIN with the remote table, then this makes sense. It can also be quite convenient for the applications not to have to deal with two different DB connections. – dbdemon Jul 15 '19 at 9:31
  • @MartinDimitrov I added a bit at the end about the difference between Connect and Spider. – dbdemon Jul 15 '19 at 9:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.