Ubuntu 16.04 + MariaDB | 技術メモの壁

※以下のサイトを参考に全面改訂しました(2017/09/12)
Ubunt 16.04でMariaDBをインストールするとパスワードが変

MySQL から MariaDB に移行するにあたって、パッケージが前提とする流儀が違ったり、罠があったりするので手当てをする。

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

通常は最初に DB root ユーザで DB エンジンに接続して、Web アプリケーションの要求するデータベースやDBユーザを作成するだろう。これを実行しようとすると、OS の一般ユーザのコマンドラインから DB に root ログインできないという事象にぶち当たる。

user@xenial:~$ mysql -u root -p
Enter password:
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

mysql_secure_installationで初期化を実行しても効果がない。

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

user@xenial:~$ sudo mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 42
Server version: 10.0.34-MariaDB-0ubuntu0.16.04.1 Ubuntu 16.04

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

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)

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

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

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

例) wordpress を「wordpress」というユーザ名で利用する場合

user@xenial:~$ sudo useradd -m -s /bin/bash wordpress (OS ユーザの作成)
user@xenial:~$ sudo mysql
MariaDB [(none)]> CREATE USER wordpress@localhost IDENTIFIED VIA unix_socket; (DB ユーザの作成)

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

user@xenial:~$ sudo -i -u wordpress
wordpress@xenial:~$ mysql -u wordpress
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 46
Server version: 10.0.34-MariaDB-0ubuntu0.16.04.1 Ubuntu 16.04

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)]> \q
Bye

※上記の方式でwordpressを動かす場合は、PHP の実行ユーザが wordpress ユーザである必要がある。例えば PHP 実行環境に php-fpm を使っている場合は、/etc/php/7.0/fpm/pool.d/www.confを編集して php-fpm 実行ユーザを wordpress に変更する。

※この認証方式では、TCP/3306 経由ではログインできないことになる。必ずローカルの UNIX ドメインソケット経由でなければならない。

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

DB の root ユーザを従来のパスワード認証に変更したい (以前の方式に戻す) 場合は、以下の手順を実行する。

user@xenial:~$ sudo mysql
MariaDB [(none)]> set password for 'root'@'localhost'=password('パスワード文字列');
MariaDB [(none)]> use mysql;
MariaDB [mysql]> update user set plugin='' where user='root';
MariaDB [mysql]> flush privileges;
MariaDB [mysql]> \q

root ユーザの認証プラグインを外し、通常のパスワードを設定している。この対処を実施した場合は、/etc/mysql/debian.cnf に平文パスワードを記述してやらないと systemctl での起動・停止が動作しなくなる。

user@xenial:~$ sudo vi /etc/mysql/debian.cnf

[client]
host     = localhost
user     = root
password = rootのパスワード文字列
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host     = localhost
user     = root
password = rootのパスワード文字列
socket   = /var/run/mysqld/mysqld.sock

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

MariaDB [(none)]> CREATE USER wordpress@localhost IDENTIFIED BY 'パスワード文字列';

4. localhost の 3306/tcp に接続できない

アプリケーションから localhost あてに接続すると、システム上 IPv6 (::1) が優先される。しかし MariaDB デフォルト設定の bind-address は 127.0.0.1 になっている。

このせいで、アプリケーションの設定で、DB サーバのホスト名 / IP アドレス指定を “localhost” にしてあると接続できない。アプリケーション側での DB サーバ指定を 127.0.0.1 とするか、MariaDB 側の設定ファイル /etc/mysql/mariadb.conf.d/50-server.cnf で bind-address を ::1 にする必要がある。