[Amazon WorkSpaces]AmazonLinux2にMariaDB10.3インストール

Amazon Linux release 2 (Karoo)

```
# インストールされているパッケージからバージョンを確認
$ rpm -qa | grep -i mariadb

# 確認結果
mariadb-libs-5.5.62-1.amzn2.0.1.x86_64
```

```
$ sudo vi /etc/yum.repos.d/MariaDB.repo
```

```mariadb.repo
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.3/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
```
```
$ sudo yum install MariaDB-server MariaDB-client -y
```

10.3インストール後のバージョン確認

```
# 実行コマンド
$ mysql -V

# 実行結果
mysql  Ver 15.1 Distrib 10.3.15-MariaDB, for Linux (x86_64) using readline 5.1

# MariaDBのサービス起動
$ sudo systemctl start mariadb
```

マイナーバージョンアップする場合

```
$ sudo yum -y update mariadb
```
MariaDBにパスワード設定

```
# 本当は mysql_secure_installationでやったほうがいいのだが今回は確認だけしたいので
$ /usr/bin/mysqladmin -uroot password '(パスワード)'
```

日本語対応の高速な全文検索機能「⁠Mroonga」(むるんが)を追加

名前の由来は「MySQL」と「Groonga」(ぐるんが)です。
「⁠MySQL」の「M」と「Groonga」の「roonga」を組み合わせて「Mroonga」。

いろいろと調べれると出てくるMariaDBに接続して以下のコマンドを実行する方法だと
Mroongaは7.07になることがわかった。最新バージョンを使いたいので違う方法でインストール
https://mariadb.com/kb/en/library/about-mroonga/

```
INSTALL PLUGIN Mroonga SONAME 'ha_mroonga.so';
CREATE FUNCTION last_insert_grn_id RETURNS INTEGER SONAME 'ha_mroonga.so';
CREATE FUNCTION mroonga_snippet RETURNS STRING SONAME 'ha_mroonga.so';
CREATE FUNCTION mroonga_command RETURNS STRING SONAME 'ha_mroonga.so';
CREATE FUNCTION mroonga_escape RETURNS STRING SONAME 'ha_mroonga.so';
```

準備

```
# groonga-release-latest.noarch.rpmインストール
sudo yum install -y https://packages.groonga.org/centos/groonga-release-latest.noarch.rpm

# Amazon Linux2 にepelリポジトリを使えるようにする
$ sudo amazon-linux-extras install epel -y
```

```
$ sudo vi /etc/yum.repos.d/groonga.repo
```

```groonga.repo
[groonga]
name=Groonga for CentOS $releasever - $basearch
baseurl=https://packages.groonga.org/centos/7/$basearch/
gpgcheck=1
enabled=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-groonga
```

```
$ sudo rpm --import http://packages.groonga.org/centos/RPM-GPG-KEY-groonga

# 途中でMariaDBのrootのパスワード聞かれます
$ sudo yum install -y --enablerepo=epel mariadb-10.3-mroonga

$ sudo yum install -y --enablerepo=epel groonga-tokenizer-mecab
```

```

# MariaDBに接続
$ mysql -u root -p

# ストレージエンジンの情報を表示
MariaDB [(none)]> show engines;
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                                          | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| MRG_MyISAM         | YES     | Collection of identical MyISAM tables                                            | NO           | NO   | NO         |
| CSV                | YES     | Stores tables as CSV files                                                       | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables                        | NO           | NO   | NO         |
| MyISAM             | YES     | Non-transactional engine with good performance and small data footprint          | NO           | NO   | NO         |
| Aria               | YES     | Crash-safe tables with MyISAM heritage                                           | NO           | NO   | NO         |
| Mroonga            | YES     | CJK-ready fulltext search, column store                                          | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, foreign keys and encryption for tables | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                                               | NO           | NO   | NO         |
| SEQUENCE           | YES     | Generated tables filled with sequential values                                   | YES          | NO   | YES        |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.000 sec)

# プラグインの情報を表示
MariaDB [(none)]> show plugins;
+-------------------------------+----------+--------------------+------------------------+---------+
| Name                          | Status   | Type               | Library                | License |
+-------------------------------+----------+--------------------+------------------------+---------+
| binlog                        | ACTIVE   | STORAGE ENGINE     | NULL                   | GPL     |
| mysql_native_password         | ACTIVE   | AUTHENTICATION     | NULL                   | GPL     |
| mysql_old_password            | ACTIVE   | AUTHENTICATION     | NULL                   | GPL     |
| wsrep                         | ACTIVE   | STORAGE ENGINE     | NULL                   | GPL     |
| CSV                           | ACTIVE   | STORAGE ENGINE     | NULL                   | GPL     |
| MEMORY                        | ACTIVE   | STORAGE ENGINE     | NULL                   | GPL     |
| MyISAM                        | ACTIVE   | STORAGE ENGINE     | NULL                   | GPL     |
| MRG_MyISAM                    | ACTIVE   | STORAGE ENGINE     | NULL                   | GPL     |
| CLIENT_STATISTICS             | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INDEX_STATISTICS              | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| TABLE_STATISTICS              | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| USER_STATISTICS               | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| SQL_SEQUENCE                  | ACTIVE   | STORAGE ENGINE     | NULL                   | GPL     |
| InnoDB                        | ACTIVE   | STORAGE ENGINE     | NULL                   | GPL     |
| INNODB_TRX                    | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_LOCKS                  | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_LOCK_WAITS             | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_CMP                    | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_CMP_RESET              | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_CMPMEM                 | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_CMPMEM_RESET           | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_CMP_PER_INDEX          | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_CMP_PER_INDEX_RESET    | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_BUFFER_PAGE            | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_BUFFER_PAGE_LRU        | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_BUFFER_POOL_STATS      | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_METRICS                | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_FT_DEFAULT_STOPWORD    | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_FT_DELETED             | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_FT_BEING_DELETED       | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_FT_CONFIG              | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_FT_INDEX_CACHE         | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_FT_INDEX_TABLE         | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_SYS_TABLES             | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_SYS_TABLESTATS         | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_SYS_INDEXES            | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_SYS_COLUMNS            | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_SYS_FIELDS             | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_SYS_FOREIGN            | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_SYS_FOREIGN_COLS       | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_SYS_TABLESPACES        | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_SYS_DATAFILES          | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_SYS_VIRTUAL            | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_MUTEXES                | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_SYS_SEMAPHORE_WAITS    | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_TABLESPACES_ENCRYPTION | ACTIVE   | INFORMATION SCHEMA | NULL                   | BSD     |
| INNODB_TABLESPACES_SCRUBBING  | ACTIVE   | INFORMATION SCHEMA | NULL                   | BSD     |
| Aria                          | ACTIVE   | STORAGE ENGINE     | NULL                   | GPL     |
| PERFORMANCE_SCHEMA            | ACTIVE   | STORAGE ENGINE     | NULL                   | GPL     |
| SEQUENCE                      | ACTIVE   | STORAGE ENGINE     | NULL                   | GPL     |
| FEEDBACK                      | DISABLED | INFORMATION SCHEMA | NULL                   | GPL     |
| user_variables                | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| partition                     | ACTIVE   | STORAGE ENGINE     | NULL                   | GPL     |
| Mroonga                       | ACTIVE   | STORAGE ENGINE     | ha_mroonga_official.so | GPL     |
+-------------------------------+----------+--------------------+------------------------+---------+
54 rows in set (0.001 sec)

# インストール済みのmroongaのバージョン
> show variables like 'mroonga_%';
+----------------------------------------+----------------------+
| Variable_name                          | Value                |
+----------------------------------------+----------------------+
| mroonga_action_on_fulltext_query_error | ERROR_AND_LOG        |
| mroonga_boolean_mode_syntax_flags      | DEFAULT              |
| mroonga_condition_push_down_type       | ONE_FULL_TEXT_SEARCH |
| mroonga_database_path_prefix           |                      |
| mroonga_default_parser                 | TokenBigram          |
| mroonga_default_tokenizer              | TokenBigram          |
| mroonga_default_wrapper_engine         |                      |
| mroonga_dry_write                      | OFF                  |
| mroonga_enable_operations_recording    | ON                   |
| mroonga_enable_optimization            | ON                   |
| mroonga_libgroonga_embedded            | OFF                  |
| mroonga_libgroonga_support_lz4         | ON                   |
| mroonga_libgroonga_support_zlib        | ON                   |
| mroonga_libgroonga_support_zstd        | ON                   |
| mroonga_libgroonga_version             | 9.0.3                |
| mroonga_lock_timeout                   | 900000               |
| mroonga_log_file                       | groonga.log          |
| mroonga_log_level                      | NOTICE               |
| mroonga_match_escalation_threshold     | 0                    |
| mroonga_max_n_records_for_estimate     | 1000                 |
| mroonga_query_log_file                 |                      |
| mroonga_vector_column_delimiter        |                      |
| mroonga_version                        | 9.03                 |
+----------------------------------------+----------------------+
23 rows in set (0.001 sec)

# mroongaを利用した登録済みUDF一覧
MariaDB [(none)]> select * from mysql.func where dl like '%mroonga%';
+----------------------------+-----+------------------------+----------+
| name                       | ret | dl                     | type     |
+----------------------------+-----+------------------------+----------+
| last_insert_grn_id         |   2 | ha_mroonga_official.so | function |
| mroonga_last_insert_grn_id |   2 | ha_mroonga_official.so | function |
| mroonga_snippet            |   0 | ha_mroonga_official.so | function |
| mroonga_command            |   0 | ha_mroonga_official.so | function |
| mroonga_escape             |   0 | ha_mroonga_official.so | function |
| mroonga_snippet_html       |   0 | ha_mroonga_official.so | function |
| mroonga_normalize          |   0 | ha_mroonga_official.so | function |
| mroonga_highlight_html     |   0 | ha_mroonga_official.so | function |
| mroonga_query_expand       |   0 | ha_mroonga_official.so | function |
+----------------------------+-----+------------------------+----------+
9 rows in set (0.000 sec)

MariaDB [(none)]> exit
Bye

# /etc/my.cnf.d/server.cnfの設定

vi /etc/my.cnf.d/server.cnf
```
```/etc/my.cnf.d/server.cnf
[client]
# 文字化け対策
default-character-set = utf8mb4

[mysqld]
# 文字化け対策(clientのところと追記内容が微妙に違うので注意！)
character-set-server = utf8mb4

innodb_ft_min_token_size=1
```
```
# 設定変えたので再起動
$ sudo systemctl restart mariadb
```

**参考URL**
[Mroongaの完全転置インデックスによる全文検索はどのくらい速いのか](https://blog.wackwack.net/entry/2016/04/02/130257)
[mariaDB10.0.21でmroongaを使ってみる](https://qiita.com/tentatsu/items/140ffc017219c2557461)
[【祝！mroonga】MariaDB-10.0.15 をインストールしてみた【同梱！】](https://qiita.com/ttsan/items/3529657cc4fff88e981e)
[MariaDB 10.0.x - Mroonga プラグインの有効化！](https://www.mk-mode.com/blog/2015/08/21/mariadb-mroonga-installation/#)
[mroongaのインストール/アンインストール時に発行するするクエリと、その用途をまとめて解説](http://y-ken.hatenablog.com/entry/mroonga-create-function)