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のインストール

apt(Advanced Packaging Tool)を使ってインストールします。

$ sudo apt install mariadb-server
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
● 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-10-25 11:41:26 JST; 1min 1s ago
     Docs: man:mysqld(8)
           https://mariadb.com/kb/en/library/systemd/
 Main PID: 2047 (mysqld)
   Status: "Taking your SQL requests now..."
    Tasks: 31 (limit: 4915)
   CGroup: /system.slice/mariadb.service
           mq2047 /usr/sbin/mysqld

MariaDBの構成

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

途中、rootユーザーのパスワード設定を行なうかの問い合わせがあります。
ここでは、「n」を応答して、rootユーザーでアクセスが必要な場合は、「unix_socket」プラグインによるログインとします。

$ 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 mysql <= unix_socketプラグインを利用してログイン
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 44
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)]> grant all on *.* to 'tokken'@'localhost' identified by 'pass' with grant option;
Query OK, 0 rows affected (0.011 sec)

MariaDB [(none)]> flush privileges; <= 念のため
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> exit
Bye
$ mysql -u tokken -p <= 作成したユーザーでログイン確認
Enter password:pass
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 45
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)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.004 sec)

MariaDB [(none)]> exit
Bye

bind-addressの変更

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
$ cat BK_50-server.cnf.original | sed -e s/^bind-address/#bind-address/g > 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
$ ss -nltu | grep 3306
tcp     LISTEN   0    80       *:3306                 *:*