KUSANAGIでMySQL8のInnoDB Clusterを使ってみよう（宮崎悟氏）

KUSANAGI TIPS

宮﨑悟

KUSANAGIはDBとしてMariaDBとPostgresqlを用意しており、MySQL8+InnoDB Cluster+MySQL Routerの組み合わせを試した結果、MySQL InnoDB Clusterを使用することで自動的にRWおよびROの入力を分散して接続することが可能になりました。さらにこれにより、通常のphp-5.6でなくphp7を指定してWordPressの設定を行うことも可能になりました。このソリューションは、DBを分散して使用するには有効な手段として語られています。

KUSANAGIはDBとして、MariaDB10.xとPostgresqlを用意しています。通常クラスタを組むときはGalera Clusterを使用します。今回は、MySQL8+InnoDB Cluster+MySQL Router の組み合わせでWordPressが使用できるか、試してみたいと思います。

Vagrant イメージの作成

InnoDB クラスタはDBが3台必要です。1台はWordPress用途共用するとして、2台分を用意します。 今回はVagrantのKUSANAGIイメージを使用します。mysql1( 192.168.33.17)、mysql2(192.168.33.16)を用意しますが、shell で起動する部分に以下のように書いてください。DBROOTPASSは全体で共通としています。mysql2では、ホスト名(mysql1)の部分をmysql2に変更してください。 shell script内で、MariaDBを削除して、mysql8 のインストールとroot権限ユーザを作成しています。また、設定ファイルmy.cnfへ、「default_authentication_plugin=mysql_native_password」で、defaultのパスワードをmysql5.x 互換にしていることもポイントです。 
  config.vm.provision "shell", inline: <<-SHELL
    DBROOTPASS="8F3.xFh7oigys"
    sudo yum clean all
    sudo yum -y --enablerepo=remi,remi-php56 update  2>&1 > /dev/null
    sudo yum erase -y MariaDB-server MariaDB-common MariaDB-client MariaDB-devel MariaDB-shared
    sudo rm -rf /var/lib/mysql
    sudo yum install -y https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm
    sudo yum install -y mysql-community-server
    echo default_authentication_plugin=mysql_native_password | sudo tee -a /etc/my.cnf
    sudo hostname mysql1
    echo mysql1 | sudo tee /etc/hostname
    echo 192.168.33.18 kusanagi71 | sudo tee -a /etc/hosts
    echo 192.168.33.17 mysql1 | sudo tee -a /etc/hosts
    echo 192.168.33.16 mysql2 | sudo tee -a /etc/hosts
    sudo systemctl start mysqld
    OLDPASS=$(grep 'temporary password' /var/log/mysqld.log|awk '{print $13}')
    echo "ALTER USER root@localhost IDENTIFIED BY '$DBROOTPASS'" | mysql -uroot -p"$OLDPASS" --connect-expired-password
    echo "CREATE USER 'root'@'192.168.33.%' IDENTIFIED BY '$DBROOTPASS'" | mysql -uroot -p"$DBROOTPASS"
    echo "GRANT ALL on *.* TO 'root'@'192.168.33.%' WITH GRANT OPTION" | mysql -uroot -p"$DBROOTPASS"
  SHELL
WordPressを起動するkusanagi71(192.168.33.18)は、以下のように記述します。ここで、kusanagi provision まで済ませておきます。 
  config.vm.provision "shell", inline: <<-SHELL
    DBROOTPASS="8F3.xFh7oigys"
    MKPASSWD=/usr/bin/mkpasswd
    FQDN=kusanagi.local
    KUSANAGI_PASS=$($MKPASSWD -l 20)
    DBNAME=$($MKPASSWD -l 10 -s 0)
    DBUSER=$($MKPASSWD -l 10 -s 0)
    DBPASS=%$($MKPASSWD -l 20 -s 0)
    ADMINUSER=$($MKPASSWD -l 10 -s 0)
    ADMINPASS=$($MKPASSWD -l 20 -s 0)
    ADMINMAIL="${ADMINUSER}@${FQDN}"
    PROF=kusanagi
    export LC_ALL=C

    #sudo yum clean all
    sudo yum -y --enablerepo=remi,remi-php56 update  2>&1 > /dev/null
    yes y | sudo kusanagi init --tz tokyo --lang en --keyboard en --passwd ${KUSANAGI_PASS} \
     --nophrase --dbrootpass ${DBROOTPASS} \
     --nginx --php7 --dbsystem mariadb --ruby24
    sudo kusanagi provision --wordpress --wplang ja --fqdn $FQDN --no-email \
     --dbname $DBNAME --dbuser $DBUSER --dbpass $DBPASS $PROF

    sudo yum erase -y MariaDB-server MariaDB-common MariaDB-client MariaDB-devel MariaDB-shared
    sudo rm -rf /var/lib/mysql
    sudo yum install -y https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm
    sudo yum install -y mysql-community-server
    echo default_authentication_plugin=mysql_native_password | sudo tee -a /etc/my.cnf
    echo 192.168.33.18 kusanagi71 | sudo tee -a /etc/hosts
    echo 192.168.33.17 mysql1 | sudo tee -a /etc/hosts
    echo 192.168.33.16 mysql2 | sudo tee -a /etc/hosts
    sudo systemctl start mysqld
    OLDPASS=$(grep 'temporary password' /var/log/mysqld.log|awk '{print $13}')
    echo "ALTER USER root@localhost IDENTIFIED BY '$DBROOTPASS'" | mysql -uroot -p"$OLDPASS" --connect-expired-password
    echo "CREATE USER 'root'@'192.168.33.%' IDENTIFIED BY '$DBROOTPASS'" | mysql -uroot -p"$DBROOTPASS"
    echo "GRANT ALL on *.* TO 'root'@'192.168.33.%' WITH GRANT OPTION" | mysql -uroot -p"$DBROOTPASS"
    sudo yum install -y mysql-shell

    echo DBROOTPASS=\"$DBROOTPASS\" >> pass
    echo KUSANAGI_PASS=\"$KUSANAGI_PASS\" >> pass
    echo DBNAME=$DBNAME >> pass
    echo DBUSER=$DBUSER >> pass
    echo DBPASS=\"$DBPASS\" >> pass
    echo FQDN=$FQDN >> pass
    echo PROF=$PROF >> pass
    echo ADMINUSER=$ADMINUSER >> pass
    echo ADMINPASS=$ADMINPASS >> pass
    echo ADMINMAIL=$ADMINMAIL >> pass
  SHELL
3台分のVagrantイメージを、それぞれvagrant.exe up で作成してください。

InnoDB Clusterの作成

WordPressを起動するkusanagi71で、以降の作業を実施します。 まずmysqlsh で、各DBの設定をクラスタ用に変更します。 dba.checkInstanceConfiguration()で確認後、dba.configureInstance()で3台のDBを初期化しています。passwordを聞かれますが、すべて変数DBPASSWDに指定した値を入力(ペースト)してください。Vagrantで、設定パラメータを「pass」ファイルに記述しているので、sourceで読み込んでおきます。 
$ source pass
$ echo $DBROOTPASS
8F3.xFh7oigys
$ mysqlsh
MySQL Shell 8.0.13

Copyright (c) 2016, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type '\help' or '\?' for help; '\quit' to exit.


 MySQL  JS > dba.checkInstanceConfiguration('root@192.168.33.18')
Please provide the password for 'root@192.168.33.18': *********************
Save password for 'root@192.168.33.18'? [Y]es/[N]o/Ne[v]er (default No):
Validating local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as kusanagi71
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system
 variable should be changed.

Checking whether existing tables comply with Group Replication requirements...
No incompatible tables detected

Checking instance configuration...

Some configuration options need to be fixed:
+--------------------------+---------------+----------------+--------------------------------------------------+
| Variable                 | Current Value | Required Value | Note                                             |
+--------------------------+---------------+----------------+--------------------------------------------------+
| binlog_checksum          | CRC32         | NONE           | Update the server variable                       |
| enforce_gtid_consistency | OFF           | ON             | Update read-only variable and restart the server |
| gtid_mode                | OFF           | ON             | Update read-only variable and restart the server |
| server_id                | 1             | <unique ID>    | Update read-only variable and restart the server |
+--------------------------+---------------+----------------+--------------------------------------------------+

Please use the dba.configureInstance() command to repair these issues.

{
    "config_errors": [
        {
            "action": "server_update",
            "current": "CRC32",
            "option": "binlog_checksum",
            "required": "NONE"
        },
        {
            "action": "restart",
            "current": "OFF",
            "option": "enforce_gtid_consistency",
            "required": "ON"
        },
        {
            "action": "restart",
            "current": "OFF",
            "option": "gtid_mode",
            "required": "ON"
        },
        {
            "action": "restart",
            "current": "1",
            "option": "server_id",
            "required": "<unique ID>"
        }
    ],
    "errors": [],
    "status": "error"
}

 MySQL  JS > dba.configureInstance('root@192.168.33.18')
Please provide the password for 'root@192.168.33.18': *********************
Save password for 'root@192.168.33.18'? [Y]es/[N]o/Ne[v]er (default No):
Configuring local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as kusanagi71
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system
 variable should be changed.

Some configuration options need to be fixed:
+--------------------------+---------------+----------------+--------------------------------------------------+
| Variable                 | Current Value | Required Value | Note                                             |
+--------------------------+---------------+----------------+--------------------------------------------------+
| binlog_checksum          | CRC32         | NONE           | Update the server variable                       |
| enforce_gtid_consistency | OFF           | ON             | Update read-only variable and restart the server |
| gtid_mode                | OFF           | ON             | Update read-only variable and restart the server |
| server_id                | 1             | <unique ID>    | Update read-only variable and restart the server |
+--------------------------+---------------+----------------+--------------------------------------------------+

Do you want to perform the required configuration changes? [y/n]: y
Do you want to restart the instance after configuring it? [y/n]: y
Configuring instance...
The instance '192.168.33.18:3306' was configured for cluster usage.
Restarting MySQL...
MySQL server at 192.168.33.18:3306 was restarted.

 MySQL  JS > dba.configureInstance('root@192.168.33.17')
Please provide the password for 'root@192.168.33.17': *********************
Save password for 'root@192.168.33.17'? [Y]es/[N]o/Ne[v]er (default No): y
Configuring MySQL instance at 192.168.33.17:3306 for use in an InnoDB cluster...

This instance reports its own address as kusanagi71
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system
 variable should be changed.

Some configuration options need to be fixed:
+--------------------------+---------------+----------------+--------------------------------------------------+
| Variable                 | Current Value | Required Value | Note                                             |
+--------------------------+---------------+----------------+--------------------------------------------------+
| binlog_checksum          | CRC32         | NONE           | Update the server variable                       |
| enforce_gtid_consistency | OFF           | ON             | Update read-only variable and restart the server |
| gtid_mode                | OFF           | ON             | Update read-only variable and restart the server |
| server_id                | 1             | <unique ID>    | Update read-only variable and restart the server |
+--------------------------+---------------+----------------+--------------------------------------------------+

Do you want to perform the required configuration changes? [y/n]: y
Do you want to restart the instance after configuring it? [y/n]: y
Configuring instance...
The instance '192.168.33.17:3306' was configured for cluster usage.
Restarting MySQL...
MySQL server at 192.168.33.17:3306 was restarted.

 MySQL  JS > dba.configureInstance('root@192.168.33.16')
Please provide the password for 'root@192.168.33.16': *********************
Save password for 'root@192.168.33.16'? [Y]es/[N]o/Ne[v]er (default No): y
Configuring MySQL instance at 192.168.33.16:3306 for use in an InnoDB cluster...

This instance reports its own address as kusanagi71
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system
 variable should be changed.

Some configuration options need to be fixed:
+--------------------------+---------------+----------------+--------------------------------------------------+
| Variable                 | Current Value | Required Value | Note                                             |
+--------------------------+---------------+----------------+--------------------------------------------------+
| binlog_checksum          | CRC32         | NONE           | Update the server variable                       |
| enforce_gtid_consistency | OFF           | ON             | Update read-only variable and restart the server |
| gtid_mode                | OFF           | ON             | Update read-only variable and restart the server |
| server_id                | 1             | <unique ID>    | Update read-only variable and restart the server |
+--------------------------+---------------+----------------+--------------------------------------------------+

Do you want to perform the required configuration changes? [y/n]: y
Do you want to restart the instance after configuring it? [y/n]: y
Configuring instance...
The instance '192.168.33.16:3306' was configured for cluster usage.
Restarting MySQL...
MySQL server at 192.168.33.16:3306 was restarted.
次に、クラスタを生成します。 root@192.168.33.18 に接続後、dba.createCluster()でクラスタ作成後、cluster.addInstance()でインスタンスを追加します。このとき、最初に作成した192.168.33.18がPRIMARY(RW可能なノード)となり、192.168.33.17、192.168.33.16がreadReplicas(ROノード)となります。作成した時点ではReplica側のstatusが「RECOVERING」となっていますが、しばらくすると同期完了して「ONLINE」となります。 ステータスを確認したら、Ctrl-Dを押してmysqlsh を抜けます。 
 MySQL  JS > \c root@192.168.33.18
Creating a session to 'root@192.168.33.18'
Please provide the password for 'root@192.168.33.18': *********************
Save password for 'root@192.168.33.18'? [Y]es/[N]o/Ne[v]er (default No):
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 8 (X protocol)
Server version: 8.0.13 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.

 MySQL  192.168.33.18:33060+ ssl  JS > cluster = dba.createCluster('mycluster')
A new InnoDB cluster will be created on instance 'root@192.168.33.18:3306'.

Validating instance at 192.168.33.18:3306...

This instance reports its own address as kusanagi71

Instance configuration is suitable.
Creating InnoDB cluster 'mycluster' on 'root@192.168.33.18:3306'...
Adding Seed Instance...

Cluster successfully created. Use Cluster.addInstance() to add MySQL instances.
At least 3 instances are needed for the cluster to be able to withstand up to
one server failure.

<Cluster:mycluster>

 MySQL  192.168.33.18:33060+ ssl  JS > cluster.addInstance('root@192.168.33.17')
A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.

Adding instance to the cluster ...

Validating instance at 192.168.33.17:3306...

This instance reports its own address as kusanagi71

Instance configuration is suitable.
The instance 'root@192.168.33.17' was successfully added to the cluster.


 MySQL  192.168.33.18:33060+ ssl  JS > cluster.addInstance('root@192.168.33.16')
A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.

Adding instance to the cluster ...

Validating instance at 192.168.33.16:3306...

This instance reports its own address as kusanagi71

Instance configuration is suitable.
The instance 'root@192.168.33.16' was successfully added to the cluster.

 MySQL  192.168.33.18:33060+ ssl  JS > cluster.status()
{
    "clusterName": "mycluster",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "192.168.33.18:3306",
        "ssl": "REQUIRED",
        "status": "OK_NO_TOLERANCE",
        "statusText": "Cluster is NOT tolerant to any failures. 2 members are not active",
        "topology": {
            "192.168.33.16:3306": {
                "address": "192.168.33.16:3306",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "RECOVERING"
            },
            "192.168.33.17:3306": {
                "address": "192.168.33.17:3306",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "RECOVERING"
            },
            "192.168.33.18:3306": {
                "address": "192.168.33.18:3306",
                "mode": "R/W",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            }
        }
    },
    "groupInformationSourceMember": "mysql://root@192.168.33.18:3306"
}


 MySQL  192.168.33.18:33060+ ssl  JS >  cluster.status()
{
    "clusterName": "mycluster",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "192.168.33.18:3306",
        "ssl": "REQUIRED",
        "status": "OK",
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
        "topology": {
            "192.168.33.16:3306": {
                "address": "192.168.33.16:3306",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            },
            "192.168.33.17:3306": {
                "address": "192.168.33.17:3306",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            },
            "192.168.33.18:3306": {
                "address": "192.168.33.18:3306",
                "mode": "R/W",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            }
        }
    },
    "groupInformationSourceMember": "mysql://root@192.168.33.18:3306"
}


 MySQL  192.168.33.18:33060+ ssl  JS >
Bye!

MySQL Router の設定

MySQL InnoDB Clusterは、MySQL Routerを使用することで自動的にRWおよびROの入力を分散して接続します。 以下のようにして、mysql-routerを作成し設定します。 
$ sudo yum install -y mysql-router
$ sudo mysqlrouter --bootstrap 'root'@'192.168.33.18':3306 --user=mysqlrouter
Please enter MySQL password for root:

Bootstrapping system MySQL Router instance...
Checking for old Router accounts
Creating account mysql_router1_ltuti33keoyl@'%'
MySQL Router  has now been configured for the InnoDB cluster 'mycluster'.

The following connection information can be used to connect to the cluster after MySQL Router has been started with generated configuration..

Classic MySQL protocol connections to cluster 'mycluster':
- Read/Write Connections: localhost:6446
- Read/Only Connections: localhost:6447
X protocol connections to cluster 'mycluster':
- Read/Write Connections: localhost:64460
- Read/Only Connections: localhost:64470


$ sudo systemctl enable mysqlrouter
$ sudo systemctl start mysqlrouter
$ systemctl status mysqlrouter
* mysqlrouter.service - MySQL Router
   Loaded: loaded (/usr/lib/systemd/system/mysqlrouter.service; enabled; vendor preset: disabled)
   Active: active (running) since Sun 2019-01-06 17:46:29 JST; 4s ago
 Main PID: 32433 (main)
   CGroup: /system.slice/mysqlrouter.service
           `-32433 /usr/bin/mysqlrouter -c /etc/mysqlrouter/mysqlrouter.conf
これで、R/W接続は、192.168.33.18もしくはlocalhostの6446番ポート、RO接続は6447番ポートで行えるようになります。

WordPressの設定

KUSANAGI のprovisionまでは設定していますが、DBを削除したので再作成します。 
$ echo "CREATE DATABASE IF NOT EXISTS \`$DBNAME\`" | mysql -uroot -p"$DBROOTPASS" -P6446
$ echo "create user '$DBUSER'@'$HOSTNAME' IDENTIFIED BY '$DBPASS'" | mysql -uroot -p"$DBROOTPASS" -P6446
$ echo "GRANT ALL PRIVILEGES ON \`$DBNAME\`.* TO '$DBUSER'@'$HOSTNAME'" | mysql -uroot -p"$DBROOTPASS" -P6446
これで、DBの作成とユーザ設定ができたので、WordPressの設定をwpコマンドで行います。ここで気をつけないといけないのは、通常wpコマンドはphp-5.6で実行されるので、php7 を指定して以下のように実行してください。 
$ sudo -u kusanagi -- php7 /usr/local/bin/wp core config --dbhost=localhost:6446 \
 --dbname=${DBNAME} --db--dbuser=$DBUSER --dbpass=$DBPASS --dbcharset=utf-8mb4 \
 --extra-php --path=/home/kusanagi/${PROF}/DocumentRoot \
 < /usr/lib/kusanagi/resource/wp-config-sample/ja/wp-config-extra.php
$ sudo -u kusanagi -- php7 /usr/local/bin/wp core install --url=http://${FQDN}/ \
 --title=test --admin_user=$ADMINUSER --admin_password=$ADMINPASS \
 --admin_email=$ADMINMAIL --path=/home/kusanagi/${PROF}/DocumentRoot
これで、hostsファイルなどに 「kusanagi.local 192.168.33.18」と記述してから、Webブラウザで「http://kusanagi.local」へアクセスすれば、WordPressの画面が表示されるはずです。

WordPressとMySQL8

現在のPHP7.3では標準でMySQL8の認証形式に対応していないことから、MySQL8は使われることが少ないですが、InnoDBクラスタはマルチマスターではないものの読み込みが多い条件ではDBを分散して使用できるので、面白いソリューションだと思います。
管理者・編集者用のWordPressではRW用、配信専用のWordPressではRO用のポートを使用することで、DB書き込み不可の堅牢なソリューションとして使用することも考えられます。現在は、上記のように手間がかかりますが、選択肢の1つとして考えられるのではないでしょうか。 では、次回をお楽しみに。

RedmineをKUSANAGIで動かす！その2(宮崎悟氏)

by 宮﨑悟

PHPの最新状況：PHP 7.4開発が本格化(廣川類氏)

by 宮﨑悟

関連記事

Webサイト運用の課題解決事例100選 プレゼント

Webサイト運用の課題を弊社プロダクトで解決したお客様にインタビュー取材を行い、100の事例を108ページに及ぶ事例集としてまとめました。

・100事例のWebサイト運用の課題と解決手法、解決後の直接、間接的効果がわかる

・情報通信、 IT、金融、メディア、官公庁、学校などの業種ごとに事例を確認できる

・特集では１社の事例を3ページに渡り背景からシステム構成まで詳解

Webサイト運用 課題解決事例100選 プレゼント