WordPress を MySQL Cluster ( ndbcluster engine ) 環境で使う方法

ご覧いただきありがとうございます。題名にあるように、このブログは MySQL Cluster 7.1.5a ( ndbcluster engine ) 上に WordPress v3.2.1 のテーブルを保存し動作させています。 httpd には apache ではなく lighttpd を利用してみました。 Google で検索すると、MySQL Cluster について書かれた WordPress サイトはヒットするのですが、  WordPress を MySQL Cluster 上で利用している人の情報が見つからなからなかったのでその手順をご参考までに公開してみたいと思います。大まかな流れは以下のとおりです。

  1. MySQL Cluster 上に WordPress 用のデータベースとユーザを作成
  2. Web サーバ上に WordPress 日本語版を展開
  3. lighttpd.conf の作成
  4. WordPress 初期設定でテーブルを作成
  5. 各テーブルを ndbcluster engine に変更
  6. SQLノードでの動作確認

構成:

  • WordPress v3.2.1 日本語版
  • MySQL Cluster v7.1.5a
  • lighttpd v1.4.28

MySQL Cluster 上に WordPress 用のデータベースとユーザを作成
この例では blog.neoflow.jp という名前でDBを作成し、10.1.1.0/24 から wp というユーザでの接続を許可しています。
接続ユーザ wp の情報は mysql.user に保存されるので各SQLノードで登録しておくほうがよいと思われます。

[[email protected] my_cluster]$ mysql -h 127.0.0.1 -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.56-ndb-7.1.15a-cluster-gpl MySQL Cluster Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database `blog.neoflow.jp`;
Query OK, 1 row affected (0.02 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| blog.neoflow.jp    |
| mysql              |
| ndbinfo            |
| test               |
+--------------------+
5 rows in set (0.00 sec)
mysql> grant all privileges on `blog.neoflow.jp`.* to 'wp'@'10.1.1.0/255.255.255.0' identified by 'password' with grant option;
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host,password from mysql.user;
+------+-------------------------------+-------------------------------------------+
| user | host                          | password                                  |
+------+-------------------------------+-------------------------------------------+
| root | localhost                     |                                           |
| root | 127.0.0.1                     |                                           |
| root | ::1                           |                                           |
|      | localhost                     |                                           |
| wp   | 10.1.1.0/255.255.255.0        | *12EC34D567890E1B23C4567E8AEA9E01C2345CE6 |
+------+-------------------------------+-------------------------------------------+
5 rows in set (0.01 sec)

mysql> quit
Bye
[[email protected] ~]$ mysql -h 127.0.0.1 -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.56-ndb-7.1.15a-cluster-gpl MySQL Cluster Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> grant all privileges on `blog.neoflow.jp`.* to 'wp'@'10.1.1.0/255.255.255.0' identified by 'password' with grant option;
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host,password from mysql.user;
+------+-------------------------------+-------------------------------------------+
| user | host                          | password                                  |
+------+-------------------------------+-------------------------------------------+
| root | localhost                     |                                           |
| root | 127.0.0.1                     |                                           |
| root | ::1                           |                                           |
|      | localhost                     |                                           |
| wp   | 10.1.1.0/255.255.255.0        | *12EC34D567890E1B23C4567E8AEA9E01C2345CE6 |
+------+-------------------------------+-------------------------------------------+
5 rows in set (0.01 sec)

mysql> quit
Bye

Web サーバ上に WordPress 日本語版を展開
svn + trac で管理しているので展開は svn co していますが、ダウンロードした wordpress-3.2.1-ja.zip を DocumentRoot に展開しているだけで未編集の状態です。

[[email protected] blog.neoflow.jp]# svn co svn+ssh:[email protected]/usr/local/repos/blog.neoflow.jp/trunk/wordpress

lighttpd.conf の作成
普通の設定です。

$HTTP["host"] == "blog.neoflow.jp" {
server.document-root = "/mnt/blog.neoflow.jp/wordpress"
server.errorlog = "/var/log/lighttpd/blog.neoflow.jp_error.log"
accesslog.filename = "/var/log/lighttpd/blog.neoflow.jp_access.log"
fastcgi.server = (
".php" => ((
"socket" => "/tmp/fastcgi-blog.neoflow.jp.sock",
"bin-path" => "/usr/bin/php-cgi",
"max-procs" => 5,
))
)
}

WordPress 初期設定でテーブルを作成
初回アクセスすると設定ファイルの生成とDBの接続情報を入力するだけでテーブルを生成してくれますが、CentOS 5.4 では PHP のバージョンが 5.1.6 と古く、WordPress v3.2.1 を利用するために必要な 5.2.4 以上という要件を満たしていなかったので「サーバーの PHP バージョンは 5.1.6 ですが WordPress 3.2.1 は 5.2.4 以上のみでご利用になれます。」とのメッセージが表示されました。また、 php-mysql パッケージもインストールしておかないと「お使いのサーバーの PHP では WordPress に必要な MySQL 拡張を利用できないようです。」とのメッセージが表示されましたので以下の対策を行いました。 php -v , php-cgi -v などであらかじめチェックしておくと良いと思われます。

[[email protected] ~]# cd /etc/yum.repos.d/
[[email protected] yum.repos.d]# wget http://dev.centos.org/centos/5/CentOS-Testing.repo
[[email protected] yum.repos.d]# cat CentOS-Testing.repo
[c5-testing]
name=CentOS-5 Testing
baseurl=http://dev.centos.org/centos/$releasever/testing/$basearch/
enabled=0
gpgcheck=1
gpgkey=http://dev.centos.org/centos/RPM-GPG-KEY-CentOS-testing

# CentOS-Testing:
# !!!! CAUTION !!!!
# This repository is a proving grounds for packages on their way to CentOSPlus and CentOS Extras.
# They may or may not replace core CentOS packages, and are not guaranteed to function properly.
# These packages build and install, but are waiting for feedback from testers as to
# functionality and stability. Packages in this repository will come and go during the
# development period, so it should not be left enabled or used on production systems without due
# consideration.

[[email protected] yum.repos.d]# echo "priority=1" >> CentOS-Testing.repo
[[email protected] yum.repos.d]# cat CentOS-Testing.repo
[c5-testing]
name=CentOS-5 Testing
baseurl=http://dev.centos.org/centos/$releasever/testing/$basearch/
enabled=0
gpgcheck=1
gpgkey=http://dev.centos.org/centos/RPM-GPG-KEY-CentOS-testing

# CentOS-Testing:
# !!!! CAUTION !!!!
# This repository is a proving grounds for packages on their way to CentOSPlus and CentOS Extras.
# They may or may not replace core CentOS packages, and are not guaranteed to function properly.
# These packages build and install, but are waiting for feedback from testers as to
# functionality and stability. Packages in this repository will come and go during the
# development period, so it should not be left enabled or used on production systems without due
# consideration.
priority=1
[[email protected] yum.repos.d]# yum --enablerepo=c5-testing update php php-mysql

各テーブルを ndbcluster engine に変更
ここが肝になります。11個のテーブルが生成されています。ただしこの状態だとSQLノード上のInnoDBに作成されただけなので、ndbcluster エンジンを利用するように変更します。
以下のクエリを実行しています。

alter table wp_commentmeta engine=ndb;
alter table wp_comments engine=ndb;
alter table wp_links engine=ndb;
alter table wp_options engine=ndb;
alter table wp_postmeta engine=ndb;
alter table wp_posts engine=ndb;
alter table wp_term_relationships engine=ndb;
alter table wp_term_taxonomy engine=ndb;
alter table wp_terms engine=ndb;
alter table wp_usermeta engine=ndb;
alter table wp_users engine=ndb;
[[email protected] my_cluster]$ mysql -h 127.0.0.1 -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 35
Server version: 5.1.56-ndb-7.1.15a-cluster-gpl MySQL Cluster Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use blog.neoflow.jp
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------------------+
| Tables_in_blog.neoflow.jp |
+---------------------------+
| wp_commentmeta            |
| wp_comments               |
| wp_links                  |
| wp_options                |
| wp_postmeta               |
| wp_posts                  |
| wp_term_relationships     |
| wp_term_taxonomy          |
| wp_terms                  |
| wp_usermeta               |
| wp_users                  |
+---------------------------+
11 rows in set (0.00 sec)

mysql> show engines;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                        | Transactions | XA   | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ndbcluster | YES     | Clustered, fault-tolerant tables                               | YES          | NO   | NO         |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| ndbinfo    | YES     | MySQL Cluster system information storage engine                | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED  | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO   | NO         |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
10 rows in set (0.00 sec)

mysql> alter table wp_commentmeta engine=ndb;
Query OK, 0 rows affected (0.30 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table wp_comments engine=ndb;
Query OK, 1 row affected (0.38 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> alter table wp_links engine=ndb;
Query OK, 7 rows affected (0.30 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> alter table wp_options engine=ndb;
Query OK, 155 rows affected (0.73 sec)
Records: 155  Duplicates: 0  Warnings: 0

mysql> alter table wp_postmeta engine=ndb;
Query OK, 2 rows affected (0.32 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> alter table wp_posts engine=ndb;
Query OK, 3 rows affected (0.46 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> alter table wp_term_relationships engine=ndb;
Query OK, 8 rows affected (0.20 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> alter table wp_term_taxonomy engine=ndb;
Query OK, 2 rows affected (0.33 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> alter table wp_terms engine=ndb;
Query OK, 2 rows affected (0.26 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> alter table wp_usermeta engine=ndb;
Query OK, 18 rows affected (0.30 sec)
Records: 18  Duplicates: 0  Warnings: 0

mysql> alter table wp_users engine=ndb;
Query OK, 1 row affected (0.28 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> quit
Bye
[[email protected] my_cluster]$

SQLノードでの動作確認
show tables; を他のSQLノードで実行した場合、Empty set (0.03 sec) と返されていたのが、alter table テーブル名 engine=ndb; を実施したことで参照できるようになることを確認します。

[[email protected] ~]$ mysql -h 127.0.0.1 -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.56-ndb-7.1.15a-cluster-gpl MySQL Cluster Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use blog.neoflow.jp
Database changed
mysql> show tables;
Empty set (0.03 sec)

mysql> show tables;
+---------------------------+
| Tables_in_blog.neoflow.jp |
+---------------------------+
| wp_commentmeta            |
| wp_comments               |
| wp_links                  |
| wp_options                |
| wp_postmeta               |
| wp_posts                  |
| wp_term_relationships     |
| wp_term_taxonomy          |
| wp_terms                  |
| wp_usermeta               |
| wp_users                  |
+---------------------------+
11 rows in set (0.01 sec)

mysql> quit
Bye
スポンサーリンク

コメント

  1. Thank you, I have recently been searching for info about this subject for ages and yours is the greatest I’ve discovered till now. But, what about the conclusion? Are you sure about the source?