Raspi3B + Ubuntu 20.04 に MySQL をインストールしてみる

# 目的
そこにUbuntu 20.04をインストールしたRaspi3Bが見えたのでついインストールしてみる。
Windows版との違いがあれれ？だったところと、インスト後からDB作成、ユーザー作成と権限付与まで必要なことをメモっておく
※スキーマは大文字・小文字を区別する(Win版では区別しない: database exists)
※テーブル名の大文字・小文字も区別する(Win版では区別しない)
※カラム名の大文字・小文字は無視する(ようだ Win版でも無視する)

## MySQLのパッケージの確認
まずは apt searchでパッケージを確認する
※事前にsudo apt search mysql | less であたりはつけている
※すでにインストール済なので[installed]付
<pre>
$sudo apt search mysql-server
Sorting... Done
Full Text Search... Done
default-mysql-server/focal 1.0.5ubuntu2 all
  MySQL database server binaries and system database setup (metapackage)

default-mysql-server-core/focal 1.0.5ubuntu2 all
  MySQL database server binaries (metapackage)

mysql-server/focal-updates,focal-security,now 8.0.25-0ubuntu0.20.04.1 all [installed]
  MySQL database server (metapackage depending on the latest version)

mysql-server-8.0/focal-updates,focal-security,now 8.0.25-0ubuntu0.20.04.1 armhf [installed,automatic]
  MySQL database server binaries and system database setup

mysql-server-core-8.0/focal-updates,focal-security,now 8.0.25-0ubuntu0.20.04.1 armhf [installed,automatic]
  MySQL database server binaries
</pre>

## MySQLのインストール

<pre>
$ sudo apt install mysql-server
</pre>
まずはTTSSH経由でコンソールから確認してみる
sudo passwd rootでrootのパスワードは設定済の状態だとパスワードは要求されない
インストール後まずは mysql_secure_installation を実行して諸々を設定する
※パスワードの強度やrootのパスワード等
<pre>
$ sudo mysql -u root -p
[sudo] password for user:
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 24
Server version: 8.0.25-0ubuntu0.20.04.1 (Ubuntu)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
</pre>

インストール直後のユーザー一覧を表示
<pre>
mysql> select user, host, plugin from mysql.user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| debian-sys-maint | localhost | caching_sha2_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | auth_socket           |
+------------------+-----------+-----------------------+
</pre>
root auth_socket はこのあたりを読む->[6.4.1.9 ソケットピア資格証明プラガブル認証](https://dev.mysql.com/doc/refman/8.0/ja/socket-pluggable-authentication.html)

## 設定ファイルの修正
mysqlx-bind-addressは[20.5.6.2 X プラグイン のオプションとシステム変数](https://dev.mysql.com/doc/refman/8.0/ja/x-plugin-options-system-variables.html)
なのだが、今のところコメントアウト
<pre>
$ sudo /etc/mysql/mysql.conf.d/mysqld.cnf

\# bind-address           = 127.0.0.1
\# mysqlx-bind-address    = 127.0.0.1
</pre>

## コンソールからの起動／停止／再起動
<pre>
※MySQLを起動する
systemctl start mysql

※MySQLを停止する
systemctl stop mysql

※MySQLを再起動する
systemctl restart mysql
</pre>

## スキーマ(データベース)の作成
スキーマを作成する->[【データベース（MySQL）】スキーマとは？](https://teratail.com/questions/3167)
※スキーマは大文字・小文字を区別する
<pre>
$ sudo mysql -u root -p
Enter password: ****

mysql> CREATE SCHEMA ZIPCODE;
mysql> create schema zipcode;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| ZIPCODE            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zipcode            |
+--------------------+
mysql> use zipcode
Database changed
</pre>

## テーブルを作成する
対象データは[郵便番号検索](http://www.post.japanpost.jp/zipcode/)の17ISHIKA.CSV
ここではSQLは記述しないが、テーブル名の大文字・小文字も区別する
だがカラム名の大文字・小文字は無視するようだ
※Pythonのコードではカラム名は大文字・小文字どちらでも正常に書き込む

```sql
set character_set_client = utf8mb4;
create table zipcode(
    seq       nchar(8)  not null,
    prefcode  nchar(3)  null,
    kubuncode nchar(8)  null,
    postal5   nchar(5)  null,
    postal    nchar(8)  null,
    prefkana  nchar(20) null,
    citykana  nchar(40) null,
    addrkana  nchar(80) null,
    prefkanji nchar(20) null,
    citykanji nchar(40) null,
    addrkanji nchar(80) null,
    flg1 int null,
    flg2 int null,
    flg3 int null,
    flg4 int null,
    flg5 int null,
    flg6 int null
) engine=innodb default charset=utf8mb4 collate=utf8mb4_general_ci;
```

## ユーザーを作成して権限を付与＆確認する
作成したユーザーに権限を付与後確認するとスキーマの大文字・小文字が区別されていることが確認できる
<pre>
mysql> create user 'demo'@'192.168.%' identified by 'demo';
mysql> select user, host, plugin from mysql.user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| demo             | 192.168.% | caching_sha2_password |
| debian-sys-maint | localhost | caching_sha2_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | auth_socket           |
+------------------+-----------+-----------------------+

mysql> grant all on ZIPCODE.* to 'demo'@'192.168.%';
mysql> grant all on zipcode.* to 'demo'@'192.168.%';
mysql> show grants for 'demo'@'192.168.%';
+-----------------------------------------------------------+
| Grants for demo@192.168.%                                 |
+-----------------------------------------------------------+
| GRANT USAGE ON *.* TO `demo`@`192.168.%`                  |
| GRANT ALL PRIVILEGES ON `ZIPCODE`.* TO `demo`@`192.168.%` |
| GRANT ALL PRIVILEGES ON `zipcode`.* TO `demo`@`192.168.%` |
+-----------------------------------------------------------+
</pre>

## pandas & mysql-connector-python をインストールする
<pre>
PS C:\> python -V
Python 3.9.5

PS C:\> pip install pandas

PS > pip3 install mysql-connector-python
</pre>

## Win10->Raspi3B+MySQLにアクセスするサンプルコード
ここでスキーマの大文字・小文字が不一致だとエラーになる
テーブル名の大文字・小文字が不一致だとエラーになる
カラム名は大文字・小文字は問わない

```python
# coding:utf-8
# Windows Add env PYTHONIOENCODING = UTF-8 & restart vscode

import pandas as pd
import mysql.connector as mydb

# コネクションの作成
conn = mydb.connect(
    host='192.168.5.xx',
    port='3306',
    user='demo',
    password='demo',
    database='zipcode'
)

count = 0
postal = pd.read_csv("17ISHIKA.CSV", header=None, encoding='cp932', dtype='object')

# autocommit しない
conn.autocommit = False
cur = conn.cursor()
cur.execute('truncate table zipcode;')

for ary in postal.values.tolist():
    count = count + 1
    sql = 'INSERT INTO zipcode (' \
        + '  SEQ'       \
        + ', PREFCODE'  \
        + ', KUBUNCODE' \
        + ', POSTAL5'   \
        + ', POSTAL'    \
        + ', PREFKANA'  \
        + ', CITYKANA'  \
        + ', ADDRKANA'  \
        + ', PREFKANJI' \
        + ', CITYKANJI' \
        + ', ADDRKANJI' \
        + ', FLG1' \
        + ', FLG2' \
        + ', FLG3' \
        + ', FLG4' \
        + ', FLG5' \
        + ', FLG6' \
        + ') VALUES ('  \
        + '  \'' + str(count).rjust(8, '0') + '\'' \
        + ', \'' + str(ary[0])[:2] + '\'' \
        + ', \'' + str(ary[0]).strip() + '\'' \
        + ', \'' + str(ary[1]).strip() + '\'' \
        + ', \'' + str(ary[2]) + '\'' \
        + ', \'' + ary[3] + '\'' \
        + ', \'' + ary[4] + '\'' \
        + ', \'' + ary[5] + '\'' \
        + ', \'' + ary[6] + '\'' \
        + ', \'' + ary[7] + '\'' \
        + ', \'' + ary[8] + '\'' \
        + ',   ' + str(ary[9])   \
        + ',   ' + str(ary[10])  \
        + ',   ' + str(ary[11])  \
        + ',   ' + str(ary[12])  \
        + ',   ' + str(ary[13])  \
        + ',   ' + str(ary[14])  \
        + ');'
    # print(sql)
    cur.execute(sql)

conn.commit()
# 挿入したデータの件数を確認する
# cursor = conn.cursor()
cur.execute("select count(*) from zipcode;")
row = cur.fetchone()
if row:
    print(row)

cur.close()
conn.close()
```

# 参考にしたのは以下のサイト
[MySQLに外部から接続したい](https://qiita.com/hum_op/items/176c866635713667c5e7)
[MySQLの起動と停止、再起動](https://www.t3a.jp/blog/infrastructure/mysql-start-stop/)
[mysql_secure_installation の初期パスワード確認](https://qiita.com/rutko/items/27c0f76cee37260a1cda)
[MySQLの使い方](https://www.dbonline.jp/mysql/)
[MySQL 8.0へ接続時に”SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client”になる場合の対処法](https://blog.janjan.net/2018/11/01/mysql8-request-authentication-method-unknown-to-the-client/)
[How To Install MySQL on Ubuntu 20.04](https://www.digitalocean.com/community/tutorials/how-to-install-mysql-on-ubuntu-20-04)