ロリポップのphpMyAdminから全データをエクスポートしました。しかしどうやらきちんとエクスポートできていなかったようです。事象の詳細と対処法をご説明します。
もくじ
MySQLのバージョンアップをするために
ロリポップのMySQLを5.6から5.7にバージョンアップすることにしました。最新のWordPressは5.6をサポートしなくなっているし、5.7は5.6に対して3倍のスピードアップになるらしいからです。
ロリポップのライトプランはデータベースを一つしか作れません。なので、バージョンアップするには一度既存のデータベースを削除する必要があります。なので、一時的にデータをローカルのパソコンに持ってくる必要がありました。より上位のプランだと、データベースを複数作れるので、そちらにコピーしておくということもできるはずで、そちらの方がずっと楽なはずですが、ライトプランで安く済ませたいなら今回のような問題に対処する必要があります。
単純にデータをパソコンにバックアップしておきたい方にも参考になると思います。
一見正常にエクスポートできたように見える
エクスポートするとブラウザでファイルのダウンロードが始まります。特にエラーやなんらかのメッセージも発生せずにダウンロードが完了します。なので、一見正常にダウンロードできたように見えます。
正常にエクスポートできたと判断して、データベースを削除してしまうとデータが失われてしまうので厄介、というか罠です。
SQL実行時にエラー
全テーブルのデータをまとめて圧縮せずにダウンロードした結果1.5GBほどの大きさになりました。
きちんとエクスポートできたか心配だったので、ローカルのMacにもMySQLにインポートしてみることにしました。ターミナルから
|
1 2 3 4 5 6 7 8 9 10 |
mysql> source xxxxxxxxxx.sql Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) |
膨大な量のデータがあるので、これがターミナル上に何万行も出力されていきます。数分待つと新たな行の表示は止まり、以下のようにエラーで終わりました。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Query OK, 105 rows affected, 273 warnings (0.00 sec) Records: 105 Duplicates: 0 Warnings: 273 Query OK, 105 rows affected, 274 warnings (0.00 sec) Records: 105 Duplicates: 0 Warnings: 274 Query OK, 104 rows affected, 267 warnings (0.01 sec) Records: 104 Duplicates: 0 Warnings: 267 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '<! <div class="error"><h1>エラー</h1> <p><strong>実行した SQL:</stron' at line 1 mysql> |
実行したSQLファイルを開いてみると、最下部に以下のような見慣れない文字列がありました。（最初viewで開いたら少し文字化けしていたのですが、tailで表示するとちゃんと見れました）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
<!-- PMA-SQL-ERROR --> <div class="error"><h1>エラー</h1> <p><strong>実行した SQL:</strong> <a href="tbl_sql.php?sql_query=SHOW+TABLE+STATUS+FROM+%60XXXXXXXXXXXXXX%60+LIKE+%27piwik_option%27&show_query=1&db=XXXXXXXXXXXXXX&table=piwik_option&server=161&token=cf121ba36d12d4b936538e003586b9cc"><span class="nowrap"><img src="themes/dot.gif" title="編集" alt="編集" class="icon ic_b_edit" /> 編集</span></a> </p> <p> <span class="syntax"><span class="inner_sql"><a href="./url.php?url=http%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.6%2Fen%2Fshow.html&server=161&token=cf121ba36d12d4b936538e003586b9cc" target="mysql_doc"><span class="syntax_alpha syntax_alpha_reservedWord">SHOW</span></a> <span class="syntax_alpha syntax_alpha_reservedWord">TABLE</span> <span class="syntax_alpha syntax_alpha_reservedWord">STATUS</span> <span class="syntax_alpha syntax_alpha_reservedWord">FROM</span> <span class="syntax_quote syntax_quote_backtick">`LAA0644427-i9jlb7`</span> <a href="./url.php?url=http%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.6%2Fen%2Fstring-comparison-functions.html%23operator_like&server=161&token=cf121ba36d12d4b936538e003586b9cc" target="mysql_doc"><span class="syntax_alpha syntax_alpha_reservedWord">LIKE</span></a> <span class="syntax_quote syntax_quote_single">'piwik_option'</span></span></span> </p> <p> <strong>MySQL のメッセージ: </strong><a href="./url.php?url=http%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.6%2Fen%2Ferror-messages-server.html&server=161&token=cf121ba36d12d4b936538e003586b9cc" target="mysql_doc"><img src="themes/dot.gif" title="ドキュメント" alt="ドキュメント" class="icon ic_b_help" /></a> </p> <code> #2006 - MySQL server has gone away </code><br /> </div>% |
色々調べましたが、以下のページなどによるとデータが多すぎるのが原因のようです。
'<!-- PMA-SQL-ERROR --> when importing db
MySQLの設定を変えれば正常にダウンロードできそうです。
MySQL error 2006: mysql server has gone away
ロリポップはMySQLの設定は変更できないので、まずは圧縮してファイルサイズを小さくしてエクスポートすることを試してみました。
圧縮してエクスポートしても破損している
また一見何の問題もなくダウンロードが完了します。今度はファイルサイズは340MB程度でした。
このファイルを解凍してみると
|
1 2 3 |
% gunzip XXXXXXXXX.sql.gz gunzip: XXXXXXXXX.sql.gz: trailing garbage ignored |
「trailing garbage ignored」というメッセージが表示されます。
色々調べていくと、ファイルが破損している可能性があるようで、再ダウンロードする必要がありそうです。
How can I extract trailing garbage from a gzipped vcf file?
解凍したファイルを開いて中を見てみると、今回は <!-- PMA-SQL-ERROR --> のようなエラーは表示されておらず、SQL文で終わっています。
破損を確認するために、このファイル内を「CREATE TABLE」という文字列で検索したところ、テーブルの数が足りません。全てのデータがエクスポートできていないことがわかりました。viで以下の記事のやり方で数えました。
ちなみに、後で分かったことですが、正常にエクスポートできたSQLファイルには、最後の部分に以下の4〜6行目のような記述があります。
|
1 2 3 4 5 6 |
(1320, NULL, 'done', 7, '2023-08-16', '2023-08-16', 1, '2023-08-16 00:25:40', 0, NULL, NULL), (1321, NULL, 'done', 8, '2023-08-16', '2023-08-16', 1, '2023-08-16 00:25:40', 0, NULL, NULL); /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; |
設定にもよるかもしれませんが、これがなくて通常のSQL文で終わってしまっている場合は、破損していると考えられます。ちなみにこれらの行が何を意味しているかは、よくわかりません。
テーブルを分けてエクスポートすることに
おそらくデータ量が多すぎることが問題なので、全テーブルを一気にエクスポートするのではなく、小分けにしてエクスポートすることにしました。
１ファイルエクスポートが完了する度に、ファイルを開いて前述したやり方で「CREATE TABLE」の数を数えて狙ったテーブルが入っているか確認しました。
最初の数ファイルはうまくいったのですが、途中でやはり解凍時に上述した「trailing garbage ignored」が表示されてしまいました。テーブルが多いのかと思って、テーブルを減らして行って、最終的に1テーブルのみでエクスポートしましたが、やはり表示されました。確かにこのテーブルは非常に行数が多いテーブルです。他にもっと行数が多いテーブルもありましたが、phpMyAdminのテーブル一覧に表示される「サイズ」は 400MBほどあり最大でした。
1テーブルを何回かに分けてエクスポートすることに
仕方ないのでこのテーブルについては、何回かに分けてエクスポートすることにしました。約11万行のテーブルなので、何となくですが3万行ずつ4回に分けてエクスポートすることにしました。
ポイントは目的のテーブルが選択された状態でエクスポートタブを開くことです。すると「行」というセクションが表示されます。「サーバー」や「データベース」が選択された状態でエクスポートタブを開くとこれが表示されません。
そして、「行数」のところに 「30,000」を指定します。この数字は固定でOKです。
「開始行」については
1回目は「0」を入れ、
2回目は「30,000」
3回目は「60,000」
4回目は「90,000」
とします。
IDがきちんと１ずつインクリメントされていて途中で削除などされていなければ確認が楽です。1つ目のファイルの最終行のIDは30,000になっているはずです。2つ目のファイルの最終行は60,000になっているでしょう。
この方法でこのテーブルもエクスポートできました。前述したエラーや破損も発生しませんでした。
残ったテーブルもエクスポート完了しました。
ちなみに、エクスポートする際に選ぶオプションで「AUTO_INCREMENT」というものがあります。以下の記事で説明しましたが、結論から言うとONにしておいた方がいいです。
ファイルを順番にインポートし行数を確認
全てのテーブルをエクスポートできたので、Mac上のMySQLにインポートしました。
そして、テーブル数とそれぞれの行数を確認し、ロリポップのMySQL上のデータと一致していることが確認できました。行数の確認の仕方は以下で説明しています。
phpMyadminではなくMySQL側の設定の問題？
ここまでの状況から考察すると、全データを1回でダウンロードできない原因は、phpMyAdminではなく、MySQL側の設定なのではないかと思います。
phpMyAdminやApacheの問題であれば、問題が発生した時点でエラーを出し、ダウンロードが完了しないはずです。その時点で何かしら問題があるということには気が付くことができるでしょう。
エクスポートが失敗したSQLファイルの中身をみると、全て特定の一つのテーブルの途中で異常終了していることがわかりました。このテーブルは前述した最もデータが大きくて分割しないと正常にエクスポートできないテーブルです。
とはいえ、このテーブルのデータ量は400MBで、全テーブルの1.5GBに比べるとずっと小さいです。しかし、このテーブル以外のテーブルの途中では異常終了していないことから考えると、１テーブルごとの抽出に、時間か容量の制限があると考えられます。なので、全体が1.5GBなどとデータ量が大きくても、仮にテーブルに偏りなく満遍なく分散していれば、1度のエクスポートで問題ない可能性はあります。
その制限とは何かと考えたところ、MySQL側に1クエリーにかかる時間や容量に制限があることが思い浮かびます。おそらくエクスポートで作られるSQLファイルに記述されるINSERT分は、「SELECT * FROM テーブル名」を実行して作られていると思われます。その際に、一度のクエリーで何行までとして何回かに分けて実行していることはないでしょう。なので、行数、データ量が多いテーブルの場合は、実行時間や容量に上限がない状態となってしまいます。
もしくはネットワーク的な制限がある可能性はあります。phpMyAdminとMySQLが違う物理サーバーにある可能性があるので、ある一定の時間や容量を超えるコネクションは切断されてしまうという可能性も考えられます。1クエリは１コネクション内で行わなければいけないが、クエリが分れれば、よしなにコネクションを新設してくれるということかもしれません。
