Ubunt 16.04でMariaDBをインストールするとパスワードが変

発表されて1ヶ月経っていないUbuntu 16.04、私は早くもアップグレード予定を立てて作業を行っています。

さっそく流行物を使ってみようと色々試しているのですが当然ながらハマりまくりです、今回はその中でも特にハマったMariaDBのパスワードの不具合と解決策をご紹介しましょう。

MariaDBのパスワードが変

多分この記事をご覧の方であれば既にこの問題に当たっている方でしょうが、一応説明しましょう。

Ubuntu 16.04にてMariaDBをインストールするべく「sudo apt-get install mariadb-server」を実行するのですが、インストール中にパスワードを求める画面が出ない、訳も分からず「mysql -u root」でログインしようとしてもエラー、「sudo mysql -u root」だとログイン出来ると言う意味不明な状況です。

test@ubuntu:~$ mysql -u root
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
test@ubuntu:~$ mysql -u root -p
Enter password: 
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
test@ubuntu:~$ sudo mysql -u root
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 44
Server version: 10.0.24-MariaDB-7 Ubuntu 16.04

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

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

MariaDB [(none)]>

ちなみに「sudo  mysql_secure_installation」でパスワードを再設定しても上手く行きません。

アカウントのテーブルを確認してみる

「sudo mysql -u root」であれば普通にログイン出来るので、ユーザアカウントの確認をしてみましょう。
(記事の都合上不要な所をカットしてますが、実際はもっと色々表示されます)

test@ubuntu:~$ sudo mysql -u root
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 44
Server version: 10.0.24-MariaDB-7 Ubuntu 16.04

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

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

MariaDB [(none)]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]> SELECT * FROM user;
+-----------+------+----------+------------------------+-------------+
| Host      | User | Password | Create_tablespace_priv | plugin      |
+-----------+------+----------+------------------------+-------------+
| localhost | root |          | Y                      | unix_socket |
+-----------+------+----------+------------------------+-------------+
1 row in set (0.00 sec)

パスワードが空で設定されている事が伺えますね、でも空ではログイン出来ませんでしたよ?

プラグインがunix_socketになっていますし……

比較のために普通にインストール出来るUbuntu 14.04上での結果を見てみましょう。

+-----------+------------------+-------------------------------------------+------------------------+--------+
| Host      | User             | Password                                  | Create_tablespace_priv | plugin |
+-----------+------------------+-------------------------------------------+------------------------+--------+
| localhost | root             | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | Y                      |        |
| ubuntu    | root             | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | Y                      |        |
| 127.0.0.1 | root             | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | Y                      |        |
| ::1       | root             | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | Y                      |        |
| localhost | debian-sys-maint | *1B9C443425E931622789A0DFE0AE0062F5B0F3CF | N                      |        |
+-----------+------------------+-------------------------------------------+------------------------+--------+

localhost、127.0.0.1、::1、そしてホスト名(上の結果ではubuntuとなっている所)に対してハッシュ化された同じパスワードが設定されています。

そして、「debian-sys-maint」ユーザ用のアカウントが存在します、どのアカウントもプラグインは使用されていません。

スマートな解決方法

コメント欄で正しい解決方法を教えて頂きましたのでこちらを先に紹介しておきます。

可能な限りこちらの手法で解決した方が良いでしょう。

原因

Ubuntu 16.04のMariaDBではrootユーザに「UNIX_SOCKET Authentication Plugin」が有効にされているらしく、これが有効になっていると認証のやり方が変わる。

今まではパスワードで認証するのが普通だったが、この方法ではアクセスしようとしているUnixユーザを利用して認証する。(MariaDBのrootに接続出来るのがUnixアカウントのrootだけになる、と言う訳)

説明が難しいが、すなわちUnixユーザ「test」が接続する時にはMariaDB側にも「test」ユーザが登録されている必要があると言う訳。

なので必然的にTCPでの接続は出来なくなるっぽい。

対処法

新しいユーザを作成する必要がある。

sudo(root)でMariaDBに接続し「CREATE USER (新しいアカウント名) IDENTIFIED BY '新しいパスワード'」のクエリを実行する。

CREATE USER 'hoge'@'localhost' IDENTIFIED BY '123';

これでhogeがlocalhostからパスワード123でアクセス出来るようになる。

触れるDBなどは別途GRANTする。

GRANT ALL ON foo.* TO 'hoge'@'localhost';

localhost以外に127.0.0.1と::1も追加しておいた方が良いかも知れない。

ちなみにこの時に「VIA unix_socket;」とすると、作成されたアカウントもunix_socketでしか接続出来なくなる。

例えば同じサーバ上のPHP(www-dataで実行)からアクセスしたい場合は以下の様な感じにするとパスワードが省略できる。

CREATE USER www-data IDENTIFIED VIA unix_socket;

それ以外の、例えばMySQL Connecter/J(Java)の様なTCP接続でしか利用出来ないアプリケーションからの接続であれば、unix_socketは使えない(普通にパスワード認証にする必要がある)

CREATE USER hoge IDENTIFIED BY '123';

必然的にrootアカウントでのアプリケーションの利用が出来なくなる、延いてはアカウントの新規作成を強制するので、セキュリティとしては確かに理にかなっているのかも知れない。

スマートじゃない解決方法

こちらが前まで掲載していた解決方法です。

要は無理矢理従来までのアクセス方法(パスワード)に変えている訳ですので、可能であればスマートな方の解決方法を用いた方が良いでしょう。

解決策A(プラグイン無効化)

要はunix_socketプラグインをなくしてしまえばいいわけで……

まずは「sudo mysql -u root」でログインした後に以下の3コマンドを実行しましょう

use mysql;
update user set plugin='' where User='root';
flush privileges;

次に「sudo mysql_secure_installation」を利用してパスワードを変更します。

解決策B(パスワードリセット)

unix_socketの解除に加えて、従来までと同じようなアカウント構成にしてしまうやり方。

説明が面倒なので以下コピペ

use mysql;
delete from user where User='root';
delete from user where User='debian-sys-maint';
flush privileges;
grant all privileges on *.* to 'root'@'localhost' identified by '任意のパスワード' with grant option;
grant all privileges on *.* to 'root'@'127.0.0.1' identified by '任意のパスワード' with grant option;
grant all privileges on *.* to 'root'@'::1' identified by '任意のパスワード' with grant option;
grant all privileges on *.* to 'root'@'ホスト名' identified by '任意のパスワード' with grant option;
grant all privileges on *.* to 'debian-sys-maint'@'localhost' identified by 'debian-sys-maintのパスワード' with grant option;
update user set Create_tablespace_priv="N" WHERE User='debian-sys-maint' ;
flush privileges;

上記のクエリをコピーしてテキストエディタとかで「任意のパスワード」を置換すると楽です、ホスト名は「hostname」で手に入る物と置き換えて下さい。

「debian-sys-maintのパスワード」は英字大小+数字を16文字のようです、それに合わせてパスワード生産ソフトでも使うと良いでしょう。

もしくは、CUIから以下のコマンドを使えばランダムな文字が手に入ります。

cat /dev/urandom | tr -dc '[:alnum:]' | head -c 16

これに合わせて/etc/mysql/debian.cnfを更新する必要があります、具体的にはuserの所を「debian-sys-maint」にして、passwordを先程設定したパスワードにして下さい。

え？めんどくさい？そのためにわざわざスクリプト作ったのでどうぞ

(参考：Ubuntu 16.04 + MariaDB - 技術メモの壁)

※ちなみに、MariaDBのレポジトリから提供されるパッケージであれば(2016/05/20の時点では)今までのパスワードでの接続になっています。(今後どうなるかは知らない)

 - サーバー Ubuntu