##やりたかったこと
そもそもログデータと実際の運用しているデータをJoinした結果が欲しかった。
ただログは運用してるサーバーとは全く別のホストに置いてあり欲しいデータがログ機の中には無い状態だった。
何が問題だったか？
上記でも書いた通り、ログ機は運用してるサーバーとは全く別のホストに置いてあり欲しいデータがログ機の中には無い状態でJoin出来なかった。
まずは簡単に環境を。（あくまで一例です。）
| DB名 | IP | table名
---- | ---- | ---- | ----
接続先 | MainDB | 192.168.1.100 | users
接続元 | LogDB | 192.168.1.200 | logs
MainDBには実データ、LogDBには名前の通り大きなログデータを保持。
LogDBにはMainDBのは基本的には存在しない。
たまにLogDBのlog.user_idとMainDBのusers.idをJoinしてデータを引っ張ってきたい。
ただLogDBには実データは無いのでJoinするにはMainDBのデータをLogDBの中に持って来なければならない。
| 解決案 | デメリット
---- | ---- | ----
1 | dump + restore | MainDBの更新の度にdump→restoreしなければならなく面倒
2 | view table | ホストをまたいだ接続が出来なさそう・・・
3 | federatedエンジンを使う | セキュリティリスク有。でも接続制限してるし今回だけだから大丈夫かな。
今回はfederatedエンジンを利用
というわけで今回はfederatedエンジンというものを使ってみました。
これは一言で言うと別ホストのテーブルがあたかもローカルにあるように使えるものです。
事前準備
1.mysqlコマンドでそもそも別ホストへの接続が出来るかチェック
以下コマンドで接続できるか確認。
$mysql -u ユーザ名 -h 192.168.1.100 DB名
これは大前提です。接続できない場合は接続先の設定を確認しておきましょう。
ここではこの設定手順は省略します。
2.エンジンが存在してるかどうかチェック。
今回僕の場合は以下のように表示され、federatedエンジンは入っていませんでした。
mysql> show engines;
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
6 rows in set (0.00 sec)
3.federatedエンジンのインストール
3-1.ha_federated.soの準備
これをインストールするにはha_federated.soというものが無いと入れられないそうなので、まずはこいつの存在してるか見てみました。
※パスは環境に合わせて変えてください。
find /usr/local/mysql/ -name "ha_federated.so"
今回はこのファイルが見つかったので次の手順に移ります。
3-2.Pluginのインストール
MySQLにログインして以下をコンソールで叩きます。
mysql> install plugin federated soname 'ha_federated.so';
そして最初に行ったshow enginesで見てみるとfederatedエンジンが入ってることがわかると思います。
ただSupportがNOの状態なのでこれを今度は有効化してあげる必要があります。
mysql> show engines;
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
7 rows in set (0.00 sec)
3-3.federatedの有効化
これはmy.confを開いて[mysqld]の下あたりにfederatedと書き加えました。
※federated=1でもいいそうです。
[mysqld]
port = 3306
socket = /tmp/mysql.sock
(省略)
federated #←これを書き加えました
これを加えたらmysqlを再起動して改めてshow enginesで見てみましょう。
mysql> show engines;
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| FEDERATED | YES | Federated MySQL storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
7 rows in set (0.00 sec)
これでfederatedエンジンの導入は完了です。
テーブルの作成
LogDBの中にusersテーブルを作成する。
今回はMainDBにログインして以下コマンドを実行。MainDBのusersテーブルの結果を少し加工してLogDBで実行。
mysql> show create table users\G
*************************** 1. row ***************************
Table: users
Create Table: CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=775394 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.00 sec)
ENGINSをfederatedに変更、接続先を追加してcreate文実行。
※こんな感じの記述を追加。
ENGINE=FEDERATED
CONNECTION='mysql://ユーザ名:パスワード@IPアドレス/DB名/テーブル名
mysql> CREATE TABLE `users` (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `name` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
-> `created_at` datetime DEFAULT NULL,
-> PRIMARY KEY (`id`),
-> ) ENGINE= FEDERATED
-> CONNECTION='mysql://mysql:XXXXXX@192.168.1.100/MainDB/users';
Query OK, 0 rows affected (0.03 sec)
検証
LogDBに作ったテーブルをselect文でみてみましょう。
mysql> select * from users limit 1;
+----+------+---------------------+
| id | name | created_at |
+----+------+---------------------+
| 1 | テスト| 2014-10-30 22:09:35 |
+----+------+---------------------+
1 row in set (8.34 sec)
データが取れましたね。とりあえずこれでLogDB上からリモートホストのMySQLのデータをJoinすることは出来そうですね。
注意点
-
結果を見てわかると思いますが、速度が非常に遅いです。（※特に今回記事ではlocal通信に書き換えましたが、実際はグローバル通信で試しました。）
-
セキュリティリスクがあるらしく現在はデフォルトで無効化されています。なので使う場合は気をつけましょう。というかなるべく使わない方が良さそうです。