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