FEDERATE Storage Engine MySQL


FEDERATED storage engine sudah ada sejak MySQL 5.0.3. Merupakan storage engine yang bisa mengakses data pada remote database (kaya DB link kalo di Oracle), jadi tanpa harus melakukan replikasi maupun clustering. Ketika table menggunakan FEDERATED storage engine maka query pada table tersebut otomatis akan mengeksekusi pada remote table (federated).

Untuk menyertakan FEDERATED storage engine pada saat buid MySQL dari Source,
pastikan menyertakan opsi –with-federated-storage-engine

Contoh Penggunaan
Host MySQL Server A : 172.17.1.23 (tanpa harus support FEDERATED)
Db MySQL Server A : test
Table MySQL Server A : test (sebarang storage engine)

Host MySQL Server B : 172.17.1.61 (harus support FEDERATED)
Db MySQL Server B : test
Table MySQL Server B : test_federated (FEDERATE storage engine)


-- ON Db MySQL Server A
CREATE TABLE test (
id INT(20) NOT NULL AUTO_INCREMENT,
NAME VARCHAR(32) NOT NULL DEFAULT '',
other INT(20) NOT NULL DEFAULT '0',
PRIMARY KEY (id),
INDEX NAME (NAME),
INDEX other_key (other)
)
ENGINE=MYISAM
DEFAULT CHARSET=latin1;

insert into `test_table` (`id`, `name`, `other`) values('1','Sugeng','2');
insert into `test_table` (`id`, `name`, `other`) values('2','Sugeng lagi','4');
insert into `test_table` (`id`, `name`, `other`) values('3','Sugeng lagi lagi','5');
-- 3 record dientrikan
-- berikan hak akses user sugeng untuk select ke table tersebut dari Db MySQL Server B

Sekarang kita buat table test_federated pada server B

-- On Db MySQL Server B
CREATE TABLE federated_table (
id INT(20) NOT NULL AUTO_INCREMENT,
NAME VARCHAR(32) NOT NULL DEFAULT '',
other INT(20) NOT NULL DEFAULT '0',
PRIMARY KEY (id),
INDEX NAME (NAME),
INDEX other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://sugeng:sugeng@172.17.1.23:3306/test/test_table';

mysql> select * from federated_table;
+----+------------------+-------+
| id | name | other |
+----+------------------+-------+
| 1 | Sugeng | 2 |
| 2 | Sugeng lagi | 4 |
| 3 | Sugeng lagi lagi | 5 |
+----+------------------+-------+
3 rows in set (0.00 sec)

3 record tersebut sebenarnya data pada remote Server (DB MySQL Server B)

4 thoughts on “FEDERATE Storage Engine MySQL

  1. permisi master

    saya sudah coba, tapi kok gak mau ya??
    di enginenya gak mau federate, tetep myisam
    kira-kira gimana ya??

    terima kasih

    1. coba diperiksa
      1. plugin engine federatenya sudah aktif belum?
      2. versi mysql yang dipakai mendukung federate engine tidak?

      1. edit /etc/my.cnf and in the [mysqld] section, add the line:

        federated

        it’s equivalent to specifying –federated on the command line

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s