1. Spiderを使ってみる

このセクションでは、3 つのデータベースを作成した 1台の MariaDB サーバー上で Spider を使ってデータベースを水平分散（シャーディング）する方法を紹介します。

古いバージョンの MariaDB 削除

CentOS に既にインストールされている MariaDB のバージョンを確認します。

[root@spider-1 ~]# rpm -qa | grep -i "mariadb"
mariadb-libs-5.5.60-1.el7_5.x86_64
[root@spider-1 ~]#

バージョン 10.3.7 以降の MariaDB で Spider が使用できるため、10.3.7 以前のバージョンの MariaDB がインストールされている場合には、既存の MariaDB をアンインストールします。

[root@spider-1 ~]# yum remove mariadb mariadb-libs
Loaded plugins: fastestmirror
No Match for argument: mariadb
Resolving Dependencies
--> Running transaction check
---> Package mariadb-libs.x86_64 1:5.5.60-1.el7_5 will be erased
--> Processing Dependency: libmysqlclient.so.18()(64bit) for package: 2:postfix-2.10.1-6.el7.x86_64
--> Processing Dependency: libmysqlclient.so.18(libmysqlclient_18)(64bit) for package: 2:postfix-2.10.1-6.el7.x86_64
--> Running transaction check
---> Package postfix.x86_64 2:2.10.1-6.el7 will be erased
--> Finished Dependency Resolution

Dependencies Resolved

================================================================================
 Package            Arch         Version                  Repository       Size
================================================================================
Removing:
 mariadb-libs       x86_64       1:5.5.60-1.el7_5         @updates        4.4 M
Removing for dependencies:
 postfix            x86_64       2:2.10.1-6.el7           @anaconda        12 M

Transaction Summary
================================================================================
Remove  1 Package (+1 Dependent package)

Installed size: 17 M
Is this ok [y/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Erasing    : 2:postfix-2.10.1-6.el7.x86_64                                1/2 
  Erasing    : 1:mariadb-libs-5.5.60-1.el7_5.x86_64                         2/2 
  Verifying  : 2:postfix-2.10.1-6.el7.x86_64                                1/2 
  Verifying  : 1:mariadb-libs-5.5.60-1.el7_5.x86_64                         2/2 

Removed:
  mariadb-libs.x86_64 1:5.5.60-1.el7_5                                          

Dependency Removed:
  postfix.x86_64 2:2.10.1-6.el7                                                 

Complete!
[root@spider-1 ~]#

既存の MariaDB がアンインストールされていることを確認します。

[root@spider-1 ~]# rpm -qa | grep -i "mariadb"
[root@spider-1 ~]#

MariaDBのインストール

最新バージョンの MariaDB をインストールするための yum レポジトリを作成します。 MariaDB用のyum レポジトリ設定ファイル（/etc/yum.repos.d/mariadb.repo ）を編集し、設定を追加します。

[root@spider-1 ~]# vi /etc/yum.repos.d/mariadb.repo
（下記の設定を追加）
# MariaDB 10.3 CentOS repository list
# http://mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.3/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
enabled=0

MariaDB の yum レポジトリを使って、必要なパッケージをインストールします。

[root@spider-1 ~]# yum install --enablerepo=mariadb MariaDB-client MariaDB-devel MariaDB-server MariaDB-shared
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: ftp-srv2.kddilabs.jp
 * extras: ftp-srv2.kddilabs.jp
 * updates: ftp-srv2.kddilabs.jp
mariadb                                                  | 2.9 kB     00:00     
mariadb/primary_db                                         |  50 kB   00:01     
Resolving Dependencies
--> Running transaction check
---> Package MariaDB-client.x86_64 0:10.3.10-1.el7.centos will be installed
--> Processing Dependency: perl(Exporter) for package: MariaDB-client-10.3.10-1.el7.centos.x86_64
--> Processing Dependency: perl(Fcntl) for package: MariaDB-client-10.3.10-1.el7.centos.x86_64
--> Processing Dependency: /usr/bin/perl for package: MariaDB-client-10.3.10-1.el7.centos.x86_64
--> Processing Dependency: perl(IPC::Open3) for package: MariaDB-client-10.3.10-1.el7.centos.x86_64
--> Processing Dependency: MariaDB-common for package: MariaDB-client-10.3.10-1.el7.centos.x86_64
--> Processing Dependency: perl(File::Temp) for package: MariaDB-client-10.3.10-1.el7.centos.x86_64
--> Processing Dependency: perl(Getopt::Long) for package: MariaDB-client-10.3.10-1.el7.centos.x86_64
--> Processing Dependency: perl(Sys::Hostname) for package: MariaDB-client-10.3.10-1.el7.centos.x86_64
---> Package MariaDB-devel.x86_64 0:10.3.10-1.el7.centos will be installed
---> Package MariaDB-server.x86_64 0:10.3.10-1.el7.centos will be installed
--> Processing Dependency: rsync for package: MariaDB-server-10.3.10-1.el7.centos.x86_64
--> Processing Dependency: perl(DBI) for package: MariaDB-server-10.3.10-1.el7.centos.x86_64
--> Processing Dependency: perl(File::Path) for package: MariaDB-server-10.3.10-1.el7.centos.x86_64
--> Processing Dependency: galera for package: MariaDB-server-10.3.10-1.el7.centos.x86_64
--> Processing Dependency: lsof for package: MariaDB-server-10.3.10-1.el7.centos.x86_64
--> Processing Dependency: perl(Data::Dumper) for package: MariaDB-server-10.3.10-1.el7.centos.x86_64
---> Package MariaDB-shared.x86_64 0:10.3.10-1.el7.centos will be installed
--> Running transaction check
---> Package MariaDB-common.x86_64 0:10.3.10-1.el7.centos will be installed
--> Processing Dependency: MariaDB-compat for package: MariaDB-common-10.3.10-1.el7.centos.x86_64
---> Package galera.x86_64 0:25.3.24-1.rhel7.el7.centos will be installed
--> Processing Dependency: libboost_program_options.so.1.53.0()(64bit) for package: galera-25.3.24-1.rhel7.el7.centos.x86_64
---> Package lsof.x86_64 0:4.87-5.el7 will be installed
---> Package perl.x86_64 4:5.16.3-292.el7 will be installed
--> Processing Dependency: perl-libs = 4:5.16.3-292.el7 for package: 4:perl-5.16.3-292.el7.x86_64
--> Processing Dependency: perl(Socket) >= 1.3 for package: 4:perl-5.16.3-292.el7.x86_64
--> Processing Dependency: perl(Scalar::Util) >= 1.10 for package: 4:perl-5.16.3-292.el7.x86_64
--> Processing Dependency: perl-macros for package: 4:perl-5.16.3-292.el7.x86_64
--> Processing Dependency: perl-libs for package: 4:perl-5.16.3-292.el7.x86_64
--> Processing Dependency: perl(threads::shared) for package: 4:perl-5.16.3-292.el7.x86_64
--> Processing Dependency: perl(threads) for package: 4:perl-5.16.3-292.el7.x86_64
--> Processing Dependency: perl(constant) for package: 4:perl-5.16.3-292.el7.x86_64
--> Processing Dependency: perl(Time::Local) for package: 4:perl-5.16.3-292.el7.x86_64
--> Processing Dependency: perl(Time::HiRes) for package: 4:perl-5.16.3-292.el7.x86_64
--> Processing Dependency: perl(Storable) for package: 4:perl-5.16.3-292.el7.x86_64
--> Processing Dependency: perl(Socket) for package: 4:perl-5.16.3-292.el7.x86_64
--> Processing Dependency: perl(Scalar::Util) for package: 4:perl-5.16.3-292.el7.x86_64
--> Processing Dependency: perl(Pod::Simple::XHTML) for package: 4:perl-5.16.3-292.el7.x86_64
--> Processing Dependency: perl(Pod::Simple::Search) for package: 4:perl-5.16.3-292.el7.x86_64
--> Processing Dependency: perl(Filter::Util::Call) for package: 4:perl-5.16.3-292.el7.x86_64
--> Processing Dependency: perl(File::Spec::Unix) for package: 4:perl-5.16.3-292.el7.x86_64
--> Processing Dependency: perl(File::Spec::Functions) for package: 4:perl-5.16.3-292.el7.x86_64
--> Processing Dependency: perl(File::Spec) for package: 4:perl-5.16.3-292.el7.x86_64
--> Processing Dependency: perl(Cwd) for package: 4:perl-5.16.3-292.el7.x86_64
--> Processing Dependency: perl(Carp) for package: 4:perl-5.16.3-292.el7.x86_64
--> Processing Dependency: libperl.so()(64bit) for package: 4:perl-5.16.3-292.el7.x86_64
---> Package perl-DBI.x86_64 0:1.627-4.el7 will be installed
--> Processing Dependency: perl(RPC::PlServer) >= 0.2001 for package: perl-DBI-1.627-4.el7.x86_64
--> Processing Dependency: perl(RPC::PlClient) >= 0.2000 for package: perl-DBI-1.627-4.el7.x86_64
---> Package perl-Data-Dumper.x86_64 0:2.145-3.el7 will be installed
---> Package perl-Exporter.noarch 0:5.68-3.el7 will be installed
---> Package perl-File-Path.noarch 0:2.09-2.el7 will be installed
---> Package perl-File-Temp.noarch 0:0.23.01-3.el7 will be installed
---> Package perl-Getopt-Long.noarch 0:2.40-3.el7 will be installed
--> Processing Dependency: perl(Pod::Usage) >= 1.14 for package: perl-Getopt-Long-2.40-3.el7.noarch
--> Processing Dependency: perl(Text::ParseWords) for package: perl-Getopt-Long-2.40-3.el7.noarch
---> Package rsync.x86_64 0:3.1.2-4.el7 will be installed
--> Running transaction check
---> Package MariaDB-compat.x86_64 0:10.3.10-1.el7.centos will be installed
---> Package boost-program-options.x86_64 0:1.53.0-27.el7 will be installed
---> Package perl-Carp.noarch 0:1.26-244.el7 will be installed
---> Package perl-Filter.x86_64 0:1.49-3.el7 will be installed
---> Package perl-PathTools.x86_64 0:3.40-5.el7 will be installed
---> Package perl-PlRPC.noarch 0:0.2020-14.el7 will be installed
--> Processing Dependency: perl(Net::Daemon) >= 0.13 for package: perl-PlRPC-0.2020-14.el7.noarch
--> Processing Dependency: perl(Net::Daemon::Test) for package: perl-PlRPC-0.2020-14.el7.noarch
--> Processing Dependency: perl(Net::Daemon::Log) for package: perl-PlRPC-0.2020-14.el7.noarch
--> Processing Dependency: perl(Compress::Zlib) for package: perl-PlRPC-0.2020-14.el7.noarch
---> Package perl-Pod-Simple.noarch 1:3.28-4.el7 will be installed
--> Processing Dependency: perl(Pod::Escapes) >= 1.04 for package: 1:perl-Pod-Simple-3.28-4.el7.noarch
--> Processing Dependency: perl(Encode) for package: 1:perl-Pod-Simple-3.28-4.el7.noarch
---> Package perl-Pod-Usage.noarch 0:1.63-3.el7 will be installed
--> Processing Dependency: perl(Pod::Text) >= 3.15 for package: perl-Pod-Usage-1.63-3.el7.noarch
--> Processing Dependency: perl-Pod-Perldoc for package: perl-Pod-Usage-1.63-3.el7.noarch
---> Package perl-Scalar-List-Utils.x86_64 0:1.27-248.el7 will be installed
---> Package perl-Socket.x86_64 0:2.010-4.el7 will be installed
---> Package perl-Storable.x86_64 0:2.45-3.el7 will be installed
---> Package perl-Text-ParseWords.noarch 0:3.29-4.el7 will be installed
---> Package perl-Time-HiRes.x86_64 4:1.9725-3.el7 will be installed
---> Package perl-Time-Local.noarch 0:1.2300-2.el7 will be installed
---> Package perl-constant.noarch 0:1.27-2.el7 will be installed
---> Package perl-libs.x86_64 4:5.16.3-292.el7 will be installed
---> Package perl-macros.x86_64 4:5.16.3-292.el7 will be installed
---> Package perl-threads.x86_64 0:1.87-4.el7 will be installed
---> Package perl-threads-shared.x86_64 0:1.43-6.el7 will be installed
--> Running transaction check
---> Package perl-Encode.x86_64 0:2.51-7.el7 will be installed
---> Package perl-IO-Compress.noarch 0:2.061-2.el7 will be installed
--> Processing Dependency: perl(Compress::Raw::Zlib) >= 2.061 for package: perl-IO-Compress-2.061-2.el7.noarch
--> Processing Dependency: perl(Compress::Raw::Bzip2) >= 2.061 for package: perl-IO-Compress-2.061-2.el7.noarch
---> Package perl-Net-Daemon.noarch 0:0.48-5.el7 will be installed
---> Package perl-Pod-Escapes.noarch 1:1.04-292.el7 will be installed
---> Package perl-Pod-Perldoc.noarch 0:3.20-4.el7 will be installed
--> Processing Dependency: perl(parent) for package: perl-Pod-Perldoc-3.20-4.el7.noarch
--> Processing Dependency: perl(HTTP::Tiny) for package: perl-Pod-Perldoc-3.20-4.el7.noarch
---> Package perl-podlators.noarch 0:2.5.1-3.el7 will be installed
--> Running transaction check
---> Package perl-Compress-Raw-Bzip2.x86_64 0:2.061-3.el7 will be installed
---> Package perl-Compress-Raw-Zlib.x86_64 1:2.061-4.el7 will be installed
---> Package perl-HTTP-Tiny.noarch 0:0.033-3.el7 will be installed
---> Package perl-parent.noarch 1:0.225-244.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

================================================================================
 Package                  Arch    Version                        Repository
                                                                           Size
================================================================================
Installing:
 MariaDB-client           x86_64  10.3.10-1.el7.centos           mariadb   53 M
 MariaDB-devel            x86_64  10.3.10-1.el7.centos           mariadb  7.0 M
 MariaDB-server           x86_64  10.3.10-1.el7.centos           mariadb  123 M
 MariaDB-shared           x86_64  10.3.10-1.el7.centos           mariadb  397 k
Installing for dependencies:
 MariaDB-common           x86_64  10.3.10-1.el7.centos           mariadb  157 k
 MariaDB-compat           x86_64  10.3.10-1.el7.centos           mariadb  2.8 M
 boost-program-options    x86_64  1.53.0-27.el7                  base     156 k
 galera                   x86_64  25.3.24-1.rhel7.el7.centos     mariadb  8.1 M
 lsof                     x86_64  4.87-5.el7                     base     331 k
 perl                     x86_64  4:5.16.3-292.el7               base     8.0 M
 perl-Carp                noarch  1.26-244.el7                   base      19 k
 perl-Compress-Raw-Bzip2  x86_64  2.061-3.el7                    base      32 k
 perl-Compress-Raw-Zlib   x86_64  1:2.061-4.el7                  base      57 k
 perl-DBI                 x86_64  1.627-4.el7                    base     802 k
 perl-Data-Dumper         x86_64  2.145-3.el7                    base      47 k
 perl-Encode              x86_64  2.51-7.el7                     base     1.5 M
 perl-Exporter            noarch  5.68-3.el7                     base      28 k
 perl-File-Path           noarch  2.09-2.el7                     base      26 k
 perl-File-Temp           noarch  0.23.01-3.el7                  base      56 k
 perl-Filter              x86_64  1.49-3.el7                     base      76 k
 perl-Getopt-Long         noarch  2.40-3.el7                     base      56 k
 perl-HTTP-Tiny           noarch  0.033-3.el7                    base      38 k
 perl-IO-Compress         noarch  2.061-2.el7                    base     260 k
 perl-Net-Daemon          noarch  0.48-5.el7                     base      51 k
 perl-PathTools           x86_64  3.40-5.el7                     base      82 k
 perl-PlRPC               noarch  0.2020-14.el7                  base      36 k
 perl-Pod-Escapes         noarch  1:1.04-292.el7                 base      51 k
 perl-Pod-Perldoc         noarch  3.20-4.el7                     base      87 k
 perl-Pod-Simple          noarch  1:3.28-4.el7                   base     216 k
 perl-Pod-Usage           noarch  1.63-3.el7                     base      27 k
 perl-Scalar-List-Utils   x86_64  1.27-248.el7                   base      36 k
 perl-Socket              x86_64  2.010-4.el7                    base      49 k
 perl-Storable            x86_64  2.45-3.el7                     base      77 k
 perl-Text-ParseWords     noarch  3.29-4.el7                     base      14 k
 perl-Time-HiRes          x86_64  4:1.9725-3.el7                 base      45 k
 perl-Time-Local          noarch  1.2300-2.el7                   base      24 k
 perl-constant            noarch  1.27-2.el7                     base      19 k
 perl-libs                x86_64  4:5.16.3-292.el7               base     688 k
 perl-macros              x86_64  4:5.16.3-292.el7               base      43 k
 perl-parent              noarch  1:0.225-244.el7                base      12 k
 perl-podlators           noarch  2.5.1-3.el7                    base     112 k
 perl-threads             x86_64  1.87-4.el7                     base      49 k
 perl-threads-shared      x86_64  1.43-6.el7                     base      39 k
 rsync                    x86_64  3.1.2-4.el7                    base     403 k

Transaction Summary
================================================================================
Install  4 Packages (+40 Dependent packages)

Total download size: 209 M
Installed size: 875 M
Is this ok [y/d/N]: y
Downloading packages:
warning: /var/cache/yum/x86_64/7/mariadb/packages/MariaDB-10.3.10-centos73-x86_64-common.rpm: Header V4 DSA/SHA1 Signature, key ID 1bb943db: NOKEY
Public key for MariaDB-10.3.10-centos73-x86_64-common.rpm is not installed
(1/44): MariaDB-10.3.10-centos73-x86_64-common.rpm         | 157 kB   00:03     
(2/44): MariaDB-10.3.10-centos73-x86_64-compat.rpm         | 2.8 MB   00:02     
(3/44): MariaDB-10.3.10-centos73-x86_64-devel.rpm          | 7.0 MB   00:06     
(4/44): MariaDB-10.3.10-centos73-x86_64-client.rpm         |  53 MB   00:39     
(5/44): MariaDB-10.3.10-centos73-x86_64-shared.rpm         | 397 kB   00:01     
(6/44): boost-program-options-1.53.0-27.el7.x86_64.rpm     | 156 kB   00:01     
(7/44): perl-Carp-1.26-244.el7.noarch.rpm                  |  19 kB   00:00     
(8/44): perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64.rpm     |  32 kB   00:00     
(9/44): perl-Compress-Raw-Zlib-2.061-4.el7.x86_64.rpm      |  57 kB   00:00     
(10/44): perl-DBI-1.627-4.el7.x86_64.rpm                   | 802 kB   00:00     
(11/44): perl-Data-Dumper-2.145-3.el7.x86_64.rpm           |  47 kB   00:00     
(12/44): perl-Encode-2.51-7.el7.x86_64.rpm                 | 1.5 MB   00:00     
(13/44): perl-Exporter-5.68-3.el7.noarch.rpm               |  28 kB   00:00     
(14/44): lsof-4.87-5.el7.x86_64.rpm                        | 331 kB   00:03     
(15/44): perl-File-Path-2.09-2.el7.noarch.rpm              |  26 kB   00:00     
(16/44): perl-File-Temp-0.23.01-3.el7.noarch.rpm           |  56 kB   00:00     
(17/44): perl-Filter-1.49-3.el7.x86_64.rpm                 |  76 kB   00:00     
(18/44): perl-Getopt-Long-2.40-3.el7.noarch.rpm            |  56 kB   00:00     
(19/44): perl-HTTP-Tiny-0.033-3.el7.noarch.rpm             |  38 kB   00:00     
(20/44): perl-IO-Compress-2.061-2.el7.noarch.rpm           | 260 kB   00:00     
(21/44): perl-Net-Daemon-0.48-5.el7.noarch.rpm             |  51 kB   00:00     
(22/44): perl-PathTools-3.40-5.el7.x86_64.rpm              |  82 kB   00:00     
(23/44): perl-PlRPC-0.2020-14.el7.noarch.rpm               |  36 kB   00:00     
(24/44): perl-Pod-Escapes-1.04-292.el7.noarch.rpm          |  51 kB   00:00     
(25/44): perl-Pod-Perldoc-3.20-4.el7.noarch.rpm            |  87 kB   00:00     
(26/44): perl-Pod-Usage-1.63-3.el7.noarch.rpm              |  27 kB   00:00     
(27/44): perl-Pod-Simple-3.28-4.el7.noarch.rpm             | 216 kB   00:00     
(28/44): perl-Scalar-List-Utils-1.27-248.el7.x86_64.rpm    |  36 kB   00:00     
(29/44): perl-Socket-2.010-4.el7.x86_64.rpm                |  49 kB   00:00     
(30/44): perl-Storable-2.45-3.el7.x86_64.rpm               |  77 kB   00:00     
(31/44): perl-Text-ParseWords-3.29-4.el7.noarch.rpm        |  14 kB   00:00     
(32/44): perl-Time-HiRes-1.9725-3.el7.x86_64.rpm           |  45 kB   00:00     
(33/44): perl-Time-Local-1.2300-2.el7.noarch.rpm           |  24 kB   00:00     
(34/44): perl-constant-1.27-2.el7.noarch.rpm               |  19 kB   00:00     
(35/44): perl-macros-5.16.3-292.el7.x86_64.rpm             |  43 kB   00:00     
(36/44): perl-libs-5.16.3-292.el7.x86_64.rpm               | 688 kB   00:00     
(37/44): perl-5.16.3-292.el7.x86_64.rpm                    | 8.0 MB   00:07     
(38/44): perl-parent-0.225-244.el7.noarch.rpm              |  12 kB   00:00     
(39/44): perl-podlators-2.5.1-3.el7.noarch.rpm             | 112 kB   00:00     
(40/44): perl-threads-1.87-4.el7.x86_64.rpm                |  49 kB   00:00     
(41/44): perl-threads-shared-1.43-6.el7.x86_64.rpm         |  39 kB   00:00     
(42/44): rsync-3.1.2-4.el7.x86_64.rpm                      | 403 kB   00:00     
(43/44): galera-25.3.24-1.rhel7.el7.centos.x86_64.rpm      | 8.1 MB   00:11     
(44/44): MariaDB-10.3.10-centos73-x86_64-server.rpm        | 123 MB   00:57     
--------------------------------------------------------------------------------
Total                                              3.0 MB/s | 209 MB  01:10     
Retrieving key from https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
Importing GPG key 0x1BB943DB:
 Userid     : "MariaDB Package Signing Key <package-signing-key@mariadb.org>"
 Fingerprint: 1993 69e5 404b d5fc 7d2f e43b cbcb 082a 1bb9 43db
 From       : https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
Is this ok [y/N]: y
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : MariaDB-common-10.3.10-1.el7.centos.x86_64                  1/44 
  Installing : MariaDB-compat-10.3.10-1.el7.centos.x86_64                  2/44 
  Installing : 1:perl-parent-0.225-244.el7.noarch                          3/44 
  Installing : perl-HTTP-Tiny-0.033-3.el7.noarch                           4/44 
  Installing : perl-podlators-2.5.1-3.el7.noarch                           5/44 
  Installing : perl-Pod-Perldoc-3.20-4.el7.noarch                          6/44 
  Installing : perl-Text-ParseWords-3.29-4.el7.noarch                      7/44 
  Installing : 1:perl-Pod-Escapes-1.04-292.el7.noarch                      8/44 
  Installing : perl-Encode-2.51-7.el7.x86_64                               9/44 
  Installing : perl-Pod-Usage-1.63-3.el7.noarch                           10/44 
  Installing : 4:perl-macros-5.16.3-292.el7.x86_64                        11/44 
  Installing : 4:perl-libs-5.16.3-292.el7.x86_64                          12/44 
  Installing : 4:perl-Time-HiRes-1.9725-3.el7.x86_64                      13/44 
  Installing : perl-Exporter-5.68-3.el7.noarch                            14/44 
  Installing : perl-constant-1.27-2.el7.noarch                            15/44 
  Installing : perl-Time-Local-1.2300-2.el7.noarch                        16/44 
  Installing : perl-Socket-2.010-4.el7.x86_64                             17/44 
  Installing : perl-Carp-1.26-244.el7.noarch                              18/44 
  Installing : perl-Storable-2.45-3.el7.x86_64                            19/44 
  Installing : perl-PathTools-3.40-5.el7.x86_64                           20/44 
  Installing : perl-Scalar-List-Utils-1.27-248.el7.x86_64                 21/44 
  Installing : perl-File-Temp-0.23.01-3.el7.noarch                        22/44 
  Installing : perl-File-Path-2.09-2.el7.noarch                           23/44 
  Installing : perl-threads-shared-1.43-6.el7.x86_64                      24/44 
  Installing : perl-threads-1.87-4.el7.x86_64                             25/44 
  Installing : perl-Filter-1.49-3.el7.x86_64                              26/44 
  Installing : 1:perl-Pod-Simple-3.28-4.el7.noarch                        27/44 
  Installing : perl-Getopt-Long-2.40-3.el7.noarch                         28/44 
  Installing : 4:perl-5.16.3-292.el7.x86_64                               29/44 
  Installing : perl-Data-Dumper-2.145-3.el7.x86_64                        30/44 
  Installing : perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64                 31/44 
  Installing : perl-Net-Daemon-0.48-5.el7.noarch                          32/44 
  Installing : MariaDB-client-10.3.10-1.el7.centos.x86_64                 33/44 
  Installing : 1:perl-Compress-Raw-Zlib-2.061-4.el7.x86_64                34/44 
  Installing : perl-IO-Compress-2.061-2.el7.noarch                        35/44 
  Installing : perl-PlRPC-0.2020-14.el7.noarch                            36/44 
  Installing : perl-DBI-1.627-4.el7.x86_64                                37/44 
  Installing : rsync-3.1.2-4.el7.x86_64                                   38/44 
  Installing : boost-program-options-1.53.0-27.el7.x86_64                 39/44 
  Installing : galera-25.3.24-1.rhel7.el7.centos.x86_64                   40/44 
  Installing : lsof-4.87-5.el7.x86_64                                     41/44 
  Installing : MariaDB-server-10.3.10-1.el7.centos.x86_64                 42/44 
chown: invalid user: ‘mysql’


PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !
To do so, start the server, then issue the following commands:

'/usr/bin/mysqladmin' -u root password 'new-password'
'/usr/bin/mysqladmin' -u root -h spider-1.novalocal password 'new-password'

Alternatively you can run:
'/usr/bin/mysql_secure_installation'

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the MariaDB Knowledgebase at http://mariadb.com/kb or the
MySQL manual for more instructions.

Please report any problems at http://mariadb.org/jira

The latest information about MariaDB is available at http://mariadb.org/.
You can find additional information about the MySQL part at:
http://dev.mysql.com
Consider joining MariaDB's strong and vibrant community:

  Installing : MariaDB-devel-10.3.10-1.el7.centos.x86_64                  43/44 
  Installing : MariaDB-shared-10.3.10-1.el7.centos.x86_64                 44/44 
  Verifying  : perl-HTTP-Tiny-0.033-3.el7.noarch                           1/44 
  Verifying  : perl-threads-shared-1.43-6.el7.x86_64                       2/44 
  Verifying  : 4:perl-Time-HiRes-1.9725-3.el7.x86_64                       3/44 
  Verifying  : perl-IO-Compress-2.061-2.el7.noarch                         4/44 
  Verifying  : perl-Exporter-5.68-3.el7.noarch                             5/44 
  Verifying  : perl-constant-1.27-2.el7.noarch                             6/44 
  Verifying  : perl-PathTools-3.40-5.el7.x86_64                            7/44 
  Verifying  : MariaDB-compat-10.3.10-1.el7.centos.x86_64                  8/44 
  Verifying  : 4:perl-macros-5.16.3-292.el7.x86_64                         9/44 
  Verifying  : perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64                 10/44 
  Verifying  : 1:perl-parent-0.225-244.el7.noarch                         11/44 
  Verifying  : perl-Net-Daemon-0.48-5.el7.noarch                          12/44 
  Verifying  : 4:perl-5.16.3-292.el7.x86_64                               13/44 
  Verifying  : 1:perl-Pod-Simple-3.28-4.el7.noarch                        14/44 
  Verifying  : perl-Pod-Usage-1.63-3.el7.noarch                           15/44 
  Verifying  : perl-File-Temp-0.23.01-3.el7.noarch                        16/44 
  Verifying  : perl-Data-Dumper-2.145-3.el7.x86_64                        17/44 
  Verifying  : perl-Time-Local-1.2300-2.el7.noarch                        18/44 
  Verifying  : 4:perl-libs-5.16.3-292.el7.x86_64                          19/44 
  Verifying  : lsof-4.87-5.el7.x86_64                                     20/44 
  Verifying  : boost-program-options-1.53.0-27.el7.x86_64                 21/44 
  Verifying  : perl-DBI-1.627-4.el7.x86_64                                22/44 
  Verifying  : perl-Socket-2.010-4.el7.x86_64                             23/44 
  Verifying  : perl-Text-ParseWords-3.29-4.el7.noarch                     24/44 
  Verifying  : MariaDB-client-10.3.10-1.el7.centos.x86_64                 25/44 
  Verifying  : perl-Carp-1.26-244.el7.noarch                              26/44 
  Verifying  : MariaDB-devel-10.3.10-1.el7.centos.x86_64                  27/44 
  Verifying  : galera-25.3.24-1.rhel7.el7.centos.x86_64                   28/44 
  Verifying  : perl-Storable-2.45-3.el7.x86_64                            29/44 
  Verifying  : perl-Scalar-List-Utils-1.27-248.el7.x86_64                 30/44 
  Verifying  : 1:perl-Compress-Raw-Zlib-2.061-4.el7.x86_64                31/44 
  Verifying  : 1:perl-Pod-Escapes-1.04-292.el7.noarch                     32/44 
  Verifying  : rsync-3.1.2-4.el7.x86_64                                   33/44 
  Verifying  : MariaDB-common-10.3.10-1.el7.centos.x86_64                 34/44 
  Verifying  : perl-PlRPC-0.2020-14.el7.noarch                            35/44 
  Verifying  : perl-Encode-2.51-7.el7.x86_64                              36/44 
  Verifying  : perl-Pod-Perldoc-3.20-4.el7.noarch                         37/44 
  Verifying  : perl-podlators-2.5.1-3.el7.noarch                          38/44 
  Verifying  : perl-File-Path-2.09-2.el7.noarch                           39/44 
  Verifying  : perl-threads-1.87-4.el7.x86_64                             40/44 
  Verifying  : MariaDB-server-10.3.10-1.el7.centos.x86_64                 41/44 
  Verifying  : perl-Filter-1.49-3.el7.x86_64                              42/44 
  Verifying  : perl-Getopt-Long-2.40-3.el7.noarch                         43/44 
  Verifying  : MariaDB-shared-10.3.10-1.el7.centos.x86_64                 44/44 

Installed:
  MariaDB-client.x86_64 0:10.3.10-1.el7.centos                                  
  MariaDB-devel.x86_64 0:10.3.10-1.el7.centos                                   
  MariaDB-server.x86_64 0:10.3.10-1.el7.centos                                  
  MariaDB-shared.x86_64 0:10.3.10-1.el7.centos                                  

Dependency Installed:
  MariaDB-common.x86_64 0:10.3.10-1.el7.centos                                  
  MariaDB-compat.x86_64 0:10.3.10-1.el7.centos                                  
  boost-program-options.x86_64 0:1.53.0-27.el7                                  
  galera.x86_64 0:25.3.24-1.rhel7.el7.centos                                    
  lsof.x86_64 0:4.87-5.el7                                                      
  perl.x86_64 4:5.16.3-292.el7                                                  
  perl-Carp.noarch 0:1.26-244.el7                                               
  perl-Compress-Raw-Bzip2.x86_64 0:2.061-3.el7                                  
  perl-Compress-Raw-Zlib.x86_64 1:2.061-4.el7                                   
  perl-DBI.x86_64 0:1.627-4.el7                                                 
  perl-Data-Dumper.x86_64 0:2.145-3.el7                                         
  perl-Encode.x86_64 0:2.51-7.el7                                               
  perl-Exporter.noarch 0:5.68-3.el7                                             
  perl-File-Path.noarch 0:2.09-2.el7                                            
  perl-File-Temp.noarch 0:0.23.01-3.el7                                         
  perl-Filter.x86_64 0:1.49-3.el7                                               
  perl-Getopt-Long.noarch 0:2.40-3.el7                                          
  perl-HTTP-Tiny.noarch 0:0.033-3.el7                                           
  perl-IO-Compress.noarch 0:2.061-2.el7                                         
  perl-Net-Daemon.noarch 0:0.48-5.el7                                           
  perl-PathTools.x86_64 0:3.40-5.el7                                            
  perl-PlRPC.noarch 0:0.2020-14.el7                                             
  perl-Pod-Escapes.noarch 1:1.04-292.el7                                        
  perl-Pod-Perldoc.noarch 0:3.20-4.el7                                          
  perl-Pod-Simple.noarch 1:3.28-4.el7                                           
  perl-Pod-Usage.noarch 0:1.63-3.el7                                            
  perl-Scalar-List-Utils.x86_64 0:1.27-248.el7                                  
  perl-Socket.x86_64 0:2.010-4.el7                                              
  perl-Storable.x86_64 0:2.45-3.el7                                             
  perl-Text-ParseWords.noarch 0:3.29-4.el7                                      
  perl-Time-HiRes.x86_64 4:1.9725-3.el7                                         
  perl-Time-Local.noarch 0:1.2300-2.el7                                         
  perl-constant.noarch 0:1.27-2.el7                                             
  perl-libs.x86_64 4:5.16.3-292.el7                                             
  perl-macros.x86_64 4:5.16.3-292.el7                                           
  perl-parent.noarch 1:0.225-244.el7                                            
  perl-podlators.noarch 0:2.5.1-3.el7                                           
  perl-threads.x86_64 0:1.87-4.el7                                              
  perl-threads-shared.x86_64 0:1.43-6.el7                                       
  rsync.x86_64 0:3.1.2-4.el7                                                    

Complete!
[root@spider-1 ~]#

MariaDB の多重起動設定

/etc/my.cnf.d/server.cnf を編集し、以下設定します。

[root@spider-1 ~]# vi /etc/my.cnf.d/server.cnf

[mysqld] セクションで、文字セットを UTF8 に設定します。

[mysqld]
character-set-server = utf8

/etc/my.cnf を編集し、1台のサーバーで複数のデータベースを多重で起動するための設定を追加します。

[root@spider-1 ~]# vi /etc/my.cnf
（以下設定を追加）
#
# mysqld_multi
#
[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
log        = /var/lib/mysql/multi.log

[mysqld1]
port = 3307
datadir  = /var/lib/mysql1
pid-file = /var/lib/mysql1/mysql.pid
socket   = /var/lib/mysql1/mysql.sock

[mysqld2]
port = 3308
datadir  = /var/lib/mysql2
pid-file = /var/lib/mysql2/mysql.pid
socket   = /var/lib/mysql2/mysql.sock

[mysqld3]
port = 3309
datadir  = /var/lib/mysql3
pid-file = /var/lib/mysql3/mysql.pid
socket   = /var/lib/mysql3/mysql.sock

mysql1 のデータベースを初期化します。

[root@spider-1 ~]# mysql_install_db --datadir=/var/lib/mysql1 --user=mysql
Installing MariaDB/MySQL system tables in '/var/lib/mysql1' ...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system


PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !
To do so, start the server, then issue the following commands:

'/usr/bin/mysqladmin' -u root password 'new-password'
'/usr/bin/mysqladmin' -u root -h spider-1.novalocal password 'new-password'

Alternatively you can run:
'/usr/bin/mysql_secure_installation'

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the MariaDB Knowledgebase at http://mariadb.com/kb or the
MySQL manual for more instructions.

You can start the MariaDB daemon with:
cd '/usr' ; /usr/bin/mysqld_safe --datadir='/var/lib/mysql1'

You can test the MariaDB daemon with mysql-test-run.pl
cd '/usr/mysql-test' ; perl mysql-test-run.pl

Please report any problems at http://mariadb.org/jira

The latest information about MariaDB is available at http://mariadb.org/.
You can find additional information about the MySQL part at:
http://dev.mysql.com
Consider joining MariaDB's strong and vibrant community:

[root@spider-1 ~]#

mysql2 のデータベースを初期化します。

[root@spider-1 ~]# mysql_install_db --datadir=/var/lib/mysql2 --user=mysql
Installing MariaDB/MySQL system tables in '/var/lib/mysql2' ...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system


PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !
To do so, start the server, then issue the following commands:

'/usr/bin/mysqladmin' -u root password 'new-password'
'/usr/bin/mysqladmin' -u root -h spider-1.novalocal password 'new-password'

Alternatively you can run:
'/usr/bin/mysql_secure_installation'

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the MariaDB Knowledgebase at http://mariadb.com/kb or the
MySQL manual for more instructions.

You can start the MariaDB daemon with:
cd '/usr' ; /usr/bin/mysqld_safe --datadir='/var/lib/mysql2'

You can test the MariaDB daemon with mysql-test-run.pl
cd '/usr/mysql-test' ; perl mysql-test-run.pl

Please report any problems at http://mariadb.org/jira

The latest information about MariaDB is available at http://mariadb.org/.
You can find additional information about the MySQL part at:
http://dev.mysql.com
Consider joining MariaDB's strong and vibrant community:

[root@spider-1 ~]#

mysql3 のデータベースを初期化します。

[root@spider-1 ~]# mysql_install_db --datadir=/var/lib/mysql3 --user=mysql
Installing MariaDB/MySQL system tables in '/var/lib/mysql3' ...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system


PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !
To do so, start the server, then issue the following commands:

'/usr/bin/mysqladmin' -u root password 'new-password'
'/usr/bin/mysqladmin' -u root -h spider-1.novalocal password 'new-password'

Alternatively you can run:
'/usr/bin/mysql_secure_installation'

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the MariaDB Knowledgebase at http://mariadb.com/kb or the
MySQL manual for more instructions.

You can start the MariaDB daemon with:
cd '/usr' ; /usr/bin/mysqld_safe --datadir='/var/lib/mysql3'

You can test the MariaDB daemon with mysql-test-run.pl
cd '/usr/mysql-test' ; perl mysql-test-run.pl

Please report any problems at http://mariadb.org/jira

The latest information about MariaDB is available at http://mariadb.org/.
You can find additional information about the MySQL part at:
http://dev.mysql.com
Consider joining MariaDB's strong and vibrant community:

[root@spider-1 ~]#

初期化した mysql1、mysql2、mysql3 のデータベースのアクセス権と所有権を確認します。この時点では、アクセス権と所有権が root ユーザーであることが確認できます。

[root@spider-1 ~]# ls -la /var/lib/
total 8
drwxr-xr-x. 30 root    root    4096 Nov 16 14:20 .
drwxr-xr-x. 19 root    root     267 Nov 16 01:03 ..
drwxr-xr-x.  2 root    root      44 Nov 16 01:51 alternatives
drwx------.  3 root    root      18 Nov 16 01:01 authconfig
drwxr-xr-x.  2 chrony  chrony    19 Nov 16 13:40 chrony
drwxr-xr-x.  8 root    root     105 Nov 16 01:18 cloud
drwxr-xr-x.  2 root    root       6 Apr 11  2018 dbus
drwxr-xr-x.  2 root    root       6 May 15  2018 dhclient
drwxr-xr-x.  2 root    root       6 Apr 11  2018 games
drwxr-xr-x.  2 root    root       6 Aug 16 23:45 initramfs
drwxr-xr-x.  2 root    root      30 Nov 16 02:29 logrotate
drwx------.  2 root    root       6 Nov 16 00:59 machines
drwxr-xr-x.  2 root    root       6 Nov 16 01:51 misc
drwxr-xr-x.  5 mysql   mysql    272 Nov 16 03:01 mysql
drwx------.  5 mysql   root     181 Nov 16 14:19 mysql1
drwx------.  5 mysql   root     181 Nov 16 14:20 mysql2
drwx------.  5 mysql   root     181 Nov 16 14:20 mysql3
drwx------.  2 root    root     194 Jun 27 23:39 NetworkManager
drwxr-xr-x.  2 root    root       6 Nov  6  2016 os-prober
drwxr-xr-x.  2 root    root      27 Nov 16 00:59 plymouth
drwxr-x---.  3 root    polkitd   28 Nov 16 00:59 polkit-1
drwx------.  2 postfix root      25 Nov 16 01:03 postfix
drwxr-xr-x.  2 root    root    4096 Nov 16 01:22 rpm
drwxr-xr-x.  2 root    root       6 Apr 11  2018 rpm-state
drwx------.  2 root    root      29 Nov 16 14:01 rsyslog
drwxr-xr-x.  2 root    root       6 Apr 12  2018 selinux
drwxr-xr-x.  4 root    root      35 Sep 27 03:25 stateless
drwxr-xr-x.  4 root    root      56 Sep 27 04:11 systemd
drwxr-xr-x.  2 root    root       6 Aug 21 03:50 tuned
drwxr-xr-x.  6 root    root      80 Nov 16 02:13 yum
[root@spider-1 ~]#

各データベースのアクセス権と所有権を mysql ユーザーに変更します。

[root@spider-1 ~]# chown -R mysql:mysql /var/lib/mysql1 /var/lib/mysql2 /var/lib/mysql3
[root@spider-1 ~]#

各データベースのアクセス権と所有権が、mysql ユーザーになっていることを確認します。

[root@spider-1 ~]# ls -la /var/lib/
total 8
drwxr-xr-x. 30 root    root    4096 Nov 16 14:20 .
drwxr-xr-x. 19 root    root     267 Nov 16 01:03 ..
drwxr-xr-x.  2 root    root      44 Nov 16 01:51 alternatives
drwx------.  3 root    root      18 Nov 16 01:01 authconfig
drwxr-xr-x.  2 chrony  chrony    19 Nov 16 13:40 chrony
drwxr-xr-x.  8 root    root     105 Nov 16 01:18 cloud
drwxr-xr-x.  2 root    root       6 Apr 11  2018 dbus
drwxr-xr-x.  2 root    root       6 May 15  2018 dhclient
drwxr-xr-x.  2 root    root       6 Apr 11  2018 games
drwxr-xr-x.  2 root    root       6 Aug 16 23:45 initramfs
drwxr-xr-x.  2 root    root      30 Nov 16 02:29 logrotate
drwx------.  2 root    root       6 Nov 16 00:59 machines
drwxr-xr-x.  2 root    root       6 Nov 16 01:51 misc
drwxr-xr-x.  5 mysql   mysql    272 Nov 16 03:01 mysql
drwx------.  5 mysql   mysql    181 Nov 16 14:19 mysql1
drwx------.  5 mysql   mysql    181 Nov 16 14:20 mysql2
drwx------.  5 mysql   mysql    181 Nov 16 14:20 mysql3
drwx------.  2 root    root     194 Jun 27 23:39 NetworkManager
drwxr-xr-x.  2 root    root       6 Nov  6  2016 os-prober
drwxr-xr-x.  2 root    root      27 Nov 16 00:59 plymouth
drwxr-x---.  3 root    polkitd   28 Nov 16 00:59 polkit-1
drwx------.  2 postfix root      25 Nov 16 01:03 postfix
drwxr-xr-x.  2 root    root    4096 Nov 16 01:22 rpm
drwxr-xr-x.  2 root    root       6 Apr 11  2018 rpm-state
drwx------.  2 root    root      29 Nov 16 14:01 rsyslog
drwxr-xr-x.  2 root    root       6 Apr 12  2018 selinux
drwxr-xr-x.  4 root    root      35 Sep 27 03:25 stateless
drwxr-xr-x.  4 root    root      56 Sep 27 04:11 systemd
drwxr-xr-x.  2 root    root       6 Aug 21 03:50 tuned
drwxr-xr-x.  6 root    root      80 Nov 16 02:13 yum
[root@spider-1 ~]#

MariaDB の多重で起動します。

[root@spider-1 ~]# mysqld_multi start
[root@spider-1 ~]#

MariaDB が多重で起動していることを確認します。

[root@spider-1 ~]# mysqld_multi report
Reporting MariaDB servers
MariaDB server from group: mysqld1 is running
MariaDB server from group: mysqld2 is running
MariaDB server from group: mysqld3 is running
[root@spider-1 ~]#

Spider を動作させるための設定

ここでは、mysql1 を spider ノードとして設定します。

Spider ノードにする mysql1 のデータベースに接続します。

[root@spider-1 ~]# mysql -uroot --socket=/var/lib/mysql1/mysql.sock
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.10-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]>

mysql1 に対して、Spider ストレージエンジンの定義をインストールします。

MariaDB [(none)]> source /usr/share/mysql/install_spider.sql
Query OK, 0 rows affected (0.064 sec)

Query OK, 0 rows affected (0.014 sec)

Query OK, 0 rows affected (0.013 sec)

Query OK, 0 rows affected (0.014 sec)

Query OK, 0 rows affected (0.011 sec)

Query OK, 0 rows affected (0.011 sec)

Query OK, 0 rows affected (0.010 sec)

Query OK, 0 rows affected (0.013 sec)

Query OK, 0 rows affected (0.010 sec)

Query OK, 0 rows affected, 1 warning (0.005 sec)

Query OK, 0 rows affected, 1 warning (0.002 sec)

Query OK, 0 rows affected (0.011 sec)

Query OK, 0 rows affected (0.021 sec)

Query OK, 23 rows affected (0.123 sec)

Query OK, 0 rows affected (0.009 sec)

Query OK, 0 rows affected (0.008 sec)

Query OK, 0 rows affected, 1 warning (0.002 sec)

Query OK, 0 rows affected (0.010 sec)

Empty set (0.012 sec)

Empty set (0.012 sec)

Empty set (0.062 sec)

Empty set (0.062 sec)

Empty set (0.062 sec)

Empty set (0.076 sec)

Empty set (0.076 sec)

Empty set (0.076 sec)

Empty set (0.076 sec)

Query OK, 0 rows affected (0.076 sec)

Query OK, 0 rows affected (0.012 sec)

MariaDB [(none)]>

サーバーのストレージエンジンに関するステータス情報を表示します。 Spider ストレージエンジンがサポートされている（YES）ことが確認できます。

MariaDB [(none)]> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                                          | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| SPIDER             | YES     | Spider storage engine                                                            | YES          | YES  | NO         |
| CSV                | YES     | Stores tables as CSV files                                                       | NO           | NO   | NO         |
| MRG_MyISAM         | YES     | Collection of identical MyISAM tables                                            | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables                        | NO           | NO   | NO         |
| Aria               | YES     | Crash-safe tables with MyISAM heritage                                           | NO           | NO   | NO         |
| MyISAM             | YES     | Non-transactional engine with good performance and small data footprint          | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                                               | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, foreign keys and encryption for tables | YES          | YES  | YES        |
| SEQUENCE           | YES     | Generated tables filled with sequential values                                   | YES          | NO   | YES        |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.022 sec)

MariaDB [(none)]>

Spider で水平分散（シャーディング）する1つの目の外部データラッパ（ mysql2 ）を作成します。ここでは、spider ユーザーが外部データラッパの所有者として作成しています。

MariaDB [(none)]> CREATE SERVER mysqld2 FOREIGN DATA WRAPPER mysql OPTIONS (USER 'spider', PASSWORD 'spider', HOST '127.0.0.1', PORT 3308);
Query OK, 0 rows affected (0.009 sec)

MariaDB [(none)]>

Spider で水平分散（シャーディング）する2つの目の外部データラッパ（ mysql3 ）を作成します。ここでは、spider ユーザーが外部データラッパの所有者として作成しています。

MariaDB [(none)]> CREATE SERVER mysqld3 FOREIGN DATA WRAPPER mysql OPTIONS (USER 'spider', PASSWORD 'spider', HOST '127.0.0.1', PORT 3309);
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]>

外部データラップが作成されていることを確認します。

MariaDB [(none)]> SELECT * FROM mysql.servers;
+-------------+-----------+----+----------+----------+------+--------+---------+-------+
| Server_name | Host      | Db | Username | Password | Port | Socket | Wrapper | Owner |
+-------------+-----------+----+----------+----------+------+--------+---------+-------+
| mysqld2     | 127.0.0.1 |    | spider   | spider   | 3308 |        | mysql   |       |
| mysqld3     | 127.0.0.1 |    | spider   | spider   | 3309 |        | mysql   |       |
+-------------+-----------+----+----------+----------+------+--------+---------+-------+
2 rows in set (0.018 sec)

MariaDB [(none)]>

Spider ノードでデータベースを作成します。ここでは、example_db といった名前でデータベースを作成しています。

[root@spider-1 ~]# mysql -uroot --socket=/var/lib/mysql1/mysql.sock -e "CREATE DATABASE example_db; GRANT ALL PRIVILEGES ON *.* TO 'spider'@'localhost' IDENTIFIED BY 'spider'; FLUSH PRIVILEGES;"
[root@spider-1 ~]#

Data ノード（mysql2 ）でデータベースを作成します。ここでは、example_db といった名前でデータベースを作成しています。

[root@spider-1 ~]# mysql -uroot --socket=/var/lib/mysql2/mysql.sock -e "CREATE DATABASE example_db; GRANT ALL PRIVILEGES ON *.* TO 'spider'@'localhost' IDENTIFIED BY 'spider'; FLUSH PRIVILEGES;"
[root@spider-1 ~]#

Data ノード（mysql3 ）でデータベースを作成します。ここでは、example_db といった名前でデータベースを作成しています。

[root@spider-1 ~]# mysql -uroot --socket=/var/lib/mysql3/mysql.sock -e "CREATE DATABASE example_db; GRANT ALL PRIVILEGES ON *.* TO 'spider'@'localhost' IDENTIFIED BY 'spider'; FLUSH PRIVILEGES;"
[root@spider-1 ~]#

水平分散（シャーディング）の構築

Spider ノードで作成したデータベース（ example_db ）にアクセスし、USERテーブルを user_id のハッシュにより mysql2 と mysql3 の2つのサーバーにシャーディングするための設定をします。

■ example_dbにアクセス
[root@spider-1 ~]# mysql -uroot --socket=/var/lib/mysql1/mysql.sock example_db
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 34
Server version: 10.3.10-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [example_db]> 

■ USERテーブルをuser_idのハッシュによりmysql2とmysql3の2つのサーバーにシャーディングするための設定
MariaDB [example_db]> CREATE TABLE `USER` (
  `user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `profile` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `level` int(10) unsigned DEFAULT NULL,
  `exp` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE = SPIDER DEFAULT CHARSET=utf8
PARTITION BY HASH(user_id) (
  PARTITION p1 comment 'server "mysqld2", table "USER"',
  PARTITION p2 comment 'server "mysqld3", table "USER"'
);
Query OK, 0 rows affected (0.027 sec)

MariaDB [example_db]> quit
Bye
[root@spider-1 ~]#

データノード（ mysql2 ）で作成したデータベース（ example_db ）にアクセスし、USERテーブルを InnoDB（ MySQLのためのデータベースエンジン）として作成します。

■ example_dbにアクセス
[root@spider-1 ~]# mysql -uroot --socket=/var/lib/mysql2/mysql.sock example_db
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.3.10-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [example_db]> 

■ USERテーブルをInnoDBとして作成
MariaDB [example_db]> CREATE TABLE `USER` (
  `user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `profile` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `level` int(10) unsigned DEFAULT NULL,
  `exp` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE = InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.109 sec)

MariaDB [example_db]> quit
Bye
[root@spider-1 ~]#

データノード（ mysql3 ）で作成したデータベース（ example_db ）にアクセスし、USERテーブルを InnoDB（ MySQLのためのデータベースエンジン）として作成します。

■ example_dbにアクセス
[root@spider-1 ~]# mysql -uroot --socket=/var/lib/mysql3/mysql.sock example_db
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.3.10-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [example_db]> 

■ USERテーブルをInnoDBとして作成
MariaDB [example_db]> CREATE TABLE `USER` (
  `user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `profile` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `level` int(10) unsigned DEFAULT NULL,
  `exp` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE = InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.119 sec)

MariaDB [example_db]> quit
Bye
[root@spider-1 ~]#

水平分散（シャーディング）の動作確認

Spider ノードのデータベース（ example_db ）にアクセスし、USERテーブルにデータをインサート（追加）する。ここでは、user_idは明示的に指定せず、オートインクリメントに任せるようにしています。

■ example_dbにアクセス
[root@spider-1 ~]# mysql -uroot --socket=/var/lib/mysql1/mysql.sock example_db
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 37
Server version: 10.3.10-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [example_db]> 

■ USERテーブルにデータをインサート
MariaDB [example_db]> INSERT INTO USER(name, profile, level, exp) VALUES 
('NAME01', 'PROF01', '1', '101')
,('NAME02', 'PROF02', '2', '102')
,('NAME03', 'PROF03', '3', '103')
,('NAME04', 'PROF04', '4', '104')
,('NAME05', 'PROF05', '5', '105')
,('NAME06', 'PROF06', '6', '106')
,('NAME07', 'PROF07', '7', '107')
,('NAME08', 'PROF08', '8', '108')
,('NAME09', 'PROF09', '9', '109')
,('NAME10', 'PROF10', '10', '110')
,('NAME11', 'PROF11', '11', '111')
,('NAME12', 'PROF12', '12', '112')
,('NAME13', 'PROF13', '13', '113')
,('NAME14', 'PROF14', '14', '114')
,('NAME15', 'PROF15', '15', '115')
,('NAME16', 'PROF16', '16', '116')
,('NAME17', 'PROF17', '17', '117')
,('NAME18', 'PROF18', '18', '118')
,('NAME19', 'PROF19', '19', '119')
,('NAME10', 'PROF20', '20', '120');
Query OK, 20 rows affected (0.169 sec)
Records: 20  Duplicates: 0  Warnings: 0

MariaDB [example_db]> 
MariaDB [example_db]> quit
Bye
[root@spider-1 ~]#

Spider ノード（ mysql1 ）で作成したデータベース（ example_db ）にアクセスし、USERテーブルを確認します。インサートしたデータが登録されていることが確確認できます。ただし、順番がバラバラに表示されています。

[root@spider-1 ~]# mysql -uroot --socket=/var/lib/mysql1/mysql.sock example_db
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 38
Server version: 10.3.10-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [example_db]> SELECT * FROM USER;
+---------+--------+---------+-------+------+
| user_id | name   | profile | level | exp  |
+---------+--------+---------+-------+------+
|       2 | NAME02 | PROF02  |     2 |  102 |
|       4 | NAME04 | PROF04  |     4 |  104 |
|       6 | NAME06 | PROF06  |     6 |  106 |
|       8 | NAME08 | PROF08  |     8 |  108 |
|      10 | NAME10 | PROF10  |    10 |  110 |
|      12 | NAME12 | PROF12  |    12 |  112 |
|      14 | NAME14 | PROF14  |    14 |  114 |
|      16 | NAME16 | PROF16  |    16 |  116 |
|      18 | NAME18 | PROF18  |    18 |  118 |
|      20 | NAME10 | PROF20  |    20 |  120 |
|       1 | NAME01 | PROF01  |     1 |  101 |
|       3 | NAME03 | PROF03  |     3 |  103 |
|       5 | NAME05 | PROF05  |     5 |  105 |
|       7 | NAME07 | PROF07  |     7 |  107 |
|       9 | NAME09 | PROF09  |     9 |  109 |
|      11 | NAME11 | PROF11  |    11 |  111 |
|      13 | NAME13 | PROF13  |    13 |  113 |
|      15 | NAME15 | PROF15  |    15 |  115 |
|      17 | NAME17 | PROF17  |    17 |  117 |
|      19 | NAME19 | PROF19  |    19 |  119 |
+---------+--------+---------+-------+------+
20 rows in set (0.038 sec)

MariaDB [example_db]>

ORDER BY を指定して、再度 USERテーブルを確認します。インサートしたデータが昇順に並び変えられ表示されていることが確認できます。

MariaDB [example_db]> SELECT * FROM USER ORDER BY user_id;
+---------+--------+---------+-------+------+
| user_id | name   | profile | level | exp  |
+---------+--------+---------+-------+------+
|       1 | NAME01 | PROF01  |     1 |  101 |
|       2 | NAME02 | PROF02  |     2 |  102 |
|       3 | NAME03 | PROF03  |     3 |  103 |
|       4 | NAME04 | PROF04  |     4 |  104 |
|       5 | NAME05 | PROF05  |     5 |  105 |
|       6 | NAME06 | PROF06  |     6 |  106 |
|       7 | NAME07 | PROF07  |     7 |  107 |
|       8 | NAME08 | PROF08  |     8 |  108 |
|       9 | NAME09 | PROF09  |     9 |  109 |
|      10 | NAME10 | PROF10  |    10 |  110 |
|      11 | NAME11 | PROF11  |    11 |  111 |
|      12 | NAME12 | PROF12  |    12 |  112 |
|      13 | NAME13 | PROF13  |    13 |  113 |
|      14 | NAME14 | PROF14  |    14 |  114 |
|      15 | NAME15 | PROF15  |    15 |  115 |
|      16 | NAME16 | PROF16  |    16 |  116 |
|      17 | NAME17 | PROF17  |    17 |  117 |
|      18 | NAME18 | PROF18  |    18 |  118 |
|      19 | NAME19 | PROF19  |    19 |  119 |
|      20 | NAME10 | PROF20  |    20 |  120 |
+---------+--------+---------+-------+------+
20 rows in set (0.069 sec)

MariaDB [example_db]>

USERテーブルに登録さているデータ数を確認します。

MariaDB [example_db]> SELECT COUNT(*) FROM USER;
+----------+
| COUNT(*) |
+----------+
|       20 |
+----------+
1 row in set (0.058 sec)

MariaDB [example_db]>

USERテーブルに登録さている levelデータの合計値を確認します。

MariaDB [example_db]> SELECT SUM(level) FROM USER;
+------------+
| SUM(level) |
+------------+
|        210 |
+------------+
1 row in set (0.072 sec)

MariaDB [example_db]> 
MariaDB [example_db]> quit
Bye
[root@spider-1 ~]#

次に、データノード（ mysql2 ）で作成したデータベース（ example_db ）にアクセスし、USERテーブルを確認します。インサートしたデータが10個だけ登録されていることが確確認できます。

[root@spider-1 ~]# mysql -uroot --socket=/var/lib/mysql2/mysql.sock example_db
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 23
Server version: 10.3.10-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [example_db]> SELECT * FROM USER;
+---------+--------+---------+-------+------+
| user_id | name   | profile | level | exp  |
+---------+--------+---------+-------+------+
|       2 | NAME02 | PROF02  |     2 |  102 |
|       4 | NAME04 | PROF04  |     4 |  104 |
|       6 | NAME06 | PROF06  |     6 |  106 |
|       8 | NAME08 | PROF08  |     8 |  108 |
|      10 | NAME10 | PROF10  |    10 |  110 |
|      12 | NAME12 | PROF12  |    12 |  112 |
|      14 | NAME14 | PROF14  |    14 |  114 |
|      16 | NAME16 | PROF16  |    16 |  116 |
|      18 | NAME18 | PROF18  |    18 |  118 |
|      20 | NAME10 | PROF20  |    20 |  120 |
+---------+--------+---------+-------+------+
10 rows in set (0.015 sec)

MariaDB [example_db]> SELECT COUNT(*) FROM USER;
+----------+
| COUNT(*) |
+----------+
|       10 |
+----------+
1 row in set (0.012 sec)

MariaDB [example_db]> SELECT SUM(level) FROM USER;
+------------+
| SUM(level) |
+------------+
|        110 |
+------------+
1 row in set (0.013 sec)

MariaDB [example_db]> 
MariaDB [example_db]> quit
Bye
[root@spider-1 ~]#

次に、データノード（ mysql3 ）で作成したデータベース（ example_db ）にアクセスし、USERテーブルを確認します。インサートしたデータが10個だけ登録されていることが確確認できます。

[root@spider-1 ~]# mysql -uroot --socket=/var/lib/mysql3/mysql.sock example_db
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 24
Server version: 10.3.10-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [example_db]> 
 
MariaDB [example_db]> SELECT * FROM USER;
+---------+--------+---------+-------+------+
| user_id | name   | profile | level | exp  |
+---------+--------+---------+-------+------+
|       1 | NAME01 | PROF01  |     1 |  101 |
|       3 | NAME03 | PROF03  |     3 |  103 |
|       5 | NAME05 | PROF05  |     5 |  105 |
|       7 | NAME07 | PROF07  |     7 |  107 |
|       9 | NAME09 | PROF09  |     9 |  109 |
|      11 | NAME11 | PROF11  |    11 |  111 |
|      13 | NAME13 | PROF13  |    13 |  113 |
|      15 | NAME15 | PROF15  |    15 |  115 |
|      17 | NAME17 | PROF17  |    17 |  117 |
|      19 | NAME19 | PROF19  |    19 |  119 |
+---------+--------+---------+-------+------+
10 rows in set (0.005 sec)

MariaDB [example_db]> 
MariaDB [example_db]> SELECT COUNT(*) FROM USER;
+----------+
| COUNT(*) |
+----------+
|       10 |
+----------+
1 row in set (0.014 sec)

MariaDB [example_db]>
MariaDB [example_db]> SELECT SUM(level) FROM USER;
+------------+
| SUM(level) |
+------------+
|        100 |
+------------+
1 row in set (0.017 sec)

MariaDB [example_db]> 
MariaDB [example_db]> quit
Bye
[root@spider-1 ~]#