MariaDB, インストール直後にやるべき作業。初期設定は常識です。

MariaDBには、インストール後にやっておくべき初期設定がふたつあります。設定ファイルの編集とmysql_secure_installationコマンドの実行。

rootパスワード設定や、だれでも使える匿名ユーザーの削除などのセキュリティ対策をします。

今回の内容はMySQLと互換性があります。（MySQLでも同じ。）

（ただし、設定ファイル名がちがうこともある。）

検証バージョン
MariaDB 10.4.11

インストール後にやること
設定ファイルの編集
文字コードの初期状態
UTF8は『utf8mb4』を使う
クライアントの文字コード変更
DBサーバーの文字コード変更
セキュアな初期設定の実行
作業前の状態
mysql_secure_installationコマンド実行
新しいデーターベース・ユーザーを追加

インストール後にやること

インストール直後のMariaDBは、ユーザー・パスワードなしでログインできたり、設定がほぼない状態だったり、ザルです。

MariaDB, Linux(CentOS, Ubuntu)に最新版をインストールする

たとえば、OS一般ユーザーでもデータベースにかんたんにログインできます。

mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 10.4.11-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)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.000 sec)

MariaDB [(none)]>

ログインユーザー・パスワード無しで、『mysql』と打つだけで見れちゃいます。infomation_schemaまでイジれてしまっては、セキュリティはないも同然です。

infomation_schemaはDBシステムの根幹です。これにおかしな変更を加えるとデータベースが動きません。

これをどうにかしないといけないのと最低限の設定も必要です。

設定ファイルの編集

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

/etc/my.cnf.d/

にあります。インストール直後は何も設定されていません。今回はとりあえず、文字コードの設定をUTF-8に変更しましょう。

ほか、ログ設定やリカバリ・ポイント作成、接続ポート番号変更、DBサーバープロセスのチューニングなど、いろんなことをしますが、必要なときに設定すればいいです。

文字コードの初期状態

いまの状態をSQL実行で確認します。

mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 19
Server version: 10.4.11-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)]>

まずは文字コードから。

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/ |
+--------------------------+----------------------------+
8 rows in set (0.001 sec)

character_set_client	DBを操作するクライアントの文字コード
character_set_connection	数値->文字列変換などで使う文字コード
character_set_database	デフォルトDBの文字コード。デフォルトDBがないとき、character_set_serverと同じ値。単独で変更しない。
character_set_filesystem	ファイル名の文字コード。 binaryは文字コード変換をしない。このままでいい。というか変えると面倒。
character_set_results	SQL結果やエラーメッセージの文字コード。
character_set_server	DBサーバーの文字コード。『Debianの文字コードとのちがいを見てください。』とあるので、OS毎にデフォルトがちがうらしい。データベース・テーブル・カラム作成に影響する。
character_set_system	つねにutf8。変更しない。意味を知らなくていい。

デフォルトは、クライアントとSQL結果はutf8を使うようですね？（DBサーバーはlatin1）

もうひとつ、文字コードと関係が深い照合順序（Collation）も確認しておきます。

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

照合順序は文字列を比較するときのルールです。大文字小文字を区別する・しないとか。

値を見れば分かるように文字コードが関係しています。

ちなみに、データベースの操作の終了はquitです。

MariaDB [(none)]> quit
Bye

UTF8は『utf8mb4』を使う

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 |
+---------+---------------+--------------------+--------+
1 row in set (0.000 sec)

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 |
+------------------------------+---------+------+---------+----------+---------+
33 rows in set (0.001 sec)

結果に表示されるので使えます。

クライアントの文字コード変更

まずはSQLクライアントの設定を変更します。

mysql-clients.cnf

[mysql]
default-character-set=utf8mb4  # <--- 追加

データベースを再起動しましょう。

systemctl restart mariadb

MariaDBのsystemdでは、設定ファイルのリロードができません。再起動で代用します。

systemctl reload mariadb
Failed to reload mariadb.service: Job type reload is not applicable for unit mariadb.service.
See system logs and 'systemctl status mariadb.service' for details.

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/ |
+--------------------------+----------------------------+
8 rows in set (0.001 sec)

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

クライアント側がまとめて変更できました。

character_set_client	utf8mb4
character_set_connection	utf8mb4
character_set_results	utf8mb4
collation_connection	utf8mb4_general_ci

default-character-setは変数ではなくオプション

***.cnfでは、設定方法がふたつあります。

変数を指定する。

オプションを指定する。

default-character-setは、クライアントの文字コードを変更する『オプション』です。

だから、クライアントに関係する複数の変数がまとめて変更できたんですね？

区別がつきにくいですが、

変数は『アンダーバー（_）』

オプションは『ハイフン（-）』

と覚えておきましょう。

変数指定では、ひとつひとつ変更しないといけない、変数の関係を知っておかないといけないので面倒。オプションでできるならそっちを優先する。

（オプション指定の後ろに変数指定を書けば併用可。）

default-character-setはクライアントの文字コードを変更するオプション。サーバーサイドでは使えない。

MySQL公式ドキュメント - mysqlのオプション

DBサーバーの文字コード変更

こんどはDBサーバーの文字コードを変更します。

server.cnf

[mysqld]
character-set-server = utf8mb4  # <--- 追加

DBサーバー再起動

systemctl restart mariadb

結果はこうなります。

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/ |
+--------------------------+----------------------------+
8 rows in set (0.001 sec)

MariaDB [(none)]> show variables like "%coll%";
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+
3 rows in set (0.001 sec)

character_set_database	utf8mb4
character_set_server	utf8mb4
collation_database	utf8mb4_general_ci
collation_server	utf8mb4_general_ci

4つの変数が変更されました。これで文字コードの変更は終わりです。

"character-set-server"はオプションで、変数"character_set_server"でないことに注意。

よく見ると『-』と『_』でちがう。

MySQL公式ドキュメント

サーバーコマンドオプション

MariaDB公式ドキュメント

Server System Variables

Setting Character Sets and Collations

Configuring MariaDB with Option Files

セキュアな初期設定の実行

インストール直後のMariaDBはユーザー設定がユルユルです。

（だからかんたんにログインできる。）

作業前の状態

いまのユーザーを確認します。

MariaDB [(none)]> select host, user, password from mysql.user;
+-----------------------+-------+----------+
| Host                  | User  | Password |
+-----------------------+-------+----------+
| localhost             | root  | invalid  |
| localhost             | mysql | invalid  |
| localhost             |       |          |
| localhost.localdomain |       |          |
+-----------------------+-------+----------+
4 rows in set (0.056 sec)

rootパスワードが無効、ユーザー・パスワードがないもの（匿名ユーザー）が2つもあります。これが無条件にログインできる正体。

ついでにユーザーの権限設定も見ときましょう。

MariaDB [(none)]> show grants;
+-----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                               |
+-----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |
+-----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

この設定を変えるツールが用意されています。

mysql_secure_installationコマンド

これを実行してゆるい状態を直します。

mysql_secure_installationコマンド実行

さっそくコマンドを実行しましょう。

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):

このコマンドはいろいろな入力を求められます。

MariaDBにログインしてセキュリティを確保するための現在の
rootユーザーのパスワード。 MariaDBをインストールしたばかりで、まだルートパスワードを設定していない場合は、ここでEnterキーを押すだけです。
和訳

rootパスワードはないのでEnter

Enter current password for root (enter for none):
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]

rootパスワードを設定するか、unix_socketを使用すると、適切な承認なしに誰もMariaDB rootユーザーにログインできなくなります。
ルートアカウントは既に保護されているため、「n」と安全に答えることができます。
和訳

Unix Socket AuthenticationってSSLのことか？sshトンネルを使うのでいらない。（後で説明）

保護されてる？意味わからんけど、そういうなら'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]

rootパスワードを設定します。

新しいパスワード、再入力の2回

Change the root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
 ... Success!


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]

デフォルトでは、MariaDBインストールには匿名ユーザーが含まれており、ユーザーアカウントを作成しなくても誰でもMariaDBにログインできます。これはテストのみを目的としており、インストールを少しスムーズにするためのものです。実稼働環境に移行する前にそれらを削除する必要があります。
和訳

削除しましょう。y

Remove anonymous users? [Y/n] y
 ... 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]

通常、rootは「localhost」からの接続のみを許可する必要があります。これにより、誰かがネットワークからルートパスワードを推測できないようになります。
rootのリモートログインを許可しない？
和訳

リモートログインは遮断しましょう。y

Disallow root login remotely? [Y/n] y
 ... 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]

デフォルトでは、MariaDBには「test」という名前のデータベースが付属しており、誰でもアクセスできます。これもテストのみを目的としており、実稼働環境に移行する前に削除する必要があります。
和訳

いりません。削除しましょう。y

Remove test database and access to it? [Y/n] y
 - 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

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!

設定が終わりました。ユーザーの確認をしましょう。

まずはOSの一般ユーザーでログインしてみます。

mysql
ERROR 1698 (28000): Access denied for user 'vagrant'@'localhost'

OKですね？ちなみにOSのrootユーザーでは今までと同じようにログインできます。

ユーザーテーブルを確認しましょう。

MariaDB [(none)]> select host, user, password from mysql.user;
+-----------+-------+-------------------------------------------+
| Host      | User  | Password                                  |
+-----------+-------+-------------------------------------------+
| localhost | root  | *3800D13EE735ED411CBC3F23B2A2E19C63CE0BEC |
| localhost | mysql | invalid                                   |
+-----------+-------+-------------------------------------------+
2 rows in set (0.001 sec)

テストデータベースが削除されてるか見てみます。

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.025 sec)

大丈夫ですね？

（mysql, performance_schemaデータベースが追加されて、ついでにデータベースの初期化までやってくれてる。）

MariaDB公式ドキュメント

mysql_secure_installation

新しいデーターベース・ユーザーを追加

OS一般ユーザーがログインできるデータベースがないので追加しましょう。ついでに、新しいユーザーも追加します。

（mysqlユーザーは元からあるのでセキュリティ上使いたくない。）

ユーザー追加

MariaDB [(none)]> create user 'mine'@'localhost' identified by 'mine';
 Query OK, 0 rows affected (0.022 sec)

テストなのでパスワードを'mine'にしてますが、きちんとした不規則な文字列を指定してください。

ホストはlocalhostだけでいいです。いまのデータベース接続はsshトンネルを使うのが一般的なので。

sshトンネル

直接データベースに接続しないでsshを介してつなげる。

データベースのポートを外に開かなくていいのでセキュリティ上よい。

ssh接続
↓
sshログインユーザーがOSシステム内でlocalhostとしてDB接続
↓
SQL結果をsshを介して外に出す

データベースを追加します。

MariaDB [(none)]> create database mydb;
Query OK, 1 row affected (0.001 sec)

作ったデータベース（mydb）の権限に新しいユーザーを追加します。

MariaDB [(none)]> grant all privileges on mydb.* to 'mine'@'localhost' identified by 'mine';
Query OK, 0 rows affected (0.001 sec)

（mydbデータベースのすべての権限（on mydb.*）をmine@localhostに与える。）

権限確認

MariaDB [(none)]> show grants for 'mine'@'localhost';
+-------------------------------------------------------------------------------------------------------------+
| Grants for mine@localhost                                                                                   |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'mine'@'localhost' IDENTIFIED BY PASSWORD '*EE22DF7E45450AC6A912653D2EBE8C0BDA759EEB' |
| GRANT ALL PRIVILEGES ON `mydb`.* TO 'mine'@'localhost'                                                      |
+-------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

OS一般ユーザーでデータベースに接続しましょう。

mysql mydb -umine -pmine
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 47
Server version: 10.4.11-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 [mydb]>

とりあえず、DBの初期設定は終了です。

前の投稿

MariaDB, Linux(CentOS, Ubuntu)に最新版をインストールする

MongoDB, CentOS7にインストールする。リポジトリ作成からのyum実行。

次の投稿