mysqlを稼動させたまま、複数テーブルのレプリケーションを行う。

社内で運用中のRailsアプリケーションでは、データベースをShardingして一つのmysql上に複数DBがのっかっている状態になっている。
このデータベースはmasterとslaveで分散させているのですが、slaveの負荷が高くなってきた為にslaveの数を増やすことになりました。

その為にmasterのdumpをとってslaveに流してやります。--flush-logオプションをつければあるタイミングでbin-logを移動してくれるので、レプリケーション先でMASTER_FILE_LOGを新しいbin-logを指定してやればいいのです。

master-db

master-dbでのファイルの位置を確認し、dumpを取ります。

 mysql> show master status;

+----------------+-----------+--------------+------------------+
| File           | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------+-----------+--------------+------------------+
| log-bin.000061 | 119120300 |              | mysql            |
+----------------+-----------+--------------+------------------+

 mysqldump --skip-lock-tables --flush-log --single-transaction --user=root --password --database database-1 database-2 database-3 | gzip -c > database_dump_`date +%Y%m%d_%H%M`.gz

で、log-binファイルを確認する。

 sudo ls -l mysql/var/log-bin

(省略)
-rw-rw---- 1 mysql staff     56193 2010-12-17 13:10 log-bin.000057
-rw-rw---- 1 mysql staff 109617173 2010-12-17 15:02 log-bin.000058
-rw-rw---- 1 mysql staff  26931166 2010-12-17 15:15 log-bin.000059
-rw-rw---- 1 mysql staff 135276244 2010-12-17 16:02 log-bin.000060
-rw-rw---- 1 mysql staff 120524056 2010-12-17 16:39 log-bin.000061
-rw-rw---- 1 mysql staff   5468112 2010-12-17 16:41 log-bin.000062
-rw-rw---- 1 mysql staff    137097 2010-12-17 16:41 log-bin.000063
-rw-rw---- 1 mysql staff   4438152 2010-12-17 16:42 log-bin.000064

なんと、log-binファイルが想定以上に移動している。

どうやら一緒に指定しても、dumpを取るdatabase毎にflushしちゃってるみたい。これだと、slaveのレプリケーションを64から開始しようとしても62に含まれるdatabase-1に対するクエリ、63に含まれるdatabase-1, 2に対するクエリが実行されていないことになってしまう。そこで、取ったdumpと共にlog-binファイルからもクエリを実行してやる必要がある。

以下のファイルをslaveにコピーする。

 scp database_dump_YYYYmmdd_HHMM.gz slave-db:/tmp
 sudo cp mysql/var/log-bin/log-bin.000062 /tmp/
 sudo cp mysql/var/log-bin/log-bin.000063 /tmp/
 sudo chmod +r /tmp/log-bin.00006*
 for log in 62 63
   scp /tmp/log-bin.0000${log} parkgree-db-slave-03:/tmp

slave-db

slaveを止めて、データベースを作り直す。

mysql> stop slave

for db in {1..3}
  mysql -uroot -p -e'drop database database-'$db
for db in {1..3}
  mysql -uroot -p -e'create database database-'$db

masterの位置を設定しなおす。

 mysql> CHANGE MASTER TO
 mysql> MASTER_LOG_FILE='log-bin.000064',
 mysql> MASTER_LOG_POS=0;

dumpファイルを実行する

gunzip -c /tmp/database_dump_YYYYmmdd_HHMM.gz | mysql -uroot -p

忘れずにbinlogファイルを読み込む

 mysqlbinlog --database=database-1 log-bin.000062 | mysql -uroot -p database-1
 mysqlbinlog --database=database-1 log-bin.000063 | mysql -uroot -p database-1
 mysqlbinlog --database=database-2 log-bin.000063 | mysql -uroot -p database-2

で、slaveを再開する。

mysql> start slave

DBが同期されていることが確認出来ればOKです。