MariaDB(MySQL) - データのみを指定件数ずつ分割ダンプ出力！
Updated:
MariaDB(MySQL) の指定したデータベースを、テーブル別にデータのみを指定件数ずつ分割してダンプ出力する方法についてです。
0. 前提条件
- Linux Mint 17.2(64bit) での作業を想定。
- MariaDB 10.0.21 サーバでの作業を想定。（MySQL や他のバージョンでも同様（のはず））
- Bash スクリプトを作成して実現させる。
- 出力されたダンプファイルはまとめて圧縮保存する。
- 必要であれば、「MySQL(MariaDB) - スキーマのみ、データのみ、ストアド・トリガーのみのダンプ！」も参考にしてください。
- 以下で紹介する Bash スクリプトでは、トランザクションの単位がテーブル・ループ単位となるため、データの整合性に注意。
（ダンプ出力時やリストア時に DB への挿入・更新・削除がないことが前提）
1. Bash スクリプトの作成
以下は、当方がデータのみを分割ダンプ出力する際に使用している Bash スクリプトである。（解説はスクリプト内のコメントにて。 (*) はさらなる説明を後述）
File: db_dump_only_data_by_split.sh
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
#!/bin/bash
# 定数定義
DB=db_name # データベース・スキーマ名
USER=user_name # ダンプ出力するユーザ名
PW=user_password # 〃 ユーザ名のパスワード
CMD=/usr/bin/mysqldump # mysqldump コマンドのフルパス
WK_DIR=/path/to/backup_mysql # 作業ディレクトリ
DMP_DIR=$WK_DIR/tabledatas_$DB # ダンプファイル格納ディレクトリ
OPTS="-t -Q -F --skip-lock-tables --skip-triggers --skip-dump-date --single-transaction"
# mysqldump オプション(*)
DCNT=100000 # 分割するレコード件数
# ダンプファイルクリア
rm -f $DMP_DIR/*
# DB 内に存在するテーブル毎にループ処理
for tbl in `mysql -u $USER -p$PW -N -s -e "show tables in $DB;"`; do
# テーブル内に存在するレコード件数(*)
rows=`mysql -u $USER -p$PW $DB -N -B -e "SELECT COUNT(*) FROM $tbl;"`
echo "* $tbl [$rows records]"
# レコード件数から分割数を算出してループ処理
for ((i = 0; i <= $(($rows / $DCNT)); i++))
do
# 出力ダンプファイル名
fname=$(printf ${tbl}_%02d $i)
# オフセット算出
offset=$((i * $DCNT))
echo " $fname [OFFSET: $offset]"
# ダンプ出力(*)
$CMD -u $USER -p$PW $DB $tbl $OPTS -w "true LIMIT $offset, $DCNT"> $DMP_DIR/$fname.sql
done;
done;
# タイムスタンプ付ファイル名で圧縮保存
cd $WK_DIR
dt=`date '+%Y%m%d_%H%M%S'`
tar zcvf ${DB}_tabledata_$dt.tar.gz tabledatas_$DB
mysqldump オプション（上記のOPTS）について。（mysqldump のデフォルトで有効のオプション --opt とは別のもの）
-t(or--no-create-info) … テーブル作成（CREATE TABLE）文を出力しないオプション。-Q(or--quote-names) … データベース名、テーブル名、カラム名を`で囲むオプション（デフォルトで有効）。-F(or--flush-logs) … ダンプ開始前にログファイルをフラッシュするオプション。--skip-lock-tables… デフォルトで有効になる全テーブルをロックするオプション--lock-tablesを無効にするオプション。--skip-triggers… trigger のダンプ出力を無効にするオプション。--skip-dump-date… ダンプ日時の出力を無効にするオプション。--single-transaction… データの整合性を保つためにダンプ処理をトランザクションで囲むオプション。- ちなみに、主キーでソートせずに LIMIT(OFFSET) 句を使用するとフルスキャンになってしまうからと、
--order-by-primaryという主キーでソートするオプションを使用すると、 Syntax error が発生しまう。従って、今回は使用していない。
主キーでソートしたければ、-wオプションのLIMIT句の前にORDER BY primary_key_colのように追加する。（但し、今回紹介のケースでは DB 内全テーブルの主キーが同じカラムである必要がある）
レコード件数取得について。
- 上記では
SELECT COUNT(*) ...としているが、データベース内の全テーブルに同じカラム名で主キーが設定してあるのならSELECT COUNT(col_name) ...とした方がよい。
OPTS 以外に追加している -w(or --where) オプションについて。（今回のポイント）
- 本来
-wは抽出条件を指定するオプションだが、 WHERE 条件はtrue（or1or1=1） でスルーしてLIMIT句を指定している。
（mysqldump に LIMIT(OFFSET) 句を指定するオプションが存在しないため） -w "true LIMIT $offset, $DCNT"は-w "true LIMIT $DCNT OFFSET $offset"としてもよい。- 主キーやユニークインデックスが歯抜けでなく件数が正確に取得できるのであれば、
-wオプションで LIMIT 句や OFFSET 句を指定せずに普通に条件指定するようにしてもよいだろう。 - プライマリキーでソートしたいからと
--order-by-primaryオプションを使用すると、WHERE true LIMIT 100000, 0 ORDER BY idとなってしまいシンタックスエラーとなる。
プライマリキーでソートしたい場合は、--order-by-primaryオプションを使用せず、-w true ORDER BY id LIMIT $offset, $DCNTなどとするとよい。
2. Bash スクリプトの実行
作業用ディレクトリやダンプファイル格納用サブディレクトリが存在することを確認し、実行権限を付与後に実行する。
$ chmod +x db_dump_only_data_by_split.sh
$ ./db_dump_only_data_by_split.sh
作業ディレクトリ内のダンプファイル格納用サブディレクトリにダンプファイルが出力され、作業ディレクトリ内にタイムスタンプ付圧縮ファイルが作成されるのを確認する。
また、 LIMIT 句を使用しているのでダンプ出力完了まで時間がかかることを覚悟すること。
3. その他
- データ量が多すぎるとダンプ出力に時間がかかる、ということに留意する。
-wオプションで LIMIT 句を使用するからと、フルスキャンを避けるために--order-by-primaryオプションを使用したり、もしくは-wオプションでORDER BY句を指定しても、ダンプ出力が徐々に遅くなる。
mysqldumpの-w(or--where) での LIMIT(OFFSET) 句指定はフルスキャンになってしまうのだろうか？（mysqldumpを EXPLAIN で確認できればいいのですが。。。） - データ量が多すぎるとダンプ出力に時間がかかるため、結局当方は、データ量が多すぎる場合は、エクスポートは
SELECT INTO ... OUTFILEで CSV 出力、インポートはLOAD DATA INFILEで CSV 取り込み、とすることにした。以下の過去記事を参照。
4. 参考サイト
mysqldump の各種オプションや LIMIT 句の指定方法については以下のサイトを参照。
結局、当方は今回紹介した方法を積極的には使用していませんが、こういうやり方もあるということを覚えておくと何かの際に役立つかもしれません。
以上。
Comments