mysql ダンプとリストア

この記事はアピリッツの技術ブログ「DoRuby」から移行した記事です。情報が古い可能性がありますのでご注意ください。

mysqlでデータをバックアップするときや、テスト環境のデータをローカル環境にコピーする時にダンプ・リストアを使います。今回は、ダンプ・リストアの方法と実際にやってみて発生した問題と対処方法についてまとめました。

ダンプ

ダンプは、DBのテーブルやデータをsqlファイルに変換します。DBのバックアップなどに使用されます。

DB指定のコマンドは、指定したDBのテーブル全てに対して以下の処理を順番に行います。テーブル指定のコマンドは、指定したDBのテーブルに対して以下の処理を順番に行います。
DB指定：$ mysqldump -u ユーザ名 -p データベース名 > 出力ファイル
テーブル指定：$ mysqldump -u ユーザ名 -p データベース名 テーブル名 > 出力ファイル
1. テーブル削除
2. テーブル作成
3. データ作成

ダンプには、いくつかオプションがあり、オプションを指定することで上記の処理を細かく分けることができます。

-dオプション：$ mysqldump -u ユーザ名 -p データベース名 -d テーブル名 > 出力ファイル
データ作成を行わずに、テーブル作成・削除のみ行います。

-tオプション：$ mysqldump -u ユーザ名 -p データベース名 -t テーブル名 > 出力ファイル
テーブル作成・削除を行わずに、データ作成のみ行います。

参考ページ：https://qiita.com/PlanetMeron/items/3a41e14607a65bc9b60c

リストア

リストアは、ダンプで出力したsqlファイルからテーブルやデータを作成・更新・削除します。

$ mysql -u ユーザ名 -p データベース名 < 出力ファイル

ダンプ・リストアで発生した問題

テスト環境のテーブルをダンプして、ローカル環境にリストアした時に発生した問題
mysql version：5.6.39
エラー内容：ERROR 1118 (42000) at line 394: Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.
１行に8126バイト以上のデータをリストアした為に発生した模様。エラーメッセージの通り、ROW_FORMATの設定をすることで解決することができます。

対処方法
/etc/my.cnfファイルに以下の設定を追加する。

innodb_file_per_table
innodb_file_format = Barracuda
innodb_file_format_max = Barracuda

追加後に、mysqlサーバを再起動するとエラーが発生しなくなります。

参考ページ：http://sawara.me/mysql/2219/

マイグレーションでテーブルを作成して、マイグレーション前にダンプしたsqlファイルをリストア後、マイグレーションを実行した時に発生した問題
エラー内容：Mysql2::Error: Table 'テーブル名' already exists ...
リストアによってDBの状態がダンプファイル出力時の状態に戻ったが、マイグレーションで作成されたテーブルは、DBに残ったままなので、マイグレーション実行時に作成する予定のテーブルがすでに存在するとエラーが表示される。

対処方法
対象のテーブルを削除する。