mk-mode BLOG

このブログは自作の自宅サーバに構築した Debian GNU/Linux で運用しています。
PC・サーバ構築等の話題を中心に公開しております。(クローンサイト: GitHub Pages

ブログ開設日2009-01-05
サーバ連続稼働時間
Reading...
Page View 合計
Reading...
今日
Reading...
昨日
Reading...

MariaDB(MySQL) - データのみを指定件数ずつ分割ダンプ出力!

[ サーバ構築 ] [ Linux, MariaDB, MySQL, bash ]

こんばんは。

MariaDB(MySQL) の指定したデータベースを、テーブル別にデータのみを指定件数ずつ分割してダンプ出力する方法についてです。

0. 前提条件

  • Linux Mint 17.2(64bit) での作業を想定。
  • MariaDB 10.0.21 サーバでの作業を想定。(MySQL や他のバージョンでも同様(のはず))
  • Bash スクリプトを作成して実現させる。
  • 出力されたダンプファイルはまとめて圧縮保存する。
  • 必要であれば、「MySQL(MariaDB) - スキーマのみ、データのみ、ストアド・トリガーのみのダンプ!」も参考にしてください。
  • 以下で紹介する Bash スクリプトでは、トランザクションの単位がテーブル・ループ単位となるため、データの整合性に注意
    ダンプ出力時やリストア時に DB への挿入・更新・削除がないことが前提

1. Bash スクリプトの作成

以下は、当方がデータのみを分割ダンプ出力する際に使用している Bash スクリプトである。(解説はスクリプト内のコメントにて。 (*) はさらなる説明を後述)

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(or 1 or 1=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 スクリプトの実行

作業用ディレクトリやダンプファイル格納用サブディレクトリが存在することを確認し、実行権限を付与後に実行する。

1
2
$ 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