How to connect with a remote database with a Server link in MariaDB like a linked server or db link

Steps to configure the server linked server with MariaDB

1. Check the federated engine is installed

show engines

Engine            |Support|Comment                                                                                        |Transactions|XA |Savepoints|
------------------+-------+-----------------------------------------------------------------------------------------------+------------+---+----------+
MEMORY            |YES    |Hash based, stored in memory, useful for temporary tables                                      |NO          |NO |NO        |
MyISAM            |YES    |Non-transactional engine with good performance and small data footprint                        |NO          |NO |NO        |
InnoDB            |DEFAULT|Supports transactions, row-level locking, foreign keys and encryption for tables               |YES         |YES|YES       |
FEDERATED         |YES    |Allows one to access tables on other MariaDB servers, supports transactions and more           |YES         |NO |YES       |

If the Federated engine is not installed, we need to install it.

2. Install the federated engine with the following command:

install plugin federated soname 'ha_federatedx.dll'

3. Create a Server connection with CREATE SERVER statement to connect to the remote database.

create server remote_link 
foreign data wrapper `mysql`
options 
(
USER 'root',
PASSWORD 'password',
HOST 'localhost',
port 3307,
database 'hello'
);

4. Verified the existing or newly created server link in table MYSQL.SERVERS table

select * from mysql.servers;

5. To make a link, we need to create a table using the server link.

Example: I created two links with an emp table as emp_1 or emp_4.

create table emp_1 engine = federated TABLE_TYPE=MYSQL connection 'remote_link/emp';

create table emp_4 engine = federated connection = 'remote_link/emp'

6. Verify the that table is fetching data:

select * from emp_1;
select * from emp_4;