Create function時のエラー

This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)
SET GLOBAL log_bin_trust_function_creators = 'ON';

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

漢(オトコ)のコンピュータ道: 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にする場合は上記設定が必要になるようです。

MySQLのインポート・エクスポート

テーブルごとにダンプ

mysqldump --quick \
          --single-transaction \
          --add-locks \
          --no-autocommit \
          --user=mysql \
          --password=passwd \
          --host=localhost \
          --default-character-set=utf8 \
          smpldb \
          smpltbl > ./tbldump.sql

CSVファイル出力

SELECT * FROM `smpltbl` INTO OUTFILE "/var/tmp/smpltbl.csv" FIELDS TERMINATED BY ',';

CSVファイル入力

LOAD DATA LOCAL INFILE "/var/tmp/smpltbl.csv" INTO TABLE `smpltbl` FIELDS TERMINATED BY ',';

バイナリログの変換

mysqlbinlog /var/lib/mysql/groonga.log > /tmp/groonga.sql

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

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

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 http://ftp.iij.ad.jp/pub/db/mysql/Downloads/MySQL-5.5/mysql-5.5.28.tar.gz
sudo tar xzvf mysql-5.5.28.tar.gz
cd mysql-5.5.28

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 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

[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

全権限ユーザーの作成

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やMroongaなどコンパイルが必要になる時いつも忘れるので