コンパイルからのインストール

漢(オトコ)のコンピュータ道: spider

必要モジュールなどをインストール

sudo aptitude install gcc g++ make cmake
sudo aptitude install bison libncurses5-dev libncursesw5-dev

MySQLユーザー/グループの追加

sudo groupadd mysql
sudo useradd -r -g mysql mysql

ソースのダウンロード

sudo wget https://launchpadlibrarian.net/131491052/mysql-5.5.14-spider-3.0-vp-0.18-hs-1.2-q4m-0.95.tgz
sudo tar xzvf mysql-5.5.14-spider-3.0-vp-0.18-hs-1.2-q4m-0.95.tgz
cd mysql-5.5.14-spider-3.0-vp-0.18-hs-1.2-q4m-0.95

Spiderホームページ

CMAKEを利用したコンパイル

sudo cmake . \
 -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
 -DDEFAULT_CHARSET=utf8 \
 -DDEFAULT_COLLATION=utf8_general_ci \
 -DENABLED_LOCAL_INFILE=true \
 -DWITH_INNOBASE_STORAGE_ENGINE=1 \
 -DWITH_EXTRA_CHARSETS=all \
 -DWITH_READLINE=ON

sudo make
sudo make install

ディレクトリの作成と権限変更

sudo chown -R mysql:mysql /usr/local/mysql/

sudo cp ./scripts/install_*.sql /usr/local/mysql/scripts/

sudo mkdir /var/log/mysql/
sudo touch /var/log/mysql/error.log
sudo touch /var/log/mysql/query.log
sudo touch /var/log/mysql/slow.log
sudo chown -R mysql:mysql /var/log/mysql/

MySQLの設定

sudo vi /etc/my.cnf
[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld]
port                = 3306
socket              = /var/run/mysqld/mysqld.sock
basedir             = /usr/local/mysql
datadir             = /var/lib/mysql
tmpdir              = /tmp
log_error           = /var/log/mysql/error.log
slow-query-log      = ON
slow-query-log-file = /var/log/mysql/slow.log
skip-external-locking
key_buffer_size     = 256M
max_allowed_packet  = 1M
table_open_cache    = 256
sort_buffer_size    = 1M
read_buffer_size    = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
thread_concurrency = 8

log-bin=mysql-bin
binlog_format=mixed
server-id       = 1

log_bin_trust_function_creators = 1

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
basedir=/usr/local/mysql

データベースの初期化

sudo mkdir /var/lib/mysql
sudo chown mysql:mysql /var/lib/mysql

cd /usr/local/mysql
sudo ./scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/var/lib/mysql

起動スクリプトの設定

sudo cp /usr/local/mysql/support-files/mysql.server /etc/init.d/

シンボリックリンクの作成

cd /usr/bin
sudo ln -s /usr/local/mysql/bin/* ./

cd /usr/share
sudo ln -s /usr/local/mysql/share ./mysql

MySQLサービスの起動

sudo service mysql.server start

全権限ユーザーの作成

mysql> CREATE USER 'sheeps'@'%' IDENTIFIED BY  '***';
mysql> GRANT ALL PRIVILEGES ON * . * TO  'sheeps'@'%' IDENTIFIED BY  '***' WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;

ユーザーと同一のDBに全権限を与える

mysql> CREATE USER 'wordpress'@'localhost' IDENTIFIED BY  '***';
mysql> GRANT USAGE ON * . * TO  'wordpress'@'localhost' IDENTIFIED BY  '***' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
mysql> CREATE DATABASE IF NOT EXISTS  `wordpress` ;
mysql> GRANT ALL PRIVILEGES ON  `wordpress` . * TO  'wordpress'@'localhost';

Spider

Spiderストレージエンジンのインストール

cd /usr/local/mysql
./bin/mysql -u root < ./scripts/install_spider.sql

インストール確認
[sourcecode language="plain"]
mysql> SELECT * FROM INFORMATION_SCHEMA.plugins WHERE PLUGIN_NAME = 'SPIDER';
+-------------+----------------+---------------+----------------+---------------------+----------------+------------------------+---------------+-----------------------+----------------+-------------+
| PLUGIN_NAME | PLUGIN_VERSION | PLUGIN_STATUS | PLUGIN_TYPE | PLUGIN_TYPE_VERSION | PLUGIN_LIBRARY | PLUGIN_LIBRARY_VERSION | PLUGIN_AUTHOR | PLUGIN_DESCRIPTION | PLUGIN_LICENSE | LOAD_OPTION |
+-------------+----------------+---------------+----------------+---------------------+----------------+------------------------+---------------+-----------------------+----------------+-------------+
| SPIDER | 3.0 | ACTIVE | STORAGE ENGINE | 50514.0 | ha_spider.so | 1.2 | Kentoku Shiba | Spider storage engine | GPL | ON |
+-------------+----------------+---------------+----------------+---------------------+----------------+------------------------+---------------+-----------------------+----------------+-------------+

mysql> SELECT * FROM mysql.func WHERE dl = 'ha_spider.so';
+------------------------------+-----+--------------+-----------+
| name | ret | dl | type |
+------------------------------+-----+--------------+-----------+
| spider_direct_sql | 2 | ha_spider.so | function |
| spider_bg_direct_sql | 2 | ha_spider.so | aggregate |
| spider_ping_table | 2 | ha_spider.so | function |
| spider_copy_tables | 2 | ha_spider.so | function |
| spider_flush_table_mon_cache | 2 | ha_spider.so | function |
+------------------------------+-----+--------------+-----------+

mysql> SELECT * FROM INFORMATION_SCHEMA.plugins WHERE PLUGIN_LIBRARY = 'ha_spider.so';
+------------------+----------------+---------------+--------------------+---------------------+----------------+------------------------+---------------+---------------------------------+----------------+-------------+
| PLUGIN_NAME | PLUGIN_VERSION | PLUGIN_STATUS | PLUGIN_TYPE | PLUGIN_TYPE_VERSION | PLUGIN_LIBRARY | PLUGIN_LIBRARY_VERSION | PLUGIN_AUTHOR | PLUGIN_DESCRIPTION | PLUGIN_LICENSE | LOAD_OPTION |
+------------------+----------------+---------------+--------------------+---------------------+----------------+------------------------+---------------+---------------------------------+----------------+-------------+
| SPIDER | 3.0 | ACTIVE | STORAGE ENGINE | 50514.0 | ha_spider.so | 1.2 | Kentoku Shiba | Spider storage engine | GPL | ON |
| SPIDER_ALLOC_MEM | 0.1 | ACTIVE | INFORMATION SCHEMA | 50514.0 | ha_spider.so | 1.2 | Kentoku Shiba | Spider memory allocating viewer | GPL | ON |
+------------------+----------------+---------------+--------------------+---------------------+----------------+------------------------+---------------+---------------------------------+----------------+-------------+
[/sourcecode]

VPストレージエンジンのインストール

cd /usr/local/mysql
./bin/mysql -u root < ./scripts/install_vp.sql

インストール確認
[sourcecode language="plain"]
mysql> SELECT * FROM INFORMATION_SCHEMA.plugins WHERE PLUGIN_NAME = 'VP';
+-------------+----------------+---------------+----------------+---------------------+----------------+------------------------+---------------+--------------------------------------+----------------+-------------+
| PLUGIN_NAME | PLUGIN_VERSION | PLUGIN_STATUS | PLUGIN_TYPE | PLUGIN_TYPE_VERSION | PLUGIN_LIBRARY | PLUGIN_LIBRARY_VERSION | PLUGIN_AUTHOR | PLUGIN_DESCRIPTION | PLUGIN_LICENSE | LOAD_OPTION |
+-------------+----------------+---------------+----------------+---------------------+----------------+------------------------+---------------+--------------------------------------+----------------+-------------+
| VP | 0.18 | ACTIVE | STORAGE ENGINE | 50514.0 | ha_vp.so | 1.2 | Kentoku Shiba | Vertical Partitioning Storage Engine | GPL | ON |
+-------------+----------------+---------------+----------------+---------------------+----------------+------------------------+---------------+--------------------------------------+----------------+-------------+

mysql> SELECT * FROM mysql.func WHERE dl = 'ha_vp.so';
+----------------+-----+----------+----------+
| name | ret | dl | type |
+----------------+-----+----------+----------+
| vp_copy_tables | 2 | ha_vp.so | function |
+----------------+-----+----------+----------+
[/sourcecode]

Handler Socketのインストール

cd /usr/local/mysql
./bin/mysql -u root < ./scripts/install_handlersocket.sql

インストール確認
[sourcecode language="plain"]
mysql> SELECT * FROM INFORMATION_SCHEMA.plugins WHERE PLUGIN_NAME = 'handlersocket';
+---------------+----------------+---------------+-------------+---------------------+------------------+------------------------+----------------------------------+--------------------+----------------+-------------+
| PLUGIN_NAME | PLUGIN_VERSION | PLUGIN_STATUS | PLUGIN_TYPE | PLUGIN_TYPE_VERSION | PLUGIN_LIBRARY | PLUGIN_LIBRARY_VERSION | PLUGIN_AUTHOR | PLUGIN_DESCRIPTION | PLUGIN_LICENSE | LOAD_OPTION |
+---------------+----------------+---------------+-------------+---------------------+------------------+------------------------+----------------------------------+--------------------+----------------+-------------+
| handlersocket | 1.2 | ACTIVE | DAEMON | 50514.0 | handlersocket.so | 1.2 | higuchi dot akira at dena dot jp | | BSD | ON |
+---------------+----------------+---------------+-------------+---------------------+------------------+------------------------+----------------------------------+--------------------+----------------+-------------+
[/sourcecode]

CREATE SERVERによりデータノードとなるMySQLサーバを登録

CREATE SERVER `db000` FOREIGN DATA WRAPPER mysql OPTIONS (USER 'mysql', PASSWORD '***', HOST '192.168.22.6', PORT 3306);
CREATE SERVER `db001` FOREIGN DATA WRAPPER mysql OPTIONS (USER 'mysql', PASSWORD '***', HOST '192.168.22.7', PORT 3306);
CREATE SERVER `db002` FOREIGN DATA WRAPPER mysql OPTIONS (USER 'mysql', PASSWORD '***', HOST '192.168.22.8', PORT 3306);

実データを保存するMySQKサーバ側

CREATE TABLE `spider-sample`.`sample` (  
  `id` int(11) NOT NULL,  
  `store` int(11) NOT NULL, 
  `test` varchar(32) DEFAULT NULL, 
  PRIMARY KEY (id,store)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Spider

CREATE TABLE `spider-sample`.`sample` (  
  `id` int(11) NOT NULL,  
  `store` int(11) NOT NULL, 
  `test` varchar(32) DEFAULT NULL, 
  PRIMARY KEY (id,store)
) ENGINE=Spider DEFAULT CHARSET=utf8
PARTITION BY HASH(store) (
  PARTITION p0 COMMENT 'server "db000", table "sample"',
  PARTITION p1 COMMENT 'server "db001", table "sample"',
  PARTITION p2 COMMENT 'server "db002", table "sample"'
);  

Spiderでincrement設定

SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+

Spiderでインサートする場合に複数台使用する場合に
上記数値を調整する

spider000

SET auto_increment_increment=3
SET auto_increment_offset=1

spider001

SET auto_increment_increment=3
SET auto_increment_offset=2

spider002

SET auto_increment_increment=3
SET auto_increment_offset=3

spiderが3台構成の例


権限エラー

Access denied; you need (at least one of) the SUPER privilege(s) for this operation

ログ設定を統一する

show variables like 'spider_internal_sql_log_off';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| spider_internal_sql_log_off | ON    |
+-----------------------------+-------+

show variables like 'spider_remote_sql_log_off';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| spider_remote_sql_log_off | -1    |
+---------------------------+-------+

SET GLOBAL spider_remote_sql_log_off = 1;

show variables like 'spider_remote_sql_log_off';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| spider_remote_sql_log_off | 1     |
+---------------------------+-------+

RDSをデータ保存用のMySQLにする場合は上記設定が必要になるようです。

Leave a reply