特定テーブルだけのスレーブをmysqld_multiで作った話

※ほぼ個人用メモなので雑です

1サーバで複数のMySQLを使いたい事案は開発環境などではとてもよくあると思います。
mysqld_multiを使いましょう。簡単です。便利です。

ポート毎の起動スクリプト及び設定ファイルを用意して、それぞれのプロセスをそれぞれ起動とかアホっぽいことしてないですよね？*1

垂直、水平分割されたデータベース(Shard1、Shard2)のスレーブを1ホストで動かしたい背景

INにVALUEがいっぱいおっぱい、COUNT IFがいっぱいおっぱいで困ってた

本番でユーザが参照しているデータベースこんなクエリ投げないで欲しいんだけど改善してもらうまでに時間がかかったりする。
スロークエリログを汚さないで！！！

SELECT name,sex,age FROM user_master WHERE id IN (1001,1003,1004,1005・・・・・); -- INに2万件とか入ってたり

SELECT -- COUNT IFがクソほど書かれてたり
  COUNT(IF( level BETWEEN 0 AND 10 )) as '初級者',
  COUNT(IF( level BETWEEN 11 AND 50 )) as '初級者+',
  ・・・・・COUNT(IF( level>=1000001 )) as '廃人'
FROM user_status;

それから、僕自身がとても本番には投げられないクエリを投げてしまうような解析機構を作りはじめちゃってるっていうのもある。*2

特定のテーブルだけレプリケーションしたい

前述のこまったちゃんクエリが参照しているのは特定のテーブルだけだったりした。
解析で参照する用に新しいスレーブを作りたいけど、USERDBはシャーディングされているので複数のスレーブが必要です。
だからといって富豪的にマスタと同等スペック*3のサーバを、マスタと同じ台数用意するなんてことは出来ないです。

ならば、単一のホストで複数台分のスレーブを用意してやればよいですね。
特定のテーブル(user_master、user_status)だけを対象とすることで、マスタとスペック差に乖離があってもレプリケーションが追いつかないとかはまぁないはず。

レプリケーション対象マスタ

DB01サーバ
- USERDB1
  - user_master
  - user_status
DB02サーバ
- USERDB2
  - user_master
  - user_status

設定とか、注意とか

スレーブのmy.cnf

こんな感じで設定してみます。

[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin

[mysql]
default-character-set = utf8

[mysqld] #ここに共通の設定を書きます。
character-set-server = utf8
read_only
innodb_buffer_pool_size = 256M
innodb_file_per_table
log-bin=mysql-bin
log-bin-index=mysql-bin
relay-log=relay-bin
relay-log-index=relay-bin
binlog_format=mixed
expire_logs_days = 3
log-error=/var/log/mysql/mysqld.log
slave-skip-errors = 1146

[mysqld1] #USERDB1のスレーブとして動かす設定
socket     = /var/lib/mysql/mysql.sock6001
port       = 6001
server-id  = 6001
pid-file   = /var/run/mysqld/mysqld.pid6001
datadir    = /var/lib/mysql/mysql6001
replicate-do-table = USERDB1.user_master
replicate-do-table = USERDB1.user_status

[mysqld2] #USERDB2のスレーブとして動かす設定
socket     = /var/lib/mysql/mysql.sock6002
port       = 6002
server-id  = 6002
pid-file   = /var/run/mysqld/mysqld.pid6002
datadir    = /var/lib/mysql/mysql6002 
replicate-do-table = USERDB2.user_master
replicate-do-table = USERDB2.user_status

起動

起動しても標準出力になにも出ないので、ログを別ペインでtailしながら起動するといいです。
問題なく起動したら、それぞれのレプリケーション設定をしたらいいです。

# mysqld_multi start

ポイント、注意

注意しなくてはならないのが、binlogは全部受信するということです。
特定のテーブルのみのレプリケーションですが、Slave_IO_Threadがマスタから受信してくるbinlogには、当然すべての更新情報が含まれています。

なので、"slave-skip-errors = 1146"は必須です。
コレを設定しておかないと、「スレーブ側にはuser_boobsなんてテーブルはないぜ」ってエラってSlave_SQL_Threadが停止しちゃいます。

*1:俺はしてたよ

*2:ほんとはこっちがメインの理由だったりする

*3:スレーブのスペックがマスタと比べて低すぎるとレプリケーションに一生追いつかないという事件が起きます。http://d.hatena.ne.jp/studio3104/20120531/1338462528

Studio3104::BLOG.new

uninitialized constant Studio3104 (NameError)