KUSANAGIはDBとして、MariaDB10.xとPostgresqlを用意しています。通常クラスタを組むときはGalera Clusterを使用します。今回は、MySQL8+InnoDB Cluster+MySQL Router の組み合わせでWordPressが使用できるか、試してみたいと思います。
管理者・編集者用のWordPressではRW用、配信専用のWordPressではRO用のポートを使用することで、DB書き込み不可の堅牢なソリューションとして使用することも考えられます。現在は、上記のように手間がかかりますが、選択肢の1つとして考えられるのではないでしょうか。 では、次回をお楽しみに。
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つとして考えられるのではないでしょうか。 では、次回をお楽しみに。
