Edited at

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

More than 1 year has passed since last update.

雑記と言うか備忘録と言うか、そんな感じで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)

出来てますね