はめじに

wordpressでリモートにある環境にデータベースを転送したい。転送というか同期というか。

通常の環境であれば管理者メニューからエクスポート > インポートとすればすむ話なのだけれど、今回はちょっと変わった事情があった。

その条件ってのは下記の通り

• 手元のサーバにあるワードプレスのデータベースの一部(コメントとかは上書きしないようにする)をリモートサーバに転送したい
• リモートサーバにはsshでログイン可能
• テータベースの一部のうち、post_metaにあるmeta_keyがviewsは上書きしたくない
• 転送するのは一日に一回ぐらいのそこそこのペースで転送したい

リモートホストにsshでログインできるのでsshとパイプを使ったワンライナーでやってしまおう的な。

またpost_metaのテーブルはちょっとやっかい。今回利用しているプラグインでWP-PostViewsという閲覧数を保管するプラグインがある。これは閲覧数をpost_metaにmeta_key = viewsで管理している。だからそこをハズしたい。

というわけでさっくり

mysqldump –h localdbhost –u localuser –plocaldbpassword  --add-drop-table wpdbname wp_options wp_posts wp_postmeta wp_term_relationships wp_term_taxonomy wp_terms wp_users wp_usermeta | ssh [email protected] mysql -f -h remotedbhost -u remotedbuser –premotedbpassword  remotewpdb

これでひとまずpost_meta以外はいけた。

データがちょっと複雑なpost_meta

post_metaに関してはmysqldumpにwhere句が使えるらしいので使ってみる。

次にpost_metaをうまく通してやるためにオプションを調整してやる。

mysqldump –h localdbhost –u localuser –plocaldbpassword   --add-drop-table=false --extended-insert=false --where="meta_key!='views'" mysqldump –h localdbhost –u localuser –plocaldbpassword　wp_postmeta  |  ssh [email protected] mysql -f -h remotedbhost -u remotedbuser –premotedbpassword remotewpdb

--add-drop-table=false

これは対象テーブルのdrop table – if exists文を出力に含めないようにするためのオプション。

基本的にはテーブルがあるっていう前提なのでcreate-infoごといらないかもしれない。(create文ごとなくすには—no-create-infoを付けるといい)

今回は安全に動作(運用)するために付けておくことにする。ここで与えたオプションはlong形式の—add-drop-tableなんだけど、実は引数でtrue falseが与えられるらしい。よくありがちなのは—no-add-drop-tableみたいに対応してる気がしてたんだけどそうじゃないみたい。

公式には書いてないので気がつかなかった。っていうか引数ありって本当意外。

• --add-drop-table

DROP TABLEステートメントをCREATE TABLEステートメントの前に追加します。
MySQL ::   MySQL 5.1 リファレンスマニュアル :: 7.12 mysqldump — データベースバックアッププログラム

--where=”meta_key!=’views'”

これはmeta_keyで上書きしたくない条件を指定。今回は手元のデータのうち閲覧数は上書きしたくなかった。sqlだったらすんなりwhere句書いちゃえば楽なのになーって思ったらmysqldumpでもwhereが書けるらしい。

• --where='where_condition', -w 'where_condition'

あるWHERE状態に選択された行のみダンプします。ユーザのコマンドインタープリタにとって特別なキャラクタ、もしくはスペースを含んでいる場合、状態の周りをクオートで囲まなければいけません。

MySQL :: MySQL 5.1 リファレンスマニュアル :: 7.12 mysqldump — データベースバックアッププログラム

–extended-insert=false

このオプションも引数を取るらしい。このオプションは

• --extended-insert, -e

複数のVALUESリストを含む、複数行INSERT構文を使用してください。これにより、ダンプファイルサイズを小さくし、ファイルが再ロードされる際の挿入スピードがあがります。

MySQL :: MySQL 5.1 リファレンスマニュアル :: 7.12 mysqldump — データベースバックアッププログラム

となっている。いわゆるbulk insertとかmultiple-row insertsとか言われてるあれにするようなやつですね。

insert hoge (id, hoge) values (1, hoge), (2, hoge1), (3, hoge), (4, hoge);

で、コレって結構早くする上では重要なものなのです。このオプションがonでないと、bulk insertが有効になっていないと、かなり遅くなります。なのですが後述するmysql -fと組み合わさって今回の運用上はoffにしてないとうまく目的を達成できません。

今回の運用上、というところはどういったところなのか。

さっきの例だとid, hogeという二つのカラムに対して四つの行を一つのinsert文でinsertしようとしてる。で、カラムの名前から分かるように、idというのはpkeyなのでunique制約がある。となるとき、上のinsertだとduplicateでinsertできないことがある。もともとid=1な行があるときとか。で、そういうのは無視しながら必要な行だけをinsertしたいので、これらのinsert文を分けて書く必要がある。なのでこのオプションを有効にしてやる。するとこんな感じのinsert文になる。

insert hoge (id, hoge) values (1, hoge);

insert hoge (id, hoge) values (2, hoge1);

insert hoge (id, hoge) values  (3, hoge);

insert hoge (id, hoge) values (4, hoge);

これでid=1は通らなくても他のinsert文は通ってくれるので期待通りの結果になるかな！

mysql –f

受け側のオプション。エラーがあっても処理を続行する。先のcreate文ですでにtableが存在しても続けたり、insert文でduplicateな行だったとしても続けてくれる。おかげさまでイケるようになります。

おわりに

これでcronに入れとけばテキトウに同期してくれそう。sshでパスワードのプロンプトをなくすために公開鍵をやりとりする、とかやる必要はあるけれど。

でもこれできっと便利。しらばらくは手でやってみるけど。