RaspiOS MariaDBのインストール

スポンサーリンク

Raspberry Pi OSに「MariaDB」をインストールします。ユーザー認証まわりで、少し悩みました。

システム環境

$ uname -rm
5.4.72-v7l+ armv7l
$ lsb_release -a
No LSB modules are available.
Distributor ID: Raspbian
Description:    Raspbian GNU/Linux 10 (buster)
Release:        10
Codename:       buster

MariaDBのインストール

$ sudo apt install mariadb-server ← aptコマンドでインストール
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following additional packages will be installed:
  galera-3 gawk libaio1 libcgi-fast-perl libcgi-pm-perl libconfig-inifiles-perl
  libdbd-mysql-perl libdbi-perl libencode-locale-perl libfcgi-perl
  libhtml-parser-perl libhtml-tagset-perl libhtml-template-perl libhttp-date-perl
  libhttp-message-perl libio-html-perl liblwp-mediatypes-perl libmariadb3
  libreadline5 libsigsegv2 libsnappy1v5 libterm-readkey-perl libtimedate-perl
  liburi-perl lsof mariadb-client-10.3 mariadb-client-core-10.3 mariadb-common
  mariadb-server-10.3 mariadb-server-core-10.3 mysql-common socat
Suggested packages:
  gawk-doc libclone-perl libmldbm-perl libnet-daemon-perl libsql-statement-perl
  libdata-dump-perl libipc-sharedcache-perl libwww-perl mailx mariadb-test tinyca
The following NEW packages will be installed:
  galera-3 gawk libaio1 libcgi-fast-perl libcgi-pm-perl libconfig-inifiles-perl
  libdbd-mysql-perl libdbi-perl libencode-locale-perl libfcgi-perl
  libhtml-parser-perl libhtml-tagset-perl libhtml-template-perl libhttp-date-perl
  libhttp-message-perl libio-html-perl liblwp-mediatypes-perl libmariadb3 libreadline5
  libsigsegv2 libsnappy1v5 libterm-readkey-perl libtimedate-perl liburi-perl lsof
  mariadb-client-10.3 mariadb-client-core-10.3 mariadb-common mariadb-server
  mariadb-server-10.3 mariadb-server-core-10.3 mysql-common socat
0 upgraded, 33 newly installed, 0 to remove and 0 not upgraded.
Need to get 18.6 MB of archives.
After this operation, 151 MB of additional disk space will be used.
Do you want to continue? [Y/n] Y
   : (割愛)
$ systemctl status mariadb.service
● mariadb.service - MariaDB 10.3.25 database server
   Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
   Active: active (running) since Sun 2020-11-29 13:44:03 GMT; 5min ago
     Docs: man:mysqld(8)
           https://mariadb.com/kb/en/library/systemd/
 Main PID: 1242 (mysqld)
   Status: "Taking your SQL requests now..."
    Tasks: 30 (limit: 4915)
   CGroup: /system.slice/mariadb.service
           └1242 /usr/sbin/mysqld

MariaDBの構成

インストール直後は、rootユーザーのパスワードは設定されていません。また、「unix_socket」プラグインが有効になっています。このプラグインは、Unixユーザーを利用して認証するためのプラグインです。

$ sudo mariadb
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 59
Server version: 10.3.25-MariaDB-0+deb10u1 Raspbian 10

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]> select user,password,plugin from user;
+------+----------+-------------+
| user | password | plugin      |
+------+----------+-------------+
| root |          | unix_socket | ← パスワードは設定がされていない
+------+----------+-------------+
1 row in set (0.001 sec)

MariaDB [none]> exit
Bye

付属の「mysql_secure_installation」を使用して、セキュリティー的に良くないデフォルト設定を変更します。

$ sudo 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
you haven't set the root password yet, the password will be blank,
so 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 ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set 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] 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] 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 ← テスト用データベースとアクセスは削除する
 - 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!

特権ユーザーの作成と権限付与

rootユーザーは、unix_socketプラグインを利用して認証するようにし、個別にデータベースに対して、ユーザーを作成して、その代わり、別途、特権ユーザーを作成して、パスワードを入力してログインができるようにします。

$ sudo mariadb
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 79
Server version: 10.3.25-MariaDB-0+deb10u1 Raspbian 10

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)]> create user 'db1user'@'localhost' identified by 'db1pass';
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> grant all on db1.* to 'db1user'@'localhost';
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> select user,password,plugin from  mysql.user;
+---------+-------------------------------------------+-------------+
| user    | password                                  | plugin      |
+---------+-------------------------------------------+-------------+
| root    |                                           | unix_socket |
| db1user | *7C5505............................21FB8D |             |
+---------+-------------------------------------------+-------------+
2 rows in set (0.001 sec)

MariaDB [(none)]> create user 'db2user'@'localhost' identified via unix_socket;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> grant all on db2.* to 'db2user'@'localhost';
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> select user,password,plugin from  mysql.user;
+---------+-------------------------------------------+-------------+
| user    | password                                  | plugin      |
+---------+-------------------------------------------+-------------+
| root    |                                           | unix_socket |
| db1user | *7C55059CBCD2275510DB535D59FC71505F21FB8D |             |
| db2user |                                           | unix_socket |
+---------+-------------------------------------------+-------------+
3 rows in set (0.001 sec)

MariaDB [(none)]> exit
Bye

リモート接続の設定

Raspberry Pi OSのMariaDBは、初期の設定ファイルで「bind-address」がローカルアドレスとなっているので、リモートからのアクセスが必要であれば、コメントアウトにするか、複数のIPアドレスを持っている場合は、通信を行いたいIPアドレスを指定します。

$ ss -nltu | grep 3306
tcp     LISTEN   0    80     127.0.0.1:3306     0.0.0.0:*
$ cd /etc/mysql/mariadb.conf.d/.
$ sudo cp -p 50-server.cnf BK_50-server.cnf.original
$ sudo vi 50-server.cnf
$ diff BK_50-server.cnf.original 50-server.cnf
28c28
< bind-address            = 127.0.0.1
---
> #bind-address            = 127.0.0.1
$ sudo systemctl restart mariadb.service
$ ss -nltu | grep 3306
tcp     LISTEN   0    80       *:3306                 *:*
タイトルとURLをコピーしました