Ubuntu 18.04 + MariaDB | 技術メモの壁

Ubuntu 標準パッケージの MariaDB を使うにあたってハマりそうな箇所を記述してみる。

DB root ユーザでの接続に失敗する

MariaDB/MySQL データベースを使い始めるときは、まず mysql_secure_installation コマンドで状態を初期化して root パスワードを設定するのが一般的だろう。そして、設定したパスワードを使って DB root 接続し、アプリケーション等に必要なデータベースや DB 一般ユーザを作成する。この手順を実行しようとすると、DB に root ログインできない (パスワード認証が通らない) という事象にぶち当たる。

user@bionic$ sudo mysql_secure_installation (初期化してrootパスワードを設定)
Set root password? [Y/n]y
New password: testpass01
Re-enter new password: testpass01
Remove anonymous users? [Y/n]y
Disallow root login remotely? [Y/n]y
Remove test database and access to it? [Y/n]y
Reload privilege tables now? [Y/n]y

user@bionic$ mysql -u root -p
Enter password: testpass01
ERROR 1698 (28000): Access denied for user 'root'@'localhost' ←認証エラー！

上記動作の原因は、初期状態の DB root ユーザ認証に unix_socket 認証プラグインが使われていることである。この状態では、DB root として接続するために OS の root 権限にならなければならない (sudo または su コマンドを使う)。OS root になっていれば、以下のようにパスワードなしで DB root として接続可能である。

user@bionic:~$ sudo mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 35
Server version: 10.1.29-MariaDB-6 Ubuntu 18.04

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

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

MariaDB [(none)]>

DB root でログインすると、認証プラグインの状態も確認できる。

MariaDB [(none)]> SELECT user,host,plugin from mysql.user;
+------+-----------+-------------+
| user | host      | plugin      |
+------+-----------+-------------+
| root | localhost | unix_socket |　← unix_socket 認証プラグインが使われている
+------+-----------+-------------+
1 row in set (0.00 sec)

さて、この認証方式をそのまま使うか？それとも昔ながらのパスワード認証に戻すか？

初期状態の認証設定でそのまま利用する

初期設定に乗っかっていくなら、DB の root パスワードを設定する必要がない。

また一般ユーザでも同一の仕組みを使う場合は、UNIX ユーザと DB ユーザを同名で作成すればよい。

(ただし、この認証方式では外部ホストから TCP/3306 経由でログインできないことになる。必ずローカルの UNIX ドメインソケット経由でなければならない)

以下はブログエンジン WordPress を「wordpress」というユーザ名で利用する場合の例。OS の wordpress ユーザを作り、DB の wordpress ユーザも作成する。

user@bionic:~$ sudo useradd -m -s /bin/bash wordpress
user@bionic:~$ sudo mysql
MariaDB [(none)]> CREATE USER wordpress@localhost IDENTIFIED VIA unix_socket;

wordpress ユーザで接続できるかどうか確認する。

user@bionic:~$ sudo -i -u wordpress
wordpress@bionic:~$ mysql -u wordpress
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 57
Server version: 10.1.29-MariaDB-6 Ubuntu 18.04

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

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

MariaDB [(none)]> \q
Bye

このやり方では、WordPress のファイル群を実行する PHP の実行ユーザが wordpress ユーザである必要がある。例えば PHP 実行環境に php-fpm を使っている場合は、/etc/php/7.2/fpm/pool.d/www.confを編集して php-fpm 実行ユーザを wordpress に変更する。

/etc/php/7.2/fpm/pool.d/www.conf 抜粋

user = wordpress
group = wordpress

wordpressデータベースの作成

user@bionic:~$ sudo mysql
MariaDB [(none)]> CREATE DATABASE wordpress;
MariaDB [(none)]> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,INDEX,ALTER ON wordpress.* TO wordpress@localhost;

wordpress 側の設定 (wp-config.php) では、パスワードを空にしておく。

define('DB_NAME', 'wordpress');
define('DB_USER', 'wordpress');
define('DB_PASSWORD', '');
define('DB_HOST', 'localhost');

従来のパスワード認証を使っていく場合

DB の root ユーザを従来のパスワード認証に変更する (以前の方式に戻す) 。私自身は、こちらの手法で行くことにする。

user@bionic:~$ sudo mysql
MariaDB [(none)]> set password for 'root'@'localhost'=password('testpass02');
MariaDB [(none)]> use mysql;
MariaDB [mysql]> update user set plugin='' where user='root';
MariaDB [mysql]> flush privileges;
MariaDB [mysql]> \q

root ユーザの認証プラグインを外し、通常のパスワードを設定している。

一般ユーザについても、通常のパスワードを設定して作成する。

user@bionic:~$ mysql -u root -p
Enter password: testpass02
MariaDB [(none)]> CREATE USER wordpress@localhost IDENTIFIED BY 'testpass03';

この場合、ログローテートの処理 (/etc/logrotate.d/mysql-server の16行目) にrootパスワードが必要になる。/etc/mysql/debian.cnf の password 行に平文パスワードを記述しておくこと。
user@bionic:~$ sudo vi /etc/mysql/debian.cnf

5行目、10行目
password = testpass02