What's New Qiita

20200714のMySQLに関する記事は4件です。

  • 投稿日：2020-07-14T23:24:02+09:00

【MySQL】ステータス確認のためのコマンド集

MySQLのステータス確認のためのコマンド集です。
時々使う & すぐ使えるようにしておきたいものをまとめました。
※一部ステータスでないものもありますがご了承を。

環境

  • OS：Windows10
  • MySQL：5.7

コマンド一覧

1. テーブルステータスの確認

(1) SHOW TABLE STATUS

> SHOW TABLE STATUS FROM データベース名;

ソートや特定のカラムのみの抽出といったカスタマイズは不可。

(2) INFORMATION_SCHEMA.TABLES

> SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'データベース名';

通常のSQLと同様、特定のカラムだけの切り出しやソートも可能。
データベース「hoge」のすべてのテーブルについて、テーブル名、エンジン、作成時刻を作成時刻の新しいもの順に表示する場合は以下の通り。

sample
> SELECT TABLE_NAME, ENGINE, CREATE_TIME FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'hoge' ORDER BY CREATE_TIME DESC;

2. DDLの取得

(1) SHOW CREATE TABLE

> SHOW CREATE TABLE テーブル名;

特定の1テーブルのDDLを取得する場合に使用。

(2) mysqldump

> mysqldump -u ユーザー名 -h ホスト名 -P ポート番号 -d データベース名 -p

指定したデータベース内の全てのDDL（VIEWも含む）を取得する際に使用。
上記実行後にパスワードを聞かれるので入力。

DDLをファイル出力する場合は以下のとおり。

> mysqldump -u ユーザー名 -h ホスト名 -P ポート番号 -d データベース名 -p > ファイルパス

3. トランザクション

(1) SHOW PROCESSLIST

> SHOW PROCESSLIST;

実行中のスレッドの確認に使用。

(2) KILL

> KILL 対象のID

トランザクションを強制終了させる。
SHOW PROCESSLIST; で表示されたId列の値を指定。

(3) tx_isolation

> SELECT @@GLOBAL.tx_isolation, @@tx_isolation;

トランザクション分離レベル確認用。
前者がグローバル、後者がセッション。
MySQLのデフォルトは両者とも、REPEATABLE READ。

それぞれ以下のコマンドで変更可能。

> SET GLOBAL tx_isolation='トランザクション分離レベル';
> SET SESSION tx_isolation='トランザクション分離レベル';

4. 文字コードの確認

(1) SHOW VARIABLES

> SHOW VARIABLES LIKE 'char%';

SHOW VARIABLES; でMySQLのシステム変数の値が表示できる。
LIKEで条件を絞る。

5. SQLのリソース使用状況の確認

(1) SHOW PROFILES

> SHOW PROFILES;

Query列で実行されたSQLを、Duration列で経過時間を確認できる。
事前準備として、profiling セッション変数を1もしくはONにしておく。

事前準備
> SET profiling = 'ON';

6. クエリログの確認

(1) 一般クエリログ

> SELECT * FROM mysql.general_log;

実行されたクエリの履歴全てを確認できる。
general_logテーブルにログを出力するための事前準備は以下の通り。

事前準備
> SET global general_log = 'ON';
> SET global log_output = 'TABLE';

(2) スロークエリログ

> SELECT * FROM mysql.slow_log;

実行に時間のかかったクエリを確認できる。
slow_query_logテーブルにログを出力するための事前準備は以下の通り（log_outputの設定は(1)と共通）。

> SET global slow_query_log = 'ON';
> SET global log_output = 'TABLE';

参考

1. テーブルステータスの確認

2. DDLの取得

3. トランザクション

4. 文字コードの確認

5. SQLのリソース使用状況の確認

6. クエリログの確認

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2020-07-14T23:24:02+09:00

【MySQL】ステータス確認・変更のためのコマンド集

MySQLのステータス確認のためのコマンド集です。
時々使う & すぐ使えるようにしておきたいものをまとめました。
※一部ステータスでないものもありますがご了承を。

環境

  • OS：Windows10
  • MySQL：5.7

コマンド一覧

1. テーブルステータスの確認

(1) SHOW TABLE STATUS

> SHOW TABLE STATUS FROM データベース名;

ソートや特定のカラムのみの抽出といったカスタマイズは不可。

(2) INFORMATION_SCHEMA.TABLES

> SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'データベース名';

通常のSQLと同様、特定のカラムだけの切り出しやソートも可能。
データベース「hoge」のすべてのテーブルについて、テーブル名、エンジン、作成時刻を作成時刻の新しいもの順に表示する場合は以下の通り。

sample
> SELECT TABLE_NAME, ENGINE, CREATE_TIME FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'hoge' ORDER BY CREATE_TIME DESC;

2. DDLの取得

(1) SHOW CREATE TABLE

> SHOW CREATE TABLE テーブル名;

特定の1テーブルのDDLを取得する場合に使用。

(2) mysqldump

> mysqldump -u ユーザー名 -h ホスト名 -P ポート番号 -d データベース名 -p

指定したデータベース内の全てのDDL（VIEWも含む）を取得する際に使用。
上記実行後にパスワードを聞かれるので入力。

DDLをファイル出力する場合は以下のとおり。

> mysqldump -u ユーザー名 -h ホスト名 -P ポート番号 -d データベース名 -p > ファイルパス

3. トランザクション

(1) SHOW PROCESSLIST

> SHOW PROCESSLIST;

実行中のスレッドの確認に使用。

(2) KILL

> KILL 対象のID

トランザクションを強制終了させる。
SHOW PROCESSLIST; で表示されたId列の値を指定。

(3) tx_isolation

> SELECT @@GLOBAL.tx_isolation, @@tx_isolation;

トランザクション分離レベル確認用。
前者がグローバル、後者がセッション。
MySQLのデフォルトは両者とも、REPEATABLE READ。

それぞれ以下のコマンドで変更可能。

> SET GLOBAL tx_isolation='トランザクション分離レベル';
> SET SESSION tx_isolation='トランザクション分離レベル';

4. 文字コードの確認

(1) SHOW VARIABLES

> SHOW VARIABLES LIKE 'char%';

SHOW VARIABLES; でMySQLのシステム変数の値が表示できる。
LIKEで条件を絞る。

5. SQLのリソース使用状況の確認

(1) SHOW PROFILES

> SHOW PROFILES;

Query列で実行されたSQLを、Duration列で経過時間を確認できる。
事前準備として、profiling セッション変数を1もしくはONにしておく。

事前準備
> SET profiling = 'ON';

6. クエリログの確認

(1) 一般クエリログ

> SELECT * FROM mysql.general_log;

実行されたクエリの履歴全てを確認できる。
general_logテーブルにログを出力するための事前準備は以下の通り。

事前準備
> SET global general_log = 'ON';
> SET global log_output = 'TABLE';

(2) スロークエリログ

> SELECT * FROM mysql.slow_log;

実行に時間のかかったクエリを確認できる。
slow_query_logテーブルにログを出力するための事前準備は以下の通り（log_outputの設定は(1)と共通）。

> SET global slow_query_log = 'ON';
> SET global log_output = 'TABLE';

参考

1. テーブルステータスの確認

2. DDLの取得

3. トランザクション

4. 文字コードの確認

5. SQLのリソース使用状況の確認

6. クエリログの確認

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2020-07-14T21:35:55+09:00

MySQL: Heroku ClearDB と AWS RDS どちらにすべきか

Heroku でアプリをデプロイする場合、デフォルトのデータベースは PostgreSQL ですが、MySQL を使うこともできます。

MySQL の一般的な導入方法は次の2つです:

  1. Heroku アドオンの ClearDB を利用する （導入手順はこちら）
  2. Amazon RDS MySQL を利用する（参考記事はこちら）

Heroku で MySQL を使う場合、これら2つのうち、どちらを採用すべきか検討してみました。

検討の前提

  • 新規開発のDBとして利用
  • 初めは少人数開発・低予算
  • 求められるストレージ容量はそこまで大きくない（はず）
  • 将来的に拡張する可能性有

結論

結論から言うと、今回は Amazon RDS を使うことにしました。

その理由は、ざっと次のとおりです。

  • 将来的にウェブサーバを Heroku で運用し続けるか未定
  • 容量が1GBを超えた場合に固定料金を49.99ドルも払いたくない
  • 設定は必要だが、それなりの作業量で導入可能

詳細は以下の「Heroku ClearDB と Amazon RDS の比較」をご参照ください。

Heroku ClearDB と Amazon RDS の比較

次の４点について比較検討します。

  • 導入コスト
  • ランニングコスト
  • セキュリティ
  • 拡張性

導入コスト

Heroku アプリに導入するためのコストを検討します。

ClearDB

Heroku のアドオンなので、コマンド1つで導入できます。

RDS

参考記事にある通り、Heroku から RDS に接続するための設定が必要です。設定の全体的な流れは、次の通りです。

  1. AWS: CA証明書のダウンロード
  2. RDS: REQIRE SSL の設定
  3. AWS: セキュリティーグループの調整
  4. Heroku: DATABASE_URL の設定

ランニングコスト

月々の利用料金について検討します。

ストレージ容量の調べ方

MySQLでのストレージ容量の調べ方はこちらの記事をご参照ください。

既存プロジェクトのストレージ容量は、新規プロジェクトの見積もり時に参考になります。

ClearDB

プランごとの固定料金が課金されます。

料金ページ

無料プランの Ignite だと容量が5MBしかないので、Punch (1GB, 月$9.99)ぐらいから利用することになりそうです。

Punch の次のプランは Drift (5GB, 月$49.99) なので、容量が1GBを超えた場合、利用料がいきなり40ドル/月も上がってしまいます。

RDS

DBサーバの稼働時間とストレージ容量、バックアップなどについて、利用した分だけ課金されます。従量課金なので「損」はありませんが、ClearDB の固定料金と比較すると少し分かりづらいです。

料金ページ

稼働時間単価は、DBサーバのスペックごとに異なります。

最安の「db.t3.micro」の場合、時間単価は「$0.017」なので（シングルAZ・バージニア北部）、月ごとの稼働料金は最低でも12.65ドルかかります。

※ バージニア北部を選んでいるのは、Heroku-RDS 間のレイテンシーを最小限にするため。

これに加えて、ストレージ料金「毎月 0.138USD/GB」やデータ転送などのコストも発生しますが、ざっくり見積もっても「月15-30ドル」では利用できるでしょう。

セキュリティ

データベースのセキュリティ面について検討します。

ClearDB

手元で確認したところ、発行される CLEARDB_DATABASE_URL の情報で、どのIPからでもデータベースにリモート接続できます。

またデフォルトではSSL接続していないので、必要な場合は自分で証明書の設定をしなくてはいけません（詳細はこちら）。

RDS

Heroku から RDS に接続する場合、その RDS が属する VPC のアクセスIP制限を一部開かないといけません。

Herokuの記事 では、すべてのIPを許可する 0.0.0.0/0 が例示されています。この設定だと、ClearDB のデフォルトと同様、どのIPからでもデータベースにリモート接続できます。

アクセスできるIPは制限した方が望ましいですが、そのためには Heroku のアドオン（Fixie など）でアプリ側のIPアドレスを固定しないといけなさそうです。この場合、リクエスト数に応じてアドオンの課金が発生します。

なのでコストを抑えたいなら、ClearDB と同じようにすべてのIPを許可するしかないです。（最低限、ユーザ名・パスワードは複雑なものにしましょう。）

なお、SSL接続については、Heroku から RDS に接続する場合は必須です。

拡張性

データベースやサービスの拡張性を検討します。

ClearDB

スペック・容量に応じたプランが用意されているので、サービスの拡大に伴いアップグレードしていけます。

ただし Heroku のアドオンなので、Heroku アプリと合わせて使っていくことになるでしょう。（Heroku 以外のサーバで利用できるかは不明です。）

したがって、AWSなどの他サービスへの移行は、DBのせいでフットワークが重たくなってしまう可能性があります。

RDS

こちらもサービスの拡大に伴い、スペックをアップグレードしていけます。

ClearDB と比較すると、Heroku 以外のサービスとの連携など、柔軟性はより高いでしょう。

まとめ

Heroku ClearDB Amazon RDS
導入コスト 1コマンドのみ　 設定が必要
ランニングコスト(月) 9.99-49.99ドル(固定) 15-30ドル(?)
セキュリティ IP制限なし IP制限可
拡張性 Heroku限定 Heroku以外も可

References

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2020-07-14T13:07:02+09:00

Wikipediaダンプからリダイレクトを抽出する

個人的な備忘録として残します。
手早くファイルを得られるよう、なるべく簡潔に書きたいと思います。

完成予想図

Wikipediaのリダイレクトを収集して以下のようなファイルを作ります。

redirects.json
{"src": "COVID-19", "dst": "新型コロナウイルス感染症_(2019年)"}
{"src": "COVID-2019", "dst": "新型コロナウイルス感染症_(2019年)"}
{"src": "Covid-19", "dst": "新型コロナウイルス感染症_(2019年)"}
{"src": "Covid-2019", "dst": "新型コロナウイルス感染症_(2019年)"}
{"src": "新型コロナウイルス感染症", "dst": "新型コロナウイルス感染症_(2019年)"}
{"src": "コビッド19", "dst": "新型コロナウイルス感染症_(2019年)"}
{"src": "COVID19", "dst": "新型コロナウイルス感染症_(2019年)"}
{"src": "2019新型コロナウイルス感染症", "dst": "新型コロナウイルス感染症_(2019年)"}

リダイレクトとは

以下参照
Wikipedia:リダイレクト

リダイレクト例

https://ja.wikipedia.org/wiki/COVID-19 にアクセスしようとすると、
自動的にhttps://ja.wikipedia.org/wiki/新型コロナウイルス感染症_(2019年) に飛ばされます。

実装等

0. 必要な色々

  • MySQL
  • Python3
    • mysqlclient
      pip install mysqlclient

1. Wikidumpのリストア

必要データのダウンロード

以下から必要なデータをダウンロードしてください。

https://dumps.wikimedia.org/jawiki/

  • jawiki-[dump取得日]-redirect.sql.gz
  • jawiki-[dump取得日]-page.sql.gz

解凍

$ gunzip jawiki-[dump取得日]-redirect.sql.gz
$ gunzip jawiki-[dump取得日]-page.sql.gz

MySQLデータベースへリストア

$ mysql -u [ユーザー名] -p [DB名] < jawiki-[dump取得日]-page.sql
$ mysql -u [ユーザー名] -p [DB名] < jawiki-[dump取得日]-redirect.sql

2. リダイレクト抽出

Pythonコード

データベースを叩いてリダイレクトを抽出しJSONで保存するコードです。

extract_redirects.py
import json
import MySQLdb

USERNAME = "[MySQLユーザー名]"
PASSWORD = "[パスワード]"
DB_NAME = "[DB名]"
OUTPUT = "./redirects.json"

def save_jsonl(file_path, data):
    json_dumps = lambda d:json.dumps(d, ensure_ascii=False)
    dumps = map(json_dumps, data)
    with open(file_path, "w") as f:
        f.write("\n".join(dumps))

if __name__ == '__main__':
    # データベースに接続
    conn = MySQLdb.connect(
        user=USERNAME,
        passwd=PASSWORD,
        host='localhost',
        db=DB_NAME
    )

    # Cursorを作成しクエリを実行
    cur = conn.cursor(MySQLdb.cursors.DictCursor)
    sql = "select page.page_title, redirect.rd_title from page, redirect where redirect.rd_from=page.page_id"
    cur.execute(sql)
    rows = cur.fetchall()

    # 実行結果を整理
    redirects = []
    for row in rows:
        row = {key:cell.decode() if type(cell) is bytes else cell for key, cell in row.items()}
        redirects.append({
            "src":row["page_title"],
            "dst":row["rd_title"]
        })

    # 保存
    save_jsonl(OUTPUT, redirects)

    cur.close()
    conn.close()

実行

python extract_redirects.py

以上です!

α. 軽い解説等

※事前知識:Wikipediaのページにはtitleの他にpage_idが個別に割り振られています。

jawiki-[dump取得日]-redirect.sql.gzでは、リダイレクト元のpage_idとリダイレクト先のtitleがレコードで紐づけられています。
jawiki-[dump取得日]-page.sql.gzでは、page_idとtitleがレコードで紐づけられています。

この2つのダンプを組み合わせることで、リダイレクト元titleとリダイレクト先titleを紐づけています。

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

日付別

タグ一覧