前提と準備

C#の記事

• C#でMySQLを使う - 1.開発環境インストール
• C#でMySQLを使う - 2.SELECT・画面表示
• C#でMySQLを使う - 3.追加更新と削除【この記事】

前回はVisual Studio 2019にMySQL Connectors .NETを用いて、とりあえずMySQLに接続して、SELECTを用いてデータの内容を画面に表示しました。今回はDBのデータそのものを変更を施します(˶ ･ᴗ･ )੭

環境

• OS：Windows 10 Pro
• 開発環境：Visual Studio 2019 (MySQL Connectors .NET利用)
• データベース：MySQL 5.7

MySQLサーバー

テスト用ユーザー：test (パスワード：test1)
データベース：manutest
テーブル名：testtb

	id	name	memo
型	INT	VARCHAR(64)	VARCHAR(256)
必須属性	PRIMARY KEY	NOT NULL	初期値NULL
その他属性	AUTO_INCREMENT	-	-

前提

前回のように、Visual Studio 2019とMySQLをインストールし、かつMySQL Connectors .NETがインストールされていること(Visual Studioは自動でMySQL Connectorsのライブラリを認識してくれる)

作業手順

Visual Studio 2019でのコーディング

コンポーネント・コントロールの配置

今回はボタンを押下→MySQLでデータを拾ってくる→画面に表示
これに加えて、名前とメモを入力し、追加や更新などができるようにフォームを追加
右上のリストボックスを選択すると該当するIDが画面表示されるので、選択している際に該当IDの更新や削除ができるようになりますが、細かいUI制御までは触れません

WindowsのC#フォームのプロジェクトを新規作成し、Form1.csデフォルトで開きました

• 左上：データグリッド dataGridView1
• 右上：リストボックス2つ listBox1・listBox2
  • 選択するとテキストボックス「idNum」に該当するIDを表示する
• 左下：SQLを読み込むボタン button1
• 右中央：名前入力 textBoxName・メモ入力 textBoxMemo
• 右下：選択中のID idNum・新規追加SQL実行 buttonAdd・更新SQL実行 buttonEdit・削除SQL実行 buttonDel

これらを貼り付けます。
データグリッドとリストボックスはSQLのデータを表示させるために配置しました

MySQL Connector .NETの参照を追加

MySQLインストール時と同じように、参照を追加していきます。

「アセンブリ」の中に実際自動で認識してくれるので、参照マネージャーで、右上の検索画面に「mysql」を入力すると、↑の画面のように「MySql.Data」が何行も出てくるので、とりあえず1つだけ、どれでもいいので選択して(行左にマウスを当てると出てくるチェックボックスをON)「OK」を選択すると…

「MySql.Data」が追加されました(*˘꒳˘*)
この中にMySQLを扱うC#オブジェクトが入っているのです。

ボタンクリック時のソースを記述する

今回も簡単のため、SQLの実行はボタンクリックで読み込むだけで、Form1.csのクリックイベント動作のみをコーディングしました。本来は規模が大きくなることがほとんどなので、formのボタンクリックではなく、独立したクラスに分けるケースがほとんどですが。。。

Form1.cs サンプルコード

Form1.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Windows.Forms;
using MySql.Data.MySqlClient;

namespace MySqlFormsTest
{
    public partial class Form1 : Form
    {
        // 取得したデータのID一覧(フォーム内部変数)
        private List<int> idNums;

        // 選択中のID
        private int selId;

        // MySQL接続情報
        private string connStr = "server=127.0.0.1;user id=test;password=test1;database=manutest";

        public Form1()
        {
            InitializeComponent();
        }

        /**
         * 接続ボタン(MySQLサーバーに接続し、一覧データを取得する)
         * */
        private void button1_Click(object sender, EventArgs e)
        {
            MySqlConnection conn = new MySqlConnection(this.connStr);

            // 画面と内部変数を初期化
            listBox1.Items.Clear();
            listBox2.Items.Clear();
            this.idNums = new List<int>();
            selId = -1;
            idNum.Text = "";
            buttonEdit.Enabled = false;
            buttonDel.Enabled = false;

            try
            {
                // 接続を開く
                conn.Open();

                // データを取得するテーブル
                DataTable tbl = new DataTable();

                // SQLを実行する
                MySqlDataAdapter dataAdp = new MySqlDataAdapter("SELECT id, name, memo FROM testtb", conn);
                dataAdp.Fill(tbl);

                // dataGridViewに表示させる
                dataGridView1.DataSource = tbl;

                // 実行結果を1行ずつ参照する場合
                for (int i = 0; i < tbl.Rows.Count; i++)
                {
                    DataRow row = tbl.Rows[i];  // データ行

                    // 右のリストボックスにアイテムを追加
                    listBox1.Items.Add(row[1]);
                    listBox2.Items.Add(row[2]);
                    // 同時にIDを内部変数に追加
                    this.idNums.Add((int)row[0]);
                }

                // 接続を閉じる
                conn.Close();
            }
            catch (MySqlException mse)
            {
                MessageBox.Show(mse.Message, "データ取得エラー", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        /**
         * リストボックスの行を選択
         */
        private void listBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            this.listBox_SelectedIndexChanged(sender, e);

            // 選択したIDを画面表示
            if(listBox1.SelectedIndex != -1)
            {
                this.selId = idNums[listBox1.SelectedIndex];
                idNum.Text = idNums[listBox1.SelectedIndex].ToString();
            }
            else
            {
                this.selId = -1;
                idNum.Text = "";
            }
        }
        private void listBox2_SelectedIndexChanged(object sender, EventArgs e)
        {
            this.listBox_SelectedIndexChanged(sender, e);

            // 選択したIDを画面表示
            if (listBox2.SelectedIndex != -1)
            {
                this.selId = idNums[listBox2.SelectedIndex];
                idNum.Text = idNums[listBox2.SelectedIndex].ToString();
            }
            else
            {
                this.selId = -1;
                idNum.Text = "";
            }
        }
        private void listBox_SelectedIndexChanged(object sender, EventArgs e)
        {
            // どちらかを選択していないと追加と削除が使えない
            if(listBox1.SelectedIndex != -1 || listBox2.SelectedIndex != -1)
            {
                buttonEdit.Enabled = true;
                buttonDel.Enabled = true;
            }
            else
            {
                buttonEdit.Enabled = false;
                buttonDel.Enabled = false;
            }
        }


        /**
         * 新規追加ボタンを選択
         */
        private void buttonAdd_Click(object sender, EventArgs e)
        {
            MySqlConnection conn = new MySqlConnection(this.connStr);
            MySqlTransaction trans = null;          // 実行トランザクション

            // 新規追加のSQLコマンド
            string sqlCmd = @"INSERT INTO testtb (name, memo) VALUES (@name, @memo)";

            // 追加クエリの開始
            MySqlCommand cmd = new MySqlCommand(sqlCmd, conn);

            try
            {
                // ステークホルダーのセット
                cmd.Parameters.AddWithValue("name", textBoxName.Text);
                cmd.Parameters.AddWithValue("memo", textBoxMemo.Text);

                cmd.Connection.Open();              // 接続を開く

                // トランザクション監視開始
                trans = cmd.Connection.BeginTransaction(IsolationLevel.ReadCommitted);

                // SQL実行
                cmd.ExecuteNonQuery();

                // DBをコミット
                trans.Commit();
            }
            catch (MySqlException mse)
            {
                trans.Rollback();                   // 例外発生時はロールバック
                MessageBox.Show(mse.Message, "データ追加エラー", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                // 接続はクローズする
                cmd.Connection.Close();
            }
        }

        /**
         * 編集ボタンを選択
         */
        private void buttonEdit_Click(object sender, EventArgs e)
        {
            MySqlConnection conn = new MySqlConnection(this.connStr);
            MySqlTransaction trans = null;          // 実行トランザクション

            // 編集のSQLコマンド
            string sqlCmd = @"UPDATE testtb SET name = @name, memo = @memo WHERE id = @id";

            // 編集クエリの開始
            MySqlCommand cmd = new MySqlCommand(sqlCmd, conn);

            try
            {
                // 選択中のIDを用いて、ステークホルダーのセット
                cmd.Parameters.AddWithValue("id", this.selId);
                cmd.Parameters.AddWithValue("name", textBoxName.Text);
                cmd.Parameters.AddWithValue("memo", textBoxMemo.Text);

                cmd.Connection.Open();              // 接続を開く

                // トランザクション監視開始
                trans = cmd.Connection.BeginTransaction(IsolationLevel.ReadCommitted);

                // SQL実行
                cmd.ExecuteNonQuery();

                // DBをコミット
                trans.Commit();
            }
            catch (MySqlException mse)
            {
                trans.Rollback();                   // 例外発生時はロールバック
                MessageBox.Show(mse.Message, "データ更新エラー", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                // 接続はクローズする
                cmd.Connection.Close();
            }
        }

        /**
         * 削除ボタンを選択
         */
        private void buttonDel_Click(object sender, EventArgs e)
        {
            MySqlConnection conn = new MySqlConnection(this.connStr);
            MySqlTransaction trans = null;          // 実行トランザクション

            // 削除のSQLコマンド
            string sqlCmd = @"DELETE FROM testtb WHERE id = @id";

            // 削除クエリの開始
            MySqlCommand cmd = new MySqlCommand(sqlCmd, conn);

            try
            {
                // 選択中のIDを用いて、ステークホルダーのセット
                cmd.Parameters.AddWithValue("id", this.selId);

                cmd.Connection.Open();              // 接続を開く

                // トランザクション監視開始
                trans = cmd.Connection.BeginTransaction(IsolationLevel.ReadCommitted);

                // SQL実行
                cmd.ExecuteNonQuery();

                // DBをコミット
                trans.Commit();
            }
            catch (MySqlException mse)
            {
                trans.Rollback();                   // 例外発生時はロールバック
                MessageBox.Show(mse.Message, "データ削除エラー", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                // 接続はクローズする
                cmd.Connection.Close();
            }
        }
    }
}

まずフォームのC#ソースの内部変数として、MySQLのデータでどのIDを取得したかと、右上のリストボックスの項目に対応するIDはどれを選択しているのかを把握するため、Form1にprivate変数を追加しました

// 取得したデータのID一覧(フォーム内部変数)
private List<int> idNums;

// 選択中のID
private int selId;

読み込むSQLを実行するボタンで「SELECT id, name, memo～」をbutton1_Click()内で実行していると思いますが、単に右上のリストボックスにピックアップするだけでなく、対応するIDを内部変数idNumsで受け持つコードも実装しました

// 実行結果を1行ずつ参照する場合
for (int i = 0; i < tbl.Rows.Count; i++)
{
    DataRow row = tbl.Rows[i];  // データ行

    // 右のリストボックスにアイテムを追加
    listBox1.Items.Add(row[1]);
    listBox2.Items.Add(row[2]);
    // 同時にIDを内部変数に追加
    this.idNums.Add((int)row[0]);
}

row[0]が取得したデータのID番号ですが、そのままListにAdd()を実行するとObjectを暗黙的に変換できないエラーとなるため、(int)でキャストしました。

listBox1.SelectedIndex
listBox2.SelectedIndex

listBox1_SelectedIndexChanged()などで使っていますが、リストボックスの選択したインデックス番号(0で始まるもの)はこれを使っています(未選択の場合は-1)。選択するとidNumの選択中IDのフィールドに表示する仕組みを入れたんです。選択中のIDは内部変数に入れています。

this.selId = idNums[listBox1.SelectedIndex];

INSERTやUPDATEなどDBの内容を更新するSQLは、MySqlCommandを使ったんですが、こっちのほうがプレースホルダーが使いやすいので、SELECT以外はMySqlCommandを使いました。

string sqlCmd = @"INSERT INTO testtb (name, memo) VALUES (@name, @memo)";
MySqlCommand cmd = new MySqlCommand(sqlCmd, conn);

そして更新の際はトランザクションを使っています。例外が発生したときはいつもロールバックするようにしていますが、正直タイミングは考えるのは大変なので、今回は簡単で例外キャッチのみで；；

try
{
    trans = cmd.Connection.BeginTransaction(IsolationLevel.ReadCommitted);
    cmd.ExecuteNonQuery();
    trans.Commit();
}
catch (MySqlException mse)
{
    trans.Rollback();                   // 例外発生時はロールバック
}

実行結果

今回は追加などのボタンに再読み込み機能がないので、追加などのボタンを押した後は「読み込み」ボタンを押して再表示させています(面倒ですが…)

最初に下の画像のようなデータがあるとして、IDが6のデータを更新すると…


「更新」→「読み込み」で、更新に成功しました(˶ ･ᴗ･ )੭

次はIDが5の「Testing」というデータを選択して消してみます


再度読み込みボタンを押して、これも削除成功しました！！

最後に追加してみます


見事成功しました(˶ ･ᴗ･ )੭！！

次回

Visual StudioでMySQLのデータがNULLの場合にハマったことがあるので、そっちについて追ってみます

参考文献

1. 【Visual Studio】C#からMySqlの使い方　接続するには？SQL文を実行するには？ - 困ったー
2. C#でMySQLからSELECTした結果を取り出したい

関連文献

1. C#のMySQLでトランザクション使用する / C#でのトランザクションの実装について
2. Symfoware Server アプリケーション開発ガイド(埋込みSQL編) - FUJITSU - / SQLのトランザクションとは何かの理解
3. 【MySQL 5.1向けのもの】MySqlTransaction の使用 / C#でのトランザクション実装例
4. IsolationLevel 列挙型 / C#でのトランザクションレベル - .NET API

データベースの設定

DB_CONNECTION=mysql
DB_HOST=localhost
DB_PORT=8889
DB_DATABASE=recommendMovie_db
DB_USERNAME=root
DB_PASSWORD=root

適当に以下のようなデータをほしいとします。今回は10万レコードを作成してみます。

その1 procedure でやってみる

create table hoge (num int, name varchar(255));

delimiter //
create procedure insert_hoge(in x int)
begin
  declare i int default 1;
  set @q = "insert into hoge values ()";
  while i < x do
    set i = i + 1;
    set @q = concat(@q, ',()');
  end while;
  prepare stmt from @q;
  execute stmt;
end
//
delimiter ; 

call insert_hoge(100000);

/* 適当に null で10万行埋めてから、update でランダムな値にする */

update hoge set
  num = round(rand()*100000),
  name = concat('name', round(rand()*100000));

これはそんなに速くなくて15秒くらいかかります。

その2 cross join でやってみる

こっちの方が早いです5秒くらいですみます。

create table piyo (num int, name varchar(255));

insert into piyo values (), (), (), (), (), (), (), (), (), ();

insert into piyo (select piyo.num, piyo.name from piyo, piyo p2, piyo p3, piyo p4, piyo p5);

update piyo set
  num = round(rand()*100000),
  name = concat('name', round(rand()*100000));

その3 procedure で、値もいれてみる

update を無くして、最初からランダムを insert してみます。これは遅くて30秒くらい

delimiter //
create procedure hoge(in x int)
begin
  declare i int default 0;
  set @q = "insert into hoge (num, name) values ";
  while i < x do
    set i = i + 1;
    set @name_value = concat('name', round(rand()*10000));
    set @value = concat('(', i , ', "', @name_value, '")');
    if i = 1 then
      set @q = concat(@q, @value);
    else
      set @q = concat(@q, concat(',', @value));
    end if;
  end while;
  prepare stmt from @q;
  execute stmt;
end
//
delimiter ;

その4 procedure でバルクインサートしない場合

while の中で毎回 insert などをすると、たぶん2分以上かかると思います。

まとめ

procedure でぴったり狙った件数のデータを出すのはかっこよさそうですが、速さは cross join でやる方が早いので、そちらの方が実用的かと思います。

procedure の中で、user-defined variable や prepared statement 使ってみたりして勉強になりました。

はじめに

前回の記事で、MySQL Database Service の Getting Started をやってみました。今回の記事では、Oracle Cloud Infrastructure(以下OCI) に標準的に備わっている Monitoring で、基本的なモニタリング監視を行う手順を紹介します。

次の2種類の方法を紹介します

• CPU 使用率が 80% を超えたことを検知して、メール送信

• 死活監視してメール送信 (厳密には異なる)

死活監視の部分は、「CPU 使用率のメトリックが無い状態」を検知してアラートを行います。「CPU使用率のメトリックが無い状態」は、MySQL インスタンス側で通常では考えにくい何らかの障害が発生していると考えられます。
厳密に、MySQL Server として稼働していることを確認したい場合は、実際に MySQL Client で 3306 Port に対して接続して Select クエリーを実行するといった、別の方法で死活監視するのがよいでしょう。

MySQL Database Service Metrics

MySQL Database Service で取得可能なメトリクスは、次の Document で一覧化されています。

https://docs.cloud.oracle.com/en-us/iaas/mysql-database/doc/mysql-database-metrics.html

• CurrentConnections : Total Connection 数
• ActiveConnections : Active Connection 数
• Statements : Statement の実行された回数
• StatementLatency : 実行された全 Statement のレイテンシー
• CPUUtilization : CPU使用率
• MemoryUtilization : メモリ使用率
• DbVolumeReadOperations : DB Volume に対する、読み込みオペレーションの回数
• DbVolumeWriteOperations : DB Volume に対する、書き込みオペレーションの回数

色々便利そうなメトリクスがありますね。特に便利そうなのが、StatementLatency です。本番稼働中の標準的な StatementLatency がわかれば、そこから閾値を計算して、レイテンシーがあまりにも遅すぎる状態のアラートが出来そうですね。

Metric を実際に見てみましょう。MySQL Database Service から、対象のインスタンスを選択します。

インスタンスの詳細画面で、Metric が見えます

メール送付設定

Notifications という名前のサービスで、メールの宛先を設定します。

Create Topic

適当に名前を入れて、Create を押します。

名前を選択します

Create Subscription

メール通知したいメールアドレスを指定して、Create を押します

Pending となります。

設定したメールアドレスに確認メールが送付されます。Confirm をクリックすると確認完了です。

オレンジ色で Pending だったものが、Active に切り替わります。これで、Notifications の設定はおわりです。

CPU 使用率でアラーム設定

それでは、MySQL Database Service の CPU 使用率の設定をしていきます。
CPU Utilication の Metric のところで、Create an Alarm on this Query を選びます。

各種パラメータを入れて、Save Alarm を押します。

Alarm Name がメールアドレスのタイトルとなります。
Alarm Body が、メール本文に書かれます。

設定完了です

動作確認 : CPU 使用率のアラーム

mysqlslap は、MySQL サーバーのクライアント負荷をエミュレートし、各段階のタイミングをレポートする診断プログラムです。複数のクライアントがサーバーにアクセスしているかのように作動します。今回は、mysqlslap を使って、MySQL Database Service に負荷を掛けてみます。

https://dev.mysql.com/doc/refman/8.0/en/mysqlslap.html

200台のクライアントがクエリーを実行し、それぞれ400 のクエリーを発行する負荷を掛けてみます。
※sqlslap で頑張っても、80% を超すのは難しかったので、実際の検証ではアラームの閾値を下げました

mysqlslap --delimiter=";" \
  --auto-generate-sql \
  --auto-generate-sql-add-autoincrement \
  --auto-generate-sql-load-type=mixed \
  --auto-generate-sql-write-number=250 \
  --number-of-queries=1000 \
  --concurrency=200 \
  --iterations=400 \
  --create-schema=test \
  --host="sugimysql.publicsubnet01.testuser001.oraclevcn.com" \
  --user="admin" \
  --password="your password"

Alarm の詳細画面を見ると、設定した閾値を超えていることがわかります

自動的にメールが送られてきます。メール文面は、Notifications で送付しているため、メタデータが多く含まれています。

メール文面をキレイにしたい場合は、Notifications ではなく、Oracle Functions と Email Delivery を使うことで出来るはずです。

死活監視

厳密な意味とは異なりますが、MySQL Database Service の死活監視設定を行います。死活監視の部分は、「CPU 使用率のメトリックが無い状態」を検知してアラートを行います。「CPU使用率のメトリックが無い状態」は、MySQL インスタンス側で、通常では考えにくい何らかの障害が発生していると考えられます。

CPU 使用率と同様に、CPU Utilization からアラーム設定をします。

各種パラメータの設定をします。大事なポイントは、OPERATOR を absent を指定するところです。これは、データが存在しない状態を判断するための指定です。

動作確認 : 死活監視

死活監視の動作確認をします。インスタンスの障害を疑似的に起こすために、停止をします。

停止方法が聞かれますが、Fast のまま Stop します (検証環境で、何も負荷が無い状態だったら、Immidiate の方が良いかも)

インスタンスを停止することで、Metric 上では、データが無くなります。この状態だと、MySQL がなんらかの問題が発生していると想定できます。

インスタンス停止してから数分後、次のメールが届きます。無事に死活監視が出来ていますね。

記事の目的

初学者の為、間違っているところはご指摘頂けると幸いです。
備忘録・アウトプット目的で投稿です。

Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

初学者の私は、こちらのエラーに何度も苦しみました。

このエラー以外にもCan't connect to local MySQL server through socket '/tmp/mysql.sock'(38)だとか、ERROR! The server quit without updating PID fileというエラーにも悩まされました。記事をみてみると、対処法として①PIDファイルの作成②権限の変更③再インストール対応が挙げられていましたが、色んな記事を試してもダメでした。（初学者なので、記事の理解・原因の理解ができていないからだと思います。）
同じように悩まれている初学者の方が私の解決策が参考となればと思い、残したいと思います。
（原因の理解はまだできていないので、解説抜きです。）
なので、試す場合は自己責任でお願いします。

対処法

私の場合、mysql8.0をインストールしており、それで不都合が出るようでした。
なのでアンインストール対応します。

brew uninstall mysql

こちらのコマンドを打ちます。

sudo rm -rf /usr/local/Cellar/mysql*
sudo rm -rf /usr/local/bin/mysql*
sudo rm -rf /usr/local/var/mysql*
sudo rm -rf /usr/local/etc/my.cnf
sudo rm -rf /usr/local/share/mysql*
sudo rm -rf /usr/local/opt/mysql*
sudo rm -rf /etc/my.cnf

次にmysql@5.7をインストールして下さい。

brew install mysql@5.7

その後、パスを通すので、以下のようにexportかけます。

export PATH="/usr/local/opt/apr-util/bin:$PATH" >> ~/.bash_profile
export PATH="/usr/local/opt/mysql@5.7/bin:$PATH" >> ~/.bash_profile
source ~/.bash_profile

その後、mysql.server startを打ちます。

mysql.server start

Starting MySQL..SUCCESS!となるでしょうか？
パソコンを再起動すると、毎回このエラーが出ますが、私の場合は上記の対処法でエラーを解決することができます。

既存のコード体系を新しい体系に変換して集計する

以下のようなテーブルがある。

地方でグルーピングし、集計を行う。

select case pref_name
    when "徳島" then "四国"
    when "香川" then "四国"
    when "愛媛" then "四国"
    when "高知" then "四国"
    when "福岡" then "九州"
    when "佐賀" then "九州"
    when "長崎" then "九州"
    else "その他"
end as district,
sum(population)
from PopTbl
-- ここで別名であるdistrictを指定する書き方は標準SQL違反らしい
-- OrcleやSQLServerではエラーとなる。
group by district

人口階級ごとに都道府県を分類する。

select case 
    when population < 100 then "01"
    when population < 200 then "02"
    when population < 300 then "03"
    when population >= 300 then "04"
    else null
end as pop_class,
count(*) as cnt
from PopTbl
group by pop_class
order by pop_class;

異なる条件の集計を1つのSQLで行う

以下のテーブルがある。

性別ごとに集計を取る場合、通常は男性の集計を行うSQL、女性の集計を行うSQLと2回のSQLを実行する。

-- 男性の集計
select pref_name,
    sum(population)
from PopTbl
where sex = "1"
group by pref_name;

-- 女性の集計
select pref_name,
    sum(population)
from PopTbl
where sex = "1"
group by pref_name;

case式を使用することで、1回のSQLで結果を取得できる。

select pref_name,
     sum(case when sex = "1" then population else 0 end) as cnt_m,
     sum(case when sex = "2" then population else 0 end) as cnt_f
from PopTbl
group by pref_name;

条件を分岐させたUPDATE

以下のテーブルがある。

以下の条件で値を更新する。
1.給料が30万以上の場合、10%減給
2.給料が25万以上28万以下の場合、20%昇給

update Salaries
set salary =
    case
        when salary >= 300000 then salary * 0.9
        when salary >= 250000 and salary <= 280000 then salary * 1.2
        else salary
    end;

クロス表を作成する

以下のテーブルがある。

select course_name,
    case 
        when course_id in (select course_id from OpenCourses where month = "200706") then "◯" else "×"
    end  as "6月",
    case 
        when course_id in (select course_id from OpenCourses where month = "200707") then "◯" else "×"
    end  as "7月",
    case 
        when course_id in (select course_id from OpenCourses where month = "200708") then "◯" else "×"
    end  as "8月"
from CourseMaster;

はじめに

Oracle Cloud Infrastructure(以下OCI) の Tokyo Region で、MySQL マネージドサービスの MySQL Database Service が最近使えるようになりました。Oracle の MySQL チームによって、開発・管理・サポートされている MySQL のマネージドサービスです。自動構成・自動バックアップ・自動メンテナンスなど、Oracle 側でマネージドされており、作成や保守作業などを一部自動化出来ます。

OCI の Document に、MySQL Database Service の Getting Start が公開されています。今回の記事は、この Getting Start に沿った各手順を丁寧に紹介していきます。

https://docs.cloud.oracle.com/en-us/iaas/mysql-database/doc/getting-started.html

やること

• MySQL Database Service を作成
• CentOS7 を MySQL のアクセス元として構築
• MySQL Shell と、MySQL Client から MySQL Database Service に接続

事前準備 : Network 作成

MySQL Database Service は、仮想ネットワーク内の Subnet に構成するため、ファイアウォールの Security List に、3306 のポートを開けておきます。

MySQL Database Service を作成

MySQL のメニューから、DB Systems を選択します。

Create MySQL DB System を選択します。

各種パラメータを指定します。いくつかのパラメータを紹介します。

• Shape : CPU や メモリなどのリソースを選択します。2020.9月時点では、次の4個から選択可能です。環境によっては、Service Limit の上限に引っかかる場合があります。こちらのDocumentに、上限緩和の申請方法が書いています。
  • VM.Standard.E2.1 : 1 OCPU (2 vCPU), 8 GBメモリ
  • VM.Standard.E2.2 : 2 OCPU (4 vCPU), 16 GBメモリ
  • VM.Standard.E2.4 : 4 OCPU (8 vCPU), 32 GBメモリ
  • VM.Standard.E2.8 : 8 OCPU (16 vCPU), 64 GBメモリ
• Configuration Options : MySQL DB システムを操作する変数のまとまりを選択します。Configuration を Custom することで、Connection の上限などのパラメータを指定可能です。

各種パラメータを指定します。ユーザー名、パスワード、構成するネットワークなどを指定します。

自動バックアップの有無や、保存期間を指定して、Create を押します。

Creating となります。自分の環境では、約13分ほどで Active に切り替わりました。

Active となっています。各種詳細な情報が見えます。

Endpoint の欄です。VCN 内で使える名前解決や、それに紐づく IP アドレス、ポートがが確認可能です。

Backup の欄です。自動バックアップの結果や、手動バックアップの実行、バックアップから新たな MySQL へリストアが出来ます。

接続確認 MySQL Shell

MySQL Database Service の作成が出来たので、早速、同一 VCN の CentOS 7 からアクセス確認をしてみましょう。まず、CentOS 7 に、MySQL Shell をインストールしていきますが、そのまえに、現在の CentOS 7 が参照しているリポジトリの一覧を確認しておきます。

[opc@test001 ~]$ sudo yum repolist
Loaded plugins: langpacks, ulninfo
repo id                                repo name                                                                               status
ol7_UEKR5/x86_64                       Latest Unbreakable Enterprise Kernel Release 5 for Oracle Linux 7Server (x86_64)          280
ol7_addons/x86_64                      Oracle Linux 7Server Add ons (x86_64)                                                     467
ol7_developer/x86_64                   Oracle Linux 7Server Development Packages (x86_64)                                       1505
ol7_developer_EPEL/x86_64              Oracle Linux 7Server EPEL Packages for Development (x86_64)                             33110
ol7_ksplice                            Ksplice for Oracle Linux 7Server (x86_64)                                                8574
ol7_latest/x86_64                      Oracle Linux 7Server Latest (x86_64)                                                    19428
ol7_oci_included/x86_64                Oracle Software for OCI users on Oracle Linux 7Server (x86_64)                            533
ol7_optional_latest/x86_64             Oracle Linux 7Server Optional Latest (x86_64)                                           14174
ol7_software_collections/x86_64        Software Collection Library release 3.0 packages for Oracle Linux 7 (x86_64)            15333
repolist: 93404
[opc@test001 ~]$

リポジトルを構成しているファイルも確認しておきます。

[opc@test001 ~]$ ls -la /etc/yum.repos.d/
total 52
drwxr-xr-x.  2 root root 4096 Aug 25 01:52 .
drwxr-xr-x. 95 root root 8192 Sep 26 01:38 ..
-rw-r--r--.  1 root root  488 Jun 11 21:19 ksplice-ol7.repo
-rw-r--r--.  1 root root  205 Sep 25 19:43 ksplice-uptrack.repo
-rw-r--r--.  1 root root  252 Jun 11 21:22 oci-included-ol7.repo
-rw-r--r--.  1 root root  252 Jun 11 21:19 oracle-epel-ol7.repo
-rw-r--r--.  1 root root 4082 Aug 25 01:47 oracle-linux-ol7.repo
-rw-r--r--.  1 root root  276 Jun 11 21:25 oracle-softwarecollection-ol7.repo
-rw-r--r--.  1 root root 1039 Dec 13  2019 oraclelinux-developer-ol7.repo
-rw-r--r--.  1 root root 2587 Jul  8 21:25 uek-ol7.repo
-rw-r--r--.  1 root root  226 Jul  8 21:25 virt-ol7.repo
[opc@test001 ~]$

一般的に公開している MySQL の Repository を参照するために、RPM ファイルをインストールします。

sudo yum install https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm

3個のRepositoryが追加されています

[opc@test001 ~]$ sudo yum repolist
Loaded plugins: langpacks, ulninfo
repo id                                 repo name                                                                              status
mysql-connectors-community/x86_64       MySQL Connectors Community                                                               165
mysql-tools-community/x86_64            MySQL Tools Community                                                                    115
mysql80-community/x86_64                MySQL 8.0 Community Server                                                               193
ol7_UEKR5/x86_64                        Latest Unbreakable Enterprise Kernel Release 5 for Oracle Linux 7Server (x86_64)         280
ol7_addons/x86_64                       Oracle Linux 7Server Add ons (x86_64)                                                    467
ol7_developer/x86_64                    Oracle Linux 7Server Development Packages (x86_64)                                      1505
ol7_developer_EPEL/x86_64               Oracle Linux 7Server EPEL Packages for Development (x86_64)                            33110
ol7_ksplice                             Ksplice for Oracle Linux 7Server (x86_64)                                               8574
ol7_latest/x86_64                       Oracle Linux 7Server Latest (x86_64)                                                   19428
ol7_oci_included/x86_64                 Oracle Software for OCI users on Oracle Linux 7Server (x86_64)                           533
ol7_optional_latest/x86_64              Oracle Linux 7Server Optional Latest (x86_64)                                          14174
ol7_software_collections/x86_64         Software Collection Library release 3.0 packages for Oracle Linux 7 (x86_64)           15333
repolist: 93877
[opc@test001 ~]$

実際のファイルをみると、mysql-community-source.repo と mysql-community.repo が追加されています。

[opc@test001 ~]$ ls -la /etc/yum.repos.d/
total 60
drwxr-xr-x.  2 root root 4096 Sep 26 01:40 .
drwxr-xr-x. 94 root root 8192 Sep 26 02:50 ..
-rw-r--r--.  1 root root  488 Jun 11 21:19 ksplice-ol7.repo
-rw-r--r--.  1 root root  205 Sep 25 19:43 ksplice-uptrack.repo
-rw-r--r--.  1 root root 2108 Apr 24  2019 mysql-community-source.repo
-rw-r--r--.  1 root root 2076 Apr 24  2019 mysql-community.repo
-rw-r--r--.  1 root root  252 Jun 11 21:22 oci-included-ol7.repo
-rw-r--r--.  1 root root  252 Jun 11 21:19 oracle-epel-ol7.repo
-rw-r--r--.  1 root root 4082 Aug 25 01:47 oracle-linux-ol7.repo
-rw-r--r--.  1 root root  276 Jun 11 21:25 oracle-softwarecollection-ol7.repo
-rw-r--r--.  1 root root 1039 Dec 13  2019 oraclelinux-developer-ol7.repo
-rw-r--r--.  1 root root 2587 Jul  8 21:25 uek-ol7.repo
-rw-r--r--.  1 root root  226 Jul  8 21:25 virt-ol7.repo
[opc@test001 ~]$

リポジトリが正常に構成できたので、MySQL Shell をインストールします

sudo yum install mysql-shell

依存関係は特にありません

=====================================================================================================================================
 Package                       Arch                     Version                        Repository                               Size
=====================================================================================================================================
Installing:
 mysql-shell                   x86_64                   8.0.21-1.el7                   mysql-tools-community                    31 M

Transaction Summary
=====================================================================================================================================

インストールしたので、mysqlsh コマンドが実行できるようになっています

[opc@test001 ~]$ which mysqlsh
/usr/bin/mysqlsh

MySQL Database Service へ接続していきましょう。接続に使う書式です。

mysqlsh Username@IPAddressOfMySQLDBSystemEndpoint 

Endpoint を見ましょう。VCN 内部の名前解決が sugimysql で出来ることが分かります。

接続します。MySQL Database Service を作成した時に指定したユーザー名と、Endpoint の名前を入れます。(FQDNを入れても大丈夫です。)

mysqlsh admin@sugimysql 

パスワードを入力します。MySQL Database Service を作成した時に指定しました。

[opc@test001 ~]$ mysqlsh admin@sugimysql
Please provide the password for 'admin@sugimysql': ***********************

接続完了です

[opc@test001 ~]$ mysqlsh admin@sugimysql
Please provide the password for 'admin@sugimysql': ***********************
MySQL Shell 8.0.21

Copyright (c) 2016, 2020, 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 '\?' for help; '\quit' to exit.
Creating a session to 'admin@sugimysql'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 9
Server version: 8.0.21-u1-cloud MySQL Enterprise - Cloud
No default schema selected; type \use <schema> to set one.
 MySQL  sugimysql:3306 ssl  JS >

動作確認のために、status コマンドを表示してみます。8.0.21-u1-cloud MySQL Enterprise - Cloud と表示されており、OCI で動いてそうだ、ということがわかります。

 MySQL  sugimysql:3306 ssl  JS > \status
MySQL Shell version 8.0.21

Connection Id:                9
Current schema:
Current user:                 admin@test001.publicsubnet01.testuser001.oraclevcn.com
SSL:                          Cipher in use: ECDHE-RSA-AES128-GCM-SHA256 TLSv1.2
Using delimiter:              ;
Server version:               8.0.21-u1-cloud MySQL Enterprise - Cloud
Protocol version:             Classic 10
Client library:               8.0.21
Connection:                   sugimysql via TCP/IP
TCP port:                     3306
Server characterset:          utf8mb4
Schema characterset:          utf8mb4
Client characterset:          utf8mb4
Conn. characterset:           utf8mb4
Result characterset:          utf8mb4
Compression:                  Disabled
Uptime:                       4 hours 51 min 32.0000 sec

Threads: 3  Questions: 11079  Slow queries: 0  Opens: 164  Flush tables: 3  Open tables: 85  Queries per second avg: 0.633
 MySQL  sugimysql:3306 ssl  JS >

接続確認 MySQL Client

では次に、なじみ深い MySQL Client から接続確認を行います。まず、インストールします。

sudo yum install mysql

依存関係です。

=====================================================================================================================================
 Package                                    Arch                  Version                     Repository                        Size
=====================================================================================================================================
Installing:
 mysql-community-client                     x86_64                8.0.21-1.el7                mysql80-community                 48 M
 mysql-community-libs                       x86_64                8.0.21-1.el7                mysql80-community                4.5 M
     replacing  mariadb-libs.x86_64 1:5.5.65-1.el7
 mysql-community-libs-compat                x86_64                8.0.21-1.el7                mysql80-community                1.2 M
     replacing  mariadb-libs.x86_64 1:5.5.65-1.el7
Installing for dependencies:
 mysql-community-common                     x86_64                8.0.21-1.el7                mysql80-community                617 k

Transaction Summary
=====================================================================================================================================

接続します

mysql --host sugimysql -u admin -p 

実行例

[opc@test001 ~]$ mysql --host sugimysql -u admin -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.21-u1-cloud MySQL Enterprise - Cloud

Copyright (c) 2000, 2020, 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>

show databases で実行確認です。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql>

これで、正常に接続できることがわかりました。

参考URL

https://docs.cloud.oracle.com/en-us/iaas/mysql-database/doc/getting-started.html

MySQLの起動（コマンドプロンプト）の際にエラーが出たのでその内容と解決方法を備忘録として投稿します。

自分のパソコンに先日MySQLをインストールしてDB環境を用意しました。
MySQLの起動をせずにいきなりログインしようとしてしまい（下記 コマンド実行）

mysql --user=root --password

以下のエラーが発生しました。

error 2003 (hy000) can't connect to mysql server on 'localhost' (10061)

解決方法を調べたところ、先にMySQLの起動が必要との事で下記コマンドを実行

brew services start mysql@5.7

するとまた、下記エラー発生

'brew'は、内部コマンドまたは外部コマンド、
操作可能なプログラムまたはバッチ ファイルとして認識されていません。

原因を調べると「brew・・・」はmac用のコマンドでした
なのでwin用の下記コマンドを実行

net start mysql57

続いて再度ログインを試みますと・・・（下記実行）

mysql --user=root --password

無事、ログインできました！！！
まだまだ学ぶことがいっぱいですがコツコツ行きたいと思います。

Qiita活用の練習もかねて、投稿しました。