20200116のMySQLに関する記事は11件です。

投稿日：2020-01-16T21:24:27+09:00

Dockerを使って、phpMyAdminをいとも簡単に接続する。

「Dockerでwebアプリケーションを作ろう！」

と思い立ち、せっせと環境構築をし、なんとか構築し終わったーと思い、phpMyAdminに接続しようとしたら、全然できない…。公式イメージ通りにやってるつもりなのにエラーの連続。

なんとか接続できたので、
Docker初学者の人のためにここに書き残します！
＊データベースの設定完了後につまずいていたので、それ以降から投稿します。

データベースがローカルで走っている状態にしてください。
そうしたら、ターミナルでコンテナを見てみましょう。
$ docker ps

CONTAINER ID        IMAGE               COMMAND                  CREATED             
STATUS              PORTS                               NAMES
655317389bfd        mysql:5.7           "docker-entrypoint.s…"   23 hours ago        
Up 34 minutes       33060/tcp, 0.0.0.0:4506->3306/tcp   twitter_clone_db_1
ぐちゃぐちゃですみません笑
これで、データベースを確認できます。

私がつまづいてしまった場所がここからです。

まず、はじめにdocker-hubから公式のimageを取ってきます。
https://hub.docker.com/r/phpmyadmin/phpmyadmin/

そしてpullしていきます。　　
pullっていうのは、特定のイメージをダウンロードしてくることです！
＄　docker pull phpmyadmin/phpmyadmin 
そして、完了。よし、runだー！と思ったら…
$ docker run --name my-own-phpmyadmin -d --link  twitter_clone_db_1 -p 8081:80 
phpmyadmin/phpmyadmin 

f1df1be3ac876912155029470016bdbe97960e293eec543cbd0bad383269fda9
docker: Error response from daemon: Cannot link to /twitter_clone_db_1, as it 
does not belong to the default network.
なんかネットワークにつながっていないみたい。
とりあえずネットワークを調べてみる。
$ docker network ls
NETWORK ID          NAME                    DRIVER              SCOPE
a05d069a01f9        bridge                  bridge              local
411a1f6ecd92        host                    host                local
5398f730cb0c        none                    null                local
8117a9fd204d        twitter_clone_default   bridge              local
つなぐべきは、一番下のネットワークだとわかったので、
オプションを使ってネットワークに接続させつつ、コンテナを起動させてみる。
$ docker run --net=twitter_clone_default  --name my-own-phpmyadmin -d --link  
twitter_clone_db_1 -p 8081:80 phpmyadmin/phpmyadmin
すると…
d762f136d4c888e4d1cb715536b44b33c98aca180bc10965fb9389624e834bb
できたーーーーーー
自力でやった甲斐がありました。泣

確認してみます！
$ docker ps
CONTAINER ID        IMAGE                   COMMAND                  CREATED             
STATUS              PORTS                               NAMES
fd762f136d4c        phpmyadmin/phpmyadmin   "/docker-entrypoint.…"   43 seconds 
ago      Up 42 seconds       0.0.0.0:8081->80/tcp           my-own-phpmyadmin

655317389bfd        mysql:5.7               "docker-entrypoint.s…"   24 hours ago        
Up 41 minutes       33060/tcp, 0.0.0.0:4506->3306/tcp   twitter_clone_db_1
しっかりできていました。

ということで、localhost:8081　へアクセスします。

usernameに「root」を入れて、Passwordは自分で設定したものを入れてください。

これで完成です！
Dockerを使えば、効率が著しく上がりますよね！
引き続き、webアプリケーション作成に励みます！

　 Tweet
Qiitaで続きを読む

投稿日：2020-01-16T19:30:25+09:00

PHPでの簡易掲示板の作成の際の考え方

登録画面(register.php)

入力欄が空の場合

入力値($_POST)をチェックする

エラーメッセージ用の連想配列を用意

重複登録を防ぐ(メールアドレス)

SELECT文で入力されたメールアドレスでSELECT文発行

値が1件以上あればエラーメッセージを出す

入力の手間を減らす

入力値($_POST)をエスケープして出力

確認画面への遷移

エラーメッセージ用の連想配列が空だったら実行する

入力値($_POST)をセッション変数に格納する

header関数で確認画面へ遷移する

書き直し

URLパラメータで書き直しかどうかを判断

セッション変数の値を$_POSTに戻す

確認画面(confirm.php)

直に確認画面へアクセスされた場合

セッション変数が空だったら、登録画面へリダイレクト

入力値を出力する

セッション変数から入力値を出力する(画像の場合はセッション変数から画像のパス名を出力)

画像のアップロード(register.php)

$POSTは使わず、$FILESを使う

HTML側

formタグのenctype属性にmultipart/form-dataを指定

inputタグのtype属性にfileを指定

画像以外はアップロードできないようにする

ファイル名を取得($_FILE['image']['name'])

ファイル名の後ろ3文字(拡張子)を切り取って文字列チェック

アップロード

ファイル名を取得($_FILE['image']['name'])

move_upload_file関数でアップロードされたファイルを新しい位置に移動する

move_uploaded_file($_FILES['image']['tmp_name'],'保存先のファイル名含むパス);

セッション変数に画像のファイル名を格納する

確認画面(登録処理)(confirm.php)

prepareメソッドでSQL文の雛形を用意

executeメソッドで実際に格納する値をセッション変数から指定する

(パスワードはsha1関数でハッシュ化)

INSERT文発行後はセッション変数をunsetメソッドで破棄する

完了画面へ遷移

ログイン機能(login.php)

ユーザからの入力値(メールアドレスとパスワード)をSELECT文に埋め込む

セッション変数にデータベースからの値(idや氏名など)を格納し、ページ遷移

入力値が空の場合とデータベースとのミスマッチとでエラーメッセージを分ける。

次回からは自動でログインする機能(login.php)

セッションはブラウザを閉じた時に情報が消えてしまうのでクッキーを使用する

プログラムを書く場所はログインのチェックをしてメンバーの情報がヒットした後に記述する

「次回からは自動でログインする機能」をONにした場合

メールアドレスの入力欄には\$_COOKIEか$_POSTのメールアドレスを出力する

メッセージ一覧画面(index.php)

前提としてログインしている状態でないとアクセスできないようにする

セッション変数のチェック、セッションが有効期限かどうかをチェック

上記の条件を満たしていない場合はログイン画面へリダイレクト

セッションの値から名前を取り出す

投稿したメッセージは投稿者のidとメッセージ本体をDBに格納する

DBに格納した後はheader関数で同じページを呼び出す

これをしないと$_POSTに値が残ったままとなるため、入力欄が空であってもDBに値が格納されてしまう

メッセージ一覧を表示する

必要な情報(名前、画像、メッセージ等)をSQLのSELECT文に組み込む

取ってきた情報をループでHTML出力する

返信機能(index.php?res=メッセージID)

表示するファイルはメッセージ一覧画面と同一で、パラメータが付与される

メッセージ一覧画面のURLパラメータに、返信先メッセージのidを付与する

上記のパラメータがセットされていたらそのidをフィルターに、SELECT文を発行し、入力欄にそのメッセージと投稿者を入力欄に出力する

hiddenタイプで返信先メッセージid(=URLパラメータと同じもの)を仕込んでおく

返信メッセージを投稿する時に、上記idもINSERT文に組み込む

メッセージ詳細画面(view.php?id=メッセージID)

日付部分に詳細へのリンクを貼る(twitterでも同様)

URLパラメータにメッセージのidを付与し、それをSELECT文のフィルターとする

データがある場合はデータを出力、データがない場合はそれ用の文言を表示

メッセージの削除機能(delete.php?id=メッセージID)

$_SESSIONのidとメッセージ投稿者のidが一致したら削除のリンクを出す

削除するプログラムに書く内容

URLパラメータをフィルターにしてDBから取得した投稿者のidとセッションのidが一致していたらDELETE文を発行する

DELETE文発行後は一覧画面へ遷移

ページネーション(index.php?page=ページ数)

URLパラメータにページ数を付与

SELECT文にLIMIT句を追記

いくつかの制約を設ける

ページ数が指定されていないとき

ページ数が1より小さいとき

ページ数の上限を超えたとき

リンクを貼る

前ページ

href属性:現在指定されているパラメータからマイナス1

条件:現在指定されているパラメータが1より大きい場合に出力する

次ページ

href属性:現在指定されているパラメータからプラス1

条件:マックスページ数に収まる場合に出力する

ログアウト機能(logout.php)

セッション変数を空の配列で上書き

session_destroy関数を実行

クッキーに保存していたメールアドレスも削除する

セッションで使用しているクッキーの値を空にする、有効期限を切る
if (ini_set('session.use_cookies')) {
    $params = session_get_cookie_params();
    setcookie(session_name() . '',time()-3600,$params['path'],$params['domain'],$params['secure'],$params['httponly']);
}

　 Tweet
Qiitaで続きを読む

投稿日：2020-01-16T19:21:43+09:00

SpringSecurityで認証機能を実装①

現在、フレームワークの勉強中でSpringBootを扱っております。
その中でログイン機能を実装する際に、SpringSecurityの認証機能を使うのが定石らしく、これを使って実装いたしました。
その際にだいぶ難儀しまして、実装までにだいぶ時間を要したので自分なりにまとめてみました。

１、SpringSecurityのイメージ

すごく簡素なイメージ申し訳ないのですが、イメージ的にはこんな感じになります。
SpringBootのDI（依存性の注入）という機能を使ってSpringSecurityを外部から読み込んでDBとやり取りを行います。
DIに関しては詳しく説明しませんが、外部のオブジェクトをDIコンテナという箱に入れておくことで、いつでもオブジェクトの機能を使うことができるみたいなイメージだと思います。
今回の場合そのオブジェクトというのが”SpringSecurity”ということです。
またSpringSecurityにはDBに接続をするメソッドが備わっていますが、実際にはSpring Data JDBCが担いますので、上の図で言うとSpringSecurityとDBの間にはJDBCが入ることになります。

２、実装の流れ。

さっそく実装をしていきますが、簡単に実装の流れを説明していきます。

１、MySQLでListを表示させる。
２、ログイン画面の実装。
３、SpringSecurityの認証機能の実装。

このような形になります。3番が今回のメインかつ一番ボリュームが大きいので1番2番を確実に実装してから3番に移行したほうが後でエラーになった時に解決しやすいです。
自分の場合最初から3番を実装しようとしてMySQLの読み込みでエラーになったりログイン画面のリダイレクト方法を間違えていたりと3番実装以前の段階でエラーが出てしまい解決に時間をかけてしまいました。

３、MySQLでListを表示させる。

では開発に取り掛かりましょう。
前提条件として”STS”と”MySQL”は使っておけるようにしておきましょう。

3-1,プロジェクトを作成します。

　「ファイル」→「新規」→「Spring Boot」→「Spring スターター・プロジェクト」
　以下の画像を参照しアプリ名やグループ名、JAVAのバージョンなどは合わせておくといいでしょう。
　

・プロジェクトの確認。
　以下の様なファイルが作成されていればOKです。
　

・プロジェクト起動します。
　「プロジェクト右クリック」→「実行」→「Spring Boot アプリケーション」の順で起動します。そうすると以下の様なエラーが吐かれると思います。

コンソール

Error starting ApplicationContext. To display the conditions report re-run your application with 'debug' enabled.
2020-01-16 15:39:27.382 ERROR 49572 --- [  restartedMain] o.s.b.d.LoggingFailureAnalysisReporter   : 

***************************
APPLICATION FAILED TO START
***************************

Description:

Failed to configure a DataSource: 'url' attribute is not specified and no embedded datasource could be configured.

Reason: Failed to determine a suitable driver class


Action:

Consider the following:
    If you want an embedded database (H2, HSQL or Derby), please put it on the classpath.
    If you have database settings to be loaded from a particular profile you may need to activate it (no profiles are currently active).

「Failed to configure a DataSource・・・」のところを翻訳すると、データソースの設定に失敗した。ドライバーを特定できないと書いてあります。
更に「If you want an embedded database ・・・」のところを翻訳すると、データベースを使う場合はクラスパスに配備しろ、データベースのロードにはプロファイルをアクティブ化しろ、と書いてあります。
このことから、今回使いたいMySQLを使うためにプロファイルに設定を書く必要があることが何となく理解できるかと思います。

DBを使う場合は「application.property」を編集します。

3-2,application.propertyの編集。

　
SpringBoot_Di_Security_DB_5.png" src="" class="autolink">https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/420031/bcbf58f1-9d17-16f3-472e-013050535ca7.png">

application.property

#//  jdbc:mysql://localhost:3306/login_app はMySQlのURL。それ以降はタイムゾーンの設定をしています。詳しくは[こちら](https://qiita.com/KKZ@github/items/e3f594b04c9233a86419)
spring.datasource.url=jdbc:mysql://localhost:3306/login_app?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
#// rootユーザでログインしています。
spring.datasource.username=root
spring.datasource.password=
#// MySQLのドライバー
spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver
spring.jpa.database=MYSQL
spring.session.store-type=jdbc

3-3,MySQLの立ち上げ。

　コマンドプロンプトまたはPowerShellにてMySQLにログインします。
　以下実際に打つコマンドは「実行コマンド」、コマンド実行時の表示結果などを一色単にしたものは「コマンド実行例」になります。参考にしながら進めてください。

実行コマンド

mysql -uroot

create database login_app;

use login_app;

CREATE TABLE user(
    user_id INT AUTO_INCREMENT,
    user_name VARCHAR(255),
    password VARCHAR(255),
    PRIMARY KEY(user_id)
);

desc user;

INSERT INTO user (user_name, password) VALUES
(
    'user1',
    'pass1+'
),
(
    'yama',
    'kawa'
);

select * from user;

コマンド実行例

PS C:\Users\MGT-RD18> mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2246
Server version: 5.7.28-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

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

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
mysql>
mysql> create database login_app;
Query OK, 1 row affected (0.02 sec)

mysql> use login_app;
Database changed
mysql>
mysql>

mysql>
mysql> CREATE TABLE user(
    ->     user_id INT AUTO_INCREMENT,
    ->     user_name VARCHAR(255),
    ->     password VARCHAR(255),
    ->     PRIMARY KEY(user_id)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql>
mysql> desc user;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| user_id   | int(11)      | NO   | PRI | NULL    | auto_increment |
| user_name | varchar(255) | YES  |     | NULL    |                |
| password  | varchar(255) | YES  |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql>
mysql> INSERT INTO user (user_name, password) VALUES
    -> (
    ->     'user1',
    ->     'pass1+'
    -> ),
    -> (
    ->     'yama',
    ->     'kawa'
    -> );
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>
mysql> select * from user;
+---------+-----------+----------+
| user_id | user_name | password |
+---------+-----------+----------+
|       1 | user1     | pass1+   |
|       2 | yama      | kawa     |
+---------+-----------+----------+
2 rows in set (0.00 sec)

mysql>

　最終的に「select * from user;」の結果が上記のようになっていればOKです。
ここまでくればアプリは実行されるはずです。

3-4,プロジェクト起動します。

　「プロジェクト右クリック」→「実行」→「Spring Boot アプリケーション」の順で起動します。そうすると正常に起動されるはずです。特にErrorなどが出なければOKです。

コンソール出力例

 .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::        (v2.2.2.RELEASE)

2020-01-16 16:38:52.112  INFO 50328 --- [  restartedMain] SpringLogin.app.SpringLoginApplication   : Starting SpringLoginApplication on MGT-RD18 with PID 50328 (started by MGT-RD18 in C:\project\テスト用\SpringLogin)
2020-01-16 16:38:52.127  INFO 50328 --- [  restartedMain] SpringLogin.app.SpringLoginApplication   : No active profile set, falling back to default profiles: default
2020-01-16 16:38:52.174  INFO 50328 --- [  restartedMain] .e.DevToolsPropertyDefaultsPostProcessor : Devtools property defaults active! Set 'spring.devtools.add-properties' to 'false' to disable
2020-01-16 16:38:52.174  INFO 50328 --- [  restartedMain] .e.DevToolsPropertyDefaultsPostProcessor : For additional web related logging consider setting the 'logging.level.web' property to 'DEBUG'
2020-01-16 16:38:52.822  INFO 50328 --- [  restartedMain] .s.d.r.c.RepositoryConfigurationDelegate : Bootstrapping Spring Data JDBC repositories in DEFAULT mode.
2020-01-16 16:38:52.837  INFO 50328 --- [  restartedMain] .s.d.r.c.RepositoryConfigurationDelegate : Finished Spring Data repository scanning in 12ms. Found 0 JDBC repository interfaces.
2020-01-16 16:38:53.137  INFO 50328 --- [  restartedMain] trationDelegate$BeanPostProcessorChecker : Bean 'org.springframework.transaction.annotation.ProxyTransactionManagementConfiguration' of type [org.springframework.transaction.annotation.ProxyTransactionManagementConfiguration] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2020-01-16 16:38:53.397  INFO 50328 --- [  restartedMain] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat initialized with port(s): 8080 (http)
2020-01-16 16:38:53.413  INFO 50328 --- [  restartedMain] o.apache.catalina.core.StandardService   : Starting service [Tomcat]
2020-01-16 16:38:53.413  INFO 50328 --- [  restartedMain] org.apache.catalina.core.StandardEngine  : Starting Servlet engine: [Apache Tomcat/9.0.29]
2020-01-16 16:38:53.491  INFO 50328 --- [  restartedMain] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring embedded WebApplicationContext
2020-01-16 16:38:53.491  INFO 50328 --- [  restartedMain] o.s.web.context.ContextLoader            : Root WebApplicationContext: initialization completed in 1317 ms
2020-01-16 16:38:53.761  INFO 50328 --- [  restartedMain] o.s.s.concurrent.ThreadPoolTaskExecutor  : Initializing ExecutorService 'applicationTaskExecutor'
2020-01-16 16:38:53.871  WARN 50328 --- [  restartedMain] ion$DefaultTemplateResolverConfiguration : Cannot find template location: classpath:/templates/ (please add some templates or check your Thymeleaf configuration)
2020-01-16 16:38:54.058  INFO 50328 --- [  restartedMain] o.s.b.d.a.OptionalLiveReloadServer       : LiveReload server is running on port 35729
2020-01-16 16:38:54.109  INFO 50328 --- [  restartedMain] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat started on port(s): 8080 (http) with context path ''
2020-01-16 16:38:54.113  INFO 50328 --- [  restartedMain] SpringLogin.app.SpringLoginApplication   : Started SpringLoginApplication in 2.453 seconds (JVM running for 3.661)
2020-01-16 16:39:08.470  INFO 50328 --- [nio-8080-exec-2] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring DispatcherServlet 'dispatcherServlet'
2020-01-16 16:39:08.470  INFO 50328 --- [nio-8080-exec-2] o.s.web.servlet.DispatcherServlet        : Initializing Servlet 'dispatcherServlet'
2020-01-16 16:39:08.485  INFO 50328 --- [nio-8080-exec-2] o.s.web.servlet.DispatcherServlet        : Completed initialization in 15 ms

　この状態だとアプリ自体は起動されましたが、localhost:8080で確認することができません。理由は表示する、Viewやcontrollerを作っていないからです。

3-5,Viewとコントローラを作成しとりあえずページを表示する。

　完成イメージは以下になります。いきなり全て作ると混乱しますので、ここでも段階的に作成していきます。今回のゴールは取りあえずViewを表示するだけです。

　・必要なファイルを作成して、以下のコードを記述します。

SpringLogin.app.controller/HomeController.java

package SpringLogin.app.controller;

import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;

@Controller
public class HomeController {

    //userListのアドレスにアクセスした際にGetメソッドを実行。
    @GetMapping("/userList")
    public String getUserList(Model model) {

        //template配下のファイル名を指定することでViewを呼び出せる。     
        return "userList";
    }
}

templates/userList.html

<!DOCTYPE html>
<!-- thymeleafを使うため以下を記述する。以後はth:xxxとすればthymeleafのメソッドが使用可能。 -->
<html xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8"></meta>
</head>
<body>
    <!-- コンテンツ部分 -->
    <div>
        <h1>ユーザリスト</h1>
        <table>
            <tr>
                <th>user_id</th>
                <th>user_name</th>
                <th>password</th>
            </tr>
            <!-- ControllerからuserListを受け取りth:each文で配列の要素を回します。 -->
            <!-- 引数のuserにはUserクラスのインスタンスが格納されているので、フィールド名を指定すれば値を引き出せます。 -->
            <tr th:each="user : ${userList}">
                <td th:text="${user.userId}"></td>
                <td th:text="${user.userName}"></td>
                <td th:text="${user.password}"></td>
            </tr>

        </table>
    </div>
</body>
</html>

・記述が出来たらこれで表示は出来るはずです。

3-6,アプリをブラウザで表示させる。

・Eclipse上でSpringBootアプリケーションが立ち上がっていることを確認し、以下にアクセスします。
http://localhost:8080/userList

・以下のような画面が表示されればOKです。現在はDB接続のオブジェクトを実装していないのでリスト自体は表示出来ていません。

3-7,DB接続用のオブジェクトを実装。

・ファイルの構成イメージは以下になります。赤枠が新規作成、黄色枠が編集するファイルです。

・また処理の流れもイメージにしましたので、参考にして下さい。（毎度簡素なイメージで申し訳ない・・・）

・コードは以下になります。

SpringLogin.app.controller/HomeController.java

package SpringLogin.app.controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;

import SpringLogin.app.service.UserService;
import SpringLogin.app.model.User;

// Controllerクラスのアノテーション
@Controller
public class HomeController {

    // インスタンスを作成しDIコンテナに格納。
    @Autowired
    UserService userService;

    //userListのアドレスにアクセスした際にGetメソッドを実行。
    @GetMapping("/userList")
    public String getUserList(Model model) {

        //@Autowiredで作成したインスタンスを元に、userServiceのメソッドを呼び出す。
        List<User> userList = userService.selectMany();
        //userServiceから受け取ったデータをView側に渡す。
        model.addAttribute("userList", userList);

        //template配下のファイル名を指定することでViewを呼び出せる。
        return "userList";
    }

}

SpringLogin.app.service/UserService.java

package SpringLogin.app.service;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import SpringLogin.app.repository.UserDao;
import SpringLogin.app.model.User;

@Transactional //メソッド内で例外が発生した際にロールバックつまりエラーが起きる直前に戻るアノテーション。
@Service //Serviceクラスのアノテーション。ServiceクラスはDAOから受け取ったデータを変換しControllerに渡す役目がある。
public class UserService {

    @Autowired
    @Qualifier("UserDaoJdbcImpl") //DAOはインターフェースをimplimentsするのが定石らしく、implimentsするファイルを明示的にする。
    UserDao dao;

    //Daoのメソッドを実行。戻り値をList型でControllerに引き渡す。
    public List<User> selectMany() {
        return dao.selectMany();

    }

}

SpringLogin.app.repository/UserDao.java

package SpringLogin.app.repository;

import java.util.List;

import org.springframework.dao.DataAccessException;

import SpringLogin.app.model.User;

public interface UserDao {

    // Userテーブルの全データを取得.
    public List<User> selectMany() throws DataAccessException;

}

SpringLogin.app.repository/UserDaoJdbcImpl.java

package SpringLogin.app.repository;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import SpringLogin.app.model.User;
import SpringLogin.app.repository.UserDao;

@Repository("UserDaoJdbcImpl") //DB接続用クラスのアノテーション。
public class UserDaoJdbcImpl implements UserDao {

    //DB接続のため、JdbcTemplateをDIする。
    @Autowired
    JdbcTemplate jdbc;

    // Userテーブルの全データを取得.
    @Override
    public List<User> selectMany() throws DataAccessException {

        // queryForListメソッドでSQLを発行。queryForListの場合、結果はMap型をList化した形式で結果が返ってくる。
        // MapはKeyとValueがセットになった配列型オブジェクトのこと。
        List<Map<String, Object>> getList = jdbc.queryForList("SELECT * FROM user");

        // Userの型に変換するため、格納用のArrayListのインスタンスを生成。
        List<User> userList = new ArrayList<>();

        for (Map<String, Object> map : getList) {

            User user = new User();
            // map.getでKeyに基づいたValueを取得。それをUserクラスのSetterに当てはめる。
            // 今回の場合はDBのuser_idというカラムから紐づくレコードを取得している。
            user.setUserId((int) map.get("user_id"));
            user.setPassword((String) map.get("password"));
            user.setUserName((String) map.get("user_name"));

            // userList(List型の配列)にUserインスタンスを追加。
            userList.add(user);
        }
        // ListをServiceに返す。
        return userList;
    }


}

SpringLogin.app.model/User.java

package SpringLogin.app.model;

import lombok.Data;

@Data //lombokを使用しているのでアノテーション一つでGetter,Setterを作成できる。
public class User {

    private int userId;
    private String password;
    private String userName;

}

・コードが書けたら、ブラウザにて確認します。Eclipseでアプリを起動していない場合は起動してから確認しましょう。以下の様な画面になっていたらOKです。

これでMySQL使えるようになりました！
長くなってしまったので続きは次回にします。

　 Tweet
Qiitaで続きを読む

投稿日：2020-01-16T16:09:27+09:00

Dockerの公式MySQLの文字コードをutf8mb4に設定する

概要

Docker公式のMySQLの文字コードをutf8mb4に変更する。

mysql> show variables like "chara%";
+--------------------------+----------------------------+
| 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.00 sec)

方法が2つあった。
また謎のエラーが出たのでメモとして残しておく。

方法1 my.cnfの書き換え

my.cnf

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
explicit-defaults-for-timestamp=1
general-log=1
general-log-file=/var/log/mysql/mysqld.log

[client]
default-character-set=utf8mb4

方法2 commandで実行

docker-compose.yml

version: '3'

services:
  # MySQL
  db:
    build: ./docker/db
    image: mysql:5.7.28
    container_name: test_db
    environment:
      MYSQL_ROOT_PASSWORD: xxxx
      MYSQL_DATABASE: test
      MYSQL_USER: test
      MYSQL_PASSWORD: xxx
      TZ: 'Asia/Tokyo'
    command: mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
    volumes:
    - ./docker/db/my.cnf:/etc/mysql/conf.d/my.cnf
    ports:
    - 43306:3306

ERROR 2019 (HY000): Can’t initialize character set utf8mb4　

my.cnfを書き換えたときに以下のようなエラーが出た。

root@b733eb496608:/# mysql
mysql: Character set 'utf8mb4　　　　　　　　　　　　　　　' is not a compiled character set and is not specified in the '/usr/share/mysql/charsets/Index.xml' file
mysql: Character set 'utf8mb4　　　　　　　　　　　　　　　' is not a compiled character set and is not specified in the '/usr/share/mysql/charsets/Index.xml' file
ERROR 2019 (HY000): Can't initialize character set utf8mb4　　　　　　　　 (path: /usr/share/mysql/charsets/)

原因はよくわからなかったが解決方法としては、Dockerfileでコンテナ内をアップデートすれば治った。
たぶん /usr/share/mysql/charsets/Index.xml内にutf8mb4がセットされてなかったからだと思う。

Dockerfile

FROM mysql:5.7.28
RUN touch /var/log/mysql/mysqld.log
RUN apt update \
 && apt install -y --no-install-recommends \
    vim \
 && apt -y clean \
 && rm -rf /var/lib/apt/lists/*

　 Tweet
Qiitaで続きを読む

投稿日：2020-01-16T13:19:08+09:00

MySQL 8.0のTLSを使った接続の暗号化について

概要

TLSプロトコルを使用したMySQLクライアントとMySQLサーバ間（通信経路）の暗号化と接続方法について確認したメモです。

環境

Windows 10 Professional 1909

MySQL Community Server 8.0.18

参考

4.2.3 Command Options for Connecting to the Server

5.1.8 Server System Variables

6.3.1 Configuring MySQL to Use Encrypted Connections

6.3.2 Encrypted Connection TLS Protocols and Ciphers

デフォルトの状態について

インストール直後の状態でオプションファイルなどの設定を変えなくても、デフォルトで接続の暗号化は有効になっています。（MySQL Community Server 8.0.18 (Zip Archive)確認）
またMySQL 8.0.16からTLSv1.3をサポートしています。

6.3.2 Encrypted Connection TLS Protocols and Ciphers
Supported Connection TLS Protocols
MySQL supports encrypted connections using the TLSv1, TLSv1.1, TLSv1.2, and TLSv1.3 protocols, listed in order from less secure to more secure.
----------------------------------------------------------------------------------------------
MySQLは、TLSv1、TLSv1.1、TLSv1.2、およびTLSv1.3プロトコルを使用した暗号化接続をサポートします。これらのプロトコルは、安全性の低いものから高いものの順にリストされています。

クライアント側の確認

このコマンドでログインした場合
> mysql -u test_user -p
この接続の暗号化で使用しているTLSプロトコルと暗号スイートの確認は下記のコマンドで行えます。
この結果から使用しているTLSプロトコルはTLSv1.3、暗号スイートはTLS_AES_256_GCM_SHA384であることがわかります。
> show session status like 'ssl_cipher';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| Ssl_cipher    | TLS_AES_256_GCM_SHA384 |
+---------------+------------------------+
1 row in set (0.01 sec)

> show session status like 'ssl_version';
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| Ssl_version   | TLSv1.3 |
+---------------+---------+
1 row in set (0.01 sec)
また、MySQLサーバがサポートしていて利用できる暗号スイートは以下のサーバステータス変数の値で確認できます。（結果が長いので省略しています）
> show session status like 'Ssl_cipher_list';
+-----------------+--------------------------------------------------------------------------------+
| Variable_name   | Value                                                                          |
+-----------------+--------------------------------------------------------------------------------+
| Ssl_cipher_list | TLS_AES_256_GCM_SHA384:TLS_CHACHA20_POLY1305_SHA256:TLS_AES_128_GCM_SHA256: ...|
+-----------------+--------------------------------------------------------------------------------+
次にこのコマンドで暗号化されていない接続でログインすると（--ssl-modeオプションについては後述します）
> mysql -u test_user -p --ssl-mode=DISABLED
この結果のようにSsl_cipherとSsl_versionの値はセットされません。（ただしパスワードはRSAキーペアファイルで SHA-256 ハッシュ化されるのでクリアテキストで送信されることはありません）
> show session status like 'ssl_cipher';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Ssl_cipher    |       |
+---------------+-------+
1 row in set (0.00 sec)

> show session status like 'ssl_version';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Ssl_version   |       |
+---------------+-------+
1 row in set (0.00 sec)

> show session status like 'Ssl_cipher_list';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Ssl_cipher_list |       |
+-----------------+-------+
1 row in set (0.00 sec)
暗号スイートを明示的に選択する

暗号スイートを明示的に選択しなかった場合はTLS_AES_256_GCM_SHA384が使用されましたが、--tls-ciphersuitesオプションで明示的に選択することができます。
> mysql -u test_user -p --tls-ciphersuites="TLS_AES_128_GCM_SHA256"
> show session status like 'ssl_cipher';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| Ssl_cipher    | TLS_AES_128_GCM_SHA256 |
+---------------+------------------------+
1 row in set (0.07 sec)

> show session status like 'ssl_version';
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| Ssl_version   | TLSv1.3 |
+---------------+---------+
1 row in set (0.00 sec)
サーバ側の確認

MySQLサーバでSSLがサポートされているかはhave_sslシステム変数の値で確認できます。
（なおhave_opensslというシステム変数もありますが、これはhave_sslの別名です）

YES: MySQLサーバでSSL接続がサポートされています。

DISABLED: MySQLサーバはSSL接続をサポートしていますがSSL接続の設定がされていません。
> show global variables like 'have_ssl';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_ssl      | YES   |
+---------------+-------+
1 row in set (0.05 sec)
SSL接続に必要な設定がされていない（若しくはMySQLサーバ起動時に--skip-sslオプションを指定した）状態だと、このようにDISABLEDになります。
> show global variables like 'have_ssl';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_ssl      | DISABLED |
+---------------+----------+
1 row in set (0.02 sec)
その他の設定は下記のシステム変数で確認できます。
> show global variables like 'ssl%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| ssl_ca        | ca.pem          |
| ssl_capath    |                 |
| ssl_cert      | server-cert.pem |
| ssl_cipher    |                 |
| ssl_crl       |                 |
| ssl_crlpath   |                 |
| ssl_fips_mode | OFF             |
| ssl_key       | server-key.pem  |
+---------------+-----------------+
8 rows in set (0.00 sec)

> show global variables like 'tls%';
+------------------+-------------------------------+
| Variable_name    | Value                         |
+------------------+-------------------------------+
| tls_ciphersuites |                               |
| tls_version      | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3 |
+------------------+-------------------------------+
2 rows in set (0.00 sec)
証明書と鍵ファイル

参考: 6.3.3.1 Creating SSL and RSA Certificates and Keys using MySQL

デフォルトでは、接続の暗号化で使用される証明書ファイルや秘密鍵ファイル、認証プラグインで使用されるRSAキーペアファイルがdataディレクトリにあります。
dataディレクトリにca.pem、server-cert.pem、server-key.pemがあると自動的に対応するシステム変数がセットされます。

ファイル名用途対応するシステム変数クライアントで利用

ca.pem 自己署名CA証明書ファイル ssl_ca yes

ca-key.pem CA秘密鍵ファイル

client-cert.pem クライアント用の公開鍵証明書ファイル yes

client-key.pem クライアント用の秘密鍵ファイル yes

private_key.pem caching_sha2_password / sha256_password認証プラグインのRSA秘密鍵ファイル

public_key.pem caching_sha2_password / sha256_password認証プラグインのRSA公開鍵ファイル yes

server-cert.pem サーバ用の公開鍵証明書ファイル ssl_cert

server-key.pem サーバ用の秘密鍵ファイル ssl_key

これらのファイルはMySQLサーバ起動時にdataディレクトリに存在しなければ自動的に作成されますが、自動生成を止めたい場合やファイルの配置場所を変えたい場合は、次のオプションで制御できます。

auto_generate_certs

データディレクトリにSSLキーと証明書ファイルがまだ存在しない場合、それらを自動生成するかどうかを制御します。

Command-Line Format: --auto-generate-certs[={OFF|ON}]

System Variable: auto_generate_certs

ssl_ca

信頼できるSSL認証局のリストを含むファイルへのパス。

Command-Line Format: --ssl-ca=file_name

System Variable: ssl_ca

ssl_capath

PEM形式の信頼できるSSL CA証明書を含むディレクトリへのパス。

Command-Line Format: --ssl-capath=dir_name

System Variable: ssl_capath

ssl_cert

安全な接続の確立に使用するSSL証明書ファイルの名前。

Command-Line Format: --ssl-cert=file_name

System Variable: ssl_cert

ssl_key

安全な接続を確立するために使用するSSLキーファイルの名前。

Command-Line Format: --ssl-key=file_name

System Variable: ssl_key

caching_sha2_password_auto_generate_rsa_keys

RSA秘密/公開鍵ペアファイルがdataディレクトリに存在しない場合、自動生成するかどうかを制御します。

Command-Line Format: --caching-sha2-password-auto-generate-rsa-keys[={OFF|ON}]

System Variable: caching_sha2_password_auto_generate_rsa_keys

caching_sha2_password_private_key_path

caching_sha2_password認証プラグインのRSA秘密鍵ファイルを指定します。デフォルトはprivate_key.pemです。

Command-Line Format: --caching-sha2-password-private-key-path=file_name

System Variable: caching_sha2_password_private_key_path

caching_sha2_password_public_key_path

caching_sha2_password認証プラグインのRSA公開キーファイルを指定します。デフォルトはpublic_key.pemです。

Command-Line Format: --caching-sha2-password-public-key-path=file_name

System Variable: caching_sha2_password_public_key_path

sha256_password_auto_generate_rsa_keys

RSA秘密/公開鍵ペアファイルがdataディレクトリに存在しない場合、自動生成するかどうかを制御します。

Command-Line Format: --sha256-password-auto-generate-rsa-keys[={OFF|ON}]

System Variable: sha256_password_auto_generate_rsa_keys

sha256_password_private_key_path

sha256_password認証プラグインのRSA秘密鍵ファイルを指定します。デフォルトはprivate_key.pemです。

Command-Line Format: --sha256-password-private-key-path=file_name

System Variable: sha256_password_private_key_path

sha256_password_public_key_path

sha256_password認証プラグインのRSA公開鍵ファイルを指定します。デフォルトはpublic_key.pemです。

Command-Line Format: --sha256-password-public-key-path=file_name

System Variable: sha256_password_public_key_path

設定例

以下は、作成したファイルをD:\\dev\\mysql-8.0.18-winx64\\secureに配置し、自動生成を行わない設定例です。
ちなみに、これらのファイルはmysql_ssl_rsa_setup.exeというツールで手動作成することができます。
auto_generate_certs = OFF

ssl_ca = D:\\dev\\mysql-8.0.18-winx64\\secure\\ca.pem
ssl_cert = D:\\dev\\mysql-8.0.18-winx64\\secure\\server-cert.pem
ssl_key = D:\\dev\\mysql-8.0.18-winx64\\secure\\server-key.pem

caching_sha2_password_auto_generate_rsa_keys = OFF

caching_sha2_password_private_key_path = D:\\dev\\mysql-8.0.18-winx64\\secure\\private_key.pem
caching_sha2_password_public_key_path = D:\\dev\\mysql-8.0.18-winx64\\secure\\public_key.pem

sha256_password_auto_generate_rsa_keys = OFF

sha256_password_private_key_path = D:\\dev\\mysql-8.0.18-winx64\\secure\\private_key.pem
sha256_password_public_key_path = D:\\dev\\mysql-8.0.18-winx64\\secure\\public_key.pem
証明書の有効期限

サーバの公開鍵証明書の有効期限は以下のサーバステータス変数の値で確認できます。この証明書はMySQLサーバが自動生成したもので期限は10年間 (3650日)です。
> show status like 'Ssl_server_not%';
+-----------------------+--------------------------+
| Variable_name         | Value                    |
+-----------------------+--------------------------+
| Ssl_server_not_after  | Jan 11 06:21:32 2030 GMT |
| Ssl_server_not_before | Jan 14 06:21:32 2020 GMT |
+-----------------------+--------------------------+
2 rows in set (0.02 sec)
Ssl_server_not_after: 証明書が有効な最後の日

Ssl_server_not_before: 証明書が有効な最初の日

opensslで確認する場合
> openssl x509 -text -noout -in /path/to/server-cert.pem
Certificate:
    Data:
        Version: 3 (0x2)
        Serial Number: 2 (0x2)
        Signature Algorithm: sha256WithRSAEncryption
        Issuer: CN = MySQL_Server_8.0.18_Auto_Generated_CA_Certificate
        Validity
            Not Before: Jan 14 06:21:32 2020 GMT
            Not After : Jan 11 06:21:32 2030 GMT
        Subject: CN = MySQL_Server_8.0.18_Auto_Generated_Server_Certificate
        Subject Public Key Info:
            Public Key Algorithm: rsaEncryption
                RSA Public-Key: (2048 bit)
                Modulus:
                    <<< 省略 >>>
                Exponent: 65537 (0x10001)
        X509v3 extensions:
            X509v3 Basic Constraints: critical
                CA:FALSE
    Signature Algorithm: sha256WithRSAEncryption
         <<< 省略 >>>
認証プラグイン

6.4.1.1 Native Pluggable Authentication

6.4.1.2 Caching SHA-2 Pluggable Authentication

6.4.1.3 SHA-256 Pluggable Authentication

MySQL 8.0のデフォルトの認証プラグインはcaching_sha2_passwordです。その他の認証プラグインにsha256_password(廃止予定)やmysql_native_passwordなどがあります。

MySQLサーバのSSL接続が無効になっているか、非暗号化接続でログインしようとすると以下のエラーが発生します。
> mysql -u test_user -p
ERROR 2061 (HY000): Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.
この場合は--get-server-public-keyでRSA公開鍵ファイルをMySQLサーバから取得するか
> mysql -u test_user -p --get-server-public-key
--server-public-key-path=file_nameで任意の場所にあるRSA公開鍵ファイルを指定してログインします。
> mysql -u test_user -p --server-public-key-path=/path/to/public_key.pem
なお、一度認証が成功するとキャッシュされるので、次からは--get-server-public-keyや--server-public-key-pathは不要になりますが、以下の操作時に全アカウントのキャッシュがクリアされます。

FLUSH PRIVILEGESの実行後

MySQLサーバのシャットダウン

また、以下の操作ではそのアカウントのキャッシュがクリアされます。

アカウント作成後

アカウントのパスワード変更後

アカウントのリネーム後

暗号化接続の確認

SSL/TLS関連のオプションが異なる5種のテストユーザを作成し、暗号化接続の方法にどのような違いがあるか確認します。

テストユーザ

CREATE USER SSL/TLS Options

REQUIRE句

ユーザにSSL/TLS関連のオプションを指定するにはREQUIRE句を使用します。

値意味

NONE (default) デフォルトで暗号化された接続を試みる。暗号化接続ができなかった場合は非暗号化の接続を試みる。非暗号化接続ができなかった場合は接続は失敗する。

SSL デフォルトで暗号化された接続を試みる。暗号化接続ができなかった場合は接続は失敗する。

X509 有効なクライアント証明書を提示する必要がある。そのために--ssl-keyおよび--ssl-certオプションでクライアントの秘密鍵と証明書ファイルを指定する必要がある。
-ssl-caの指定はオプション。

CIPHER 'cipher' 特定の暗号方式でのみ接続を許可する。

テストユーザの作成
CREATE USER 'test_user'@'localhost'  IDENTIFIED BY 'test_user'; /*REQUIRE NONE*/
CREATE USER 'test_user2'@'localhost' IDENTIFIED BY 'test_user2' REQUIRE SSL;
CREATE USER 'test_user3'@'localhost' IDENTIFIED BY 'test_user3' REQUIRE X509;
CREATE USER 'test_user4'@'localhost' IDENTIFIED BY 'test_user4' REQUIRE CIPHER 'TLS_AES_128_GCM_SHA256';     /*TLSv1.3*/
CREATE USER 'test_user5'@'localhost' IDENTIFIED BY 'test_user5' REQUIRE CIPHER 'DHE-RSA-AES128-GCM-SHA256';  /*TLSv1.2*/
> SELECT host,user,ssl_type,ssl_cipher,plugin FROM mysql.user;
+-----------+------------------+-----------+---------------------------+-----------------------+
| host      | user             | ssl_type  | ssl_cipher                | plugin                |
+-----------+------------------+-----------+---------------------------+-----------------------+
| localhost | mysql.infoschema |           |                           | caching_sha2_password |
| localhost | mysql.session    |           |                           | caching_sha2_password |
| localhost | mysql.sys        |           |                           | caching_sha2_password |
| localhost | root             |           |                           | caching_sha2_password |
| localhost | test_user        |           |                           | caching_sha2_password |
| localhost | test_user2       | ANY       |                           | caching_sha2_password |
| localhost | test_user3       | X509      |                           | caching_sha2_password |
| localhost | test_user4       | SPECIFIED | TLS_AES_128_GCM_SHA256    | caching_sha2_password |
| localhost | test_user5       | SPECIFIED | DHE-RSA-AES128-GCM-SHA256 | caching_sha2_password |
+-----------+------------------+-----------+---------------------------+-----------------------+
9 rows in set (0.00 sec)
--ssl-mode=mode別の接続確認

暗号化接続のモードを指定します。このオプションはクライアント専用です。

値意味 (Google翻訳)

DISABLED 暗号化されていない接続を確立します。

PREFERRED (default) サーバが暗号化された接続をサポートしている場合は暗号化された接続を確立し、暗号化された接続を確立できない場合は暗号化されていない接続にフォールバックします。

REQUIRED サーバが暗号化された接続をサポートしている場合、暗号化された接続を確立します。暗号化された接続を確立できない場合、接続の試行は失敗します。

VERIFY_CA REQUIREDに似ていますが、構成されたCA証明書に対してサーバー認証局（CA）証明書をさらに検証します。有効な一致するCA証明書が見つからない場合、接続の試行は失敗します。

VERIFY_IDENTITY VERIFY_CAに似ていますが、サーバーがクライアントに送信する証明書のIDに対してサーバーへの接続にクライアントが使用するホスト名を確認することにより、さらにホスト名のID検証を実行します。

--ssl-modeを指定せず且つ--ssl-caまたは--ssl-capathを指定した場合、--ssl-mode=VERIFY_CAを指定したのと同じ挙動になります。

--ssl-mode=VERIFY_CAを指定した場合、--ssl-caを指定する必要があります。

--ssl-mode=PREFERRED

PREFERREDはデフォルトなので以下の例では明示的に指定していません。

REQUIRE NONE

デフォルトで暗号化接続を行います。このパターンでは暗号化接続ができなかった（若しくはしなかった）場合、非暗号化接続を行います。
> mysql -u test_user -p
// ログイン成功

> show session status like 'ssl_cipher';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| Ssl_cipher    | TLS_AES_256_GCM_SHA384 |
+---------------+------------------------+
1 row in set (0.00 sec)
REQUIRE SSL

デフォルトで暗号化接続を行います。このパターンでは暗号化接続ができなかった場合は接続は失敗します。
> mysql -u test_user2 -p
// ログイン成功

> show session status like 'ssl_cipher';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| Ssl_cipher    | TLS_AES_256_GCM_SHA384 |
+---------------+------------------------+
1 row in set (0.00 sec)
REQUIRE X509

X509では少なくとも--ssl-certと--ssl-keyでクライアントの公開鍵証明書ファイルと秘密鍵ファイルを提示する必要があります。--ssl-caはオプションですが指定する方がより安全です。
// --ssl-cert,--ssl-keyの指定が無い場合はエラー
> mysql -u test_user3 -p
ERROR 1045 (28000): Access denied for user 'test_user3'@'localhost' (using password: YES)

> mysql -u test_user3 -p --ssl-cert=/path/to/client-cert.pem --ssl-key=/path/to/client-key.pem
// ログイン成功

> show session status like 'ssl_cipher';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| Ssl_cipher    | TLS_AES_256_GCM_SHA384 |
+---------------+------------------------+
1 row in set (0.00 sec)
REQUIRE CIPHER 'TLS_AES_128_GCM_SHA256' (TLSv1.3)

特定の暗号スイートが必要な場合は--tls-ciphersuitesで指定する必要があります。
なお--tls-ciphersuitesはTLSv1.3の暗号スイートを指定するオプションで、TLSv1.2以前のものは--ssl-cipherで指定します。
// --ssl-cert, --ssl-key, --tls-ciphersuitesの指定が無い場合はエラー
> mysql -u test_user4 -p
ERROR 1045 (28000): Access denied for user 'test_user4'@'localhost' (using password: YES)

// --tls-ciphersuitesの指定が無い場合はエラー
> mysql -u test_user4 -p --ssl-cert=/path/to/client-cert.pem --ssl-key=/path/to/client-key.pem
ERROR 1045 (28000): Access denied for user 'test_user4'@'localhost' (using password: YES)

// --ssl-cert,--ssl-keyの指定が無い場合はエラー
> mysql -u test_user4 -p --tls-ciphersuites="TLS_AES_128_GCM_SHA256"
ERROR 1045 (28000): Access denied for user 'test_user4'@'localhost' (using password: YES)

> mysql -u test_user4 -p --ssl-cert=/path/to/client-cert.pem --ssl-key=/path/to/client-key.pem --tls-ciphersuites="TLS_AES_128_GCM_SHA256"
// ログイン成功

> show session status like 'ssl_cipher';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| Ssl_cipher    | TLS_AES_128_GCM_SHA256 |
+---------------+------------------------+
1 row in set (0.00 sec)
tls_ciphersuites

暗号化接続を許可するTLSv1.3のTLSプロトコルを使用する暗号スイートのリスト。

Command-Line Format: --tls-ciphersuites=ciphersuite_list

System Variable: tls_ciphersuites

REQUIRE CIPHER 'DHE-RSA-AES128-GCM-SHA256' (TLSv1.2)

暗号スイートがTLSV1.2の場合は--ssl-cipherで指定します。さらに--tls-versionでプロトコルのバージョンを指定する必要があります。
// --ssl-cert, --ssl-key, --ssl-cipher, --tls-versionの指定が無い場合はエラー
> mysql -u test_user5 -p
ERROR 1045 (28000): Access denied for user 'test_user5'@'localhost' (using password: YES)

// --ssl-cipher, --tls-versionの指定が無い場合はエラー
> mysql -u test_user5 -p --ssl-cert=/path/to/client-cert.pem --ssl-key=/path/to/client-key.pem
ERROR 1045 (28000): Access denied for user 'test_user5'@'localhost' (using password: YES)

// --ssl-cert, --ssl-keyの指定が無い場合はエラー
> mysql -u test_user5 -p --ssl-cipher=DHE-RSA-AES128-GCM-SHA256 --tls-version="TLSv1.2"
ERROR 1045 (28000): Access denied for user 'test_user5'@'localhost' (using password: YES)

// 別の暗号スイートを指定した場合はエラー
> mysql -u test_user5 -p --ssl-cert=/path/to/client-cert.pem --ssl-key=/path/to/client-key.pem --tls-ciphersuites="TLS_AES_128_GCM_SHA256"
ERROR 1045 (28000): Access denied for user 'test_user5'@'localhost' (using password: YES)

> mysql -u test_user5 -p --ssl-cert=/path/to/client-cert.pem --ssl-key=/path/to/client-key.pem --ssl-cipher=DHE-RSA-AES128-GCM-SHA256 --tls-version="TLSv1.2"
// ログイン成功

> show session status like 'ssl_cipher';
+---------------+---------------------------+
| Variable_name | Value                     |
+---------------+---------------------------+
| Ssl_cipher    | DHE-RSA-AES128-GCM-SHA256 |
+---------------+---------------------------+
1 row in set (0.00 sec)

> show session status like 'ssl_version';
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| Ssl_version   | TLSv1.2 |
+---------------+---------+
1 row in set (0.00 sec)
ssl_cipher

暗号化接続を許可するTLSv1.2までのTLSプロトコルを使用する暗号スイートのリスト。

Command-Line Format: --ssl-cipher=name

System Variable: ssl_cipher

tls_version

暗号化接続を許可するプロトコルのリスト（カンマ区切り）。

Command-Line Format: --tls-version=protocol_list

System Variable: tls_version

--ssl-mode=DISABLED

REQUIRE NONE

このパターンは非暗号化接続でもRSAキーペアファイルでパスワード交換ができればログインできます。
> mysql -u test_user -p --ssl-mode=DISABLED --get-server-public-key
// ログイン成功

> show session status like 'ssl_cipher';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Ssl_cipher    |       |
+---------------+-------+
1 row in set (0.00 sec)
REQUIRE SSL

このパターンは暗号化接続が必須なので、暗号化接続ができなければ接続は失敗します。
> mysql -u test_user2 -p --ssl-mode=DISABLED --get-server-public-key
ERROR 1045 (28000): Access denied for user 'test_user2'@'localhost' (using password: YES)
REQUIRE X509、REQUIRE CIPHERも同様なので結果は省略します。

--ssl-mode=REQUIRED

REQUIRE NONE

このパターンは暗号化接続が必須です。暗号化接続ができれば下記のようにログインは成功します。
> mysql -u test_user -p --ssl-mode=REQUIRED
// ログイン成功

+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| Ssl_cipher    | TLS_AES_256_GCM_SHA384 |
+---------------+------------------------+
1 row in set (0.00 sec)
サーバ側が暗号化接続をサポートしていない場合はエラーになります。
> mysql -u test_user -p --ssl-mode=REQUIRED
ERROR 2026 (HY000): SSL connection error: SSL is required but the server doesn't support it
REQUIRE SSL
> mysql -u test_user2 -p --ssl-mode=REQUIRED
// ログイン成功

> show session status like 'ssl_cipher';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| Ssl_cipher    | TLS_AES_256_GCM_SHA384 |
+---------------+------------------------+
1 row in set (0.00 sec)
REQUIRE X509
> mysql -u test_user3 -p --ssl-mode=REQUIRED --ssl-cert=/path/to/client-cert.pem --ssl-key=/path/to/client-key.pem
// ログイン成功

> show session status like 'ssl_cipher';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| Ssl_cipher    | TLS_AES_256_GCM_SHA384 |
+---------------+------------------------+
1 row in set (0.00 sec)
REQUIRE CIPHER 'TLS_AES_128_GCM_SHA256' (TLSv1.3)
> mysql -u test_user4 -p --ssl-mode=REQUIRED --ssl-cert=/path/to/client-cert.pem --ssl-key=/path/to/client-key.pem --tls-ciphersuites="TLS_AES_128_GCM_SHA256"
// ログイン成功

> show session status like 'ssl_cipher';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| Ssl_cipher    | TLS_AES_128_GCM_SHA256 |
+---------------+------------------------+
1 row in set (0.00 sec)
REQUIRE CIPHER 'DHE-RSA-AES128-GCM-SHA256' (TLSv1.2)
> mysql -u test_user5 -p --ssl-mode=REQUIRED --ssl-cert=/path/to/client-cert.pem --ssl-key=/path/to/client-key.pem --ssl-cipher="DHE-RSA-AES128-GCM-SHA256" --tls-version="TLSv1.2"
// ログイン成功

> show session status like 'ssl_cipher';
+---------------+---------------------------+
| Variable_name | Value                     |
+---------------+---------------------------+
| Ssl_cipher    | DHE-RSA-AES128-GCM-SHA256 |
+---------------+---------------------------+
1 row in set (0.00 sec)

> show session status like 'ssl_version';
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| Ssl_version   | TLSv1.2 |
+---------------+---------+
1 row in set (0.00 sec) 
--ssl-mode=VERIFY_CA

このモードでは、--ssl-caオプションでCA証明書を指定する必要があります。

REQUIRE NONE
// --ssl-caの指定が無い場合はエラー 
> mysql -u test_user -p --ssl-mode=VERIFY_CA
ERROR 2026 (HY000): SSL connection error: CA certificate is required if ssl-mode is VERIFY_CA or VERIFY_IDENTITY

> mysql -u test_user -p --ssl-mode=VERIFY_CA --ssl-ca=/path/to/ca.pem
// ログイン成功

> show session status like 'ssl_cipher';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| Ssl_cipher    | TLS_AES_256_GCM_SHA384 |
+---------------+------------------------+
1 row in set (0.00 sec)
REQUIRE SSL
// --ssl-caの指定が無い場合はエラー
> mysql -u test_user2 -p --ssl-mode=VERIFY_CA
ERROR 2026 (HY000): SSL connection error: CA certificate is required if ssl-mode is VERIFY_CA or VERIFY_IDENTITY

> mysql -u test_user2 -p --ssl-mode=VERIFY_CA --ssl-ca=/path/to/ca.pem
// ログイン成功

> show session status like 'ssl_cipher';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| Ssl_cipher    | TLS_AES_256_GCM_SHA384 |
+---------------+------------------------+
1 row in set (0.00 sec)
REQUIRE X509

このパターンではCA証明書に加え、クライアント証明書と秘密鍵が必要です。
// --ssl-cert, --ssl-keyの指定が無い場合はエラー
> mysql -u test_user3 -p --ssl-mode=VERIFY_CA --ssl-ca=/path/to/ca.pem
ERROR 1045 (28000): Access denied for user 'test_user3'@'localhost' (using password: YES)

> mysql -u test_user3 -p --ssl-mode=VERIFY_CA --ssl-ca=/path/to/ca.pem --ssl-cert=/path/to/client-cert.pem --ssl-key=/path/to/client-key.pem
// ログイン成功

> show session status like 'ssl_cipher';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| Ssl_cipher    | TLS_AES_256_GCM_SHA384 |
+---------------+------------------------+
1 row in set (0.00 sec)
REQUIRE CIPHER 'TLS_AES_128_GCM_SHA256' (TLSv1.3)
// --ssl-cert, --ssl-keyの指定が無い場合はエラー
> mysql -u test_user4 -p --ssl-mode=VERIFY_CA --ssl-ca=/path/to/ca.pem --tls-ciphersuites="TLS_AES_128_GCM_SHA256"
ERROR 1045 (28000): Access denied for user 'test_user4'@'localhost' (using password: YES)

> mysql -u test_user4 -p --ssl-mode=VERIFY_CA --ssl-ca=/path/to/ca.pem --ssl-cert=/path/to/client-cert.pem --ssl-key=/path/to/client-key.pem --tls-ciphersuites="TLS_AES_128_GCM_SHA256"
// ログイン成功

> show session status like 'ssl_cipher';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| Ssl_cipher    | TLS_AES_128_GCM_SHA256 |
+---------------+------------------------+
1 row in set (0.00 sec)
REQUIRE CIPHER 'DHE-RSA-AES128-GCM-SHA256' (TLSv1.2)
// --ssl-cert, --ssl-keyの指定が無い場合はエラー
> mysql -u test_user5 -p --ssl-mode=VERIFY_CA --ssl-ca=/path/to/ca.pem --ssl-cipher="DHE-RSA-AES128-GCM-SHA256" --tls-version="TLSv1.2"
ERROR 1045 (28000): Access denied for user 'test_user5'@'localhost' (using password: YES)

> mysql -u test_user5 -p --ssl-mode=VERIFY_CA --ssl-ca=/path/to/ca.pem --ssl-cert=/path/to/client-cert.pem --ssl-key=/path/to/client-key.pem --ssl-cipher="DHE-RSA-AES128-GCM-SHA256" --tls-version="TLSv1.2"
// ログイン成功

> show session status like 'ssl_cipher';
+---------------+---------------------------+
| Variable_name | Value                     |
+---------------+---------------------------+
| Ssl_cipher    | DHE-RSA-AES128-GCM-SHA256 |
+---------------+---------------------------+
1 row in set (0.00 sec)

> show session status like 'ssl_version';
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| Ssl_version   | TLSv1.2 |
+---------------+---------+
1 row in set (0.00 sec)
--ssl-mode=VERIFY_IDENTITY

このモードは、下記に引用した通りサーバが自動生成したか、mysql_ssl_rsa_setupツールで手動生成した自己署名証明書では機能しないので動作検証は省略します。

--ssl-mode=mode
Host name identity verification with VERIFY_IDENTITY does not work with self-signed certificates that are created automatically by the server or manually using mysql_ssl_rsa_setup (see Section 6.3.3.1, “Creating SSL and RSA Certificates and Keys using MySQL”). Such self-signed certificates do not contain the server name as the Common Name value.
-------------------------------------------------------------------------------------------
VERIFY_IDENTITYを使用したホスト名ID検証は、サーバーによって自動的に作成されるか、mysql_ssl_rsa_setupを使用して手動で作成される自己署名証明書では機能しません（セクション6.3.3.1「MySQLを使用したSSLおよびRSA証明書とキーの作成」を参照）。このような自己署名証明書には、共通名の値としてサーバー名が含まれていません。

サーバ側の設定で暗号化接続を必須とする

オプションファイルにrequire_secure_transportを設定すると、ユーザーのSSL/TLS関連のオプションにかからわらず非暗号化接続は許可されません。
require_secure_transport = ON
require_secure_transport

この変数を有効にすると、サーバーはSSLを使用するTCP/IP接続、またはソケットファイル(Unix)、または共有メモリ(Windows)を使用する接続のみを許可します。

Command-Line Format: --require-secure-transport[={OFF|ON}]

System Variable: require_secure_transport

このように非暗号化接続でログインしようとすると接続は失敗します。
> mysql -u test_user -p --ssl-mode=DISABLED --get-server-public-key
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
特定の暗号スイートまたはTLSプロトコルのみを許可する

オプションファイルにtls_ciphersuites (TLSv1.3)、ssl_cipher ( ～ TLSv1.2)、tls_versionを設定すると、その条件の接続のみを許可します。
以下の設定ではプロトコルバージョンがTLSv1.2か、TLSv1.3かつ暗号スイートがTLS_AES_256_GCM_SHA384の場合のみを許可します。
tls_ciphersuites = TLS_AES_256_GCM_SHA384
tls_version = TLSv1.2,TLSv1.3
> show global variables like 'tls%';
+------------------+------------------------+
| Variable_name    | Value                  |
+------------------+------------------------+
| tls_ciphersuites | TLS_AES_256_GCM_SHA384 |
| tls_version      | TLSv1.2,TLSv1.3        |
+------------------+------------------------+
2 rows in set (0.03 sec)
--ssl-mode=PREFERRED

上記の設定で且つ--ssl-modeがPREFERRED (default)の場合の各テストユーザの接続がどうなるか確認します。

REQUIRE NONE
> mysql -u test_user -p
// ログイン成功
REQUIRE SSL
> mysql -u test_user2 -p
// ログイン成功
REQUIRE X509
> mysql -u test_user3 -p --ssl-cert=/path/to/client-cert.pem --ssl-key=/path/to/client-key.pem
// ログイン成功
REQUIRE CIPHER 'TLS_AES_128_GCM_SHA256' (TLSv1.3)

暗号スイートが条件を満たしていないので接続は許可されません。
> mysql -u test_user4 -p --ssl-cert=/path/to/client-cert.pem --ssl-key=/path/to/client-key.pem --tls-ciphersuites="TLS_AES_128_GCM_SHA256"
ERROR 2026 (HY000): SSL connection error: error:14094410:SSL routines:ssl3_read_bytes:sslv3 alert handshake failure
REQUIRE CIPHER 'DHE-RSA-AES128-GCM-SHA256' (TLSv1.2)

TLSプロトコルがTLSv1.2という条件を満たしているので接続は許可されます。
> mysql -u test_user5 -p --ssl-cert=/path/to/client-cert.pem --ssl-key=/path/to/client-key.pem --ssl-cipher="DHE-RSA-AES128-GCM-SHA256" --tls-version="TLSv1.2"
// ログイン成功
しかし、オプションファイルでtls_versionを下記のようにTLSv1.3とした場合、
tls_version = TLSv1.3
このユーザの接続は許可されなくなります。
> mysql -u test_user5 -p --ssl-cert=/path/to/client-cert.pem --ssl-key=/path/to/client-key.pem --ssl-cipher="DHE-RSA-AES128-GCM-SHA256" --tls-version="TLSv1.2"
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2026 (HY000): SSL connection error: error:1409442E:SSL routines:ssl3_read_bytes:tlsv1 alert protocol version
補足

暗号スイート (Cipher Suite)

参考: 理解してるつもりの SSL/TLS でも、もっと理解したら面白かった話

TLSv1.3で利用できる暗号スイート

参考: Transport Layer Security (TLS) Parameters

RFC 8446で定義。

TLS_AES_256_GCM_SHA384

TLS_AES_128_GCM_SHA256

TLS_AES_128_CCM_8_SHA256

TLS_AES_128_CCM_SHA256

TLS_CHACHA20_POLY1305_SHA256
> openssl version
OpenSSL 1.1.1d  10 Sep 2019
> openssl ciphers -V
          0x13,0x02 - TLS_AES_256_GCM_SHA384  TLSv1.3 Kx=any      Au=any  Enc=AESGCM(256) Mac=AEAD
          0x13,0x03 - TLS_CHACHA20_POLY1305_SHA256 TLSv1.3 Kx=any      Au=any  Enc=CHACHA20/POLY1305(256) Mac=AEAD
          0x13,0x01 - TLS_AES_128_GCM_SHA256  TLSv1.3 Kx=any      Au=any  Enc=AESGCM(128) Mac=AEAD

//... 省略...
先頭の2つの16進数は、暗号スイートセレクターコード(The cryptographic suite selector code)という、暗号スイートを識別するコード。

Command Options for Encrypted Connections

Option Name System Variable Scope Type

--get-server-public-key

have_openssl

have_ssl Global String

--server-public-key-path

--skip-ssl

--ssl

--ssl-ca ssl_ca Global File name

--ssl-capath ssl_capath Global Directory name

--ssl-cert ssl_cert Global File name

--ssl-cipher ssl_cipher Global String

--ssl-crl ssl_crl Global File name

--ssl-crlpath ssl_crlpath Global Directory name

--ssl-fips-mode ssl_fips_mode Global Enumeration

--ssl-key ssl_key Global File name

--ssl-mode

--tls-ciphersuites tls_ciphersuites Global String

--tls-version tls_version Global String

mysql_ssl_rsa_setup.exe
> mysql_ssl_rsa_setup.exe -v --datadir=D:\dev\mysql-8.0.18-winx64\secure
2020-01-14 00:28:26 [NOTE]    Destination directory: D:\dev\mysql-8.0.18-winx64\secure
2020-01-14 00:28:26 [NOTE]    Executing : openssl version
OpenSSL 1.1.1d  10 Sep 2019
2020-01-14 00:28:26 [NOTE]    Executing : openssl req -newkey rsa:2048 -days 3650 -nodes -keyout ca-key.pem -subj /CN=MySQL_Server_8.0.18_Auto_Generated_CA_Certificate -out ca-req.pem && openssl rsa -in ca-key.pem -out ca-key.pem
Ignoring -days; not generating a certificate
Generating a RSA private key
......................................................................................+++++
..........+++++
writing new private key to 'ca-key.pem'
-----
writing RSA key
2020-01-14 00:28:27 [NOTE]    Executing : openssl x509 -sha256 -days 3650 -extfile cav3.ext -set_serial 1 -req -in ca-req.pem -signkey ca-key.pem -out ca.pem
Signature ok
subject=CN = MySQL_Server_8.0.18_Auto_Generated_CA_Certificate
Getting Private key
2020-01-14 00:28:27 [NOTE]    Executing : openssl req -newkey rsa:2048 -days 3650 -nodes -keyout server-key.pem -subj /CN=MySQL_Server_8.0.18_Auto_Generated_Server_Certificate -out server-req.pem && openssl rsa -in server-key.pem -out server-key.pem
Ignoring -days; not generating a certificate
Generating a RSA private key
......................+++++
........................................................................................+++++
writing new private key to 'server-key.pem'
-----
writing RSA key
2020-01-14 00:28:27 [NOTE]    Executing : openssl x509 -sha256 -days 3650 -extfile certv3.ext -set_serial 2 -req -in server-req.pem -CA ca.pem -CAkey ca-key.pem -out server-cert.pem
Signature ok
subject=CN = MySQL_Server_8.0.18_Auto_Generated_Server_Certificate
Getting CA Private Key
2020-01-14 00:28:27 [NOTE]    Executing : openssl req -newkey rsa:2048 -days 3650 -nodes -keyout client-key.pem -subj /CN=MySQL_Server_8.0.18_Auto_Generated_Client_Certificate -out client-req.pem && openssl rsa -in client-key.pem -out client-key.pem
Ignoring -days; not generating a certificate
Generating a RSA private key
.............................................+++++
....+++++
writing new private key to 'client-key.pem'
-----
writing RSA key
2020-01-14 00:28:27 [NOTE]    Executing : openssl x509 -sha256 -days 3650 -extfile certv3.ext -set_serial 3 -req -in client-req.pem -CA ca.pem -CAkey ca-key.pem -out client-cert.pem
Signature ok
subject=CN = MySQL_Server_8.0.18_Auto_Generated_Client_Certificate
Getting CA Private Key
2020-01-14 00:28:27 [NOTE]    Executing : openssl verify -CAfile ca.pem server-cert.pem client-cert.pem
server-cert.pem: OK
client-cert.pem: OK
2020-01-14 00:28:28 [NOTE]    Executing : openssl genrsa  -out private_key.pem 2048
Generating RSA private key, 2048 bit long modulus (2 primes)
..................................................................................................................................+++++
.................................................................................+++++
e is 65537 (0x010001)
2020-01-14 00:28:28 [NOTE]    Executing : openssl rsa -in private_key.pem -pubout -out public_key.pem
writing RSA key
2020-01-14 00:28:28 [NOTE]    Success!
mysql_ssl_rsa_setup.exeを実行すると、実際には下記のopensslコマンドが実行されています。
# version check
> openssl version
OpenSSL 1.1.1d  10 Sep 2019
# Create CA certificate
> openssl req -newkey rsa:2048 -days 3650 -nodes -keyout ca-key.pem -subj /CN=MySQL_Server_8.0.18_Auto_Generated_CA_Certificate -out ca-req.pem && openssl rsa -in ca-key.pem -out ca-key.pem

> openssl x509 -sha256 -days 3650 -extfile cav3.ext -set_serial 1 -req -in ca-req.pem -signkey ca-key.pem -out ca.pem
ca-key.pem

ca.pem
# Create server certificate
> openssl req -newkey rsa:2048 -days 3650 -nodes -keyout server-key.pem -subj /CN=MySQL_Server_8.0.18_Auto_Generated_Server_Certificate -out server-req.pem && openssl rsa -in server-key.pem -out server-key.pem

> openssl x509 -sha256 -days 3650 -extfile certv3.ext -set_serial 2 -req -in server-req.pem -CA ca.pem -CAkey ca-key.pem -out server-cert.pem
server-key.pem (private key)

server-cert.pem (public key)
# Create client certificate
> openssl req -newkey rsa:2048 -days 3650 -nodes -keyout client-key.pem -subj /CN=MySQL_Server_8.0.18_Auto_Generated_Client_Certificate -out client-req.pem && openssl rsa -in client-key.pem -out client-key.pem

# client-cert.pem = public key, client-key.pem = private key
> openssl x509 -sha256 -days 3650 -extfile certv3.ext -set_serial 3 -req -in client-req.pem -CA ca.pem -CAkey ca-key.pem -out client-cert.pem
client-key.pem (private key)

client-cert.pem (public key)
# Verify certificates
> openssl verify -CAfile ca.pem server-cert.pem client-cert.pem
# Create private RSA key pair
> openssl genrsa -out private_key.pem 2048

# Create public RSA key pair
> openssl rsa -in private_key.pem -pubout -out public_key.pem
private_key.pem

public_key.pem

クラウドサービス

AWS : MySQL on Amazon RDS - Using SSL with a MySQL DB Instance

Google Cloud : SSL / TLS を構成する | Cloud SQL for MySQL

Azure : Azure Database for MySQL に安全に接続するためにアプリケーションで SSL 接続を構成する

ファイル名	用途	対応するシステム変数	クライアントで利用
ca.pem	自己署名CA証明書ファイル	ssl_ca	yes
ca-key.pem	CA秘密鍵ファイル
client-cert.pem	クライアント用の公開鍵証明書ファイル		yes
client-key.pem	クライアント用の秘密鍵ファイル		yes
private_key.pem	caching_sha2_password / sha256_password認証プラグインのRSA秘密鍵ファイル
public_key.pem	caching_sha2_password / sha256_password認証プラグインのRSA公開鍵ファイル		yes
server-cert.pem	サーバ用の公開鍵証明書ファイル	ssl_cert
server-key.pem	サーバ用の秘密鍵ファイル	ssl_key

値	意味
NONE (default)	デフォルトで暗号化された接続を試みる。暗号化接続ができなかった場合は非暗号化の接続を試みる。非暗号化接続ができなかった場合は接続は失敗する。
SSL	デフォルトで暗号化された接続を試みる。暗号化接続ができなかった場合は接続は失敗する。
X509	有効なクライアント証明書を提示する必要がある。そのために--ssl-keyおよび--ssl-certオプションでクライアントの秘密鍵と証明書ファイルを指定する必要がある。 -ssl-caの指定はオプション。
CIPHER 'cipher'	特定の暗号方式でのみ接続を許可する。

値	意味 (Google翻訳)
DISABLED	暗号化されていない接続を確立します。
PREFERRED (default)	サーバが暗号化された接続をサポートしている場合は暗号化された接続を確立し、暗号化された接続を確立できない場合は暗号化されていない接続にフォールバックします。
REQUIRED	サーバが暗号化された接続をサポートしている場合、暗号化された接続を確立します。暗号化された接続を確立できない場合、接続の試行は失敗します。
VERIFY_CA	REQUIREDに似ていますが、構成されたCA証明書に対してサーバー認証局（CA）証明書をさらに検証します。有効な一致するCA証明書が見つからない場合、接続の試行は失敗します。
VERIFY_IDENTITY	VERIFY_CAに似ていますが、サーバーがクライアントに送信する証明書のIDに対してサーバーへの接続にクライアントが使用するホスト名を確認することにより、さらにホスト名のID検証を実行します。

Option Name	System Variable	Scope	Type
--get-server-public-key
	have_openssl
	have_ssl	Global	String
--server-public-key-path
--skip-ssl
--ssl
--ssl-ca	ssl_ca	Global	File name
--ssl-capath	ssl_capath	Global	Directory name
--ssl-cert	ssl_cert	Global	File name
--ssl-cipher	ssl_cipher	Global	String
--ssl-crl	ssl_crl	Global	File name
--ssl-crlpath	ssl_crlpath	Global	Directory name
--ssl-fips-mode	ssl_fips_mode	Global	Enumeration
--ssl-key	ssl_key	Global	File name
--ssl-mode
--tls-ciphersuites	tls_ciphersuites	Global	String
--tls-version	tls_version	Global	String

　 Tweet
Qiitaで続きを読む

投稿日：2020-01-16T12:43:41+09:00

【初心者】create database dotinstall_todo_app;が出たときの対処法

mysqlをいじっていたらデータベースが作れなくなる事態に陥りました^^;
create database dotinstall_todo_app;

ERROR 1006 (HY000): Can't create database 'dotinstall_todo_app' (errno: 2)
このようなエラーとなってしまいました。

ネットで解決方法を調べていると$ mysql.server restartすればいいと記載が！
しかし、mysqlを再起動してもERROR! MySQL server PID file could not be found!
またもエラー

解決方法

$ ps ax | grep mysql

sudo kill -TERM 17259でmysqldを削除

その後、mysql.server restartではなく
sudo mysql.server restartを実行する

rooter:var neko$ sudo mysql.server restart
Password:
Sorry, try again.
Password:
ERROR! MySQL server PID file could not be found!
Starting MySQL
.. SUCCESS!

これでcreate database dotinstall_todo_app;が作成できた。

　 Tweet
Qiitaで続きを読む

投稿日：2020-01-16T12:39:30+09:00

ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statementは秒で解決

mysql> CREATE USER dbuser@'localhost' IDENTIFIED BY 'murasaki';

ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

見かけないエラー文が表示されて困りました・・・
しかし、調べたらすぐに解決したので共有させて頂きます。

解決方法

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER ‘sawaryot’@’localhost’ IDENTIFIED BY ‘sawaryot’;
Query OK, 0 rows affected (0.03 sec)

これだけで無事解決しました。

　 Tweet
Qiitaで続きを読む

投稿日：2020-01-16T10:39:19+09:00

?【CakePHP2】処理の中で別のDB設定ファイルを読み、その内容にてDB再接続をする

環境

PHP 7.2.21
CakePHP 2.10.18
MySQL 5.7.27

やりたいこと

CakePHPの処理中にて別のDBからデータを取得するためにDBの再接続を行いたい
前の記事にて別のDB設定ファイル読込み関数を用意したのでそれと合わせてControllerからDB切替えを行う

やったこと

ビヘイビアに別のDB情報を読み込む関数を用意し
ModelでactAsでビヘイビアを指定

Modelには更にDB再接続の関数を追加しておいてControllerで呼び出す

Model

Model/AppModel.php

<?php
App::uses('AppBaseModel', 'Model');

/**
 * AppModel
 */
class AppModel extends AppBaseModel {
    /**
     * Hogeビヘイビアを使うためactAs追加
     */
    public $actsAs = [
        'Hoge',
    ];

    /**
     * 指定した設定ファイルを読込んでdataSourceを変更しDB再接続
     * @param $dbFileName 読み込むdatabase.phpファイル名
     * @param $dBConfig   database.php内DBの参照情報
     */
    public function connectAnotherDatabase($dbFileName, $dBConfig = 'default') {
        if (!empty($dbFileName)) {
            // $dbFileNameからDB情報呼び出し
            $dbc = $this->readDatabaseValue($dbFileName);
            // 前の処理でインスタンス化したClassを破棄
            ConnectionManager::drop($dBConfig);
            // DB再接続
            $db = ConnectionManager::create($dBConfig, $dbc);
            $db->reconnect($dbc);
            $this->setDataSource($dBConfig);
            return true;
        } else {
            return false;
        }
    }
}

Behavior

https://qiita.com/eltociear/items/55d45a35a8891c52c126
設定ファイル読み込み関数は↑記事と同じものを使用

Model/Behavior/HogeBehavior.php

<?php
/**
 * Hoge behavior
 */
class HogeBehavior extends ModelBehavior {
    /**
     * 別のDB情報を環境database.phpから読込み
     *
     *@param Model  $model
     *@param string $dbFileName 読み込みたいdatabase.phpファイル名
     *@return array 取得情報
     */
    public function readDatabaseValue(Model $model, string $dbFileName) {
        $cnt = 0;
        $flg = false;

        // 読み込みたいdatabase.phpのフルパス
        $filePath = ROOT . DS . 'app' . DS . 'Config' . DS . $dbFileName;
        if (file_exists($filePath)) {
            $fdata = file($filePath, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
            foreach ($fdata as $line) {
                $line = preg_replace("/( |,|')/", "", $line);
                $data = explode('=>', $line);
                if (strpos($line, 'public') !== false) {
                    $cnt++;
                    $flg = false;
                }
                if ($cnt == 1 && $flg == true) {
                    $result[$data[0]] = $data[1];
                }
                $flg = true;
            }
        }

        return [
            'datasource' => $result['datasource'],
            'persistent' => $result['persistent'] === 'true' ? true : false,
            'host'       => $result['host'],
            'login'      => $result['login'],
            'password'   => $result['password'],
            'database'   => $result['database'],
            'prefix'     => $result['prefix'],
            'encoding'   => $result['encoding'],
        ];
    }
}

Controller

別DBに再接続したい処理のあるControllerに設定ファイル名と上記で指定した関数呼び出しを記述

Controller/HogeController.php

// 再接続対象のDB情報がかかれた設定ファイル
$dbFileName = 'Neko.php';
// AppModelをextendsしたModelを使い別のDBに再接続
$this->Users->connectAnotherDatabase($dbFileName);

結果

変数で対象のDB設定ファイルを指定できるので再接続対象を動的にも指定できます

　 Tweet
Qiitaで続きを読む

投稿日：2020-01-16T10:23:35+09:00

MySQLを起動しようとすると『ERROR! The server quit without updating PID file』と表示される。

MySQLが起動できない

HomebrewでインストールしたMySQL5.7を起動しようとすると以下のエラーが表示される。
% mysql.server start                                                                                   
Starting MySQL
. ERROR! The server quit without updating PID file (/usr/local/var/mysql/xxxxx.local.pid).
解決策

MySQLを一度アンインストールして/usr/local/var/mysql内のファイルを全削除した後、再インストールする。
% brew uninstall mysql@5.7
% cd /usr/local/var/mysql

# カレントディレクトリ内(/usr/local/var/mysql)の全フォルダとファイルを削除します。
% rm -rf *
% brew install mysql@5.7 
以下のように表示されれば成功。
% mysql.server start      
Starting MySQL
. SUCCESS! 
原因

MySQLの他のバージョンを使っていたために、ファイルの競合が起こっていた模様。

参考にさせていただいた記事

「ERROR! The server quit without updating PID file」となり、MacOSでmysqlにアクセスできない。
https://qiita.com/furafura_nau/items/7d8f5370899df125d459

　 Tweet
Qiitaで続きを読む

投稿日：2020-01-16T09:19:55+09:00

値がNULLのレコードをCOUNT集計をしてくれなかった件

やりたかったこと

カラムの値別のレコード件数を知りたい

データ

id pref_id

100000 27

138659 NULL

200000 13

262241 NULL

268371 NULL

269365 NULL

300000 13

期待していた結果

pref_id cnt

13 2

27 1

NULL 4

ここで、つまづいた
mysql> select pref_id, count(pref_id) as cnt from dtb_customer group by pref_id;
+---------+-------+
| pref_id | cnt   |
+---------+-------+
|      13 |     2 |
|      27 |     1 |
|    NULL |     0 |
カラムの値がNULLであるレコード件数がゼロで返ってきた

解決
mysql> select pref_id, count(*) as cnt from dtb_customer group by pref_id;
+---------+-------+
| pref_id | cnt   |
+---------+-------+
|      13 |     2 |
|      27 |     1 |
|    NULL |     4 |
集計関数countにカラムを指定せず、レコード全体を指定すると
NULLレコードをカウントしてくれた

環境

MySQL 5.7.27

id	pref_id
100000	27
138659	NULL
200000	13
262241	NULL
268371	NULL
269365	NULL
300000	13

pref_id	cnt
13	2
27	1
NULL	4

　 Tweet
Qiitaで続きを読む

投稿日：2020-01-16T02:53:41+09:00

heroku 登録から（rails mysql）デプロイまで

ローカルでは動作確認済みのRailsアプリを、heroku初デプロイ

環境

heroku

MacBookpro

ruby 2.6.3

bundrer　2.0.2

rails 6.0.1

Mysql2 0.5.3

GitHub　にて　git clone（もしくは　push）してある状態

<今後の時短のために記録> 今回Qiita自己用記録も含め、１０時間かかった様子。
他でデプロイに１４日かけてうまく行かず...原因がMVCではない事を再認識できた。知識不足、書籍等を活用する予定。
heroku１日で出来るのは有難い。

①heroku にアカウント登録
https://jp.heroku.com/

＜＜AWS と違い、基本ローカルからコマンド入力　＞＞

②config/routes.rbのindex編集
Rails.application.routes.draw do
resources :blogs 
root 'home#top'   #トップページ:homeコントローラのtopアクションに設定
gem mysql (の確認。今回はローカルから記入済みなので、もし変更したらbundle install)
＄ git add . 
$ git commit  
$ git push origin master   (GitHubへ）
次回以降のapp作成時は　④$ heroku login へ

③herokuのインストール
~~$ brew tap heroku/brew && brew install heroku~~ ここ保留

$ heroku -v（ersion）　　　　確認
④$ heroku login　のため　sshキー設定 <④は省略内容にはなる為他サイト参考必須>
（ローカルの.ssh に移動）
$ ssh-keygen -t rsa
：作成したいキー名_rsa
ls確認。
$ eval "$(ssh-agent -s)"
cd デプロイ先の階層に戻る
$ heroku keys:add ~/.ssh/作成した_rsa.pub
........SSH key... done　で成功。
$ heroku keys で確認可　＊キーはherokuのHPでも確認可

⑤　
$ heroku create  （herokuのHPで作成したアプリ名）**
or
＄ git remote heroku （herokuのHPで作成したいアプリ名）
Name （作成したいアプリ名） is already taken。。。　で作成された

＜＜＜ $ git remote -v

origin
　origin git@github.com:。。。。。。。

(GitHubのしかないため、リモート：herokuを設定。アプリ名を指定しないとherokuリモートは自動で
作成される様子。）

$ heroku git:remote -a （作成したアプリ名）

set git remote heroku to https://git.heroku.com/（作成したアプリ名）　　と出る
$ git remote -v　で確認。
heroku
origin
＞＞＞

⑥　DB設定
＜　[公式heroku参考] https://elements.heroku.com/addons/cleardb ＞
$ heroku create cleardb:ignite

 Please verify your account to install this add-on plan (please enter a credit card) For more
 ▸    information, see https://devcenter.heroku.com/categories/billing Verify now at
 ▸    https://heroku.com/verify
heroku hpにてクレカの登録

2回目アプリデプロイ時、
Name must start with a letter, end with a letter or digit and can only
▸ contain lowercase letters, digits, and dashes.

と出た為、HPでアドオン　DBFree設定した。　＝　⑦へ

⑦環境変数の設定
$ heroku config　（$ git config --list）で内容確認)


> CLEARDB_DATABASE_URL: mysql://<ユーザー名>:<パスワード>@<ホスト名>/<データベース名>？reconnect=true
$ heroku config:add DB_NAME='<データベース名>'
$ heroku config:add DB_USERNAME='<ユーザー名>'
$ heroku config:add DB_PASSWORD='<パスワード>'
$ heroku config:add DB_HOSTNAME='<ホスト名>'
$ heroku config:add DB_PORT='3306'
$ heroku config:add DATABASE_URL='mysql2://<ユーザー名>:<パスワード>@<ホスト名>/<データベース名>?reconnect=true'
$ heroku config で設定内容　再確認

⑧　config/environments/production.rb　を記入
config/environments/production.rb
config.assets.compile = true
config.assets.initialize_on_precompile=false
＜herokuへpush＞
$ git add .
$ git commit 


$ git push heroku master
⑨database.yml　設定
変更)
production:
  <<: *default
  database: [データベース名]
  username: [ユーザ名]
  password: <%= ENV['DATABASE_PASSWORD'] %>
(１０) herokuのDBのマイグレーション
$ heroku rake db:migrate

$ heroku open
remote: Verifying deploy... done.　　成功。

[上のURLの方。ターミナ下方のURLではない。

https://（各自）...herokuapp.com/ 　（←　これ　=>　deployed to Heroku ]

＜urlに接続＞

<ログを表示>
$ heroku logs    
(heroku logs --tail)
↑　見ても原因が良くわからない為 ↓で今回はエラー解決した
$ heroku run rails console　の方が分かる

＜urlに接続したが、エラーindex.htmlに繋がる。＞

ここからエラー内容3回目
$ heroku restart 一応。
（firebaseデプロイ時は時差があった、が変わらず。）
$ heroku logs --tail
内容が分からず、
$ heroku run rails console　　で原因が分かりやすい。

Could not load the 'mysql' Active Record adapter. Ensure that the adapter is spelled correctly in config/database.yml and that you've added the necessary adapter gem to your Gemfile. (LoadError)
ここで、Gemfile に合わせ　mysql　→　mysql２　に変更した。

デプロイ成功

②config/routes.rbで指定したindex.htmlが表示された。

ここから↓ git push heroku master成功までの

実際のエラー対処記載　一部を参考に残す。

⑨ ←ここでエラー内容1回目
Could not load the 'mysql' A....database.yml　も確認しろと出る。
　database.yml　確認修正↓

⑦の　$ heroku config:add DATABASE_URL='mysql２://<ユーザー名>:<パスワード>@<ホスト名>/<データベース名>?reconnect=true'

を　Gemfile に合わせ　mysql　→　mysql２　に最終的に変更。かつ bundle install

エラー内容2回目
...
Could not detect rake tasks
remote:  !     ensure you can run `$ bundle exec rake -P` ...URI::InvalidURIError: bad URI(is not URI?): mysql2:....
$ rbenv -v
rbenv 1.1.2
$ ruby -v
ruby '2.6.4'

$ gem install bundler -v 2.0.2
$ bundle install( + update)
$ rbenv -v が1.1.2のままの為、
$ rm Gemfile.lock
$ bundle install　でGemfile.lock　再　bundler -v 2.0.2へ。
（add .  commit )
$ git push heroku master
変わらず同エラー。
今回は公式サイトを参考に、 ruby '2.6.3'に下げる。
$ rbenv install 2.6.3
$ rbenv local 2.6.3
（Gemfileも変更。$ bundle install　$ rbenv rehash）
変わらず同エラー。
$ heroku config　設定ミスか確認。
$ heroku config:add DATABASE_URL='mysql://...をmysql2:...
に変更。
変わらず同エラー。
<直接な解決なのかは不明だが、今回は、$ heroku create アプリ名を指定しないで再度やり直す。このエラーは解決>
$ heroku create
$ git remote　で自動herokuリモートが作成された。
$ git push heroku master
bad URI(is not URI?): mysql2:...エラーは解決したが、
新たなエラー1回目
remote:  !
remote:  !     Precompiling assets failed.
Precompil　なので、
config/application.rbに
config.assets.initialize_on_precompile = true　を追記。
config/environments/production.rbの
onfig.assets.compile = false を　trueに。

変わらず。
$ RAILS_ENV=production （bundle exec） rake assets:precompile　
変わらず。
webpacker.yml　.gitignore の編集で解決した。
webpacker.yml　内に
production:
<<: *default
compile: true　がある為と思われる。全て知識不足が原因。

実際には③で　小さいエラーが出て　bundle update　install 等をプラス作業した
その後、push。

＜基本だが一応残す＞
rails -v
ruby -v
mysql -v

。。。ローカルの諸々の確認はした前提の内容。
③で　
...Warning: heroku update available from 7.35.0 to 7.35.1.....

$ heroku uodate　を実行。
$ heroku -v　確認
⑨　mysql2 エラー

（$ brew update && brew upgrade ruby-build できなかった）

GitHub 接続確認
$ ssh -T github git@github.com　
他サーバーにて設定変更してしまった為今回再設定。

参考にしたサイト

mysqlを使ったRailsアプリをHerokuにデプロイする流れ

追えなくなってしまいましたが、その他、多々多々参考にさせて頂きました。

herokuチュートリアル
一部コマンドが公式の方が新しい様子です...

　 Tweet
Qiitaで続きを読む

20200116のMySQLに関する記事は11件です。

Dockerを使って、phpMyAdminをいとも簡単に接続する 。

PHPでの簡易掲示板の作成の際の考え方

登録画面(register.php)

確認画面(confirm.php)

画像のアップロード(register.php)

確認画面(登録処理)(confirm.php)

ログイン機能(login.php)

次回からは自動でログインする機能(login.php)

メッセージ一覧画面(index.php)

返信機能(index.php?res=メッセージID)

メッセージ詳細画面(view.php?id=メッセージID)

メッセージの削除機能(delete.php?id=メッセージID)

ページネーション(index.php?page=ページ数)

ログアウト機能(logout.php)

SpringSecurityで認証機能を実装①

１、SpringSecurityのイメージ

２、実装の流れ。

３、MySQLでListを表示させる。

3-1,プロジェクトを作成します。

3-2,application.propertyの編集。

3-3,MySQLの立ち上げ。

3-4,プロジェクト起動します。

3-5,Viewとコントローラを作成しとりあえずページを表示する。

3-6,アプリをブラウザで表示させる。

3-7,DB接続用のオブジェクトを実装。

Dockerの公式MySQLの文字コードをutf8mb4に設定する

概要

方法1 my.cnfの書き換え

方法2 commandで実行

ERROR 2019 (HY000): Can’t initialize character set utf8mb4

MySQL 8.0のTLSを使った接続の暗号化について

概要

デフォルトの状態について

クライアント側の確認

暗号スイートを明示的に選択する

サーバ側の確認

証明書と鍵ファイル

設定例

証明書の有効期限

認証プラグイン

暗号化接続の確認

テストユーザ

--ssl-mode=mode別の接続確認

--ssl-mode=PREFERRED

REQUIRE NONE

REQUIRE SSL

REQUIRE X509

REQUIRE CIPHER 'TLS_AES_128_GCM_SHA256' (TLSv1.3)

REQUIRE CIPHER 'DHE-RSA-AES128-GCM-SHA256' (TLSv1.2)

--ssl-mode=DISABLED

REQUIRE NONE

REQUIRE SSL

--ssl-mode=REQUIRED

REQUIRE NONE

REQUIRE SSL

REQUIRE X509

REQUIRE CIPHER 'TLS_AES_128_GCM_SHA256' (TLSv1.3)

REQUIRE CIPHER 'DHE-RSA-AES128-GCM-SHA256' (TLSv1.2)

--ssl-mode=VERIFY_CA

REQUIRE NONE

REQUIRE SSL

REQUIRE X509

REQUIRE CIPHER 'TLS_AES_128_GCM_SHA256' (TLSv1.3)

REQUIRE CIPHER 'DHE-RSA-AES128-GCM-SHA256' (TLSv1.2)

--ssl-mode=VERIFY_IDENTITY

サーバ側の設定で暗号化接続を必須とする

特定の暗号スイートまたはTLSプロトコルのみを許可する

--ssl-mode=PREFERRED

REQUIRE NONE

REQUIRE SSL

REQUIRE X509

REQUIRE CIPHER 'TLS_AES_128_GCM_SHA256' (TLSv1.3)

REQUIRE CIPHER 'DHE-RSA-AES128-GCM-SHA256' (TLSv1.2)

補足

暗号スイート (Cipher Suite)

Command Options for Encrypted Connections

mysql_ssl_rsa_setup.exe

クラウドサービス

【初心者】create database dotinstall_todo_app;が出たときの対処法

Dockerを使って、phpMyAdminをいとも簡単に接続する。

ERROR 2019 (HY000): Can’t initialize character set utf8mb4　

次回以降のapp作成時は　④$ heroku login へ

④$ heroku login　のため　sshキー設定 <④は省略内容にはなる為他サイト参考必須>

⑧　config/environments/production.rb　を記入

⑨database.yml　設定

$ heroku run rails console　の方が分かる

実際のエラー対処記載　一部を参考に残す。