MariaDB 10.0 インストール
ディストリビューションごとにインストール方法が記載されている
https://downloads.mariadb.org/mariadb/repositories/
root@hostname:/home/shimizu# vi /etc/apt/sources.list
====================
# MariaDB 10.0
deb http://ftp.yz.yamagata-u.ac.jp/pub/dbms/mariadb/repo/10.0/debian wheezy main
deb-src http://ftp.yz.yamagata-u.ac.jp/pub/dbms/mariadb/repo/10.0/debian wheezy main
====================
root@hostname:/home/shimizu# aptitude update
W: GPG エラー: http://ftp.yz.yamagata-u.ac.jp wheezy Release: 公開鍵を利用できないため、以下の署名は検証できませんでした: NO_PUBKEY CBCB082A1BB943DB
root@hostname:/home/shimizu# apt-key adv --keyserver keyserver.ubuntu.com --recv-keys CBCB082A1BB943DB
Executing: gpg --ignore-time-conflict --no-options --no-default-keyring --secret-keyring /tmp/tmp.0ehY9Bpqrn --trustdb-name /etc/apt//trustdb.gpg --keyring /etc/apt/trusted.gpg --primary-keyring /etc/apt/trusted.gpg --keyring /etc/apt/trusted.gpg.d//debian-archive-squeeze-automatic.gpg --keyring /etc/apt/trusted.gpg.d//debian-archive-squeeze-stable.gpg --keyring /etc/apt/trusted.gpg.d//debian-archive-wheezy-automatic.gpg --keyring /etc/apt/trusted.gpg.d//debian-archive-wheezy-stable.gpg --keyserver keyserver.ubuntu.com --recv-keys CBCB082A1BB943DB
gpg: 鍵1BB943DBをhkpからサーバーkeyserver.ubuntu.comに要求
gpg: 鍵1BB943DB: 公開鍵“MariaDB Package Signing Key <package-signing-key@mariadb.org>”を読み込みました
gpg: 絶対的に信用する鍵が見つかりません
gpg: 処理数の合計: 1
gpg: 読込み: 1
root@hostname:/home/shimizu# aptitude update
root@hostname:/home/shimizu# aptitude search mariadb
p libmariadbclient-dev - MariaDB database development files
p libmariadbclient18 - MariaDB database client library
p libmariadbd-dev - MariaDB embedded database development files
p mariadb-client - MariaDB database client (metapackage depending on the latest version)
p mariadb-client-10.0 - MariaDB database client binaries
p mariadb-client-core-10.0 - MariaDB database core client binaries
p mariadb-common - MariaDB database common files (e.g. /etc/mysql/conf.d/mariadb.cnf)
p mariadb-connect-engine-10.0 - Connect storage engine for MariaDB
p mariadb-galera-server - MariaDB database server with Galera cluster (metapackage depending on the latest vers
p mariadb-galera-server-10.0 - MariaDB database server with Galera cluster binaries
p mariadb-oqgraph-engine-10.0 - Oqgraph storage engine for MariaDB
p mariadb-server - MariaDB database server (metapackage depending on the latest version)
p mariadb-server-10.0 - MariaDB database server binaries
p mariadb-server-core-10.0 - MariaDB database core server files
p mariadb-test - MariaDB database regression test suite (metapackage depending on the latest version)
p mariadb-test-10.0 - MariaDB database regression test suite
root@hostname:/home/shimizu# aptitude install mariadb-server
以下の新規パッケージがインストールされます:
libaio1{a} libdbd-mysql-perl{a} libmariadbclient18{a} libmysqlclient18{a} mariadb-client-10.0{a} mariadb-client-core-10.0{a} mariadb-common{a} mariadb-server
mariadb-server-10.0{a} mariadb-server-core-10.0{a} mysql-common{a}
更新: 0 個、新規インストール: 11 個、削除: 0 個、保留: 0 個。
アーカイブ 36.4 M バイト中 36.3 M バイトを取得する必要があります。展開後に 127 M バイトのディスク領域が新たに消費されます。
...(中略)
root@hostname:/home/shimizu# mysqld --version
mysqld Ver 10.0.11-MariaDB-1~wheezy-log for debian-linux-gnu on x86_64 (mariadb.org binary distribution)
root@hostname:/home/shimizu# mysql -uroot -ppassword
MariaDB [(none)]> \s;
--------------
mysql Ver 15.1 Distrib 10.0.11-MariaDB, for debian-linux-gnu (x86_64) using readline 5.1
Connection id: 40
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server: MariaDB
Server version: 10.0.11-MariaDB-1~wheezy-log mariadb.org binary distribution
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/run/mysqld/mysqld.sock
Uptime: 10 hours 46 min 50 sec
Threads: 1 Questions: 677 Slow queries: 0 Opens: 567 Flush tables: 2 Open tables: 29 Queries per second avg: 0.017
--------------
ERROR: No query specified
文字コードを統一する
root@hostname:/home/shimizu# vi /etc/mysql/conf.d/mariadb.cnf ======= [client] # Default is Latin1, if you need UTF-8 set this (also in server section) default-character-set = utf8 [mysqld] # # * Character sets # # Default is Latin1, if you need UTF-8 set all this (also in client section) character-set-server = utf8 ======= root@hostname:/home/shimizu# /etc/init.d/mysql restart [ ok ] Stopping MariaDB database server: mysqld. [ ok ] Starting MariaDB database server: mysqld. [info] Checking for corrupt, not cleanly closed and upgrade needing tables.. root@hostname:/home/shimizu# mysql -uroot -ppassword MariaDB [(none)]> \s; -------------- mysql Ver 15.1 Distrib 10.0.11-MariaDB, for debian-linux-gnu (x86_64) using readline 5.1 Connection id: 39 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server: MariaDB Server version: 10.0.11-MariaDB-1~wheezy-log mariadb.org binary distribution Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8 Db characterset: utf8 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /var/run/mysqld/mysqld.sock Uptime: 12 sec Threads: 1 Questions: 109 Slow queries: 0 Opens: 108 Flush tables: 1 Open tables: 84 Queries per second avg: 9.083 -------------- ERROR: No query specified
spiderストレージエンジン
特徴
MySQL/MariaDBのプラグインで別サーバにあるテーブルもしくはビューをローカルDBにあるテーブルもしくはパーティションとして利用できる
MariaDB 10.0.4から標準でバンドルされている
MariaDB 10.0.4でSpider 3.0(Beta)
MariaDB 10.0.11でSpider 3.2(Gamma)となった
- DBシャーディング
- 並列処理(パラレル処理)
- 別々のアプリケーションで利用するDBを1つのDBとして利用している
- 1つのデータベースに接続できれば、他のデータベースを意識できずに利用できる
- SPIDERを使ってデータを格納するノードを分ければ、各ノードでは別個のデータをキャッシュすることになり、メモリを効率的に利用できる
- 冗長機能：テーブル、パーティションの単位で冗長度を設定可能
※接続先のデータベースについてSPIDERエンジンがインストールされていなくても問題ない
制限
- Query cacheは無効にしたほうが良い
- まだexperimental
- Spiderの特性としてデータノードへのオーバーヘッドがある分、通常の単体DBに比べると最初はパフォーマンスが落ちる。並列アクセス（同時アクセス数）が多くなり、データ数も多くなってくるとSpiderの優位性が出てくるとのこと
- 外部キーが使用できないなど、Spiderはいくつかの特殊な制限がある
インストール
MariaDB [(none)]> SHOW ENGINES; +--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+ | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | MRG_MyISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | FEDERATED | YES | FederatedX pluggable storage engine | YES | NO | YES | | Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO | +--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+ 10 rows in set (0.00 sec) MariaDB [(none)]> source /usr/share/mysql/install_spider.sql Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected, 1 warning (0.01 sec) Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 0 rows affected (0.03 sec) Query OK, 0 rows affected (0.02 sec) Query OK, 1 row affected (0.04 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 0 rows affected (0.00 sec) Empty set (0.01 sec) Empty set (0.02 sec) Empty set (0.02 sec) Empty set (0.02 sec) Empty set (0.02 sec) Empty set (0.02 sec) Empty set (0.02 sec) Query OK, 0 rows affected (0.02 sec) Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> SHOW ENGINES; +--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+ | SPIDER | YES | Spider storage engine | YES | YES | NO | | MRG_MyISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | FEDERATED | YES | FederatedX pluggable storage engine | YES | NO | YES | | Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | +--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+ 11 rows in set (0.00 sec)
設定
SPIDERノード1つ、DBノード1つで設定する
spiderdb.s(テーブル)にSPIDERを設定
ユーザ：spideruser
パスワード：spiderpassword
mysql> CREATE DATABASE `spiderdb` DEFAULT CHARACTER SET utf8; Query OK, 1 row affected (0.00 sec) mysql> USE spiderdb; Database changed mysql> CREATE TABLE s(id INT NOT NULL AUTO_INCREMENT, code VARCHAR(10), PRIMARY KEY(id)); Query OK, 0 rows affected (0.03 sec) mysql> GRANT ALL PRIVILEGES ON *.* TO 'spideruser'@localhost IDENTIFIED BY 'spiderpassword'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT ALL PRIVILEGES ON *.* TO 'spideruser'@'%' IDENTIFIED BY 'spiderpassword'; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.01 sec)
MariaDB > CREATE DATABASE `spiderdb` DEFAULT CHARACTER SET utf8;
Query OK, 1 row affected (0.00 sec)
MariaDB > USE spiderdb;
Database changed
MariaDB [spiderdb]> GRANT ALL PRIVILEGES ON *.* TO 'spideruser'@localhost IDENTIFIED BY 'spiderpassword';
Query OK, 0 rows affected (0.06 sec)
MariaDB [spiderdb]> GRANT ALL PRIVILEGES ON *.* TO 'spideruser'@'%' IDENTIFIED BY 'spiderpassword';
Query OK, 0 rows affected (0.02 sec)
MariaDB [spiderdb]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
MariaDB [spiderdb]> CREATE TABLE s(id INT NOT NULL AUTO_INCREMENT, code VARCHAR(10), PRIMARY KEY(id))
-> ENGINE=SPIDER
-> COMMENT 'host "xx.xx.xx.xx", user "spideruser", password "spiderpassword", port "13306"';
Query OK, 0 rows affected (0.01 sec)
MariaDB [spiderdb]> select * from s;
Empty set (0.01 sec)
試してみる
mysql> INSERT INTO spiderdb.s (code) VALUES (1); Query OK, 1 row affected (0.00 sec) mysql> select * from s; +----+------+ | id | code | +----+------+ | 1 | 1 | +----+------+ 1 row in set (0.00 sec)
MariaDB [spiderdb]> select * from s; +----+------+ | id | code | +----+------+ | 1 | 1 | +----+------+ 1 row in set (0.01 sec) MariaDB [spiderdb]> INSERT INTO spiderdb.s (code) VALUES (2); Query OK, 1 row affected (0.04 sec) MariaDB [spiderdb]> select * from s; +----+------+ | id | code | +----+------+ | 1 | 1 | | 2 | 2 | +----+------+ 2 rows in set (0.01 sec)
参考
http://d.hatena.ne.jp/akishin999/20140418/1397832289
https://mariadb.com/kb/en/spider-storage-engine-overview/
http://spiderformysql.com/product.html
http://memocra.blogspot.jp/2011/08/ec2mysqlspider1-spider.html