通常WordPressのデータベースのバックアップ・復元は、phpMyAdminなどのクライアントツールを使って簡単に行うことができますが、1つのテーブルがあまりに肥大化していたり、データベースのファイルサイズが大きすぎると、書き出しに失敗することがあります。
そんな時に使う初歩的な手段の1つとして、SSH接続でデータベースをバックアップ、復元をする方法があります。普段あまりやらない作業なので、備忘録代わりに手順を書きます。
前提
- バックアップを自動化しておらず、手動作業が必要なケース
- テスト環境のWordPressデータを本番環境へ移行、またはサイトのドメインを変更したい
- データベースが大きすぎてphpMyAdminなどのクライアントツールで正しい出力ができない
- WordpPressのバージョン、MySQLのバージョンは、移行元、移行先ともに同じ状態
1.サーバにSSH接続でログイン
ターミナルを起動してSSH接続でサーバにログインします。
$ ssh -i {秘密鍵（pem,rsa）が必要なら入力} {ログインユーザ名}@｛接続先アドレス｝
2.ファイルのdump先に移動
.sqlファイルの書き出し場所を作成し、移動（ここの作業は任意）
$ mkdir backup // backupディレクトリを作成
$ cd backup // backupディレクトリに移動
3.データベースのエクスポート・バックアップ
3-1.MySQLへの接続確認
$ mysql -u {データベース名} -h {データベースホスト or IP} -p {DBユーザ名}
入力後、パスワードを求められます。パスワード入力後にコマンド画面に入ります。mysqldumpする場合にもパスワードは必要です。
3-2.ファイルとしてdumpする
一式をまとめてdump
$ mysqldump --single-transaction -u {データベース名} -h {データベースホスト or IP} -p {DBユーザ名} > {出力するファイル名}.sql
一部のテーブルを除外してdump
不要だったり、上書きしたくないDBを除外してdumpすることもできます。
$ mysqldump --single-transaction -u {データベース名} -h {データベースホスト or IP} -p {DBユーザ名} --ignore-table={データベース名.dumpから除外するテーブル名} > {出力するファイル名}.sql
オプション操作
確認作業のために使ったSQLコマンドも記載。
ターミナル上にdump出力
$ mysqldump --single-transaction -u {データベース名} -h {データベースホスト or IP} -p {DBユーザ名}
データベース内の全てのテーブルを確認
SHOW TABLES;
テーブル削除
DROP TABLE {TABLE名};
4.データベースのインポート
mysql -u {DBユーザ名} -h {データベースホスト or IP} -p {データベース名} < {インポートするファイル名}.sql
※現在地がMySQLファイルの場所と違う場合、パスを指定してインポートするファイル名を指定します
5.データベース内の書き換え
最後にデータベース内のドメイン・フルパスを書き換えます。
SQL文による置き換え
SQLコマンド画面に入って以下のコマンドを入力。
update wp_comments set comment_author_url = replace(comment_author_url, '{置き換え前のドメイン}', '{置き換え後のドメイン}'));
update wp_options set option_value = replace(option_value, '{置き換え前のドメイン}', {置き換え後のドメイン}'));
update wp_postmeta set meta_value = replace(meta_value, '{置き換え前のドメイン}', '{置き換え後のドメイン}'));
update wp_posts set post_content = replace(post_content, '{置き換え前のドメイン}', '{置き換え後のドメイン}')), guid = replace(guid, '{置き換え前のドメイン}', '{置き換え後のドメイン}'));
※自分の場合、EWWW Image Optimizerという画像をカテゴライズできるプラグインをよく使いますが、その場合"wp_ewwwio_images"というテーブルが作成され、画像のフルパスが登録されます。このフルパスの書き換えも必要となるので注意してください。
update wp_ewwwio_images set path = replace(path, '{置き換え前のドメイン}', '{置き換え後のドメイン}');
ツールを使った置き換え
SQL文で置き換えをするのは肝が冷えるため、多くの場合はGUI上で操作可能で、書き換えテストもできるDATABASE SEARCH AND REPLACE SCRIPT IN PHPというツールを使用しています。ツールの使い方自体は以下の記事で詳しく書かれています。
WordPress移行時にURLをSQLで直接一括置換はダメ！ 「Search and Replace for WordPress Databases Script」を使おう
##参考記事