概要

Ubuntu 19.10 Eoan Ermine に MariaDB をインストールする
データベースを作成する
データベースの管理者アカウントとアプリケーション用アカウントを作成する
Python スクリプトを作成してデータを操作する

今回の環境

Ubuntu 19.10 Eoan Ermine
MariaDB 10.3.22
Python 3.7.5 + MariaDB Connector/Python 0.9.54-beta

$ uname -mrsv
Linux 5.3.0-40-generic #32-Ubuntu SMP Fri Jan 31 20:24:34 UTC 2020 x86_64

MariaDB のインストール

mariadb-server と mariadb-client パッケージをインストールする。
mariadb-server をインストールすれば mariadb-client は依存関係により一緒にインストールされる。

参考: Ubuntu – eoan の mariadb-server パッケージに関する詳細

$ sudo apt install mariadb-server

インストールされた主要なパッケージを確認。

$ dpkg -l | grep -E "mariadb|mysql"
ii  libdbd-mysql-perl:amd64              4.050-2build1                          amd64        Perl5 database interface to the MariaDB/MySQL database
ii  libmysqlclient21:amd64               8.0.19-0ubuntu0.19.10.3                amd64        MySQL database client library
ii  mariadb-client-10.3                  1:10.3.22-0ubuntu0.19.10.1             amd64        MariaDB database client binaries
ii  mariadb-client-core-10.3             1:10.3.22-0ubuntu0.19.10.1             amd64        MariaDB database core client binaries
ii  mariadb-common                       1:10.3.22-0ubuntu0.19.10.1             all          MariaDB common metapackage
ii  mariadb-server                       1:10.3.22-0ubuntu0.19.10.1             all          MariaDB database server (metapackage depending on the latest version)
ii  mariadb-server-10.3                  1:10.3.22-0ubuntu0.19.10.1             amd64        MariaDB database server binaries
ii  mariadb-server-core-10.3             1:10.3.22-0ubuntu0.19.10.1             amd64        MariaDB database core server files
ii  mysql-common                         5.8+1.0.5ubuntu2                       all          MySQL database common files, e.g. /etc/mysql/my.cnf

MariaDB のバージョンを確認。

$ mysqld --version
mysqld  Ver 10.3.22-MariaDB-0ubuntu0.19.10.1 for debian-linux-gnu on x86_64 (Ubuntu 19.10)

MariaDB Monitor は mysql または mariadb コマンドで起動できる。

$ ls -la $(which mariadb)
lrwxrwxrwx 1 root root 5  1月 29 16:03 /usr/bin/mariadb -> mysql

$ ls -la $(which mysql)
-rwxr-xr-x 1 root root 4101496  1月 29 16:03 /usr/bin/mysql

$ mysql --version
mysql  Ver 15.1 Distrib 10.3.22-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Ubuntu での MariaDB サーバは systemd で管理されている。

$ systemctl list-unit-files --type=service | grep -E "mariadb|mysql"
mariadb.service                        enabled        
mariadb@.service                       disabled       
mysql.service                          enabled        
mysqld.service                         enabled

参考: systemd - MariaDB Knowledge Base

設定ファイルは /etc/mysql 以下にある。

$ tree /etc/mysql
/etc/mysql
├── conf.d
│   ├── mysql.cnf
│   └── mysqldump.cnf
├── debian-start
├── debian.cnf
├── mariadb.cnf
├── mariadb.conf.d
│   ├── 50-client.cnf
│   ├── 50-mysql-clients.cnf
│   ├── 50-mysqld_safe.cnf
│   └── 50-server.cnf
├── my.cnf -> /etc/alternatives/my.cnf
└── my.cnf.fallback

2 directories, 11 files

mysql_secure_installation によるセキュリティ改善

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!
(以下略)

今回は以下のように設定している。

Set root password? [Y/n] n ← root のパスワードは設定しない
Remove anonymous users? [Y/n] Y ← 匿名アカウントを削除する
Disallow root login remotely? [Y/n] Y ← root によるリモートログインを許可しない
Remove test database and access to it? [Y/n] Y ← テスト用のデータベースとそのアクセス権限を削除する
Reload privilege tables now? [Y/n] Y ← 権限テーブルを再読み込みして設定を反映する

参考: mysql_secure_installation - MariaDB Knowledge Base

mysql -u root -p では MariaDB monitor を起動できない

仮にパスワードを設定していてもそのままではログインできない。

$ mysql -u root -p
Enter password: 
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

root でのログインには OS の root 権限が必要。

$ sudo mysql

root ユーザーの認証プラグインが unix_socket に設定されており、これにより MariaDB ユーザー名と一致する Linux アカウントが必要になる。

MariaDB [(none)]> SELECT host, user, plugin from mysql.user;
+-----------+------+-------------+
| host      | user | plugin      |
+-----------+------+-------------+
| localhost | root | unix_socket |
+-----------+------+-------------+
1 row in set (0.002 sec)

データベース管理やアプリケーションからの利用などの必要に応じて、パスワード認証による MariaDB ユーザーを作成して対応する。

参考:

キャラクタセット(文字コード)設定の確認

$ sudo mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 50
Server version: 10.3.22-MariaDB-0ubuntu0.19.10.1 Ubuntu 19.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 VARIABLES LIKE 'character_set%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.003 sec)

MariaDB [(none)]> SHOW VARIABLES LIKE 'collation%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+
3 rows in set (0.001 sec)

MariaDB [(none)]> exit
Bye

参考: MySQL :: MySQL 5.6 リファレンスマニュアル :: 10.1.4 接続文字セットおよび照合順序

データベースの作成

MariaDB Monitor にて CREATE DATABASE で作成。

MariaDB [(none)]> CREATE DATABASE IF NOT EXISTS test_db
    -> DEFAULT CHARACTER SET utf8mb4
    -> DEFAULT COLLATE utf8mb4_general_ci
    -> ;
Query OK, 1 row affected (0.002 sec)

データベースが作成されていることを確認。

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test_db            |
+--------------------+
4 rows in set (0.002 sec)

information_schema.schemata テーブルには作成したデータベースの情報が入っている。

MariaDB [(none)]> SELECT * FROM information_schema.schemata WHERE schema_name = 'test_db';
+--------------+-------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+-------------+----------------------------+------------------------+----------+
| def          | test_db     | utf8mb4                    | utf8mb4_general_ci     | NULL     |
+--------------+-------------+----------------------------+------------------------+----------+
1 row in set (0.002 sec)

参考:

CREATE DATABASE - MariaDB Knowledge Base
SHOW DATABASES - MariaDB Knowledge Base
Information Schema SCHEMATA Table - MariaDB Knowledge Base

ユーザーの作成

データベース管理ユーザー (テーブルの作成などが可能) を作成。

MariaDB [(none)]> GRANT ALL PRIVILEGES ON test_db.* TO 'your_admin'@'localhost' IDENTIFIED BY 'your_admin_password';
Query OK, 0 rows affected (0.004 sec)

アプリケーション用ユーザー (テーブル内のデータを操作可能) を作成。

MariaDB [(none)]> GRANT SELECT, INSERT, UPDATE, DELETE ON test_db.* TO 'your_app'@'localhost' IDENTIFIED BY 'your_app_password';
Query OK, 0 rows affected (0.002 sec)

ユーザーが作成されているのを確認。

MariaDB [(none)]> SELECT host, user, plugin from mysql.user;
+-----------+------------+-------------+
| host      | user       | plugin      |
+-----------+------------+-------------+
| localhost | root       | unix_socket |
| localhost | your_admin |             |
| localhost | your_app   |             |
+-----------+------------+-------------+
3 rows in set (0.002 sec)

作成したユーザーの権限情報を確認。

MariaDB [(none)]> SHOW GRANTS FOR 'your_admin'@'localhost';
+-------------------------------------------------------------------------------------------------------------------+
| Grants for your_admin@localhost                                                                                   |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'your_admin'@'localhost' IDENTIFIED BY PASSWORD '*C1C47E6E83ACE28E79DE3A23B1D96E0986BF4EE5' |
| GRANT ALL PRIVILEGES ON `test_db`.* TO 'your_admin'@'localhost'                                                   |
+-------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.001 sec)

MariaDB [(none)]> SHOW GRANTS FOR 'your_app'@'localhost';
+-----------------------------------------------------------------------------------------------------------------+
| Grants for your_app@localhost                                                                                   |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'your_app'@'localhost' IDENTIFIED BY PASSWORD '*4B887F6BD32CD7F9ECA0060A453F631BFC30BD53' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `test_db`.* TO 'your_app'@'localhost'                                   |
+-----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.001 sec)

参考:

テーブルの作成

作成したデータベース管理ユーザーで MariaDB monitor を起動する。

$ mysql -u your_admin -p test_db

テーブルを作成する。

MariaDB [test_db]> CREATE TABLE IF NOT EXISTS test_table (
    -> id         INTEGER      NOT NULL AUTO_INCREMENT,
    -> name       VARCHAR(256) NOT NULL,
    -> created_at TIMESTAMP    NOT NULL,
    -> PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.015 sec)

作成したテーブルの情報を確認する。

MariaDB [test_db]> SHOW TABLES;
+-------------------+
| Tables_in_test_db |
+-------------------+
| test_table        |
+-------------------+
1 row in set (0.001 sec)

MariaDB [test_db]> SHOW COLUMNS FROM test_table;
+------------+--------------+------+-----+---------------------+-------------------------------+
| Field      | Type         | Null | Key | Default             | Extra                         |
+------------+--------------+------+-----+---------------------+-------------------------------+
| id         | int(11)      | NO   | PRI | NULL                | auto_increment                |
| name       | varchar(256) | NO   |     | NULL                |                               |
| created_at | timestamp    | NO   |     | current_timestamp() | on update current_timestamp() |
+------------+--------------+------+-----+---------------------+-------------------------------+
3 rows in set (0.003 sec)

MariaDB [test_db]> SELECT column_name, character_set_name, collation_name FROM information_schema.columns WHERE table_schema='test_db' AND table_name='test_table';
+-------------+--------------------+--------------------+
| column_name | character_set_name | collation_name     |
+-------------+--------------------+--------------------+
| id          | NULL               | NULL               |
| name        | utf8mb4            | utf8mb4_general_ci |
| created_at  | NULL               | NULL               |
+-------------+--------------------+--------------------+
3 rows in set (0.001 sec)

MariaDB [test_db]> SHOW CREATE TABLE test_table;
+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                                                  |
+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_table | CREATE TABLE `test_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(256) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.002 sec)

参考: CREATE TABLE - MariaDB Knowledge Base

Python スクリプトでデータを操作できるか確認

MariaDB Connector/Python とそのインストールに必要な Ubuntu パッケージをインストールする。

$ sudo apt install libmariadb-dev

$ pip3 install --pre mariadb

$ pip3 list | grep mariadb
mariadb                0.9.54

現時点 (2020年3月6日現在) では MariaDB Connector/Python はまだベータ版とのこと。

Python - MariaDB Knowledge Base

Important note: MariaDB Connector/Python is still beta and is not intended for use in production environments.

スクリプトファイル sushi.py を作成。

sushi.py

import time
import mariadb

# connect db
conn = mariadb.connect(host="localhost",
                       user="your_app",
                       password="your_app_password",
                       database="test_db")
cursor = conn.cursor(named_tuple=True)

# insert
insert_sql = "INSERT INTO test_table (name, created_at) VALUES (%s, %s)"
name = "寿司ビール🍣🍺𩸽🐟🐠🐡文字化けしないで"
created_at = mariadb.TimestampFromTicks(time.time())
cursor.execute(insert_sql, (name, created_at))
conn.commit()

# select
select_sql = "SELECT id, name, created_at FROM test_table"
cursor.execute(select_sql)
for row in cursor:
  print(f"{row.id}: {row.name} ({row.created_at})")

conn.close()

実行結果。

$ python3 sushi.py 
1: 寿司ビール🍣🍺𩸽🐟🐠🐡文字化けしないで (2020-03-06 22:31:00)

MariaDB Monitor にてデータが追加されているのを確認できる。

MariaDB [test_db]> select * from test_table;
+----+-----------------------------------------------------------------+---------------------+
| id | name                                                            | created_at          |
+----+-----------------------------------------------------------------+---------------------+
|  1 | 寿司ビール🍣🍺𩸽🐟🐠🐡文字化けしないで                               | 2020-03-06 22:31:00 |
+----+-----------------------------------------------------------------+---------------------+
1 row in set (0.001 sec)

参考: