さくらインターネットの MySQL が5.5から5.7へバージョンアップしました。そこで、当サイトもMySQL5.7へ移行することにしました。ところが、移行するデーターベースが大容量だったため、phpMyAdminでインポートできませんでした。SSHでインポートできたので、備忘録を兼ねてその方法を紹介します。
はじめに
さくらインターネット(以下、さくら)が2018年1月18日からMySQL5.7の提供を開始しました。
旧バージョンのMySQLは、サポートが終了しているそうです。
このため、もし将来的に何かしらの脆弱性が発見された場合、さくらは対処できないそうです。
また、MySQLを5.5から5.7へ移行したことでサイトの表示速度が上がったという報告もありました。
そして、ページの表示速度は、微々たるものですが、2012年の段階でSEOに影響を与えるそうです。
今後のことを考慮すると、MySQLのバージョンアップは避けられません。
さくらのMySQLのバージョンアップ方法を色々と調べてみると、phpMyAdminを使うので簡単そうでした。
そこで、大分時間が経っていますが、MySQLを5.7へバージョンアップすることにしました。
ところが、phpMyAdminでは、MySQL5.5からエクスポートしたデータベースをMySQL5.7にインポートできませんでした。
この理由は、エクスポートしたデータベースが、zipファイルに圧縮しても16MBを超えていたからです。
次に、16MB以上の大容量のデータベースをインポートする方法を調べてみることにしました。
phpMyAdminでインポート可能なファイルサイズの上限は16MBとなります。
これ以上の容量をインポートされたい場合は、メールにてサポートまでご相談ください。
さくらでファイルサイズが16MBを超えるデータベースをインポートする場合はメールで問い合わせが必要なようです。
そこで、メールで問い合わせたところ、二つの方法が提示されました。
一つはSSHクライアントソフトを使って自分でやる方法、そして、もう一つはさくらのサポートに代行してもらう方法でした。
ただ、どちらの方法を選んでも共通して言えることは、何か不具合が生じても保証はできないということでした。
仮に代行を依頼して、不具合が生じた場合、その度に代行を依頼するのも大変だと思いました。
とりあえず、自分で大容量のデータベースをSSHでMySQLにインポートすることにしました。
最初は、サポートのメールで教えられた以下のコマンドでデータベースのインポートを試しました。
ところが、教えられたコマンドを実行してもインポートできませんでした。
今回のデータベースのインポートにはかなり梃子摺ったので、詳細に関しては長くなるので後述します。
その前に、突然ではありますが、先に言っておきたいことがあります。
今回、データベースのインポート方法を調べるにあたり、幾つかのサイトではMySQL5.5のデータベースの中身を削除するように書かれています。
しかも、データベースをMySQL5.7にインポートする前に…。
この必要性に関して、さくらのサポートに確認したところ、MySQL5.5とMySQL5.7はサーバーが分かれているので、削除する必要はないそうです。
なぜこんなことを書くかというと、もし知らずに参考サイトの通りにMySQL5.5のデータベースの中身を削除した後、MySQL5.7にデータベースをインポートできなかったら様々な問題が生じます。
私の場合、MySQL5.5のデータベースの中身を削除せずにMySQL5.7にインポートしているサイトを事前に見ていました。
このため、用心してMySQL5.5のデータベースの中身を削除しませんでした。
また、私が今回紹介する方法以外で、さくらのレンタルサーバーのスタンダードでSSHクライアントソフトを使ってデーターベースのインポートを紹介しているサイトがありました。
ところが、私の場合、その方法でデータベースをインポートすることができませんでした。
ひょっとしたら、その逆で、私が紹介する方法でデータベースをインポートできない場合があるかもしれません。
なので、MySQL5.7にデータベースをインポートして動作確認するまでは、バックアップ元のMySQL5.5のデータベースを削除するべきではありません！
MySQL5.7に移行して動作確認をした後で消しても何ら問題はありません。
実際に、私はMySQL5.5のデーターベースもその中身も削除せずにMySQL5.7のインポートから動作確認まで行えました。
今回、MySQLの移行にあたり、色々と試したので、その備忘録も兼ねて、さくらのMySQLで大容量のデータベースをインポートする方法を紹介したいと思います。
また、ここで紹介する他でどこまで適用できるかは分かりませんが、何かしらの参考になれば幸いです。
MySQL 5.5のデータベースのエクスポート
データーベースのエクスポートの方法ですが、さくらの場合はサーバーコントロールパネル(以下、SCP)からphpMyAdminにログインして行います。
SCPの左メニューの「アプリケーションの設定」から「データベースの設定」をクリックします(図1)。
図1
phpMyAdminのログインページが表示されるので、「サーバの選択」で任意のサーバーを確認し、ユーザー名とパスワードを入力して、「実行する」ボタンをクリックします(図2)。
図2
次に表示されるページで「エクスポート」タブをクリックします(図3の赤枠)。
図3
「エクスポート」タブでは、まず「エクスポート」の項目で該当するデータベース名を選択します(図4)。
Information_schemaは、MySQLサーバーに関するデータベースです。
今回の場合は、MySQL5.5のデータベースなので必要ありません。
次に、「構造」の項目で「DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT / TRIGGER コマンドを追加する」をチェックします(図4)。
ここで重要なのは”DROP TABEL”コマンドで、これは既存のデータベースのテーブルを削除します。
既存のデータベースに再インポートする場合などに必要だと思うので、インポートを複数回テストする場合は入れておいた方が良いかもしれません。
最後に、「ファイルに保存する」項目で「zip 形式」を選択します(図4)。
非圧縮形式では、ファイルサイズが16MBを超える可能性が高いです。
さくらの場合、ファイルサイズが大きいと、まずSCPでアップロードできない可能性があります。
また、phpMyAdminでデータベースをインポートできない可能性があります。
上記三つの設定を確認し、問題がなければ、「実行する」ボタンをクリックします(図4)。
図4
「実行する」ボタンをクリックして、しばらくするとダウンロードが開始します。
ダウンロードしたファイル名は特に指定しなかった場合は「mysql***.db.sakura.ne.jp.sql.zip」になります。
これで、データベースのエクスポートは終了です。
MySQL 5.7のデータベースの作成
データベースの作成は、SCPの左メニューの「アプリケーションの設定」から「データベースの設定」をクリックします(図5)。
次に表示される「データベース一覧」で「データベースの新規作成」をクリックします(図5)。
図5
「データベースの新規作成」では、「データベース名」と「接続用パスワード」を設定します(図6)。
データベースバージョンは5.7以外選択できません。
ここで設定する「データベース名」と「接続用パスワード」はMySQL5.5で設定したものと同じにします。
こうすることで、wp-config.phpを編集する場合、データベースのサーバー名だけを変更するだけで済みます。
各項目を入力して、内容に問題がなければ「同意する」をチェックし、「データベースを作成する」ボタンをクリックします(図6)。
図6
データベースが作成されると、今回の場合、MySQL5.5のデータベースとMySQL5.7のデータベースが二つ表示されます(図7)。
図7
今後、新規データベースを作成した場合、データベースバージョンMySQL5.7に追加されます。
データベースのアップロードと解凍
さくらのSCPにある「ファイルマネージャー」は、アップロードできるファイルサイズが5MB以下です。
そして、5MBを超えるファイルをアップロードする場合、ファイル転送ソフトを利用する必要があります。
FileZillaの設定とアップロード
ここで紹介するファイル転送ソフトはFileZillaです。
FileZillaを起動し、メニューにある「ファイル」→「サイトマネージャー」をクリックしてサイトマネージャーを表示します(図8)。
図8
「新しいサイト」をクリックし、追加された新規サイトに名前を設定します。
今回の場合、分かりやすくするために、「さくらインターネット」にします(図9)。
デフォルトで表示されている「一般」タブでは以下の項目を設定します(図9)。
- プロトコル…SFTP – SSH File Transfer Protocol
- ホスト…SCPログイン時のドメイン名(****.sakura.ne.jp)
- ユーザー…SCPに表示されているお客様情報のアカウント(****と同じ)
- パスワード…SCPログイン時のパスワード
図9
次に、「詳細」タブを表示し、以下の項目を設定します(図10)。
- デフォルトのローカルディレクトリ
- デフォルトのリモートディレクトリ
図10
デフォルトのローカルディレクトリはドキュメントが指定されています。
必要に応じて、任意の場所を指定してください。
デフォルトのリモートディレクトリは、さくらの場合、以下のようになります。
/home/****/www
****はホストのドメインの部分と同じです。
全ての設定に問題がなければ「接続」ボタンをクリックします。
設定の初期ではパスワードが保存されていないので、「パスワードを保存しますか？」というダイアログボックスが表示されます(図11)。
パスワードの再入力が面倒な場合などは「パスワードを保存する」を選択して「OK」ボタンをクリックします(図11)。
図11
次に、「不明なホスト鍵」が表示されるので、「常にこのホストを信頼し、この鍵をキャッシュに追加」をチェックして「OK」ボタンをクリックします(図12)。
図12
サーバーに接続されると、右側にはリモートで指定したwwwフォルダの内容が表示されます(図13)。
また、左側にはローカルで指定したドキュメントの内容が表示されます(図13)。
図13
wwwフォルダに「mysql***.db.sakura.ne.jp.sql.zip」をアップロードする場合は、ドラッグアンドドロップでも可能です。
データベースの解凍
圧縮したデータベースをアップロードしたら、次は解凍をSCPで行います(図14)。
図14
wwwフォルダにある「mysql***.db.sakura.ne.jp.sql.zip」を右クリックすると、ドロップダウンメニューが表示されます。
フィルを解凍する場合は、「リモートで解凍」をクリックします(図15の赤枠)。
図15
次に表示される「解凍先の選択」ダイアログボックスでは、特に解凍先を変更する必要がなければ、「OK」ボタンをクリックします(図16)。
図16
すると、「リモートで解凍」ダイアログボックスが表示されて既存の同名ファイルがなくても上書きを確認されます。
特に問題がなければ「はい」ボタンをクリックします(図17)。
図17
ファイルが解凍されると、図18のように圧縮ファイルの10倍近いサイズのファイルが出現します。
図18
データベースのインポート
データベースの解凍が完了したら、次はそのデータベースのインポートを行います。
データベースのインポートにはSSHクライアントを使用します。
さくらでは幾つかのSSHクライアントを紹介しています。
以下のサイトでは、簡単にPoderosaの紹介をしていたので、Poderosaを使用することにしました。
Poderosaの導入
Poderosaは上記のサイトからダウンロードします。
しかし、ダウンロードしたファイルを解凍しても実行できない場合があります。
これは、アクセスブロックが設定されているからです。
このアクセスブロックは、ダウンロードしたzipファイルの状態で解除する必要があります。
まず、ダウンロードしたzipファイルを右クリックして、表示されるドロップダウンメニューから「プロパティ」をクリックします。
次に表示される「プロパティ」で、以下の内容の「セキュリティ」で「許可する」をチェックします(図19)。
このファイルは他のコンピューターから取得したものです。
このコンピューターを保護するため、このファイルへのアクセスはブロックされる可能性があります。
図19
zipファイルを解凍し、Poderosaのフォルダを任意の場所に置きます。
フォルダの中にある”Poderosa.exe”を実行してPoderosaを起動します(図20)。
次に、図20の赤枠に示すコンピューターのアイコンをクリックします。
図20
「新規セッション」が表示されるので、以下の項目に入力します(図21)。
- ホスト…SCPログイン時のドメイン名(****.sakura.ne.jp)
- アカウント…SCPに表示されているお客様情報のアカウント(****と同じ)
- パスフレーズ…SCPログイン時のパスワード
たまに、ローカルエコーが「しない」から「する」になってキー入力が繰り返される場合があるので注意して下さい。
入力項目に問題がなければ、「OK」ボタンをクリックします。
図21
図22のように公開鍵を登録するかどうかを確認されるので、「はい」ボタンをクリックします。
図22
MySQL へのログインとデータベースのインポート
MySQLへのログインログインとデータベースのインポートは以下のサイトを参考にしました。
上記のサイトでは、この方法ではデータの上書きはできず、既存のデータがある場合はエラーが出るそうです。
このため、インポート先のデーターベースを空にしておく必要があるとのことでした。
しかし、私の場合、エクスポート時の設定で「DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT / TRIGGER コマンドを追加する」をチェックしていたからか、上書きでもエラーが出ずにインポートできました。
この点について上書きするかどうかは自己責任でお願いします。
「新規セッション」でサーバーに接続されると、図23のような画面が表示されます。
図23
さくらのサポートからはデータベースをインポートするコマンドを以下のように教えられました。
mysql -h[データベースホスト名] -u[ユーザー名] -p[パスワード] [インポート先データベース名] < [インポート元ファイル名]
ところが、教えられたコマンドを実行してもデータベースをインポートすることが出来ませんでした。
さくらは、他のレンタルサーバーと異なるようで、Webサーバーとデータベースサーバーが分かれています。
そして、データベースをインポートする場合、最初にMySQL5.7にログインする必要があります。
さくらでMySQLにログインするためのコマンドは以下の通りです。
mysql -u ユーザー名 -h mysql***.db.sakura.ne.jp -pパスワード
インポート先データベース名とインポート元ファイル名がないだけのように思えます。
しかし、実際には、ユーザー名・インポート先データベース名・パスワードの入力順が違うとエラーが出ます。
コマンドを入力する際は、コピペも出来るので、順番を間違えないように入力します(図24)。
図24
MySQL5.7にログインすると、図25のようにメッセージが表示されます(図25)。
図25
次にインポート先のデータベースを指定します(図26)。
データベースを指定するコマンドは”use”で、データベースの指定は以下の通りです。
use データベース名
図26
データベースが選択されると、”Database changed”と表示されます(図27)。
図27
データベースを選択したら、今度はいよいよデータベースのインポートです(図28)。
データベースをインポートするためのコマンドは”source”で、wwwにアップロードして解凍したインポートするデータベースのパスの指定は以下の通りです。
source /home/データベースユーザー名/www/mysql***.db.sakura.ne.jp.sql
MySQL5.5のデータベースをエクスポートした場合、ファイル名を変更していなければデータベースサーバー名が指定のファイル名になります。
図28
データベースのインポートが開始されると、ログが流れていき、完了すると図29のようになります。
図29
SCPでも確認すると、使用量が0MBから増えていることが分かります(図30)。
図30
ちなみに、どういう訳か、この容量は同じファイルでもインポートする度に変わります。
wp-config.phpの編集
MySQL 5.7にデータベースをインポートできたら、今度は”wp-config.php”を編集します。
“wp-config.php”はWordpress がインストールされたフォルダの中にあります。
さくらの場合はSCPで編集できます。
編集する箇所は以下の通りです。
// ** MySQL 設定 - こちらの情報はホスティング先から入手してください。 ** //
/** WordPress のためのデータベース名 */
define('DB_NAME', 'データベース名');
/** MySQL データベースのユーザー名 */
define('DB_USER', 'データベース ユーザ名');
/** MySQL データベースのパスワード */
define('DB_PASSWORD', '接続パスワード');
/** MySQL のホスト名 */
define('DB_HOST', 'mysql***.db.sakura.ne.jp');
各項目は、SCPのデータベース一覧で作成したときに設定した内容です。
MySQL 5.7でデータベースの作成時にMySQL 5.5と以下の項目を同じにしているのであれば、MySQLのホスト名以外を編集する必要はありません。
- データベース名
- データベース ユーザー名
- 接続パスワード
このため、MySQLのホスト名では、MySQL 5.7のmysql***.db.sakura.ne.jpの***部分だけを変更します。
最後に
MySQL 5.5から5.7への移行は、メールで問い合わせを行い、必要内容を記入して送れば、さくらのサポートで代行してもらえます。
このため、自信がなければ無理に自分で行う必要はありません。
また、仮に自分で移行する場合、仮に技術的な内容について問い合わせをしても、ほとんど答えてもらえません。
ただし、サポートで代行してもらっても、動作保証はしておらず、データ形式によってはインポートしてもらえない場合もあります。
とりあえず、さくらは、MySQL 5.5からMySQL 5.7への移行は推進しているようで、対応は消極的です。
さくらでWordpressを長期間利用している人はMySQLのバージョンが5.5以下で、利用者数も多いと思うのですが、他の人はどうしたんでしょうね？
以下には、MySQL5.5から5.7へ移行するまでに色々と試したことを記載しておきます。
サポートで教わったコマンドについて
まず、さくらのサポートで教えられたコマンドは、恐らく、MySQL5.5に対応するものだったと思います。
mysql -h[データベースホスト名] -u[ユーザー名] -p[パスワード] [インポート先データベース名] < [インポート元ファイル名]
このコマンドを実行したときに表示されたエラーは以下の物でした。
ERROR: ASCII ‘\0’ appeared in the statement, but this is not allowed unless option –binary-mode is enabled and mysql is run in non-interactive mode. Set –binary-mode to 1 if ASCII ‘\0’ is expected. Query: ‘PK’.
このエラーについて調べてみると、以下のサイトがありました。
これは、最初にインポートしようとしたファイルがzip形式だからでした。
次に、解凍したファイルでインポートしようとしたところ、以下に記載する三つのメッセージが表示されました。
mysql: [Warning] Using a password on the command line interface can be insecure.
まず、1行目のエラーは、コマンドラインインターフェースでパスワードを使うのは安全ではないということでした。
ERROR 1007 (HY000) at line 21: Can’t create database ‘インポート先データベース名’; database exists
2行目のエラー1007は、既存のデータベース名があると、同じ名前のデータベース名を作れないということです。
ちなみに、違う名前でデータベースを作成して、本来のインポートすべきデータベース名を指定してもダメでした。
ERROR 1044 (42000): Access denied for user ‘ユーザー名’@’%’ to database ‘インポート先データベース名’
3行目のエラー1044は、指定したユーザー名で、インポート先データベース名のデータベースにアクセスできないということです。
外部ファイルによるパスワードの指定
1行目のエラーを解決する方法として、ユーザー名とパスワードを外部ファイルから読み込ませるという方法があります。
-
MySQL5.6のUsing a password on the command line interface can be insecureの対応
-
スクリプトでmysqlコマンドを実行すると「Warning: Using a password on the command line interface can be insecure」
上記で紹介したサイトでは、外部ファイルを説明をサラッとしています。
しかし、初心者の私にはSSHクライアントソフトで外部ファイルを作成・編集する方法が全く分かりませんでした。
SSHクライアントソフトで利用するコマンドは以下のサイトを参考にしました。
まず、さくらで外部ファイルやテキストファイルを編集する場合、viを使用します。
ファイルに日本語などを使用している場合、可能な人はvimをインストールして日本語編集をすることが出来ます。
とりあえず、今回はユーザー名とパスワードを外部ファイルに設定するので、viでmy.cnfを作成・編集します。
viのコマンドは以下のサイトを参考にしました。
外部ファイルでユーザー名とパスワードを指定するコマンドは以下の通りです。
mysql --defaults-extra-file=/tmp/my.cnf -h データベースホスト名 インポート先データベース名 < インポート元ファイル名
この場合、”tmp”フォルダを作成し、その中に”my.cnf”を作成・編集しています。
ところが、このコマンドでもエラーが表示されました。
ERROR 1045 (28000): Access denied for user ‘ユーザー名’@’IPアドレス’ (using password: NO)
試しに、外部ファイルの指定のみでMySQLにログインできるか試すことにしました。
mysql --defaults-extra-file=/tmp/my.cnf
残念ながら、外部ファイルでユーザー名とパスワードを指定しても、以下のようなエラーが表示され、ログインすらできませんでした。
ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)
インポートの成功例？
他にさくらのレンタルサーバーで大容量のデータベースをMySQL5.5から5.7に移行できた例を探してみたところ、以下のサイトを見つけました。
このサイトで使用したコマンドは以下の通りです。
mysql -h データベースホスト名 インポート先データベース名 -u ユーザー名 -p < インポート元ファイル名
ところが、このコマンドでもエラー1044が表示され、失敗に終わりました。
まぁ、参考サイトでも、その環境ではできたということなので、同じさくらのレンタルサーバーでどういう違いがあるのかは私にもわかりません。
ログインへの成功例
とりあえず、データーベースのインポートが上手くいかないので、せめてMySQLにログインする方法を調べたところ、以下のサイトを見つけました。
結果
とりあえず、上記のように色々と試して紆余曲折があった結果、無事に大容量のデータベースをインポートすることが出来ました。
もともと、MySQLを5.5から5.7に移行する目的は何だったかというと、サイトの表示速度の改善でした。
では、結果的にMySQLのバージョンをアップしてサイトの表示速度がどうなったかというと、PageSpeed Insightsで確認したところ…
モバイルでは変わらず、PCでは遅くなる
という結果でした。
そして、Wordpressにログインするまでの時間が大幅に伸びました。
これらの結果に関しては、インポートの失敗なのか、テーマが古いからなのかは分かりません。
ただ、何度試しても結果はほぼ変わらずで、たまにPCも変わらないという具合でした。
それ以外には特に問題もなく？、記事も表示されるし、プラグインも動いているようです。
今度はテーマを更新するか、変更するかでページの表示速度が改善されるかを試すしかないようです。
コメント