mysqldumpコマンドまとめ「基礎から実践まで」

1. 基本的な使い方

1-1. 基本のコマンド

mysqldumpコマンドは指定したDBのデータを標準出力してくれるコマンド。
なので、標準出力のリダイレクト>を組み合わせることでファイルに出力させてdumpファイルを作成するのが普通の使い方である。

# DBの全てのテーブルを取得
mysqldump -h[db_server_host_name] -u[db_user_name] -p[db_password] [DB_Name] > ./dump_file_name.sql
# 指定したテーブルだけ取得
mysqldump -h[db_server_host_name] -u[db_user_name] -p[db_password] [DB_Name] [table_name] > ./dump_file_name.sql

1-2. テーブルの構造・データを指定して取得

上記みたいに何も指定しないとテーブルの構造＋データを取得するが、指定して取得する方法もある。やり方は以下。

1. 構造のみ取得したい時は「--no-data」か「-d」オプションをつける
2. データのみ取得したい時は「-t」オプションをつける

これを上記で紹介したDB全体・テーブル指定を応用したら下記のような例のパターンが作れる

# db全てのテーブルの構造・データ
mysqldump -u [username] -p [password] -h [host_name] [database_name] > ./dump_file_name.sql
# db全てのテーブルの構造
mysqldump -u [username] -p [password] -h [host_name] -d [database_name] > ./dump_file_name.sql
# db全てのテーブルのデータ
mysqldump -u [username] -p [password] -h [host_name] -t [database_name] > ./dump_file_name.sql

# dbの指定テーブルの構造・データ
mysqldump -u [username] -p [password] -h [host_name] [database_name] [table_name] > ./dump_file_name.sql
# dbの指定テーブルの構造
mysqldump -u [username] -p [password] -h [host_name] -d [database_name] [table_name] > ./dump_file_name.sql
# dbの指定テーブルのデータ
mysqldump -u [username] -p [password] -h [host_name] -t [database_name] [table_name] > ./dump_file_name.sql

1-3. 離れたサーバーのdumpをローカルで取る方法

~/.ssh/configに保存させたなら下記で簡単に取れる

ssh [Host Name] 'mysqldump -h[db_server_host_name] -u[db_user_name] -p[db_password]' > ./dump_file_name.sql

sshでサーバー繋げてmysqldumpコマンドを走らせて標準出力したものをローカルのファイルに出力する方法。

2. 応用編

本番のDBからdumpを取る時は気をつけなければいけないことがいくつかあるので紹介する。

2-1. テーブルロックの問題

まだ公開されていないシステムだといいかもしれないが、既に運用中のシステムのdumpを取る時はテーブルロックに注意しなければならない。
でないとREADロックがかかってしまって、ユーザはdump対象のテーブルにselect以外の処理が出せなくなってしまうという大惨事が怒る😨
結論から言うとdump先のテーブルがinnoDBの時は下記のオプションを追加するだけでいい。

--single-transaction
--skip-lock-tables

--single-transactionだけでロックはかからないらしいけど、怖いので一応--skip-lock-tablesもつけている。
もし詳しい方いらっしゃったら教えてください🙇‍♂️

2-2. データ量が重すぎる問題

規模の大きいサービスだとdump取るのかなりの時間を消耗することは普通にあることなので、できるだけ時間を短縮する必要がある。
mysqldumpはテーブル指定の場合は--whereオプションが使えるらしい。
あとgzipで圧縮しながらとなると最終的に下記のコマンドに落ち着いた。

ssh sid_pd 'mysqldump --single-transaction --skip-lock-tables -h[db_server_host_name] -u[db_user_name] -p[db_password] -t [database_name] [table_name] --where="id > 11005 limit 0,50200" | gzip' | cat > "file_name_`date "+%Y%m%d_%H%M%S"`.sql.gz"

参考にしたサイト

https://masyus.work/articles/if-using-mysqldump-add-single-transaction-skip-lock-table/
https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0062
https://dev.mysql.com/doc/refman/5.6/ja/mysqldump.html#option_mysqldump_single-transaction