読者です 読者をやめる 読者になる 読者になる

14Room

みんな泣きながらオトナになったんだ。

mysqldumpslowの結果をslackに投げてみた

概要

以前、mysqlのslow query数をslackに投稿させていましたが、数だけでなく質も見ないとダメだなということで、今回はmysqldumpslowによる統計結果を投稿させるようにしました。

mysqldumpslow

MySQLに付属しているツールで、スロークエリログを集計してくれます。

mysqldumpslow実行

例)

mysqldumpslow -s t mysql-slow.log

mysqldumpslow実行結果

回数、平均実行時間などを総消費時間数順に表示してくれます。

Reading mysql slow query log from mysql-slow.log
Count: 1179  Time=1.88s (2222s)  Lock=0.00s (0s)  Rows=10.0 (11790), naked[naked]@33hosts
  SELECT  `timeline_threads`.* FROM `timeline_threads` INNER JOIN `naked_friends` ON `naked_friends`.`naked_id` = N and `naked_friends`.`friend_naked_id` = `timeline_threads`.`naked_id` WHERE `timeline_threads`.`deleted_at` IS NULL ORDER BY `timeline_threads`.`created_at` DESC, `timeline_threads`.`id` DESC LIMIT N

Count: 88  Time=17.08s (1502s)  Lock=0.00s (0s)  Rows=1.0 (88), naked[naked]@24hosts
  SELECT COUNT(DISTINCT `naked_items`.`id`) FROM `naked_items` INNER JOIN `naked_item_countries` ON `naked_item_countries`.`naked_item_id` = `naked_items`.`id` LEFT OUTER JOIN `nakeds` ON `nakeds`.`id` = `naked_items`.`naked_id` WHERE `naked_item_countries`.`country_id` = 'S' AND `nakeds`.`banned_at` IS NULL AND `nakeds`.`deleted_at` IS NULL AND `naked_items`.`target_type` != 'S' AND (`naked_items`.deleted_at IS NULL)

Count: 76  Time=9.09s (690s)  Lock=0.00s (0s)  Rows=0.8 (59), naked[naked]@[10.10.0.4]
  SELECT  `nakeds`.* FROM `nakeds`  WHERE `nakeds`.`reset_password_token` = 'S' LIMIT N

・
・
・
・
・

slack.sh

mysqldumpslowの結果を全部送ると見切れないので上から3つまでの結果をmessageに入れて投稿するシェルスクリプトを作りました。

#!/bin/bash
date=`date '+%Y-%m-%d'`
server_name=`hostname`
url="https://hooks.slack.com/services/zzzzzzz/xxxxxx/123456789aaa?parse=full"

num=`mysqldumpslow -s t /var/log/mysql/mysql-slow.log | head -8`
message="($date)  $server_name のslow-query top3は\n \`\`\` $num \`\`\` です。"
payload="payload={\"text\": \"${message}\", \"username\": \"naked\"}"
curl --data "${payload}" ${url}

定期実行

cronで定期実行します。

# 
# m h  dom mon dow   command
30 6 * * * /root/work/slack.sh

あとがき

この仕組みで今度こそ他のメンバーの興味を引ければと考えています。。。

MySQL+MHA+HAproxy+consul環境構築ログ

MySQL

MHAで使う万能ユーザを用意します。

grant all privileges on *.* to mha@'10.%' identified by 'mhapassword';

mysqlチェック用ユーザを作成

grant select on *.* to haproxy@'10.%';]

MHA

準備

mha manager

ssh-keygen -t rsa -f /root/.ssh/id_rsa -q -N ""
cat /root/.ssh/id_rsa.pub >> /root/.ssh/authorized_keys

mha node(master + slave db全台)

mkdir /root/.ssh/
vim /root/.ssh/id_rsa
vim /root/.ssh/authorized_keys
chmod 400 /root/.ssh/id_rsa
chmod 600 /root/.ssh/authorized_keys
chmod 700 /root/.ssh/

MHA install

manager

apt-get install libdbd-mysql-perl
apt-get install libconfig-tiny-perl
apt-get install liblog-dispatch-perl
apt-get install libparallel-forkmanager-perl
wget https://mysql-master-ha.googlecode.com/files/mha4mysql-manager_0.55-0_all.deb
wget https://mysql-master-ha.googlecode.com/files/mha4mysql-node_0.54-0_all.deb
dpkg -i mha4mysql-node_0.54-0_all.deb
dpkg -i mha4mysql-manager_0.55-0_all.deb

node(master + slave db全台)

apt-get install libdbd-mysql-perl
wget https://mysql-master-ha.googlecode.com/files/mha4mysql-node_0.54-0_all.deb
dpkg -i mha4mysql-node_0.54-0_all.deb

config

コンフィグファイル作成

/etc/mha.conf

[server default]
user=mha
password=mhapass
manager_workdir=/var/lib/mha
manager_log=/var/log/mha.log
remote_workdir=/var/lib/mha
repl_user=repl
repl_password=replpass
ssh_user=root
ssh_port=20022
master_ip_failover_script=/var/lib/mha/master_ip_failover_script.sh

[server1]
hostname=192.168.0.182
[server2]
hostname=192.168.0.76
[server3]
hostname=192.168.0.181

/var/lib/mha/master_ip_failover_script.sh

#!/bin/bash -u

OPT=$(getopt -q -o a -l command:,ssh_user:,orig_master_host:,orig_master_ip:,orig_master_port:,new_master_host:,new_master_ip:,new_master_port:,new_master_user:,new_master_password: -- "$@")


eval set -- "$OPT"
MODE=none
NEW_IP=0.0.0.0
NEW_PORT=0

while true
do
    case "$1" in
    --command)
        if [ "$2" == "start" ]; then
            MODE=$2
        elif [ "$2" == "status" ]; then
            exit 0
        elif [ "$2" == "stop" -o "$2" == "stopssh" ]; then
            exit 0
        else
            exit 0
        fi
        shift 2
        ;;
    --new_master_ip)
        if [ $MODE == "start" ]; then
            NEW_IP=$2
        fi
        shift 2
        ;;
    --new_master_port)
        if [ $MODE == "start" ]; then
            NEW_PORT=$2
        fi
        shift 2
        ;;
    --)
        shift
        break
        ;;
    *)
        shift
        ;;
    esac
done

case "$MODE" in
start)
    # 新マスター登録処理
    curl -q -XPUT -d "${NEW_IP}" http://127.0.0.1:8500/v1/kv/service/mha/ip
    curl -q -XPUT -d "${NEW_PORT}" http://127.0.0.1:8500/v1/kv/service/mha/port
    exit 0
    ;;
*)
    exit 0
    ;;
esac

確認

SSH接続

masterha_check_ssh --conf=/etc/mha.conf

下記のように表示されればOK

[info] All SSH connection tests passed successfully.

mysql レプリケーション確認

masterha_check_repl --conf=/etc/mha.conf

下記のように表示されればOK

192.168.0.76 (current master)
 +--192.168.0.182
 +--192.168.0.181

Mon Aug  3 08:03:35 2015 - [info] Checking replication health on 192.168.0.182..
Mon Aug  3 08:03:35 2015 - [info]  ok.
Mon Aug  3 08:03:35 2015 - [info] Checking replication health on 192.168.0.181..
Mon Aug  3 08:03:35 2015 - [info]  ok.
Mon Aug  3 08:03:35 2015 - [warning] master_ip_failover_script is not defined.
Mon Aug  3 08:03:35 2015 - [warning] shutdown_script is not defined.
Mon Aug  3 08:03:35 2015 - [info] Got exit code 0 (Not master dead).

MySQL Replication Health is OK.

起動

slave追加時

slaveサーバ追加後、/etc/mha.confの[server x]を追加、masterha_managerのプロセスを再起動すればOK

HAproxy

インストール(全APPサーバにて)

apt-get update
apt-get install haproxy

config

/etc/haproxy/haproxy.cfg

global
    log     127.0.0.1 local2
    chroot      /var/lib/haproxy
    pidfile     /var/run/haproxy.pid
    maxconn     10000
    user        haproxy
    group       haproxy
    daemon
    stats socket    /var/lib/haproxy/stats

defaults
    mode        tcp
    log     global
    option      tcplog
    retries     3
    timeout connect 10s
    timeout client  1m
    timeout server  1m

consul

インストール(全サーバにて)

cd /usr/local/src/
wget https://dl.bintray.com/mitchellh/consul/0.5.2_linux_amd64.zip
unzip 0.5.2_linux_amd64.zip
mv consul /usr/local/bin/

起動スクリプト

は無いのでupstartに登録しましょう

mhaサーバ(192.168.0.77)

description "consul"
author "Your Name <uematsu@kiheitai.co.jp>"

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

chdir /var/lib/consul
respawn limit 5 60
exec consul agent -server -bootstrap-expect 1 -client=127.0.0.1 -dc=mha -node=`hostname` -data-dir=/var/lib/consul -bind=0.0.0.0 >> /var/log/consul.log 2>&1

mysqlサーバ

description "consul"
author "Your Name <uematsu@kiheitai.co.jp>"

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

chdir /var/lib/consul
respawn limit 5 60
exec  consul agent -server -dc=mha -node=`hostname` -config-dir=/etc/consul -data-dir=/var/lib/consul -bind=0.0.0.0 -client=127.0.0.1 -join=192.168.0.77 >> /var/log/consul.log 2>&1

appサーバ

description "consul"
author "Your Name <uematsu@kiheitai.co.jp>"

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

chdir /var/lib/consul
respawn limit 5 60
exec consul agent -dc=mha -node=`hostname` -data-dir=/var/lib/^Cnsul -bind=0.0.0.0 -client=127.0.0.1 -join=192.168.0.77

ちなみに一回落とすとleaderを選出する機能がちゃんと働かないので、データディレクトリを削除してからあげ直します。 rm -fr /var/lib/consul/*

mysql チェック

{
    "service": {
        "name": "mysql",
        "tags": ["mysql"],
        "port": 3306,
        "check": {
            "script": "mysql -u haproxy -h 127.0.0.1 -P 3306 -e 'select 1' >/dev/null 2>&1",
            "interval": "5s"
        }
    }
}

consul-template

インストール(全APPサーバにて)

cd /usr/local/src/
wget https://github.com/hashicorp/consul-template/releases/download/v0.10.0/consul-template_0.10.0_linux_amd64.tar.gz
tar xvzf consul-template_0.10.0_linux_amd64.tar.gz
mv consul-template_0.10.0_linux_amd64/consul-template /usr/local/bin/
chown root: /usr/local/bin/consul-template

config

global
    log     127.0.0.1 local2
    chroot      /var/lib/haproxy
    pidfile     /var/run/haproxy.pid
    maxconn     10000
    user        haproxy
    group       haproxy
    daemon
    stats socket    /var/lib/haproxy/stats

defaults
    mode        tcp
    log     global
    option      tcplog
    retries     3
    timeout connect 10s
    timeout client  1m
    timeout server  1m

listen mysql-master
    bind 127.0.0.1:3306
    mode tcp
    option mysql-check user haproxy
    server master {{key "service/mha/ip"}}:{{key "service/mha/port"}} check port {{key "service/mha/port"}} inter 2000 fall 5

listen mysql-slave
    bind 127.0.0.1:3307
    mode tcp
    option mysql-check user haproxy
    balance leastconn{{range service "mysql"}}
    server {{.Node}} {{.Address}}:{{.Port}} check port {{.Port}} inter 2000 fall 5{{end}}

起動

consul-template -consul=127.0.0.1:8500 -template=/etc/haproxy/haproxy.cfg.ctmpl:/etc/haproxy/haproxy.cfg:"service haproxy reload"

起動シーケンス

  1. 全ノードでconsul起動
  2. appでconsul-template起動
  3. mhaノードでmha manager起動
  4. master-db情報を初回登録する
curl -XPUT -d '192.168.151.101' http://127.0.0.1:8500/v1/kv/service/mha/ip
curl -XPUT -d '3306' http://127.0.0.1:8500/v1/kv/service/mha/port

upstartでdaemon化してみよう

概要

OSSの中にはinitスクリプトの無いモノが少なく無いですが、upstartで手軽にdaemon化してしまいましょう。ちなみにdaemon化したプロセスが不意に落ちた場合も自動で立ち上げ直してくれる機能もあります。ここではMHAを例にdaemon化してみました。

install

apt-get update
apt-get install upstart

設定

設定ファイル

/etc/init/mha.conf

内容

description "MHA"
author "Your Name <uematsu@kiheitai.co.jp>"

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

chdir /opt/masterha
respawn limit 5 60
exec  /usr/bin/masterha_manager --conf=/etc/mha.conf >> /var/log/masterha/mha.log 2>&1

反映

initctl reload-configuration
initctl list | grep mha

mha stop/waitingと出力されればOK

起動

initctl start mha

期待値 mha start/running, process 10065

initctl list | grep mha

期待値 mha start/running, process 10065

終了

initctl stop mha

HTTP load balancingで海を跨いだ負荷分散をしてみた

概要

GCPのHTTP load balancingには一つのIPアドレス複数のリージョンに負荷分散できる機能があります。

Google Cloud Platform Blog: Unveiling scalable HTTP load balancing across cloud regions

これを上手く使えば、ニューヨークにいる人にはアメリカのサーバに、フランクフルトにいる人はEUのサーバに、そして東京にいる人はアジアのサーバに導いて快適なアクセス環境を提供できるのでは?という夢が広がるので検証してみました。

設定方法

  1. 各リージョンにサーバを構築
  2. 各リージョンにインスタンスグループを作成、サーバを追加。
  3. 上記のインスタンスグループをHTTP load balancerのバックエンドサービスに追加。

以上、簡単ですね。

結果

普通は

一つのリージョンにサーバを構築してアクセスを受けるので、下の図のように世界各地からアクセスが一つのリージョンに集まっています。 f:id:naked123:20160104185735p:plain

設定後

下の図のようにアジア圏の人はアジアのサーバに、北アメリカの人はアメリカのサーバに、ヨーロッパの人はEUのサーバにルーティングされているようです。 f:id:naked123:20160104192356p:plain

あとがき

ただし、中央にDBを持って書き込みを行うようなシステムの場合、話はこんなに単純では無いですね。 マルチマスター構成が取れるDBなら良いですが、普通はどこかの一つのリージョンにマスターDBを固定しなければなりません。 taptripの場合はDBをマスター、スレーブ構成で持ち、Slaveを各リージョンに配置して引き続き検証を続けたいと思います。

DKIM導入

概要

送信したメールがSPAM扱いされるので、証明書による送信元証明を付加しました。

インストール

インストール sudo apt-get install opendkim opendkim-tools

ディレクトリ用意 mkdir -p /etc/opendkim/keys/naked.com

鍵生成

cd /etc/opendkim/keys/naked.com
opendkim-genkey -d sample.com -s dkimselector
chown -R opendkim:opendkim /etc/opendkim/keys

サーバ設定

Syslog           yes
SyslogSuccess   yes
LogWhy  yes
UMask           002
Mode            sv
Domain                  naked.com
Selector                default
SOCKET                  inet:8891@localhost
UserID  opendkim:opendkim
KeyTable refile:/etc/opendkim/KeyTable
SigningTable refile:/etc/opendkim/SigningTable
InternalHosts refile:/etc/opendkim/TrustedHosts
SOCKET="inet:8891@localhost"

KeyTable設定

dkimselector._domainkey.naked.com naked.com:dkimselector:/etc/opendkim/keys/naked.com/dkimselector.private

SigningTableの設定

*@naked.com dkimselector._domainkey.naked.com

TrustedHostsの設定 内部でメール転送してくるホストのIPを一つずつ記述する。

10.10.10.1
10.10.10.2
10.10.10.3
10.10.10.4
10.10.10.5
10.10.10.6
10.10.10.7
10.10.10.8
10.10.10.9
10.10.10.10
10.10.10.11
10.10.10.12
10.10.10.13
10.10.10.14
10.10.10.15
10.10.10.16
10.10.10.17
10.10.10.18
10.10.10.19
10.10.10.20
10.10.10.21
10.10.10.22
10.10.10.23
10.10.10.24
10.10.10.25
.....

Postfix側の設定

下記を追記

# DKIM
smtpd_milters = inet:127.0.0.1:8891
non_smtpd_milters = $smtpd_milters
milter_default_action = accept

OpenDKIMを起動、postfix reload

service opendkim start
postfix reload

DNS設定

DNSにOpenDKIMの公開鍵を設定

cat /etc/opendkim/keys/naked.com/dkimselector.txt 結果

dkimselector._domainkey  IN  TXT ( "v=DKIM1; k=rsa; "
      "p=MIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQC08E9Dos+LX2dZZFwBX2vSTfqAvvsME9rWELT+lxQI+GtuS7ZUIugYEzURX1H1DwDPo+Rm6YRnku9iWX7vdV/CfxFSTbYwwn9XA7DZbGpSCLOS4ySzJJYiOq+cw8Hat17u7pvl6fq4me3NWWCA88XkIVW5HykS5WYBcm3eb9/o1QIDAQAB" )  ; ----- DKIM key dkimselector for naked.com

上記の設定をDNSに設定。あと下記の設定も追加。

Name adsp.domainkey.naked.com
Type    TXT
Value   "dkim=unknown"

ゾンビDBの作り方 at GCP

概要

毎朝、前の日に本番DBから取ったバックアップを元に復活するDBをGCPでも作りました。

やってることは、

  1. mysql止める
  2. 古いvolumeをアンマウント
  3. 古いvolumeをdetach
  4. スナップショットから新しいボリュームを作成
  5. 新しいvolumeをattach
  6. 新しいvolumeをマウント
  7. mysql起動

前提条件

  • 実行サーバ(zombi-db)のAPI accessのcomputeにread/write権限がついてること
  • db-backup-20141205xxxみたいなスナップショットが存在すること

実行シェル

#!/bin/bash
date=`date +%Y%m%d%H`
DESCRIPTION="db-backup-$date"
newdisk="zombi-disk"
olddisk="zombi-disk"

# Stop mysql
sudo /sbin/initctl stop mysql

# Unmount
sleep 60
sudo /bin/umount -l /var/lib/mysql

# Detach old volume
sleep 60
gcloud compute -q --project "naked.co.jp:api-project-1234567890" instances detach-disk "zombi-db" --disk "$olddisk" --zone "asia-east1-a"

# Delete old volume
gcloud compute -q --project "naked.co.jp:api-project-1234567890" disks delete "$olddisk" --zone "asia-east1-a"

# Create new DB volume
sleep 60
gcloud compute -q --project "naked.co.jp:api-project-1234567890" disks create "$newdisk" --size "150" --zone "asia-east1-a" --source-snapshot "$DESCRIPTION" --type "pd-ssd"

# Attach new volume
sleep 60
gcloud compute -q --project "naked.co.jp:api-project-1234567890" instances attach-disk "zombi-db" --disk "$newdisk" --zone "asia-east1-a"

# Remount volume
sudo /bin/mount -a

# Start mysql
sudo /sbin/initctl start mysql

その他

例によって前処理が終わってない場合があったので適当にsleepを入れています。

ゾンビDBの作り方 at AWS

概要

開発メンバーからの要望で毎日、本番DBから取ったバックアップを元にデータをリフレッシュするDBを作りました。 何度データを壊しても次の朝には復活してるので社内ではコレをゾンビDBと呼んでいます。

やってることは、

1.定期的に取ってるsnapshotからvolumeを作成 2.mysql止める 3.古いvolumeをアンマウント 4.古いvolumeをdetach 5.新しいvolumeをattach 6.新しいvolumeをマウント 7.mysql起動

やりたいことはシンプルですが、awsから必要な情報を取ってくるのが少々面倒です。

前提条件

  • aws cliがインストールされている
  • IAM roleで実行サーバにEC2フルアクセス権限がついてる
  • DB_backup_20141205xxxみたいなスナップショットが存在する

シェルスクリプト

#!/bin/bash

date=`date +%Y%m%d%H`
DESCRIPTION="DB_backup_$date"
instance=`curl http://169.254.169.254/latest/meta-data/instance-id`


# Create new DB volume
snap=`/usr/local/bin/aws ec2 describe-snapshots --filters Name=description,Values=$DESCRIPTION"*" --query Snapshots[*].SnapshotId --output text`
VAR="$snap"
ary=($VAR)

attach_volume=`/usr/local/bin/aws ec2 create-volume --availability-zone us-east-1d --volume-type gp2 --snapshot-id ${ary[0]} --query VolumeId --output text`

# Stop mysql
sleep 10
sudo /usr/sbin/service mysql stop
#/sbin/swapoff /var/lib/mysql/swap

# Unmount
sleep 10
sudo /bin/umount -f /var/lib/mysql

# Detach old volume
sleep 10
detach_volume=`/usr/local/bin/aws ec2 describe-volumes --filters Name=attachment.instance-id,Values=$instance Name=attachment.device,Values=/dev/sdf --query Volumes[].Attachments[].[VolumeId] --output text`

/usr/local/bin/aws ec2 detach-volume --volume-id $detach_volume --instance-id $instance

# Attach new volume
sleep 60
/usr/local/bin/aws ec2 attach-volume --volume-id $attach_volume --instance-id $instance --device /dev/sdf

# Remount volume
sleep 60
sudo /bin/mount -a

# Start mysql
sleep 10
sudo /usr/sbin/service mysql start

# Delete old volume
/usr/local/bin/aws ec2 delete-volume --volume-id $detach_volume

その他

前の処理が完全に終わってなかったりするので適当にsleepを入れてます。