かってぃのブログ

喫茶店を遊牧しながら勉強したり開発したりする大学院生のブログです。

katty0324

MySQLのレプリケーションできたー。少しずつサーバ1台態勢から脱却できるように頑張ろう。 http://t.co/6K1v6taM

on 2011-10-18 02:18:27

webから

はじめに

MySQLのレプリケーションに挑戦してみたのでその記録です。

レプリケーションって何?

「レプリカ」を作ること、データベースサーバの複製を作ることです。

WEBサイトへのアクセスが増えて1台のデータベースサーバでさばききれなくなった場合などに使います。1台でさばけないなら2台にすればいいんですが、2台のデータベースサーバが同じデータをもつようにしてやらねばなりません。

その一つの方法がレプリケーションです。

1台目をマスター、2台目をスレーブとして、主従関係をもたせます。データベースへの更新処理はマスターに対してのみ行なって、マスターには常に最新のデータがあることを保証します。マスターに対する更新処理のログを残しておき、それを定期的にスレーブにも実行します。

そうするとマスターに対して少し遅れをとるものの、スレーブもマスターと同じ内容にすることができます。

そもそも「1台のデータベースサーバで十分です。」という状況ではレプリケーションは必要ありません。データベースサーバを増やしたくなるのは、性能を上げたいとか、システムがダウンしないように予備を用意しておきたいとか、そういう場合です。(アクセスの少ないサイトを、落ちたら諦めようというスタンスで運営していた場合必要ないと思います。)

レプリケーションで何をしたの?

MySQLのバックアップを定期的に取りたいと思いました。mysqldumpというコマンドを使えば、データベースの内容をテキストに落としこんでくれます。

mysqldump --all-databases --opt -u user -ppassword > mysqldump.sql

--optのオプションについて。mysqldumpの実行中にデータベースの中身が書き変わってしまうと、データに不整合が生じる危険性があるので、mysqldumpの実行中はデータベースの書き込みをロックしています。

しかし、書き込みがロックされている間にWEBサイトにアクセスしてくれた人は、ロックが解除されるまでアクセスができないという状態になります。(更新系クエリがなければアクセスできます)

そこで、レプリケーションを組んで、スレーブ側でmysqldumpを実行すれば良いんじゃないかと思いました。

マスター側だけでWEBサイトが動いている状況では、スレーブをロックしてもサイトには影響を及ぼしません。スレーブにはレプリケーションによってマスター側と同じものがコピーされているはずなので、これをmysqldumpすれば良いという寸法です。(そもそもレプリケーションが正しく行われないかもしれない・・・という心配がありますが、とりあえず無視しておきます。)

設定の仕方

以下のページを見ながらやっただけなので、こちらを見たほうが分かりやすいと思います。

MySQL レプリケーションのセットアップ手順

http://wadslab.net/wiki/index.php?MySQL%20%A5%EC%A5%D7%A5%EA%A5%B1%A1%BC%A5%B7%A5%E7%A5%F3%A4%CE%A5%BB%A5%C3%A5%C8%A5%A2%A5%C3%A5%D7%BC%EA%BD%E7

設定は、マスターサーバと、スレーブサーバを行ったりきたりします。

どちらもさくらVPSを使っていて、マスターはサービス用にそこそこ良いやつ、スレーブは前に契約して余っていた一番安いやつです。

作業ログ

緑色はmysql内やvim内で入力する内容。赤色は適宜書き換えるところです。マスターのIPが49.212.yyy.yyyで、スレーブのIPが49.212.xxx.xxxとしています。

# master
yum update -y mysql

# slave
yum update -y mysql

#master
mysql -u username -p

grant replication slave on *.* to 'repl'@'49.212.xxx.xxx' identified by 'password';
quit

sudo vim /etc/my.cnf

log-bin=mysql-bin
server-id=1001

sudo /etc/init.d/mysqld restart

# slave
sudo vim /etc/my.cnf

server-id=1002

sudo /etc/init.d/mysqld restart

# master
mysql -u username -p

flush tables with read lock;
show master status
# FileとPositionを控えておく

# master(別のターミナルから)
mysqldump -u username -p database --lock-all-tables > dbdump.db

# mster(元のターミナルから)
unlock tables;
scp ./dbdump.db 49.212.xxx.xxx:~/dbdump.db

# slave
mysql -u username -p database < dbdump.db
mysql -u
username -p

# 先ほど控えたFileとPositionをここで使う
change master to master_host = '49.212.yyy.yyy', master_user='repl', master_password='password', master_log_file='mysql-bin.000001', master_log_pos=9201;
start slave;

バックアップの取り方

レプリケーションと関係ないですが、目的が自動バックアップをとるところだったので、そのあたりも書いておきます。mysqldumpを実行するシェルスクリプトをcronで定期的に実行させてやるだけです。

cd /home/user
mkdir bak
vim bak_mysql.sh

file="/home/user/bak/mysqldump-"`date '+%Y%m%d%H%M'`".sql"
mysqldump --all-databases --opt -u user -ppassword  > $file

chmod 755 bak_mysql.sh
crontab -e

0 1 */1 * * cd /home/user;/bin/bash /home/user/bak_mysql.sh

おわりに

サーバ2台使うならスレーブ側にも読み込み系処理をさせたら良いではないかとか、バックアップとるだけなら他に方法がとか、セキュリティ的にとか、気づいたことがあればどうか突っ込んでください。

by katty0324 on 2011-10-20 03:18:35

コメント(0)

katty0324

「JOINしまくった長いSQL1回か、JOINしないSQL複数回か」の答えは、「テーブルを一つのサーバにまとめて置くか、複数のサーバに分散させるか」、によって決めれば良いようです。つまり小規模システムで分散不要の時は長いSQLでOK?

on 2011-02-26 21:46:04

webから

SQLのJOINについて考える

「JOINは使わない方が良い」というのを小耳に挟んで「なぜだろう」と思っていたのですが、ひとつの理由が分かったのでそれを記しておきます。

SQLのJOINについて考えるべく、Twitterみたいな掲示板システムを考えてみます。

自分がフォローしているユーザーのコメントをすべて取得するような状況です。

friendテーブル

まず、友達の一覧を保持するテーブルfriendが必要です。

自分のIDと友達のIDを保持しておきます。

CREATE TABLE friend (id int, friend_id int);

commentテーブル

次に投稿を保持するテーブルcommentが必要です。

発言者のIDと、コメントの本文が必要です。

CREATE TABLE comment(id int, comment text);

JOINを使って友人のコメントを取得する

非常に基本的なSQLで書くことができます。

たとえば、自分のIDが1なら、次のようなSQL文になります。

SELECT * FROM comment AS c JOIN friend AS f ON c.id = f.friend_id WHERE f.id = 1;

JOINを使わないで友人のコメントを取得する

友人のIDの一覧を取得する

JOINを使わないので、SQLを分割する必要があります。

まずは、自分のIDを元に友人のID一覧を取得します。

SELECT friend_id FROM friend WHERE id = 1;

友人のIDを元にコメントの一覧を取得する

つづいて、取得したidをPHPなどで整形して、コメント取得のSQLに投げ込みます。

たとえば、友人が4人いて、友人のIDが2,3,4,5と取得できた場合は次のようなSQLになります。

SELECT * FROM comment WHERE id IN (2,3,4,5);

どちらのSQLが優れているか?

この比較は難しいかもしれませんが、ひとつの判断の観点として、システムの規模があげられると思います。

JOINを使った方が楽

この説明を見ても分かる通り、JOINを使ってしまった方が、明らかに楽です。

だから、使えるものなら、JOINを使いたいところです。

大規模システムでは、JOINが使えない?

大規模なシステムでは、複数のテーブルをひとつのデータベースにまとめて置いておくのは効率が悪い場合があります。

データベースのサイズが大きくなると、すべてのデータがメモリに乗り切らずに、HDDへのアクセスが増加し速度が極端に低下するためです。

そこで、friendテーブルとcommentテーブルを別々のデータベースサーバに置くようにして解決する方法があります。(パーティショニングと言います。)

しかし、この状態では2つのテーブルが別のサーバにあってJOINが使えないので、「JOINを使わないで友人のコメントを取得する」方法が採用されます。

まとめ

以上をまとめると、こうなります。

テーブルを複数のサーバに分散させて置く場合は、JOINしない。

すべてのテーブルがひとつのデータベースサーバに置かれているような小規模で簡単なシステムでは、JOINすればいい。

by katty0324 on 2011-03-24 18:43:51

コメント(0)

katty0324

かってぃはCREATE TRIGGERを身につけた!DELETEコマンドをガンガン使えるようになった!

on 2011-02-28 17:26:41

webから

概要!

MySQLでDELETEコマンドを使いたい。

でも、「うっかりSQLを書き間違えて全消去」という展開は避けたい。

そんな時に、TRIGGERを作成して、削除したレコードを自動でコピーしておくことができます。

DELETEコマンド

概要

MySQLのレコードを削除するコマンドです。

DELETE FROM hoge WHERE id = 1;

などとすれば、hogeテーブルの「idが1」のレコードを削除します。

問題点

しかし、うっかりSQLを書き間違えて、次のようにしてしまったらどうなるでしょうか。

DELETE FROM hoge;

この時は、残念ですが、hogeテーブルの「すべて」のレコードを削除します。

TRIGGER

概要

そこで登場するのがTRIGGERです。

その名の通り、何かをトリガー(引き金)にして、SQLを実行するものです。

これを利用して、「DELETEコマンドの実行を引き金」にしてレコードのコピーを実行すれば良いです。

使い方

使い方は、こんな具合。

DELIMITER $$
CREATE TRIGGER trigger_hoge AFTER DELETE ON hoge FOR EACH ROW
  BEGIN
    INSERT INTO trigger_hoge (id, text) VALUES (OLD.id, OLD.text);
  END;
$$
DELIMITER ;

DELIMITERコマンド

SQLの区切りを指定するコマンドです。

初期値は「;」ですが、DELIMITERコマンドで変更することができます。

処理を複数行にわたって書きたい場合に、「;」を書いてしまうとそこで処理が実行されてしまいます。

そのため、一度区切りを「$$」などに変更します。

CREATE TRIGGERコマンド

TRIGGERを作成します。

ここでは、「hogeテーブル」の「各行」について「削除」が実行された「後」に実行する処理を記述するTRIGGERの作成です。

INSERTコマンド

基本的なコマンドですが、TRIGGERを使う時には、ひとつ特殊なことがあります。

挿入する値が、「OLD.id」とか「OLD.text」になっています。

これは、削除を実行する前の値ということです。

まとめ

TRIGGERを指定しておけば、レコード削除時に「うっかりSQLを書き間違えて全消去」しても別テーブルにコピーを保存しておくことができます。

他にも、「レコードが挿入された時」や「変更が加えられた時」をトリガーにして処理を実行することもできます。

便利ですね!


by katty0324 on 2011-03-04 19:24:31

コメント(0)