これからは mysqlpump を使っていくことにする

この記事は「MySQL Casual Advent Calendar 2018」21日目の記事です。

9日目の記事としてこちらも書きました。

masayuki14.hatenablog.com

今回は mysqlpump に関する記事です。

mysqlpump って？ mysqldump の typo じゃないよ

mysqlpump は MySQL5.7.8から追加された新しい論理バックアップツールです。複数のMySQLデータベースをダンプすることができます。 mysqldump の拡張ツールではなく1から新しく設計・開発されたもので、高速化のためにデータベース、テーブルなどのを並列処理でダンプすることができます。また出力を圧縮したり、進捗を表示するなどの機能もあります。

MySQL5.7で導入された機能を使用しているので、MySQL5.7以上で利用することを前提としています。

バージョン

$ mysqld --version
/usr/sbin/mysqld  Ver 8.0.13 for Linux on x86_64 (MySQL Community Server - GPL)

$ mysqlpump --version
mysqlpump  Ver 8.0.13 for Linux on x86_64 (MySQL Community Server - GPL)

基本的な使い方

mysqlpump -uroot -p > all-database.pump.sql

デフォルトですべてのデータベースを出力しますが、sys performance_schema information_schema は出力しません。

以降、Tips的にオプションの使い方などを並べていきます

データベースを指定して出力する

mysqlpump -uroot -p --databases db1 db2 ...

複数のデータベースを指定する場合は --databases オプションを指定します。オプションの後に空白区切りでデータベース名を並べます。 = で指定する方法は使えないので気をつけましょう。

テーブルを指定して出力する

mysqlpump -uroot -p database_name table_1 table_2 ...

最初の引数をデータベース、2番目以降をテーブルと解釈します。

mysqlpump -uroot -p --include-tables 'table1, table2, ...'
mysqlpump -uroot -p --include-tables=table1,table2,...

--include-table オプションでテーブルを指定することができます。オプションには一つの値が渡るようにします。クオートでくくる場合は空白が入ってもいいですが、くくらない場合は空白が入るとエラーになります。同じテーブル名が複数のデータベースにある場合はどちらも出力されます。

mysqlpump -uroot -p --include-tables='db1.table1, table2'

table1はdb1にあるもののみとなり、table2は複数のデータベースのものが出力されます。

mysqlpump -uroot --databases db1 db2 \
    --exclude-tables='db1.table3, table4'

--exclude-tables で除外するテーブルを指定できます。

DROP ステートメントを追加する

デフォルトでは DROP XXX のステートメントが出力されません。

mysqlpump -uroot -p --databases db1 db2 \
    --add-drop-database \
    --add-drop-table

オプションを指定すると DROP DATABASE IF EXISTS db1 DROP TABLE IF EXISTS db1.table1 が出力されます。

一貫性のあるバックアップを取得する

mysqlpump -uroot -p --single-transaction

STAER TRANSACTION を発行して一貫性のあるバックアップを出力しますが、対象となるストレージは InnoDB のみで MyISAM や MEMORY ストレージの一貫性は保証しません。トランザクションの分離レベルは REPEATABLE READ になります。

また、バックアップの取得中に ALTER TABLE CREATE TABLE などテーブルに変更を加える操作を実行してはいけません。

キューやスレッド数を指定して取得する

mysqlpump -uroot -p --default-parallelism=3

デフォルトでは2つのスレッドを持つ1つのキューを設定し処理を実行します。 --default-parallelism オプションで並列処理キューのスレッド数を指定します。この場合、1つのキューを3つのスレッドが処理します。

mysqlpump -uroot -p --parallel-schemas=db1,db2,db3

db1 db2 db3 を処理するキューを設定し処理を実行します。それ以外のデータベースは別のキューで処理されるので、この場合は2つのキューで処理を行います。スレッド数はデフォルトの2になりますが --default-parallelism で指定することもできます。

mysqlpump -uroot -p \
    --parallel-schemas=db1,db2 \
    --parallel-schemas=db3

db1 db2 を処理するキュー、db3 を処理するキュー、それ以外を処理するキューを設定し処理を実行します。合計3つのキューで処理します。

mysqlpump -uroot -p \
    --parallel-schemas=db1,db2 \
    --parallel-schemas=db3 \
    --default-parallelism=4

前の例とおなじキューを設定し、各キューのスレッド数を4として実行します。

mysqlpump -uroot -p \
    --parallel-schemas=6:db1,db2 \
    --parallel-schemas=db3 \
    --default-parallelism=3

db1 db2 を処理するキューをスレッド数を6で、db3 を処理するキュー、それ以外を処理するキューのスレッド数を3に設定し処理を実行します。

mysqlpump -uroot -p --default-parallelism=0

この場合はシングルスレッドプロセスとして実行されます。

並列処理は mysqldump にはない機能なので、ダンプするデータが大きい状況ではうまく利用したいですね！

出力を圧縮する

mysqlpump -uroot -p --compress

クライアントとサーバーの両方が圧縮に対応している場合に、クライアント、サーバー間で送信されるデータを圧縮します。クライアントに出力される結果は圧縮されていません。

mysqlpump -uroot -p --compress-output=lz4 > database.lz4

# 解凍
lz4_decompress database.lz4 database.sql

出力を圧縮します。 LZ4 と ZLIB が指定可能で、結果はそれぞれのコマンドで解凍できます。

進捗を表示する

mysqlpump -uroot -p --watch-progress

処理中の進捗状況を定期的に標準エラーに出力します。デフォルトで有効なのでオプションで指定する必要はありません。表示したいくない時は --skip-watch-progress を指定します。

テーブル定義とデータを別に出力する

mysqlpump -uroot -p --skip-dump-rows
mysqlpump -uroot -p -d

テーブルのデータを出力せず、 CREATE TABLE のテーブル定義だけになります。

mysqlpump -uroot -p --no-create-info
mysqlpump -uroot -p -t

テーブル定義を出力せず INSERT INTO によるデータの出力だけになります。前の例と合わせることで、テーブル構造とデータを分離することができます。

mysqlpump -uroot -p --no-create-db

CREATE DATABASE ステートメントを出力しません。

これらのオプションは --add-drop-database や --add-drop-table と組み合わせると面倒なことになります。

主夫ときどきプログラマ

データベース、Webエンジニアリング、コミュニティ、etc