Spider環境構築
ローカルの開発環境にMariaDBをインストールし、複数DBを立ち上げSpider Engineを試す。
環境:
mysql Ver 15.1 Distrib 10.4.7-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
Linux 5.0.0-27-generic #28~18.04.1-Ubuntu
MariaDBのインストール
$ curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash $ sudo apt-get update -y $ sudo apt-get upgrade -y $ sudo apt install mariadb-server-10.4 -y
unix_socketがどうとかでrootユーザでログインできないので別の管理者ユーザを作成する
mysql - mariadb password and unix_socket authentication for root - Stack Overflow MariaDB 10.4.1〜のユーザー認証がカオスな話(Unix_Socket) | GIZMELER
$ su - $ mysql MariaDB [(none)]> use mysql; MariaDB [(none)]> CREATE USER admin@localhost IDENTIFIED BY 'password'; MariaDB [(none)]> GRANT ALL ON *.* TO admin@localhost WITH GRANT OPTION; MariaDB [(none)]> flush privileges; MariaDB [(none)]> exit
mysqld_multiの設定
/etc/mysql/my.cnf
バックアップ取る
$ sudo cp my.cnf my.cnf.bk
my.cnfに追加
[mysqld_multi] mysqld = /usr/bin/mysqld_safe mysqladmin = /usr/bin/mysqladmin log = /var/log/mysql/multi.log user = admin password = password # インスタンス1の設定 [mysqld1] port = 3307 datadir = /var/lib/mysql1 pid-file = /var/run/mysql1/mysql.pid socket = /var/run/mysql1/mysql.sock log-error = /var/log/mysql/error1.log general_log_file = /var/log/mysql/query1.log # インスタンス2の設定 [mysqld2] port = 3308 datadir = /var/lib/mysql2 pid-file = /var/run/mysql2/mysql.pid socket = /var/run/mysql2/mysql.sock log-error = /var/log/mysql/error2.log general_log_file = /var/log/mysql/query2.log # インスタンス3の設定 [mysqld3] port = 3309 datadir = /var/lib/mysql3 pid-file = /var/run/mysql3/mysql.pid socket = /var/run/mysql3/mysql.sock log-error = /var/log/mysql/error3.log general_log_file = /var/log/mysql/query3.log
$ mysql_install_db --datadir=/var/lib/mysql1 --user=mysql $ mysql_install_db --datadir=/var/lib/mysql2 --user=mysql $ mysql_install_db --datadir=/var/lib/mysql3 --user=mysql $ chown -R mysql:mysql /var/lib/mysql1 /var/lib/mysql2 /var/lib/mysql3
mkdir /var/log/mysql
chown mysql:mysql /var/log/mysql
sudo mysqld_multi start
エラーの場合は、/var/log/mysql/multi.log
にログが出る。
[mysqld_multi] mysqld = /usr/bin/mysqld_safe mysqladmin = /usr/bin/mysqladmin
ここのmysqldのバイナリパスを間違うとこんなエラーが出る
FATAL ERROR: Tried to start mysqld under group [mysqld1], but no mysqld binary was found. Please add "mysqld=..." in group [mysqld_multi], or add it to group [mysqld1] separately.
$ where mysqld_safe
でパスを探して正しいパスを入れると直る。
mysqld1に入る
$ sudo mysql -uroot --socket=/var/run/mysql1/mysql.sock MariaDB [(none)]> show variables like 'port';
port:3307のはず
adminユーザを作成する
MariaDB [(none)]> CREATE USER admin@localhost IDENTIFIED BY 'password'; MariaDB [(none)]> GRANT ALL ON *.* TO admin@localhost WITH GRANT OPTION; MariaDB [(none)]> flush privileges;
mysqld2とmysqld3も同じようにユーザを作成する
Spider Engineのインストール
mysqld1をSpiderノードにして、mysqld2とmysqld3をデータノードにする
Spider Installation - MariaDB Knowledge Base
If Spider does not appear in this list, you need to run the configuration script. This is installed with the MariaDB server in CentOS. Debian distributions need to install the mariadb-plugin-spider package first. The package is usually found in
debian系は自分で入れないといけないらしい。
sudo apt install mariadb-plugin-spider
$ ls /usr/share/mysql/
install_spider.sql が作成されているのでmysqld1で実行する
MariaDB [(none)]> source /usr/share/mysql/install_spider.sql
データノードを登録
MariaDB [(none)]> CREATE SERVER mysqld2 FOREIGN DATA WRAPPER mysql OPTIONS (USER 'admin', PASSWORD 'password', HOST '127.0.0.1', PORT 3308); MariaDB [(none)]> CREATE SERVER mysqld3 FOREIGN DATA WRAPPER mysql OPTIONS (USER 'admin', PASSWORD 'password', HOST '127.0.0.1', PORT 3309);
mysqld1にテーブルを作成
CREATE TABLE employees ( id INT AUTO_INCREMENT NOT NULL, name VARCHAR(255) NOT NULL, department_id INT(11) NOT NULL, created_at DATETIME NOT NULL, updated_at DATETIME, PRIMARY KEY (id) ) ENGINE = SPIDER DEFAULT CHARSET=utf8mb4 PARTITION BY HASH(id) ( PARTITION p1 comment 'server "mysqld2", table "employees"', PARTITION p2 comment 'server "mysqld3", table "employees"' );
mysqld2とmysqld3に同じテーブルを作成。 innoDBでいいらしい。
CREATE TABLE employees ( id INT AUTO_INCREMENT NOT NULL, name VARCHAR(255) NOT NULL, department_id INT(11) NOT NULL, created_at DATETIME NOT NULL, updated_at DATETIME, PRIMARY KEY (id) ) ENGINE = InnoDB DEFAULT CHARSET=utf8mb4;
データを挿入
INSERT INTO employees(name, department_id, created_at) VALUES ('Tom', 1, NOW()); INSERT INTO employees(name, department_id, created_at) VALUES ('Jim', 2, NOW()); INSERT INTO employees(name, department_id, created_at) VALUES ('Watson', 3, NOW());
mysqld2とmysqld3に分散されて保存される。
mysqld1から, SELECT * FROM books WHERE title LIKE '%JAVA'
で普通に取れる。
JOINを試す
mysqld1に別のテーブルを作成
CREATE TABLE departments ( id INT AUTO_INCREMENT NOT NULL, name VARCHAR(255) NOT NULL, PRIMARY KEY (id) ) ENGINE = SPIDER DEFAULT CHARSET=utf8mb4 PARTITION BY HASH(id) ( PARTITION p1 comment 'server "mysqld2", table "departments"', PARTITION p2 comment 'server "mysqld3", table "departments"' );
mysqld2,mysqld3にテーブルを作成。
CREATE TABLE departments ( id INT AUTO_INCREMENT NOT NULL, name VARCHAR(255) NOT NULL, PRIMARY KEY (id) ) ENGINE = InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO departments(name) VALUES ('部署1'); INSERT INTO departments(name) VALUES ('部署2'); INSERT INTO departments(name) VALUES ('部署3');
SELECT * FROM employees AS e INNER JOIN departments AS d ON d.id = e.department_id
JOINできた。すごい。
参考:
akishin.hatenablog.jp
ngyuki.hatenablog.com
mariadb.com
Ubuntu 18.04 LTS に MariaDB 10.3 をインストール - Qiita