以下のようなサーバを作るために、MariaDBをインストール。

OS: Ubuntu 18.04 LTS / Ubuntu 20.04 LTS / Ubuntu 22.04 LTS
- および Raspberry Pi OS Lite Release date: April 4th 2022.04
同じマシンに DB サーバとWebアプリサーバをインストール、
Webアプリからは DB へ localhost で接続
DB には日本語テーブル名、日本語カラム名を用いる。

簡単なインストール作業。
(2020/06/10：Ubuntu20.04LTS において同様にOKなことを確認し、タイトルと冒頭を変更)
(2020/12/18：再インストールについて追記)
(2021/03/25：Ubuntu20.04LTS において CREATE TABLE でエラーが出ることに対して対応、追記)
(2022/06/06：Ubuntu22.04LTS において同様にOKなことを確認し、タイトルと冒頭を変更)
(2022/08/03：Raspberry Pi OS Lite Release date: April 4th 2022 において同様にOKなことを確認し、タイトルなど変更)
(2022/08/10: TIMEZONE の確認について追記)

インストール

Ubuntu / Raspberry Pi OS 公式レポジトリにあるものをそのまま使用する。


$ sudo apt-get install mariadb-server mariadb-client

バージョン確認


$ sudo mysql -v
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 42
Server version: 10.1.44-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04

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

Reading history-file /home/nanbuwks/.mysql_history
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> exit;
Writing history-file /home/nanbuwks/.mysql_history
Bye

なお、別の日に行ったそれぞれの環境でのバージョン確認は以下の通り

Ubuntu20.04 (2020/06/10)
- MariaDB Sever version は 10.3.22-MariaDB-1ubuntu1 Ubuntu 20.04
Ubuntu22.04 (2022/06/06)
- MariaDB Sever version は 10.6.7-MariaDB-2ubuntu1 Ubuntu 22.04
Raspberry Pi OS Lite Release date: April 4th 2022.04 (2022/08/02)
- 10.5.15-MariaDB-0+deb11u1 Raspbian 11

ほぼはじめて mariadb 使ったけど CLI は mysql で呼び出せるんだ(知らなかった)。

文字コード設定

/etc/mysql/mariadb.conf.d/50-server.cnf を以下のように変更


#
# * Character sets
#
# MySQL/MariaDB default is Latin1, but in Debian we rather default to the full
# utf8 4-byte character set. See also client.cnf
#
character-set-server  = utf8mb4
collation-server      = utf8mb4_general_ci

↓


#
# * Character sets
#
# MySQL/MariaDB default is Latin1, but in Debian we rather default to the full
# utf8 4-byte character set. See also client.cnf
#
character-set-server  = utf8mb4
collation-server      = utf8mb4_bin

~~大文字小文字区別なし、4バイト含むUTF8コードの設定になる。~~

4バイト含むUTF8コードで設定、照合は大文字小文字区別ありになります。

(2022/01/08 @lowln さんのご指摘により修正)


 sudo systemctl restart mariadb

で反映。

設定

Ubuntu の場合、最初に通常ユーザで設定しようとしたら・・・


$ 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): 
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
Aborting!

Cleaning up...

Raspberry Pi OS の場合はAbortしなかったけれど Access denied になった。


$ sudo mysql_secure_installation

sudo して動いた。
以下は Ubuntu の場合



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): 
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] Y
New password: 
Re-enter new password: 
Sorry, passwords do not match.

New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!


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
 ... 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!

Disallow root login remotely? [Y/n] Y
後ろの3単語だけ見て、脊髄で反応しようとして間違うところだった。

以下は Rasbperry Pi OS の場合



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
haven't set the root password yet, you should just press enter here.

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password or using the unix_socket ensures that nobody
can log into the MariaDB root user without the proper authorisation.

You already have your root account protected, so you can safely answer 'n'.

Switch to unix_socket authentication [Y/n] n
 ... skipping.

You already have your root account protected, so you can safely answer 'n'.

Change the root password? [Y/n] y
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!


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
 ... 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!

Webアプリ用のユーザとテストデータベースを作る


$ sudo mysql -u root -p

テストデータベースを作ってみる。データベース名についても、試しに日本語にしてみた。

MariaDB [(none)]> create database テストデータベース;
Query OK, 1 row affected (0.00 sec)

Webアプリから接続するためのユーザを作る

MariaDB [(none)]>  CREATE USER 'webdb'@'localhost' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

( 'password' は適宜変更 )

権限を設定

MariaDB [(none)]> GRANT ALL ON テストデータベース.* TO 'webdb'@'localhost';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> exit;
Bye

接続してみる


$ mysql -u webdb -h localhost -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 44
Server version: 10.1.44-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04

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)]> use テストデータベース
Database changed
MariaDB [テストデータベース]> show tables;
Empty set (0.00 sec)

うまくいきました

ネットワークのいずれからもアクセス可能とする場合は(root権限で)もうひとつ webdb ユーザを作る


MariaDB [(none)]> CREATE USER 'webdb'@'%' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> GRANT ALL ON テストデータベース.* TO webdb@'%';
Query OK, 0 rows affected (0.00 sec)

ユーザ設定の確認

ユーザ、ホストが設定できているかどうか


MariaDB [(none)]> select user, host from mysql.user;
+----------+-----------+
| user     | host      |
+----------+-----------+
| webdb    | %         |
| root     | localhost |
| testUser | localhost |
| webdb    | localhost |
+----------+-----------+
4 rows in set (0.00 sec)

データベースへの権限の割当ができているかどうか

MariaDB [(none)]> show grants for 'webdb'@'localhost'
+--------------------------------------------------------------------------------------------------------------+
| Grants for webdb@localhost                                                                                   |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'webdb'@'localhost' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |
| GRANT ALL PRIVILEGES ON `テストデータベース`.* TO 'webdb'@'localhost'                                        |
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

MariaDB [(none)]> show grants for `webdb`@'%';
+------------------------------------------------------------------------------------------------------+
| Grants for webdb@%                                                                                   |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'webdb'@'%' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |
| GRANT ALL PRIVILEGES ON `テストデータベース`.* TO 'webdb'@'%'                                        |
+------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

外部からの接続を許す設定


$ sudo vim /etc/mysql/mariadb.conf.d/50-server.cnf

として、


・・・ 
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 127.0.0.1
・・・

↓変更


・・・ 
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address           = 127.0.0.1
bind-address            = 0.0.0.0
・・・

mariadbを再起動


$ sudo service mariadb restart

これでOk.

sudo 無しで root ユーザでアクセスできるようにする

やむを得ずこの設定が必要な場合は以下のようにします。

まず、現在の設定の確認

> SHOW GRANTS FOR root@localhost;
+--------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                                        |
+--------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED VIA unix_socket USING '*45BD13A4098870D220D0D524550A3E6F3E698C5A' WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION                                                                                    |
+--------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

設定を変更します

> grant all privileges on *.* to root@localhost identified by 'password' with grant option;
Query OK, 0 rows affected (0.022 sec)
> flush privileges;
Query OK, 0 rows affected (0.093 sec)

変更後の設定はこうなっています

> SHOW GRANTS FOR root@localhost;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION                                                                          |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

テストテーブル作って動作確認

「sensor」テーブルを作成。
「センサ」という名前のテーブルも、日本語が通るかテストのために作ってみた。

create table if not exists sensor (
id SERIAL PRIMARY KEY AUTO_INCREMENT NOT NULL,
sensor integer,
 temp real,
 hemi real,
 memo text
);

create table if not exists センサ (
id SERIAL PRIMARY KEY AUTO_INCREMENT NOT NULL,
センサ integer,
 温度 real,
 湿度 real,
 メモ text
);

(2021/03/25 追記、2022/08/03修正：)
Ubuntu20.04 (MariaDB バージョン 10.3.25-MariaDB-0ubuntu0.20.04.1 Ubuntu 20.04 ) 以降だと上記でエラーが発生


MariaDB [テストデータベース]> create table if not exists センサ ( id SERIAL PRIMARY KEY AUTO_INCREMENT NOT NULL, センサ integer,  温度 real,  湿度 real,  メモ text );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AUTO_INCREMENT NOT NULL, センサ integer,  温度 real,  湿度 real,  メ...' at line 1
MariaDB [テストデータベース]>


id SERIAL PRIMARY KEY AUTO_INCREMENT NOT NULL,

のところの SERIAL 型の指定と AUTO_INCREMENT を同時に指定するとエラーが起こるようだ。

「MySQL :: MySQL 8.0 Reference Manual :: 11.1.1 Numeric Data Type Syntax」
https://dev.mysql.com/doc/refman/8.0/en/numeric-type-syntax.html

によると、

SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.

SERIAL DEFAULT VALUE in the definition of an integer column is an alias for NOT NULL AUTO_INCREMENT UNIQUE.

ということでした。

なので、SERIAL型をやめて、以下のようにしたらうまくいきました

 
MariaDB [テストデータベース]> create table if not exists センサ ( id BIGINT PRIMARY KEY AUTO_INCREMENT NOT NULL, センサ integer,  温度 real,  湿度 real,  メモ text );
Query OK, 0 rows affected (0.017 sec)

(：追記終わり)

テストデータ入れてみる


MariaDB [test]> INSERT INTO sensor ( sensor,temp,hemi,memo ) VALUES (1,22.0,85.0,'ストーブON');
Query OK, 1 row affected (0.00 sec)

MariaDB [test]>  INSERT INTO センサ ( センサ,温度,湿度,メモ ) VALUES (1,22.0,85.0,'ストーブON');
Query OK, 1 row affected (0.00 sec)

確認


MariaDB [test]> select * from sensor;
+----+--------+------+------+----------------+
| id | sensor | temp | hemi | memo           |
+----+--------+------+------+----------------+
|  1 |      1 |   22 |   85 | ストーブON     |
+----+--------+------+------+----------------+
1 row in set (0.00 sec)

MariaDB [test]> select * from センサ;
+----+-----------+--------+--------+----------------+
| id | センサ    | 温度   | 湿度   | メモ           |
+----+-----------+--------+--------+----------------+
|  1 |         1 |     22 |     85 | ストーブON     |
+----+-----------+--------+--------+----------------+
1 row in set (0.01 sec)

TIMEZONEの設定

select now();

で時刻を確認。

https://dev.mysql.com/doc/refman/8.0/ja/time-zone-support.html
では、

システムタイムゾーン。サーバーは起動時に、ホストマシンのタイムゾーンを自動的に判別し、それを使用して system_time_zone システム変数を設定しようとします。その後、この値は変更しません。

とある。もしホストマシンのタイムゾーンを未設定だった場合は設定して再起動。

再インストール

apt パッケージの purge などがうまく設定されていないのかな？
Ubuntu 18.04 で、


$ sudo apt purge mariadb-server

してもうまくいかないようだ。

cf.,「UbuntuでMariaDBをうまく再インストールできなくなったときの対処方法」
https://qiita.com/SUZUKI_Masaya/items/c587512643b7d6337538

MariaDB インストール ( Ubuntu 18.04 / 20.04 / 22.04 LTS , Raspberry Pi OS)