progateもある程度進めた今日この頃。
自分のPCでSQLを叩いてみたい。そう思ったのだが、自宅にあった参考書にはなぜかWindowsでのSQL開発環境構築方法しか書いていなかった。

なのでGoogle先生の力と先人たちの力を借りて自分のMACにSQLをインストールしてみた。

① MySQLのインストール

まずmacにhomebrewがインストールされていることが大前提となる。
ここではあまり深く言及しないがHomebrewとはMacOS専用のソフトウェア管理ツールです。
（知見が溜まってきたらこの辺りも記事にしたいな。）
他のわかりやすい記事をたくさん書かれているので今はそちらを参考にしてください。
Macのターミナルにて以下のコマンドを入力します。10分ほどでインストールが完了します。

$brew install mysql

② 練習で使用するサンプルデータをダウンロードする

こちら データベースの元となるデータをダウンロードします。
このデータのアレを取得したり加工したりするデータの元となる物です。

③ ターミナルにてMySQLを起動し、以下のコマンドを実行

... SUCCESS!が出れば接続成功です。

$mysql.server srart
~
... SUCCESS!
$mysql -uroot

④ 使用するDBのソースファイルをインポートする

mysql > SOURCE ファイルのディレクトリ

⑤ インポートが完了したら、使用するデータベースを指定する

mysql > USE hogehoge;

⑥ 適当にクエリを叩いてみる!

⑦ SQLの終了

SQLの実行モードを終了するには以下のコマンドを実行します。

mysql > exit
$mysql.server stop //SQLへの接続終了

こんな感じでいかがだったでしょうか？
次はもう少し深掘りした記事を書きたいと思います。（余裕があれば）

年末年始大学授業も忙しくて、セキュリティノートを書くことをずいぶん忘れてしまった。

データベースをめぐるセキュリティホール、脆弱性などセキュリティ問題は、ほとんどSQLインジェクションでしょう。

mysql

default port：3306
攻撃手法：
1,弱いパスワード、デフォルト設定そのまま使う:アカウント名：'localhost'@'@''　パスワードは任意
2,DOS攻撃（shellおよびHigh権限が必要）
3,CVE-2012-2122など

PostgreSQL

default port：5432
攻撃手法：
1,弱いパスワード設定： postgres postgres
2,buffer-overrun（バッファオーバーラン）：CVE-2019-10164
3,CVE-2014-2669
4,CVE-2018-1058など

Oracle

default port：1521
1158
8080
210
それぞれは、データベースポート、Oracle-EMCTL、Oracle-XDB、Oracle-XDB FTPサービス。

問題点：パラメータの設置ミス：
1,UTL_FILE_DIRは*にする（この場合、攻撃者は既知のorapworclファイルを入れ替え、低権限ユーザーもSYSパスワードを再設置できる）
2,AVASYSPRIV
EXECUTE ANY PROCEDURE
DBMS_IJOBそれぞれは低権限ユーザーに与えてしまう。

対応：
OSファイルへのアクセス用変数（もしくはパラメータ）、書き込みなど、禁止or削除
マニュアルに従って設置する（人間はセキュリティの最大の弱点）
ペネトレーションテスト、ツールを使い、定期診断を行う。

参考文献：

https://docs.oracle.com/cd/E16662_01/doc/timesten.1121/b56057/accesscntl.htm

概要

Active RecordをRailsではなく、Rubyから利用する機会があったので、手順についてまとめておく。

$ ruby -v

ruby 2.6.3p62 (2019-04-16 revision 67580)

準備

今回はMySQLを利用することを前提とする。

MySQL2とActiveRecordの2種類のgemをインストールする。

gem install mysql2

gem install activerecord

データベースの準備

Mysqlで事前に、sampleデータベースを作成し、usesテーブルを作成しておく。
データベースや、テーブルの作成方法については、ここでは説明を省く。

利用

UserクラスでActiveRecordが利用できるようにする。

require "active_record"

ActiveRecord::Base.establish_connection(
    :adapter    =>"mysql2", 
    :host   =>"<ホスト名>",
    :database   =>"<db名>",
    :username   =>"<user名>",
    :password   =>"<パスワード>",
)

class User < ActiveRecord::Base
    def self.create_User
        User.create(name: "taro",age: 18) <= nameカラムとageカラムがある場合
    end
end

以上のような形で、usersテーブルに新たなレコードを追加することができる。当然createだけでなく、CRUD処理の全てを行うことができる。

まとめ

RailsからしかActiveRecordを利用したことがなかったが、Rubyからも簡単に利用することができた。

はじめに

今回はrailsでのデータの新規作成、更新、削除の方法をまとめていきます。

データの新規保存　

newメソッド+saveメソッドかcreateメソッドでレコードを新規作成します。

newメソッド+saveメソッドの例

#モデル名.new(カラム名: データ)＋モデル名.saveのセットで保存されます。
#saveメソッド忘れに注意ください。これがないと保存されません。

#以下が例です。
post = Post.new(name: "記事１", title: "hogehoge") 
post.save

createメソッドの例

#モデル名.create(カラム名: データ)で保存されます。
#createメソッドはnewとsaveメソッドを一度に実行できます。

#以下が例です。
Post.create(name: "記事１", title: "hogehoge") 

データの更新

saveメソッドかupdateメソッドでレコードの更新を行います。
自身はupdateメソッドをよく使います。

updateメソッドの例

#モデル名.update(カラム名: データ)で保存されます。
#update前にfindでupdateをするデータを取得します。

#以下が例です。
post= Post.find(1)
post.update(name: "記事2", title: "higehige") 

saveメソッドの例

#モデルの各項目に値を入れていき、最後にsvaeメソッドで更新をかけます。

#以下が例です。
post= Post.find(1)
post.name = "記事2"
post.title = "higehige"
post.save

データの削除

destroyメソッドかdeleteメソッドを使いデータを削除します。

destroyメソッドの例（モデルクラスでdependent: :destroyを指定して関連付けたデータも自動的に削除されます）

#モデル名.find(削除したいid).destroy(カラム名: データ)でデータを削除します。

#以下が例です
Post.find(1).destroy
#ちなみにレコード全消去したい場合は以下になります。
Post.destroy_all

deleteメソッドの例（関連するデータは削除されません）

#モデル名.find(削除したいid).delete(カラム名: データ)でデータを削除します。

#以下が例です
Post.find(1).delete
#ちなみにレコード全消去したい場合は以下になります。
Post.delete_all

おわりに

基本的なrailsの新規作成、更新、削除について紹介いたしました。
データ新規登録時のsaveメソッドを忘れてはまらないように注意ください！！

はじめに

MySQLテーブルに適切なインデックスを作成することにより、SELECTクエリのパフォーマンスを大幅に向上させることができます。 しかし、インデックスをテーブルに追加するコストが高いですし、テーブルのサイズによっては完了するまでにかなりの時間がかかる場合があります。インデックスを追加している間にインデックス自体の作成にリソースが使われているためDBのパフォーマンスが低下する可能性があります。 この投稿では、通常のワークロードが影響を受けないようにMySQLインデックス作成プロセスを最適化するアプローチについて説明します。記事のソースはこちらです。

インデックスのローリング作成

MySQLマスター/スレーブのセットがある場合、一度に一つのノードでローリング方式でインデックスを作成できます。 マスターのパフォーマンスに影響を与えないように、スレーブノードでのみインデックスを作成する必要があります。スレーブ上でインデックスの作成が完了すると、現在のマスターを降格し、新しいマスターとしてスレーブを昇格させます。 この時点で、インデックスの構築は元のマスターノード（現在はスレーブ）で続行されます。 フェールオーバーのためにデータベースへの接続が失われる間、短い期間（数十秒）がありますが、これはアプリケーションレベルでリトライを行うことで乗り越えられます。

ローリング作成の利点

インデックスのローリング作成のパフォーマンスの利点を理解するために、小さな実験を行いました。

次のテストでは、Sysbenchを使用して作成されたMySQLデータセットを使用しました。各データベースには、5000万行の三つのテーブルがありました。 バランスの取れたワークロード（50％の読み取りと50％の書き込み）を10分間実行する30のクライアントでMySQLマスターに負荷をかけて、同時に、次のシナリオでいずれかのテーブルに単純なセカンダリインデックスを構築しました：

• マスターでインデックスを作成する
• スレーブでインデックスを作成する

MySQLコンフィグ

MySQLインスタンスタイプ	EC2インスタンス m4.large with 8GB RAM
デプロイメントタイプ	2 Node Master-Slave Set 準同期レプリケーション
MySQL Version	5.7.25

試験結果

シナリオ	クエリ/秒	95パーセンタイルレイテンシー
マスターでのインデックス作成	453.63	670 ms
ローリング作成	790.03	390 ms

結論

マスターでのインデックス作成時のスループットがローリング作成時のスループットの60%しか出せませんでした。 クエリの95パーセンタイルレイテンシも、マスターでインデックス作成が行われたときに1.8倍高くなりました。

ローリング作成の自動化

ScaleGridは、MySQLインデックスのローリング作成の自動化に役に立ちます。UIも結構シンプルです。

上記のUIでは、データベース名とテーブル名を選択し、「インデックスの追加」をテーブル変更操作として選択できます。 次に、カラム名とインデックス名を指定すると、テーブル変更コマンドが生成されて表示されます。 [作成]をクリックすると、インデックスの作成はローリング方式で一度に1つのノードで行われます。

さらに、ScaleGridは、新しいカラムをローリング方式でテーブルに追加するなど、他の単純なテーブル変更操作もできます。

概要

dockerを触り始めてMySQLのコンテナを立ててみたものの、Macのターミナルから接続できずに結構ハマった話。似たような事象に陥ってる人が、もしこれで解消できる人がいればと思い記事にしました。

結論

コンテナの3306ポートをEXPOSEしていなかった。docker runで-Pオプションを付けて実行すると上手く接続できた。

before

$ docker run -d --name test-db -e MYSQL_ROOT_PASSWORD=password mysql/mysql-server

after

$ docker run -d --name test-db -e MYSQL_ROOT_PASSWORD=password -P --expose=3306 mysql/mysql-server

経緯とか

症状

作成したdockerコンテナに対して、Macのターミナルからmysqlコマンドで入ろうとしても、以下の通りエラー

$ mysql -h 127.0.0.1 -u user -p
Enter password: 
ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (61)

エラー内容的にそもそもIPアドレスがわからないと言われている。
ネットで上手く接続できている人たちの記事を見ると、docker ps時のPORTSに0.0.0.0があることに気がつく。というわけで、以下の記事を参考にEXPOSEしてみた。

MySQLに外部接続できない時のチェックポイント

EXPOSEする前

$ docker ps
CONTAINER ID        IMAGE                COMMAND                  CREATED             STATUS                       PORTS                                               NAMES
35b706562745        mysql/mysql-server   "/entrypoint.sh mysq…"   About an hour ago   Up About an hour (healthy)   3306/tcp, 33060/tcp                                 test-db

EXPOSEした後

$ docker ps
CONTAINER ID        IMAGE                COMMAND                  CREATED             STATUS                       PORTS                                               NAMES
42a9d040091a        mysql/mysql-server   "/entrypoint.sh mysq…"   14 minutes ago      Up 14 minutes (healthy)      0.0.0.0:3306->3306/tcp, 0.0.0.0:32772->33060/tcp    test-db

EXPOSE後のPORTS欄を見ると0.0.0.0:3306->3306/tcpとなっており、ローカルホストから接続できていることがわかる。

コピペで適当に作業していると何かあったときにハマりますね...

症状

アプリを作成している時、いつもの如くSequel Pro確認したらdbがない

原因

いつもはrails newする時に

rails new -d mysql

としていたのだが

rails new

というやり方でディレクトリを作成してしまった。

処置

Gemfile

gem 'mysql'
#gem 'sqlite3'

上記のようにgemfileにmysqlを追記。
sqlite3は念のためコメントアウトして置いて

bundle install

そしてdatabase.ymlを下記のように編集

database.yml

default: &default
  adapter: sqlite3
  pool: 5
  timeout: 5000

development:
  adapter: mysql2
  encoding: utf8
  database: [データベース名]
  pool: 5
  username: root
  password:
  host: localhost

その後

rake db:create

でデータベースを作成。これでなんとかデータベースはmysqlになった。

まとめ

色々中途半端な対策かもしれません。今回はまだデータベースにデータを入れてない状態だったのでこれで済んだ。あとアプリを仕上げて本番環境とかになったら後遺症見たいのが出てくるのかもしれないがそれはその時対策します。
備忘録としてですが、
ご指摘あればぜひよろしくお願いします。