MySQL ShellでAWS上のMySQLを自在に操る！SSHトンネルとマルチスレッドダンプの徹底解説

Posted at 2024-05-25

AWS 上の MySQL に対して mysqlsh を試してみた

こんにちは、皆さん！今回は、AWS 上の MySQL に対して MySQL Shell（mysqlsh）を使用した結果を共有したいと思います。MySQL は踏み台サーバを経由しなければアクセスできないため、SSH トンネルを作成してアクセスします。

SSH トンネルの作成

MySQL へアクセスするために、まず SSH トンネルを作成します。

# 踏み台サーバの SSH のポート番号
$ SSH_PORT=22
# このポート番号にアクセスすると MySQL につながる
$ LOCAL_PORT=13306
$ DB_HOST=<AWS 上で設定されているホスト名>
# DB自体が待ち受けているポート番号
$ DB_PORT=3306
$ SSH_OS_USER=<踏み台サーバへのログインユーザ名>
$ SSH_HOST=<踏み台サーバのホスト情報 or IPアドレス>

# トンネル作成
$ ssh -p "${SSH_PORT}" -N -L "${LOCAL_PORT}:${DB_HOST}:${DB_PORT}" "${SSH_OS_USER}@${SSH_HOST}"

# もしバックグラウンドで実行したい場合は最後に & をつける
$ ssh -p "${SSH_PORT}" -N -L "${LOCAL_PORT}:${DB_HOST}:${DB_PORT}" "${SSH_OS_USER}@${SSH_HOST}" &

# ターミナルを閉じた後も実行されている状態にしたい場合は nohup で実行する
$ nohup ssh -p "${SSH_PORT}" -N -L "${LOCAL_PORT}:${DB_HOST}:${DB_PORT}" "${SSH_OS_USER}@${SSH_HOST}" > /tmp/nohup-ssh.out &

MySQL へのアクセス

SSH トンネルが作成されたら、MySQL へのアクセスは以下のコマンドで行えます。

$ DB_USER=<MySQL のユーザ名>
$ mysql --host=127.0.0.1 --port="${LOCAL_PORT}" -u "${DB_USER}" -p

MySQL Shell のインストール

MacOS に MySQL Shell をインストールするには、Homebrew を使用します。

$ brew install --cask mysql-shell

mysqlsh を利用した操作

ダンプの作成

MySQL Shell を利用してデータベースのダンプを作成します。

# JavaScript モードでログイン
$ mysqlsh --host=127.0.0.1 --port="${LOCAL_PORT}" -u "${DB_USER}" -p --js

# ダンプスクリプト
> const databases = ['ダンプ対象のDB名']; // 複数指定可能
> const output = '出力先のURL or Path';
> util.dumpSchemas(databases, output, {
  defaultCharacterSet: 'utf8',
  consistent: true,
  chunking: true,
  threads: 4,
});

ダンプのオプション

defaultCharacterSet: エクスポートするファイルの文字コード
consistent: バックアップファイルの整合性を保つ（true にするべきだが、DB の設定や権限によっては false にする必要あり）
chunking: バックアップを一定のチャンクにまとめるかどうか
threads: エクスポート処理を実行するスレッド数

その他のオプションは公式ドキュメントを参照してください。MySQL Shell Utilities Dump Instance Schema

ログ

 MySQL  127.0.0.1:13306 ssl  JS > util.dumpSchemas(databases, output, {
                               ->   defaultCharacterSet: 'utf8',
                               ->   consistent: false,
                               ->   chunking: true,
                               ->   threads: 4,
                               -> })
Initializing - done 
1 schemas will be dumped and within them 465 tables, 5 views.
Gathering information - done 
WARNING: The dumped value of gtid_executed is not guaranteed to be consistent
Writing global DDL files
Running data dump using 4 threads.
NOTE: Progress information uses estimated values and may not be accurate.
Writing schema metadata - done       
Writing DDL - done           
Writing table metadata - done           
Starting data dump
...(略)
1 thds dumping / 109% (20.26M rows / ~18.55M rows), 40.06K rows/s, 13.43 MB/s uncompressed, 579.97 KB/s compressed                
Dump duration: 00:09:15s
Total duration: 00:09:16s
Schemas dumped: 1
Tables dumped: 465
Uncompressed data size: 7.10 GB
Compressed data size: 577.19 MB
Compression ratio: 12.3
Rows written: 20256710
Bytes written: 577.19 MB
Average uncompressed throughput: 12.78 MB/s
Average compressed throughput: 1.04 MB/s

NOTE

AWS の設定上 FLUSH TABLES WITH READ LOCK が行えない場合は、consistent を false に設定するとエラーが発生しません。

ローカルへの読み込み

ダンプしたデータをローカルに読み込みます。

$ LOCAL_DB_PORT=<ローカル上のDBのポート番号>
$ LOCAL_DB_USER=<ローカル上のDBのユーザ名>
$ mysqlsh --host=127.0.0.1 --port="${LOCAL_DB_PORT}" -u "${LOCAL_DB_USER}" -p --js

# ダンプ読み込みスクリプト
> const schema = '読み込み先のデータベース名';
> const input = '読み込み元の URL or Path';
> const excludeTables = ['除外したいテーブル名']; // view を参照する view がある場合などに失敗するため除外しておく
> \sql SET GLOBAL local_infile=on; // ローカルファイルを読み込む場合
> \js
> util.loadDump(input, {
  schema,
  threads: 4,
  loadIndexes: true,
  deferTableIndexes: 'all',
  excludeTables,
  resetProgress: true,
});

ロードのオプション

schema: 読み込み先のデータベース名
threads: インポート処理を実行するスレッド数
loadIndexes: テーブルのセカンダリインデックスを作成するかどうか
deferTableIndexes: セカンダリインデックスの作成を遅らせる対象 ('off', 'fulltext', 'all')
excludeTables: インポートから除外するテーブル
resetProgress: 読み込みの進捗状態をリセットするか否か

その他のオプションは公式ドキュメントを参照してください。MySQL Shell Utilities Load Dump

ログ

 MySQL  127.0.0.1:3366 ssl  performance_schema  JS > util.loadDump(input, {   schema,   threads: 4,   loadIndexes: true,   deferTableIndexes: 'all',   excludeTables,   resetProgress: true, })
Loading DDL and Data from '/tmp/hogehgoe' using 4 threads.
Opening dump...
Target is MySQL 5.7.41-log. Dump was produced from MySQL 5.7.12-log
NOTE: Load progress file detected for the instance but 'resetProgress' option was enabled. Load progress will be discarded and the whole dump will be reloaded.
Scanning metadata - done       
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL - done           
Executing view DDL - done       
Starting data load
1 thds loading - 2 thds indexing | 100% (7.10 GB / 7.10 GB), 19.71 MB/s (78.32K rows/s), 465 / 465 tables done
Recreating indexes - done             
Executing common postamble SQL
629 chunks

 (20.26M rows, 7.10 GB) for 465 tables in 1 schemas were loaded in 3 min 34 sec (avg throughput 33.14 MB/s, 94.49K rows/s)
468 DDL files were executed in 2 sec.
Data load duration: 3 min 34 sec
1349 indexes were recreated in 1 min 24 sec.
Total duration: 5 min 10 sec
0 warnings were reported during the load.

NOTE

excludeTables にはデータベース名を指定する必要があります。

const excludeTables = ['database_name.table_name'];

Docker 上の DB が MySQL 5.7 で performance_schema が正しくない場合、以下のコマンドを実行して修正できます。

# docker にログイン
$ docker exec -it [コンテナ名] /bin/bash
# mysql のデータをアップグレード
$ mysql_upgrade -u root -p --force

まとめ

MySQL Shell を使用すると、マルチスレッドで高速にデータベースのダンプやロードができることがわかりました。mysqldump に比べて効率的であり、今後は mysqlsh を積極的に利用してみようと思います。

参考文献

この記事が、皆さんの MySQL 管理の効率化に役立てば幸いです。質問やコメントがあればお気軽にどうぞ！

余談

内容はメモを ChatGPT に投げるだけで良い感じに整形してくれて、タイトルとタグも教えてくれました！
今まで作業しても記事にするのにかかる労力が高くて諦めていましたが、ChatGPTにすべてお願いできるので今後も何かあれば記事にして行こうと思います！

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up

MySQL ShellでAWS上のMySQLを自在に操る！SSHトンネルとマルチスレッドダンプの徹底解説

AWS 上の MySQL に対して mysqlsh を試してみた

目次

SSH トンネルの作成

MySQL へのアクセス

MySQL Shell のインストール

mysqlsh を利用した操作

ダンプの作成

ダンプのオプション

ログ

NOTE

ローカルへの読み込み

ロードのオプション

ログ

NOTE

まとめ

参考文献

余談