令和3 年 2月 15日
CentOS7 MariaDB 10.5 の導入
-------------------------------------------------------

# mysql --version
mysql  Ver 15.1 Distrib 5.5.60-MariaDB, for Linux (x86_64) using readline 5.1

CentOS7 の MariaDB 5.5 をアンインストールする。
# yum list installed | grep -i mariadb

mariadb.x86_64                            1:5.5.68-1.el7             @base
mariadb-libs.x86_64                       1:5.5.68-1.el7             @base
mariadb-server.x86_64                     1:5.5.68-1.el7             @base

MariaDBを停止する
# systemctl status mariadb.service
# systemctl stop mariadb.service

# yum remove mariadb		<- mariadb-server も削除される。
# yum mariadb-libs		<- postfix も依存性で削除されしまう。

# yum remove mariadb

上記の処理を行います。よろしいでしょうか？ [y/N]y

完了しました!
# 

削除されずに残っているファイル・ディレクトリがあるので、手動で削除します。

ディレクトリ	 説明
/var/lib/mysql   デフォルトのデータファイル、ログファイルの格納先
/etc/my.cnf.d    オプションファイルの格納先
/data            このディレクトリにデータファイル、手動で削除する

# rm -rf /var/lib/mysql
# rm -rf /etc/my.cnf.d
# rm -rf /data


PHP を削除する。
# php -v

インストール済みのPHPを削除する場合。
# yum remove php-*

# reboot		<- 再起動

CentOS7 の MariaDB 10.1 をインストールする。

# curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | bash
[info] Repository file successfully written to /etc/yum.repos.d/mariadb.repo
[info] Adding trusted package signing keys...
[info] Successfully added trusted package signing keys
[root@web ~]#

これでリポジトリ登録は完了です。

MariDB 10.1 インストール
# yum install MariaDB-server MariaDB-client
    |
インストール:
  MariaDB-client.x86_64 0:10.5.8-1.el7.centos     MariaDB-server.x86_64 0:10.5.8-1.el7.centos    
依存性関連をインストールしました:
  MariaDB-common.x86_64 0:10.5.8-1.el7.centos     MariaDB-compat.x86_64 0:10.5.8-1.el7.centos   
  boost-program-options.x86_64 0:1.53.0-28.el7    galera-4.x86_64 0:26.4.6-1.el7.centos         
  socat.x86_64 0:1.7.3.2-2.el7                   
完了しました!

# mysql --version
mysql  Ver 15.1 Distrib 10.5.8-MariaDB, for Linux (x86_64) using readline 5.1

MariaDB の状態を確認する。
# systemctl status mariadb.service
# systemctl start mariadb.service	<- 開始でパスワード<Enter> が可能
# systemctl enable mariadb.service	<- 起動時ON

# mysql -u root -p
Enter password: 			<- 過去のパスワードになっている場合がある　admin1234
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

# mysql -u root -p
Enter password: <Enter>
  |
MariaDB [(none)]> show variables like "char%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

MariaDB [(none)]> show variables like "%coll%";
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+

MariaDBのutf8は絵文字が文字化けして問題です。utf8はサイズが3byteで絵文字が使えません。
UTF8での絵文字は4byteで表現するのでutf8mb4に変える必要があります。
データベースがutf8mb4を使えるか確認します。

MariaDB [(none)]> show charset like "%utf8mb4%";
+---------+---------------+--------------------+--------+
| Charset | Description   | Default collation  | Maxlen |
+---------+---------------+--------------------+--------+
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci |      4 |
+---------+---------------+--------------------+--------+

MariaDB [(none)]> show collation like "%utf8mb4%";
+------------------------------+---------+------+---------+----------+---------+
| Collation                    | Charset | Id   | Default | Compiled | Sortlen |
+------------------------------+---------+------+---------+----------+---------+
| utf8mb4_general_ci           | utf8mb4 |   45 | Yes     | Yes      |       1 |
| utf8mb4_bin                  | utf8mb4 |   46 |         | Yes      |       1 |
| utf8mb4_unicode_ci           | utf8mb4 |  224 |         | Yes      |       8 |
.
.
| utf8mb4_unicode_nopad_ci     | utf8mb4 | 1248 |         | Yes      |       8 |
| utf8mb4_unicode_520_nopad_ci | utf8mb4 | 1270 |         | Yes      |       8 |
+------------------------------+---------+------+---------+----------+---------+

MariaDBの設定ファイルを編集します。

/etc/my.cnf.d/mysql-clients.cnf
[mysql]
default-character-set=utf8mb4		<- 追加

データベースを再起動しましょう。
# systemctl restart mariadb

# mysql -u root -p
   |
MariaDB [(none)]> show variables like "char%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

MariaDB [(none)]> show variables like "%coll%";
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | latin1_swedish_ci  |
| collation_server     | latin1_swedish_ci  |
+----------------------+--------------------+

/etc/my.cnf.d/server.cnf
[mysqld]
character-set-server = utf8mb4		<- 追加

# systemctl restart mariadb

# mysql -u root -p
MariaDB [(none)]> show variables like "%char%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

MariaDB [(none)]> show variables like "%coll%";
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+

# mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
haven't set the root password yet, you should just press enter here.

Enter current password for root (enter for none):    #### Enter
OK, successfully used password, moving on...

Setting the root password or using the unix_socket ensures that nobody
can log into the MariaDB root user without the proper authorisation.

You already have your root account protected, so you can safely answer 'n'.

Switch to unix_socket authentication [Y/n] n         #### ソケット認証を有効にするか
 ... skipping.

You already have your root account protected, so you can safely answer 'n'.

Change the root password? [Y/n] n        #### root パスワードを変更するか
 ... skipping.

By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] n                   #### 匿名ユーザを削除するか、Y で Error!
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] Y             #### root ユーザのリモートログインを無効にするか
 ... Success!

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] Y    #### テスト DB を削除するか
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] Y              #### ユーザ権限テーブルをリロードするか
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

# mysql -u root -p
Enter password: admin1234					<- この場合、admin1234
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.5.8-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)]> exit						<- exit
Bye