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 *:*
