MySQL/MariaDBのダンプリストア方法一覧【高速】
MySQLやMariaDBを扱う際に、バックアップやデータ同期の目的で、ダンプリストアを行うことが多々あります。
シンプルにmysqldumpする以外の方法を考えないといけない場面もあるので、知っている限りのダンプリストアの手法を説明します。
ちなみに有料・商用アプリケーションを利用した方法については紹介せず、全部無料でできる方法のみです。
MySQL/MariaDBのダンプリストア一覧
mysqldump
いつものやつです。
ダンプ
$ mysqldump -u{ユーザー名} -p{パスワード} {DB名} {テーブル名} > /tmp/table.dumpリストア
$ mysql -u{ユーザー名} -p{パスワード} {DB名} < /tmp/table.dumpダンプデータを開くと、create tableやinsert文がただ記述されていて、非常に素朴な方法であることが分かります。
エンジンがInnoDB、MyISAMいずれでも動きます。
mysqlpump
MySQL5.7で加わった進化版mysqldumpです。
ダンプ
$ mysqlpump -u{ユーザー名} -p{パスワード} {DB名} {テーブル名} > /tmp/table.pumpリストア
$ mysql -u{ユーザー名} -p{パスワード} {DB名} < /tmp/table.pumpmysqldumpとの最も大きな違いは、並列実行できるところにあります。mysqldumpは直列にシングルスレッドで処理を進めますが、mysqlpumpはスレッド数を調整することが可能なので、テーブル数が増えてくるとmysqldumpよりも高速に処理ができます。
下記の記事が詳しくmysqldumpとmysqlpumpの比較をしているので参考になりました。
SELECT INTO OUTFILE / LOAD DATA LOCAL INFILE
データをCSVファイルでダンプリストアする方法です。
ダンプ
mysql> SELECT * FROM {テーブル名} INTO OUTFILE '/var/lib/mysql-files/outfile.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';リストア
mysql> LOAD LOCAL DATA INFILE '/var/lib/mysql-files/outfile.csv' INTO TABLE {テーブル名};インデックス情報や定義情報は書き出されないため、リストアする前に対象テーブルをcreate tableしておく必要があります。
mysqldumpの20倍速いという噂も。（本当か？)
–secure-file-privオプションが有効な場合、/var/lib/mysql-files以外に吐き出すことが封じられています。この設定を切らないまま、他の場所にダンプしたい場合は、SELECT INTO OUTFILEなしでCSV成形することで対応できます。
また、LOAD DATA INFILEはmysql内でのコマンドですが、mysql clientで同様のことができるmysqlimportコマンドが用意されています。
$ mysqlimport -u{ユーザー名} -p{パスワード} --local {DB名} '{テーブル名}.csv'Parallel Table Import Utility
MySQL8.0.17以降では新たにLOAD DATA INFILEをマルチスレッドで処理できるParallel Table Import Utilityが用意されています。
パラレルで実行する分、単純なLOAD DATA INFILEよりも速いです。
詳細は下記の記事がとても詳しくまとまっているので、気になる方はこちらを参照ください。
MyISAMのファイルコピー
MySQLのコマンドでダンプリストアするのではなく、データディレクトリ上のデータファイルを直接移動させる方法もあります。MyISAMでは、.frmが定義データ、.MYDが実データ、.MYIがインデックスデータとなっているので、対象テーブルのこれらのファイルを持っていけば良いです。
$ scp -r /var/lib/mysql/{DB名}/{テーブル名}.* enutake@other-server:/var/lib/mysql/バイナリデータのまま動かすことができるため、ここまでに出てきた方法の中で最も高速にリストアすることができます。
しかし、データディレクトリにアクセスする必要があるので、mysqlユーザーかrootユーザーでないとこの方法は使えません。バッチでリストアをする際は実行ユーザーに気をつける必要があります。
トランスポータブルテーブルスペース(InnoDBのファイルコピー)
InnoDBの場合のファイルコピー方法です。InnoDBでは.frmが定義データ、.ibdが実データ+インデックスデータとなっています。
コピー先
mysql> CREATE TABLE {テーブル名} ・・・・;
mysql> ALTER TABLE {テーブル名} DISCARD TABLESPACE;コピー元
$ scp -r /var/lib/mysql/{DB名}/{テーブル名}.ibd enutake@other-server:/var/lib/mysql/コピー先
mysql> ALTER TABLE {テーブル名} IMPORT TABLESPACE;概ねリストアの時間はMyISAMの場合と変わりません。
公式サイトにも方法が記載されているので、詳細は下記ページを参考にしてください。
https://dev.mysql.com/doc/refman/5.6/ja/tablespace-copying.html
CSVテーブルを利用したインポート
漢のコンピュータ道で紹介されていた、select into outfileなどで作成したcsvデータをデータディレクトリに直接配置する方法です。一旦csvとして取り込むことで高速にリストアすることができますが、インデックスデータは作り直すことになるのでそこまで含めて速度を計測する必要があります。
コピー先サーバー
mysql> CREATE {テーブル名} ・・・・ engine=CSV;コピー元サーバー
mysql > SELECT * FROM {テーブル名} INTO OUTFILE '/var/lib/mysql-files/{テーブル名}.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';$ scp -r /var/lib/mysql-files/{テーブル名}.csv enutake@other-server:/var/lib/mysql/コピー先サーバー
mysql > ALTER TABLE {テーブル名} ADD INDEX ・・・, engine=InnoDB;サイト運営者 えぬたけ
都内で働くゆるふわフルスタックwebエンジニア。