Is there a way to create a linked "database" in mariadb?

Question

I was doing this process in a same server but different databases.

1. Insert data into a table in dev database
2. Check the data
3. If the data is fine, insert into a same table in prd database

but now I separated those databases into different servers. development server, production server. I didn't want to edit so many existing queries, so I created federated tables that connected to the production server. but every time when I alter production tables.. I had to recreate federated tables again.

Is there a way to not recreate federated tables every time when I alter the original table?

Can I make a linked database?

Answer 1

This answer is assuming you have federatedX plugin available instead or in addition to federated.

You should try the following :

uninstall soname 'ha_federated.so' ;
install soname 'ha_federatedx.so' ;

If this is the case, the engine federatedx (used only for the plugin name, the engine name in DDL statements is still federated) you can use the following statements :

1. You create a server, as follow :

create server 'server_one' foreign data wrapper 'mysql' options
  (HOST '192.168.1.123',        
  DATABASE 'first_db',      
  USER 'patg',
  PASSWORD '',
  PORT 3306,
  SOCKET '',        
  OWNER 'root');

of course, you have to replace the values using your remote server ip, user, password, database, etc.

Later on, if anything change in your remote server properties, you just have to alter the server (no alter table are required anymore).

2. Then you create your local table using federated engine and the server you've created, as follow :

create table test_fed engine = federated connection = 'server_one' ;

Creating your local table like this will activate federatedx discovery mode, making your local table structure as the exact copy of the remote one.

What if your remote table structure change : unfortunately it's not automatic (the federatedx discovery mode is activated one time when the local table is created and it's not kept up to date), however, it's quiet easy as you just have to re-create your local table the same way

create or replace table test_fed engine = federated connection = 'server_one' ;

And you're done !

The drawback of this local tables declaration method, is to require your local tables' names to be exactly the same as remote ones

If you absolutely need different names for your local and remote tables, you can use the "old" connection syntax used in the create table (finishing with <remote_db_name>/<remote_table_name>)