何となくEC2(Amazone Linux)にMariaDBを入れてSpiderで水平Shardingしてみた #AWS

雑記と言うか備忘録と言うか、そんな感じでEC2にMariaDBの10.0系をyum経由で導入し、水平Sharding環境を構築しました

＃RDS使えばいいんですけど、高いんですよね。それなりにお試しでならEC2を3台構成にすれば水平Shardingは遊べますし。

※構成例

MariaDB：USER / PASS = root / Spider
SERVER:三台 Spiderサーバ：1台 DATANODE：2台
※ IPは仮として
SpiderNode：192.168.10.10
Datanode1：192.168.10.11
Datanode2：192.168.10.12
としますので読み替えてご活用下さい
DB：example_db
table：books
使うエンジン：Spider＆Innodb

下準備

yum -y update && shutdown -r now

1.MariaDBインストール
●MariaDBのレポジトリ追加

echo '# MariaDB 10.0 CentOS repository list - created 2014-04-02 07:21 UTC' >> /etc/yum.repos.d/mariadb.repo
echo '# http://mariadb.org/mariadb/repositories/' >> /etc/yum.repos.d/mariadb.repo
echo '[mariadb]' >> /etc/yum.repos.d/mariadb.repo
echo 'name = MariaDB' >> /etc/yum.repos.d/mariadb.repo
echo 'baseurl = http://yum.mariadb.org/10.0/centos6-amd64' >> /etc/yum.repos.d/mariadb.repo
echo 'gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB' >> /etc/yum.repos.d/mariadb.repo
echo 'gpgcheck=1' >> /etc/yum.repos.d/mariadb.repo
echo 'enable=1' >> /etc/yum.repos.d/mariadb.repo

●MariaDBの起動

yum install -y MariaDB-server MariaDB-client

●MariaDB起動

chkconfig mysql on
/etc/rc.d/init.d/mysql start

2.MariaDBの初期設定
●設定適用(対象：全サーバ)


/usr/bin/mysqladmin -u root password 'Spider'
mysql -u 'root' --password='Spider' -e 'CREATE DATABASE example_db;GRANT ALL PRIVILEGES ON *.* TO "root"@"192.168.10.%" IDENTIFIED BY "Spider";FLUSH PRIVILEGES;'

●設定適用(対象：Spiderサーバのみ)
▼MariaDBへのspiderエンジンのインストール

mysql -u 'root' --password='Spider' -e 'source /usr/share/mysql/install_spider.sql'

確認は　mysql -u 'root' --password='Spider' -e 'show engines;'　にて

●データノードのspiderサーバへの登録(対象：Spiderサーバのみ)

mysql -u 'root' --password='Spider' -e 'CREATE SERVER db1 FOREIGN DATA WRAPPER mysql OPTIONS (USER "root", PASSWORD "Spider", HOST "192.168.10.11", PORT 3306);'
mysql -u 'root' --password='Spider' -e 'CREATE SERVER db2 FOREIGN DATA WRAPPER mysql OPTIONS (USER "root", PASSWORD "Spider", HOST "192.168.10.12", PORT 3306);'

こちらも確認は mysql -u 'root' --password='Spider' -e 'select * from servers;' mysql にて

●スキーマの登録(対象：SpiderNodeのみ)

    CREATE TABLE books
    (
        id int AUTO_INCREMENT NOT NULL,
        name VARCHAR(255) NOT NULL,
        price int(11) NOT NULL default 0,
        created_at DATETIME NOT NULL,
        updated_at DATETIME,
        lock_version int(11) NOT NULL default 0,
        PRIMARY KEY (id)
    ) ENGINE = SPIDER DEFAULT CHARSET=utf8
    PARTITION BY HASH(id) (
      PARTITION p1 comment 'server "db1", table "books"',
      PARTITION p2 comment 'server "db2", table "books"'
    );

●スキーマの登録(対象：db1,db2)

    CREATE TABLE books
    (
        id int AUTO_INCREMENT NOT NULL,
        name VARCHAR(255) NOT NULL,
        price int(11) NOT NULL default 0,
        created_at DATETIME NOT NULL,
        updated_at DATETIME,
        lock_version int(11) NOT NULL default 0,
        PRIMARY KEY (id)
    ) ENGINE = SPIDER DEFAULT CHARSET=utf8
    ;

●Spiderサーバの「my.cnf」設定変更(対象：Spiderサーバのみ)

echo '[mysqld]' >> /etc/my.cnf
echo 'spider_internal_sql_log_off = ON' >> /etc/my.cnf
echo 'spider_remote_sql_log_off   = 1' >> /etc/my.cnf

●Spiderサーバの「my.cnf」設定変更(対象：Spiderサーバのみ)
/etc/rc.d/init.d/mysql restart

お仕舞。

テスト：以下10行のクエリを発行する
※クエリの発行はSpiderサーバのexample_dbで実施

    INSERT INTO books(name, price, created_at) VALUES ('3日で分かるJava', 2500, NOW());
    INSERT INTO books(name, price, created_at) VALUES ('3日で分かるRuby', 2300, NOW());
    INSERT INTO books(name, price, created_at) VALUES ('独習仮想化',      5000, NOW());
    INSERT INTO books(name, price, created_at) VALUES ('Java入門',        2000, NOW());
    INSERT INTO books(name, price, created_at) VALUES ('入門Ruby',        2800, NOW());
    INSERT INTO books(name, price, created_at) VALUES ('Effective Ruby',  4200, NOW());
    INSERT INTO books(name, price, created_at) VALUES ('すごいRuby',      5800, NOW());
    INSERT INTO books(name, price, created_at) VALUES ('Ruby徹底入門',    3000, NOW());
    INSERT INTO books(name, price, created_at) VALUES ('RubyからJavaへ',  1800, NOW());
    INSERT INTO books(name, price, created_at) VALUES ('クラウド大全',    6000, NOW());

●Spiderノード上で全件Select

  MariaDB [example_db]> select * from books order by id;
  +----+----------------------+-------+---------------------+------------+--------------+
  | id | name                 | price | created_at          | updated_at | lock_version |
  +----+----------------------+-------+---------------------+------------+--------------+
  |  1 | 3日で分かるJava      |  2500 | 2017-09-13 00:06:51 | NULL       |            0 |
  |  2 | 3日で分かるRuby      |  2300 | 2017-09-13 00:06:51 | NULL       |            0 |
  |  3 | 独習仮想化           |  5000 | 2017-09-13 00:06:51 | NULL       |            0 |
  |  4 | Java入門             |  2000 | 2017-09-13 00:06:51 | NULL       |            0 |
  |  5 | 入門Ruby             |  2800 | 2017-09-13 00:06:51 | NULL       |            0 |
  |  6 | Effective Ruby       |  4200 | 2017-09-13 00:06:51 | NULL       |            0 |
  |  7 | すごいRuby           |  5800 | 2017-09-13 00:06:51 | NULL       |            0 |
  |  8 | Ruby徹底入門         |  3000 | 2017-09-13 00:06:51 | NULL       |            0 |
  |  9 | RubyからJavaへ       |  1800 | 2017-09-13 00:06:51 | NULL       |            0 |
  | 10 | クラウド大全         |  6000 | 2017-09-13 00:06:51 | NULL       |            0 |
  +----+----------------------+-------+---------------------+------------+--------------+
  10 rows in set (0.01 sec

●db1ノード上で全件Select

  MariaDB [example_db]> select * from books order by id;
  +----+----------------------+-------+---------------------+------------+--------------+
  | id | name                 | price | created_at          | updated_at | lock_version |
  +----+----------------------+-------+---------------------+------------+--------------+
  |  2 | 3日で分かるRuby      |  2300 | 2017-09-13 00:06:51 | NULL       |            0 |
  |  4 | Java入門             |  2000 | 2017-09-13 00:06:51 | NULL       |            0 |
  |  6 | Effective Ruby       |  4200 | 2017-09-13 00:06:51 | NULL       |            0 |
  |  8 | Ruby徹底入門         |  3000 | 2017-09-13 00:06:51 | NULL       |            0 |
  | 10 | クラウド大全         |  6000 | 2017-09-13 00:06:51 | NULL       |            0 |
  +----+----------------------+-------+---------------------+------------+--------------+
  5 rows in set (0.00 sec)

●db2ノード上で全件Select

  MariaDB [example_db]> select * from books order by id;
  +----+----------------------+-------+---------------------+------------+--------------+
  | id | name                 | price | created_at          | updated_at | lock_version |
  +----+----------------------+-------+---------------------+------------+--------------+
  |  1 | 3日で分かるJava      |  2500 | 2017-09-13 00:06:51 | NULL       |            0 |
  |  3 | 独習仮想化           |  5000 | 2017-09-13 00:06:51 | NULL       |            0 |
  |  5 | 入門Ruby             |  2800 | 2017-09-13 00:06:51 | NULL       |            0 |
  |  7 | すごいRuby           |  5800 | 2017-09-13 00:06:51 | NULL       |            0 |
  |  9 | RubyからJavaへ       |  1800 | 2017-09-13 00:06:51 | NULL       |            0 |
  +----+----------------------+-------+---------------------+------------+--------------+
  5 rows in set (0.00 sec)

出来てますね