mysqldumpのinsertを1000件ずつコミットする

きっかけ

mysqldumpを取り込む際の悩みとして、一斉に全てのレコードを取り込んでしまうとメモリが溢れてしまう場合があります。今回はダンプファイルのinsertを1000件ずつコミットして取り込むことでメモリが溢れる問題を解決する方法を紹介しようと思います。

環境

• OS: macOS13.0
• DB: 10.5.9-MariaDB

完成したコード

完成したシェルスクリプトは以下の様になります

ダンプファイルを作る際

# shell
DB=[target database]
TABLE=[target table]
(
echo 'begin;'
(
mysqldump --skip-extended-insert --order-by-primary $DB $TABLE \
| egrep '^INSERT'
) | awk '(NR%1000==0){$0=$0"\ncommit;\nbegin;"}{print}'
echo 'commit;'
) | gzip > table.dump

ダンプファイルをインサートする際

# shell
gzcat table.dump | mysql -u [user] -p [password] --default-character-set=utf8mb4　[target table]

insert処理をコミットで囲んだダンプファイルを作成する

MySQLではbeginでトランザクションの開始を宣言してcommitでその変更を反映させることができます。この機能を利用してmysqldumpのinsert文を1000レコード単位で分割していきます。

1レコードずつのinsert文を作る

まずは1レコードずつのinsert文を作成します。

$ mysqldump --skip-extended-insert

skip-extended-insertオプションを使用することで複数行をまとめたinsertが行われず、1レコードずつの処理を書くことができます

begin/commitでinsert処理を挟む

begin;
INSERT INTO `target_table` VALUES (1,'rooter','test1');
INSERT INTO `target_table` VALUES (2,'rooter','test2');
...
INSERT INTO `target_table` VALUES (1000,'rooter','test1000');
commit;


begin;
INSERT INTO `target_table` VALUES (1001,'rooter','test1001');
INSERT INTO `target_table` VALUES (1002,'rooter','test1002');
...
INSERT INTO `target_table` VALUES (2000,'rooter','test2000');
commit;

mysqldumpコマンドで作成されたinsert文に対して1000レコードずつのタイミングでbegin/commitを挟み込んでいくことでinsert処理を分割していきます。

1000行ずつawkで分割

[mysqldump] | awk '(NR%1000==0){$0=$0"\ncommit;\nbegin;"}{print}'

awkのNR変数を使用して、指定した行数ごとの処理を書いていきます。今回は1000行ずつにbegin/commitを出力していきます。

作成したmysqldumpをgzipで圧縮

書き出されたダンプファイルをgzipコマンドに渡すことで圧縮していきます。

[command] | gzip > table.dump

mysqldumpをインポート

圧縮されたdumpファイルを展開

今回はmac環境でgzip圧縮されたファイルを展開するため、zcatではなくgzcatを使用します。

gzcat table.dump

事前にDBとテーブル構造を作成しておく

単純なことですが、ダンプファイルを取り込む前に事前にテーブルを作成しておきましょう。

# テーブルがない状態で取り込もうとするとこんな感じのエラーが返ってくる
ERROR 1146 (42S02) at line 2: Table '[database].[table]' doesn't exist

mysqlクライアントのエンコードを指定しておく

取り込むデータに絵文字が含まれている場合はエンコードに気をつけましょう。

# エンコードで弾かれる時のエラー
ERROR 1366 (22007) at line 123: Incorrect string value: 'XXX' for column `[database]`.`[table]`.`[column]` at row 1

ここで絵文字を含む様なレコードをインポートする際にはmysqlのクライアント側のエンコード設定を絵文字が扱える様にしておきます

mysql --default-character-set=utf8mb4 [table]

参考

ダンプしたレコードを特定の件数毎にトランザクションで囲って処理する　@kitamin(弁護士ドットコム株式会社)

https://qiita.com/kitamin/items/cf04dc932da818a12b46