2014年1月28日火曜日

WordPressの重複した投稿を、MySQLからまとめて削除するSQL


WordPressの重複した投稿を削除したいときに使えるSQLです。
重複を削除するプラグインはいくつかありますが、投稿の数が膨大になると重すぎて使えなくなってしまいます。
そこで、MySQLのレコードをSQLで直接削除してしまおうという試みです。
※必ずバックアップを取ってから実行しましょう。

重複した投稿を探すSQL

SELECT a.ID, a.post_title, a.post_type, a.post_status
FROM wp_posts AS a
   INNER JOIN (
      SELECT post_title, MIN( id ) AS min_id
      FROM wp_posts
      WHERE post_type = 'post'
      AND post_status = 'publish'
      GROUP BY post_title
      HAVING COUNT( * ) > 1
   ) AS b ON b.post_title = a.post_title
AND b.min_id <> a.id
AND a.post_type = 'post'
AND a.post_status = 'publish';


重複した投稿を削除するSQL

DELETE a.*
FROM wp_posts AS a
   INNER JOIN (
      SELECT post_title, MIN( id ) AS min_id
      FROM wp_posts
      WHERE post_type = 'post'
      AND post_status = 'publish'
      GROUP BY post_title
      HAVING COUNT( * ) > 1
   ) AS b ON b.post_title = a.post_title
AND b.min_id <> a.id
AND a.post_type = 'post'
AND a.post_status = 'publish';

かんたんですね!

参考)Find and delete duplicate posts in WordPress
http://wpkrauts.com/2013/find-and-delete-duplicate-posts-in-wordpress/

2014年1月24日金曜日

MHA for MySQLをセットアップして、お手軽にMySQLのフェイルオーバーを実現する方法


言わずと知れた、MySQLのフェイルオーバーツール、MHA for MySQL( https://code.google.com/p/mysql-master-ha/ )をお手軽に使う方法を紹介します。


フェイルオーバー時に、アプリサーバからMySQLへの接続先を変更する方法として、”アプリサーバへ /etc/hosts を rsync で配布する” といった方法をとります。
いままでは、MySQLサーバのIPアドレスの付け替えや、HAProxyの設定変更を自動で行う、などといった方法がありましたが、これは、敷居低めで実装できるのが嬉しいです。

動作イメージは下のようになります。
mysql1が壊れたとき、mysql2が自動でマスターに昇格してサービスを継続します。


詳しく説明します。
app1、app2の /etc/hosts には、MySQLマスターのホスト名 mysql-master.myservice.com と mysql1 のIPアドレスを紐づける記述しておきます。
app1、app2のアプリケーションは、このホスト名 mysql-master.myservice.com 宛にMySQL接続をします。
フェイルオーバー時には、mysql-master.myservice.com の IPアドレスを mysql2 のものに変更した /etc/hosts を rsync で差し替えます。

サーバIDとIPアドレスはこのようになっているとします。
app1(100.100.100.1) :アプリケーション1 + MHAマネージャ
app2(100.100.100.2) :アプリケーション2
mysql1(200.200.200.1) :MySQLマスタ
mysql2(200.200.200.2) :MySQLスレーブ

試験環境は、CentOS 6.5、MySQL 5.6です。
MySQLは、既にインストールされている状態ではじめます。
MHAマネージャは、app1サーバにセットアップします。


◆ 事前準備

1) sshのrootユーザで各サーバにログインできるようにしておく。
app1 -> app1, app2, mysql1, mysql2
mysql1 -> mysql2
mysql2 -> mysql1

2) MySQLの設定を編集(mysql1, mysql2)
/etc/my.cnfの[mysqld]に、log-bin、server-idを設定。

default-character-setの設定はオフにしておきます。
vim /etc/my.cnf
[client]
#default-character-set=utf8

3) MySQL接続ユーザ、レプリケーション用ユーザを作る(mysql1)
MHAマネージャから、rootユーザでMySQLアクセスできるようにしておく。
mysql1 -> mysql2 レプリケーションの為のユーザを作る。

4) MySQLマスタ -> スレーブのレプリケーションを構築しておく。(mysql1, mysql2)
※ 準同期レプリケーションを設定しておくと、レプリケーションの安全性が高まります。
参考) 準同期レプリケーションの実装方法 - サーバサイドWiki - Confluence
※ MySQL 5.6では、mysql2の/var/lib/mysql/auto.cnfを削除してrestartしておく。
(masterとslaveとで、server-uuidがかぶっていなければOK。)
※ 今回は、スレーブにread_onlyは付けないでおきました。

5) MySQLホスト名を /etc/hosts に記述する。(app1, app2)
# vim /etc/hosts
---
200.200.200.1 mysql-master.myservice.com
---

アプリケーションからは、mysql-master.myservice.com 宛に接続するようにしておきましょう。



◆ MHAのインストール

1) mysql1, mysql2サーバに、MHAノード(mha4mysql-node)をインストール

# cd /usr/local/src
# yum -y install epel-release
# curl -o mha4mysql-node-0.56-0.el6.noarch.rpm "http://www.mysql.gr.jp/frame/modules/bwiki/index.php?plugin=attach&pcmd=open&file=mha4mysql-node-0.56-0.el6.noarch.rpm&refer=matsunobu"
# yum localinstall mha4mysql-node-0.56-0.el6.noarch.rpm
# rpm -ql mha4mysql-node

2) app1サーバに、MHAマネージャ(mha4mysql-manager)をインストール
※ 依存ライブラリを先にインストールする必要があります。先にMHAノードのインストールをします。

# cd /usr/local/src
# yum -y install epel-release
# curl -o mha4mysql-node-0.56-0.el6.noarch.rpm "http://www.mysql.gr.jp/frame/modules/bwiki/index.php?plugin=attach&pcmd=open&file=mha4mysql-node-0.56-0.el6.noarch.rpm&refer=matsunobu"
# curl -o mha4mysql-manager-0.56-0.el6.noarch.rpm "http://www.mysql.gr.jp/frame/modules/bwiki/index.php?plugin=attach&pcmd=open&file=mha4mysql-manager-0.56-0.el6.noarch.rpm&refer=matsunobu"
# yum localinstall mha4mysql-node-0.56-0.el6.noarch.rpm
# yum localinstall mha4mysql-manager-0.56-0.el6.noarch.rpm
# rpm -ql mha4mysql-manager

◆ MHAの設定ファイルを用意

app1サーバで用意します。

設定ファイル類を置くディレクトリを作成
# mkdir /usr/share/masterha

ログファイル用のディレクトリを作成
# mkdir -p /var/log/masterha/mysql/

MHA設定ファイルを書く。
# vim /usr/share/masterha/masterha_default.cnf
---
[server default]

# mysql user and password
user=root
password=xxxxxxxxxxxxxxxxxxxxxxxxxxxx
ssh_user=root

# working directory on the manager
manager_workdir=/var/log/masterha/mysql

# working directory on MySQL servers
remote_workdir=/var/log/masterha/mysql

# MySQL TCP connection check
ping_type=CONNECT

# failover script
master_ip_failover_script=/usr/share/masterha/master_ip_failover.sh
master_ip_online_change_script=/usr/share/masterha/master_ip_failover.sh

[server1]
hostname=200.200.200.1

[server2]
hostname=200.200.200.2
candidate_master=1
---


◆  フェイルオーバー時に実行するスクリプトを用意

app1サーバで用意します。
※ rootによるssh疎通を要チェックしておきましょう。

rsyncで /etc/hosts を配布するスクリプトを設置します。
このスクリプトが、フェイルオーバー時に実行されます。
# vim /usr/share/masterha/master_ip_failover.sh
---
#!/bin/bash
DIR=/usr/share/masterha
if [ "$1" = "--command=start" ]; then
  # app1
  rsync -avz ${DIR}/hosts 100.100.100.1:/etc/hosts
  # app2
  rsync -avz ${DIR}/hosts 100.100.100.2:/etc/hosts
fi
---
# chmod 755 /usr/share/masterha/master_ip_failover.sh

フェイルオーバー時に app1,app2 サーバへ配布する /etc/hosts ファイルを設置します。
mysql-master.myservice.com のIPアドレスを mysql2のモノにします。
# vim hosts
---
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
200.200.200.2 mysql-master.myservice.com
---


◆ MHAをスタート

app1サーバでMHAを実行します。

sshコネクションをチェック
# masterha_check_ssh --conf=/usr/share/masterha/masterha_default.cnf

MySQLコネクションをチェック
# masterha_check_repl --conf=/usr/share/masterha/masterha_default.cnf

MHAマネージャの起動を試す(フォアグランド)
# masterha_manager --conf=/usr/share/masterha/masterha_default.cnf
[Ctrl + c]で抜けます。

MHAマネージャの起動(バックグランド)
# nohup masterha_manager --conf=/usr/share/masterha/masterha_default.cnf < /dev/null > /var/log/masterha/mysql/manager.log 2>&1 &
# ps aux | grep masterha_manager
# tail -f /var/log/masterha/mysql/manager.log
---
FriJan 24 23:35:09 2014 - [info] Ping(CONNECT) succeeded, waiting until MySQL doesn't respond..
---
上記のように出ているでしょうか?出ていたらおkです。
mysql1のmysqlをストップしたり、アクセス制限を掛けたりしてフェイルオーバーできるかチェックしてみましょう。


※ MHAマネージャを停止したいときは?以下のコマンドを叩きます。
#  masterha_stop --conf=/usr/share/masterha/masterha_default.cnf

※ フェイルオーバーを実行した後は下記のファイルを削除してから、MHAを立ち上げる必要があります。
成功していた場合
# rm /var/log/masterha/mysql/masterha_default.failover.complete
失敗していた場合
# rm /var/log/masterha/mysql/masterha_default.failover.error


◆ MHAマネージャをデーモン化

Upstart を使って、MHAマネージャをデーモン化してみます。
なにかしらの原因で、MHAマネージャが落ちたときに自動で立ち上げ直してくれます。
一般的には、daemontoolsが使われていますが、Upstartの方がデフォルトで入っているし、らくちんです。
ここでは "respawn limit"によりプロセスの再起動回数を制限します。 10秒間に5回以上再起動が起きた場合には、自動的に停止され、再起動されなくなります。

設定ファイルを用意
# vim /etc/init/mha.conf
---
description "MySQL-MHA"
author "Takeshi Yako <yako.takeshi@googlemail.com>"

start on runlevel [2345]
stop on runlevel [016]

chdir /usr/share/masterha/
respawn
respawn limit 5 10
exec  /usr/bin/masterha_manager --conf=/usr/share/masterha/masterha_default.cnf >> /var/log/masterha/mysql/manager.log 2>&1
---

設定を反映します。
# initctl reload-configuration
# initctl list | grep mha

起動します。
# initctl start mha

確認。
# initctl list | grep mha
# ps auxf | grep masterha_manager
# tail -f /var/log/masterha/mysql/manager.log

停止するときは?
# initctl stop mha

参考)Upstart を使ってお手軽 daemon 化
http://heartbeats.jp/hbblog/2013/02/upstart-daemon.html


◆ まとめ

MySQL for MHAをセットアップして、/etc/hostsや、rsyncなどの枯れたモノを使ってMySQLをフェイルオーバできるようにしました。
インフラ構成が比較的ちいさなサービスなどで、”MySQLのHA構成が欲しい”という要望があったときに、使えたりしそうです。
試してみてください。

2014年1月20日月曜日

Linux kernelのTCP_TIMEWAIT_LEN を修正して、TIME_WAITを減らす


大量トラフィックを捌くサーバにありがちなのですが、
# netstat | grep tcp | wc -l
の結果が60000 とかになっていると、ポートが枯渇してTCPコネクションが詰まっている状態です。
そこで、TIME_WAIT となったセッションを早く終了させ、ポートの空きを作って、ポートの枯渇状態を解決します。
Linux kernelのTCP_TIMEWAIT_LENを60 -> 5にします。

環境は、CentOS6.5です。

rpmのビルドに必要なモジュールのインストール
# yum -y install rpm-build.x86_64 redhat-rpm-config.noarch patchutils.x86_64 elfutils-libelf-devel.x86_64 binutils-devel.x86_64 hmaccalc.x86_64 rng-tools.x86_64
# yum -y install redhat-rpm-config patchutils xmlto asciidoc binutils-devel newt-devel python-devel perl-ExtUtils-Embed bison flex hmaccalc elfutils-devel audit-libs-devel  bfa-firmware

SRPM(カーネル)の入手と解凍
# cd /usr/local/src
# wget http://vault.centos.org/6.5/updates/Source/SPackages/kernel-2.6.32-431.3.1.el6.src.rpm
# rpm -ivh kernel-2.6.32-431.3.1.el6.src.rpm

パッチの作成
# cd ~/rpmbuild/SOURCES/
# cp linux-2.6.32-431.3.1.el6.tar.bz2 /tmp
# cd /tmp
# tar xjvf linux-2.6.32-431.3.1.el6.tar.bz2
# mv linux-2.6.32-431.3.1.el6 linux-2.6.32-431.3.1.namibuild.el6
# cd linux-2.6.32-431.3.1.namibuild.el6

Makefileファイルを編集
# emacs Makefile
EXTRAVERSION =
->
EXTRAVERSION = -431.3.1.namibuild.el6

tcp.hファイルを編集
# emacs include/net/tcp.h
#define TCP_TIMEWAIT_LEN (60*HZ)
->
#define TCP_TIMEWAIT_LEN (5*HZ)

固めてコピー
# cd ..
# tar cjvf linux-2.6.32-431.3.1.namibuild.el6.tar.bz2 linux-2.6.32-431.3.1.namibuild.el6
# cp linux-2.6.32-431.3.1.namibuild.el6.tar.bz2 ~/rpmbuild/SOURCES/
# cd ~/rpmbuild/SPECS/

kernel.specファイルを編集
# emacs kernel.spec

%define distro_build 431.3.1
->
%define distro_build 431.3.1.namibuild

%define kversion 2.6.32-431.3.1.el6
->
%define kversion 2.6.32-431.3.1.namibuild.el6

Source0: linux-2.6.32-431.3.1.el6.tar.bz2
->
Source0: linux-2.6.32-431.3.1.namibuild.el6.tar.bz2

カーネルをビルドする
結構時間がかかります
# rpmbuild -bb --with firmware kernel.spec

gpg: keyring `./secring.gpg' created
gpg: keyring `./pubring.gpg' created
でストップしたら、別ターミナルでつないで、以下のコマンドを実行
# rngd -r /dev/urandom

インストール
# cd ../RPMS/x86_64/
# rpm -Uvh kernel-2.6.32-431.3.1.namibuild.el6.x86_64.rpm kernel-devel-2.6.32-431.3.1.namibuild.el6.x86_64.rpm kernel-headers-2.6.32-431.3.1.namibuild.el6.x86_64.rpm kernel-firmware-2.6.32-431.3.1.namibuild.el6.x86_64.rpm

確認します
# cat /proc/version
Linux version 2.6.32-431.3.1.el6.x86_64 (mockbuild@c6b10.bsys.dev.centos.org) (gcc version 4.4.7 20120313 (Red Hat 4.4.7-4) (GCC) ) #1 SMP Fri Jan 3 21:39:27 UTC 2014

リブートで反映です
# reboot

ベンチマークをしたりして、ポートの空きが早くできているか確認してみましょう。

参考)TIME_WAIT状態のTCPコネクションを早く終了させるべくKernelをリビルド
http://d.hatena.ne.jp/rx7/20131204/p1

※ See also.
革命の日々! Linuxカーネルの「TCP_TIMEWAIT_LEN」変更は無意味? の件について
http://mkosaki.blog46.fc2.com/blog-entry-1292.html
Linux - ぜんぶTIME_WAITのせいだ! - Qiita
http://qiita.com/kuni-nakaji/items/c07004c7d9e5bb683bc2
isucon3_cheatsheet/02.kernel.md at master · sonots/isucon3_cheatsheet
https://github.com/sonots/isucon3_cheatsheet/blob/master/02.kernel.md

2014年1月12日日曜日

CentOS 6.4にPlaggerをインストール (2014年版)

Plaggerをインストールします。(2014年版)
環境は、Windows8 + Vagrantに立てたCentOS 6.4です。
RSSを取得して、csvにするところまでやります。

参考URL


Windows8にVagrantをインストールしてCentOSを入れる
http://d.hatena.ne.jp/fitflavor/20131020/1382250424

CentOS6.3にperlbrew,Perl 5.16, cpanmを一気にインストールする方法
http://dqn.sakusakutto.jp/2012/08/centos63_perlbrew_perl_cpanm.html


yumライブラリをインストール


# yum -y install openssl-devel expat-devel db4-devel libxml2-devel perl gcc make git


Perlのインストール


perlbrewを利用して、使用するPerlのバージョンを上げます。
※ CentOSにデフォルトでインストールされているPerlだと、コアモジュール不足でダメだそうです。 https://github.com/miyagawa/cpanminus/issues/303

# curl -Lk  http://xrl.us/perlbrewinstall | bash
# echo "source ~/perl5/perlbrew/etc/bashrc" >> ~/.bashrc
# source ~/.bashrc
# source ~/perl5/perlbrew/etc/bashrc
# perlbrew available
# perlbrew install perl-5.16.3

switchします。
# perlbrew switch  perl-5.16.3

cpanmをインストール。
# perlbrew install-cpanm

Perlのバージョンを確認。
# perl -v
This is perl 5, version 16, subversion 3 (v5.16.3) built for x86_64-linux

Plaggerのインストール


cpanモジュールをインストール。
# cpanm local::lib
# cpanm Module::Install
# cpanm Test::Base
# cpanm YAML
# cpanm Text::CSV_PP
# cpanm XML::Simple
# cpanm XMLRPC::Lite
# LANG=C cpanm Crypt::SSLeay
# LANG=C cpanm --force Net::MovableType
# cpanm Module::Install::TestBase

Plaggerをインストール。
# cd /usr/local/share
# git clone https://github.com/miyagawa/plagger.git
# cd plagger/
# cpanm --installdeps .

シェバンを変更。
# vim plagger
#!/usr/bin/perl
->
#!/usr/bin/env perl

Plaggerのバージョンを確認。
# ./plagger -v
Plagger version 0.7.17



Plaggerの動作チェック


http://d.hatena.ne.jp/otomojamjam/rss フィードからtitileを抜き出して、/tmp/plagger-test/rss.csv に落とし込みます。

Plaggerが使うディレクトリを作成。
# mkdir -p /tmp/plagger-test/

設定ファイルを書きます。
# vim /tmp/plagger-test/config.yaml
global:
  assets_path: /tmp/plagger-test/assets
  timezone: Asia/Tokyo
  cache:
    base: /tmp/plagger-test
  log:
    level: debug

plugins:
  - module: Subscription::Config
    config:
      feed:
        - url: http://d.hatena.ne.jp/otomojamjam/rss

  - module: Filter::Rule
    rule:
      module: Deduped

  - module: Publish::CSV
    config:
      dir: /tmp/plagger-test
      encoding: utf-8
      filename: rss.csv
      mode: append
      column:
       - title

Plaggerを実行させます。
# /usr/local/share/plagger/plagger -c /tmp/plagger-test/config.yaml

rssを取得できているでしょうか?
# cat /tmp/plagger-test/rss.csv
"紅白「あまちゃん 第157話」"
"PITINN4デイズ〜紅白リハ"
"お腹いっぱいかもだけど年末年始いろいろ出ま〜〜す"
"紅白あまちゃんスペシャルビッグバンド出演決定 メンバー詳細です"
"Quartets in Bangkok"

※ Dedupedオプションを使用しているので、一度読み込んだフィードは再度読み込みません。
再読み込みしたい場合は、/tmp/plagger-test/Deduped.dbファイルを削除しましょう。


Plaggerをcronで定期実行



環境変数のPATHを表示します。
# env | grep PATH
MANPATH=/root/perl5/perlbrew/perls/perl-5.16.3/man:
PATH=/root/perl5/perlbrew/bin:/root/perl5/perlbrew/perls/perl-5.16.3/bin:/sbin:/bin:/usr/sbin:/usr/bin
PERLBREW_MANPATH=/root/perl5/perlbrew/perls/perl-5.16.3/man
PERLBREW_PATH=/root/perl5/perlbrew/bin:/root/perl5/perlbrew/perls/perl-5.16.3/bin

PATH=の行をcrontabに記述します。
さらに、cron実行結果を/var/log/messagesに出力するようにしました。
# crontab -e
PATH=/root/perl5/perlbrew/bin:/root/perl5/perlbrew/perls/perl-5.16.3/bin:/sbin:/bin:/usr/sbin:/usr/bin
* * * * * /usr/local/share/plagger/plagger -c /tmp/plagger-test/config.yaml 2>&1 | logger -t plagger-test

実行できているでしょうか?
# tail -f /var/log/messages



まとめ


Perlの実行環境を整えて、Plaggerを定期実行できるようにしました。

Perl・Plaggerの構築方法は、数年前と変わらないです。
perlbrew・cpanmのおかげですね。