通常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」を使おう

##参考記事