Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
Help us understand the problem. What is going on with this article?

MySQLのFEDERATEDストレージエンジンを利用する

More than 5 years have passed since last update.

はじめに

MySQLのFEDERATEDのストレージエンジンを設定する方法を記述します。

リモートサーバに存在するテーブルをあたかもローカルに存在するかのように
利用することができるようになります。

環境

  • CentOS6.5

  • MySQL 5.6.20

構成

db001に格納されたデータをdb002から操作できるように
db002にFEDERATEDストレージエンジンの設定を行います。

MySQLのインストール

  • MySQL Yum Repositoryの準備
$ wget http://dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm
$ sudo yum localinstall mysql-community-release-el6-5.noarch.rpm
  • MySQLのインストール
$ sudo yum install mysql-community-client.x86_64 mysql-community-common.x86_64 mysql-community-libs.x86_64 mysql-community-libs-compat.x86_64 mysql-community-server.x86_64
  • MySQLの起動
$ sudo /etc/init.d/mysqld start
  • mysql_secure_installationの実行
$ /usr/bin/mysql_secure_installation

FEDERATEDストレージエンジンの設定

  • db001

データベースを作成します。

mysql> CREATE DATABASE master;
Query OK, 1 row affected (0.00 sec)

テーブルを作成し、データを挿入しておきます。

mysql> use master;
Database changed

mysql> CREATE TABLE table001 (
    ->   id int not null auto_increment,
    ->   name varchar(128) not null,
    ->   PRIMARY KEY (id),
    ->   INDEX idx_name (name)
    ->);

mysql> desc table001;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(128) | NO   | MUL | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> insert into table001 (name) values ('Java');
Query OK, 1 row affected (0.04 sec)

mysql> insert into table001 (name) values ('Ruby');
Query OK, 1 row affected (0.05 sec)

mysql> insert into table001 (name) values ('Python');
Query OK, 1 row affected (0.03 sec)

mysql> select * from table001;
+----+--------+
| id | name   |
+----+--------+
|  1 | Java   |
|  3 | Python |
|  2 | Ruby   |
+----+--------+
3 rows in set (0.00 sec)

db002からのアクセスを許可しておきます。

mysql> GRANT ALL ON master.* TO federated_user@'db002' IDENTIFIED BY '${password}';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
  • db002

サポートされているストレージエンジンを確認します。

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | 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        |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

FEDERATEDストレージエンジンを有効にします。

/etc/my.cnf
federated = 1

mysqldを再起動します

$ sudo /etc/init.d/mysqld restart

FEDERATEDストレージエンジンが有効となっていることを確認します。

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | 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        |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| FEDERATED          | YES     | Federated MySQL storage engine                                 | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

データベースを作成します。

mysql> CREATE DATABASE federated;
Query OK, 1 row affected (0.01 sec)

db001のmasterデータベースのtable001を操作できるようにFEDERATEDストレージエンジンを
利用してテーブルを作成します。

mysql> USE federated;
Database changed

mysql> CREATE TABLE table001 (
    ->   id int not null auto_increment,
    ->   name varchar(128) not null,
    ->   PRIMARY KEY (id),
    ->   INDEX idx_name (name)
    ->)
    ->ENGINE=FEDERATED
    ->CONNECTION='mysql://federated_user:${password}@db001/master/table001';
Query OK, 0 rows affected (0.05 sec)

動作確認

  • db002

データの参照を確認します。

mysql> select * from federated.table001;
+----+--------+
| id | name   |
+----+--------+
|  1 | Java   |
|  3 | Python |
|  2 | Ruby   |
+----+--------+
3 rows in set (0.01 sec)

データの更新を確認します。

mysql> insert into federated.table001 (name) values ('PHP');
Query OK, 1 row affected (0.04 sec)

mysql> select * from federated.table001;
+----+--------+
| id | name   |
+----+--------+
|  1 | Java   |
|  4 | PHP    |
|  3 | Python |
|  2 | Ruby   |
+----+--------+
4 rows in set (0.00 sec)
  • db001

データの参照を確認します。

mysql> select * from master.table001;
+----+--------+
| id | name   |
+----+--------+
|  1 | Java   |
|  4 | PHP    |
|  3 | Python |
|  2 | Ruby   |
+----+--------+
4 rows in set (0.00 sec)

参考

toshiro3
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away