.::: Create a link or dblink to a remote server MariaDD MySQL using federated ha_federatedx.so plugin :::.

A. Sample other database to get database (source)
create database teguhth_maria_dwh;
use teguhth_maria_dwh;
create table barang(
KODE_BARANG char(6) not null ,
NAMA_BARANG varchar(25),
SATUAN_BARANG varchar(20),
STOK_BARANG decimal(4),
primary key (KODE_BARANG));

insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-01','RICE COOKER','BUAH',20);
insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-02','LEMARI ES','UNIT',8);
insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-03','TELEVISI','UNIT',30);
insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-04','RADIO/TAPE','BUAH',35);
insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-05','KOMPUTER','UNIT',28);
insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-06','KIPAS ANGIN','BUAH',38);
select * from barang;
 
 

--create user readonly to access dblink

CREATE USER 'admin'@'localhost' IDENTIFIED BY 'admin';
GRANT SELECT, SHOW VIEW ON *.* TO `admin`@`%` WITH GRANT OPTION;
ALTER USER 'admin'@'localhost' IDENTIFIED BY 'admin';
FLUSH PRIVILEGES;

CREATE USER 'admin'@'%' IDENTIFIED BY 'admin';
GRANT SELECT, SHOW VIEW ON *.* TO `admin`@`localhost` WITH GRANT OPTION;
ALTER USER 'admin'@'%' IDENTIFIED BY 'admin';
FLUSH PRIVILEGES;

B. configure source
1. install FederatedX engine
INSTALL PLUGIN federated SONAME 'ha_federatedx.so';

2. check mysql.server

select * from mysql.servers;
## if want to delete
drop server dblink_maria;
select * from mysql.servers;
 
3. create server link to akces other database

CREATE SERVER dblink_maria
                FOREIGN DATA WRAPPER `mysql`
                OPTIONS (
                USER 'admin',
                PASSWORD 'admin',
                HOST '10.10.10.9',
                port 3306,
                DATABASE 'teguhth_maria_dwh'
                );
                
4. check mysql.server

select * from mysql.servers;

 

5. create table

create database dblink_teguh;
use dblink_teguh;
CREATE TABLE dblink_teguh.barang_dblink ENGINE = federated TABLE_TYPE=mariadb CONNECTION 'dblink_maria/barang';

-- menjadi
CREATE TABLE `barang_dblink` (
  `KODE_BARANG` char(6) NOT NULL,
  `NAMA_BARANG` varchar(25) DEFAULT NULL,
  `SATUAN_BARANG` varchar(20) DEFAULT NULL,
  `STOK_BARANG` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`KODE_BARANG`)
) ENGINE=FEDERATED DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci CONNECTION='dblink_maria/barang'

select * from dblink_teguh.barang_dblink;
 

C. Testing from dblink n link

1. from destination
select *,@@hostname from teguhth_maria_dwh.barang;
 

2. from source
select *,@@hostname from dblink_teguh.barang_dblink;

3. drop sample at destination
select *,@@hostname from teguhth_maria_dwh.barang;
delete from teguhth_maria_dwh.barang where KODE_BARANG='ELK-06';
select *,@@hostname from teguhth_maria_dwh.barang;
 

4. from source
select *,@@hostname from dblink_teguh.barang_dblink;

5. try delete from source
delete from dblink_teguh.barang_dblink where KODE_BARANG='ELK-05';

6. from destination
select *,@@hostname from teguhth_maria_dwh.barang;