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