概要
- 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 ユーザーを作成して対応する。
参考:
- mysql Command-line Client - MariaDB Knowledge Base
- Authentication from MariaDB 10.4 - MariaDB Knowledge Base
- Authentication Plugin - Unix Socket - MariaDB Knowledge Base
- Passwordless login with unix auth_socket
- Ubunt 16.04で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)
参考: