unshiu: MySQLレプリケーション設定

MySQLレプリケーション設定

MySQLのレプリケーション機能についてまとめます。

1 概要
2 導入方法
3 障害対応方法
1. 3.1 スレーブでエラー内容をスキップして復帰する手順
2. 3.2 マスタ障害時にスレーブをマスタへ昇格させる
4 参考

概要

データベース冗長化のためにDBをマスタとスレーブにわけ、更新はマスタで行い、参照はスレーブで行うことで負荷分散や高可用性を実現する機能です。

MySQLのレプリケーションは標準で搭載されており、非同期で行われマスタで更新されたログがスレーブへ送られます。

導入方法

Mysql-5.1.26 インストール

$ ./configure --prefix=/usr/local/mysql --with-charset=utf8 \
  --with-extra-charsets=all --with-mysqld-user=mysql --with-innodb --with-partition

$ make
$ make install
$ groupadd mysql
$ useradd mysql -g mysql
$ ./scripts/mysql_install_db --user=mysql
$ cp ./support-files/mysql.server /etc/init.d/mysql
$ cd /usr/local/mysql/
$ mkdir -m 0700 var
$ mkdir -m 0700 var/innodb var/log-bin var/relay-log
$ chown -R mysql:mysql var
$ chmod 700 /etc/init.d/mysql
$ /etc/init.d/mysql start

マスタ設定

スレーブ用ユーザの接続権限を与える

$mysql > GRANT REPLICATION SLAVE ON *.* TO unshiu_rep@'%' IDENTIFIED BY 'unshiu_rep';
$mysql > FLUSH PRIVILEGES;

注意：接続権限を特定のテーブルのみといったことはできないです。よって以下の例のようなものはうけつけません。

例)

$mysql > GRANT REPLICATION SLAVE ON unshiu_development.* TO unshiu_rep@'%' IDENTIFIED BY 'unshiu_rep';

/etc/my.cnf設定

以下の2行を追加します。なおbinary-logディレクトリは事前に作成しておく必要があります。

server-id=1 
log-bin=/usr/local/mysql/var/binary-log/ # レプリケーション用のバイナリログの場所

MySQLを再起動して設定を有効化

$ /etc/init.d/mysql stop
$ /etc/init.d/mysql start

スレーブ設定

/etc/my.cnf設定

以下を追加します。server-idに関してはマスタとは別のものを指定してください。

server-id=2
master-host = 192.168.10.102
master-user = unshiu_rep
master-password = unshiu_rep
master-port = 3306

MySQL再起動


$ /etc/init.d/mysql stop
$ /etc/init.d/mysql start

スレーブを開始します

$mysql > START SLAVE;

障害対応方法

障害時はなにかとパニくるので、経験のない人は一度ローカルの環境を使い実際に障害を起こし、復旧してみることをおすすめします。
スレーブ側に更新SQLを流してしまった場合

この場合、スレーブはマスタと同期がとれなくなり、停止します。同期がとれない箇所を修正しスレーブを再起動する必要があります。復帰する方法は複数あります。原因となった行為に応じて使い分けてください。

スレーブでエラー内容をスキップして復帰する手順

1. スレーブ側でエラー内容を確認

エラー内容が出力されます。

mysql) show slave status \G;
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.0.1
                ・・・・略
                Slave_SQL_Running: No

スレーブが停止している場合は以下の出力がされます。

Slave_SQL_Running: No

そのほか、エラー内容や原因となったSQLも表示されます。

2. 手動でスレーブの内容をマスタにあわせる

更新内容やミスにもよりますが、ここの整合性は手動であわせるしか方法はありません。

3. エラー内容をスキップしスレーブを起動

以下のコマンドをスレーブ側で実行するとエラー内容をスキップします。 SQL_SLAVE_SKIP_COUNTERに設定する数字はエラーとなった数分指定することになります。

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> START SLAVE SQL_THREAD;

マスタの内容でスレーブを上書きして復帰する手順

1. マスタ側のポジションを確認

マスタの現在のポジションを確認します。

mysql) show master status;
+----------------+---------+--------------+------------------+
| File           | Position| Binlog_Do_DB | Binlog_Ignore_DB |
+----------------+---------+--------------+------------------+
| db1-bin.00000x | 000000  |              |                  |
+----------------+---------+--------------+------------------+

2. スレーブ側でレプリケーション停止


mysql> stop slave;

3. スレーブ側をマスタ情報で上書き

mysql> change master to master_host='host名',
master_user='接続ユーザー',
master_password='接続パスワード',
master_log_file='db1-bin.000009', #show master statusの内容
master_log_pos=288992; #show master statusの内容

4. スレーブを起動


mysql> start slave;

マスタ障害時にスレーブをマスタへ昇格させる

ゆくゆくはある程度は自動化したいですが、基本的にマスタの避けられない障害（HDDやサーバ異常）はどのみち手動作業が発生します。

今回の手順はマスタが生きている前提で、スレーブをマスタに昇格させています。マスタのサーバ自体が死んでいる場合は以下の 1 ステップは飛ばします。

1. サービス停止

作業中にマスタが更新されるのを防ぐためにサービスは停止させます。

2. スレーブへの更新が終わっていることを確認


mysql> STOP SLAVE IO_THREAD

まずスレーブのIOスレッドを止めます。


mysql> show processlist\G

Has read all relay log というログが出たら受け取ったバイナリログの処理が終わったことを示しています。

3. スレーブ上のスレーブ情報を削除

これによりバイナリログをみにいかなくなります。

mysql> reset master;

4. my.cnfの設定を変更

スレーブ側にマスタとして必要なログを追加します。障害対応のために事前にスレーブにマスタのcnfをコピーした my.cnf.master を用意しておくことを推奨します。

5. mysql 再起動

これによりスレーブがマスタに昇格しました。最後にアプリケーション側の設定を変えることをお忘れなく。
実装上の注意点

unshiuアプリケーション上ではacts_as_readonlyableを採用しており、マスタ・スレーブを区別してコーディングすることを最小限に抑えている。ただし、いくつか注意点があるので、必ずacts_as_readonlyableに目を通して下さい。
運用上の注意点
長期間スレーブが停止していた状態で再度レプリケーションを再開する場合

レプリケーションを再開したタイミングでマスタから送られてきたバイナリログ情報を元に同期をとります。差分が多すぎるとエラーになる場合があります。その際は以下のパラメータの調整をしてください。

max_allowed_packet = 1M

ただし一般的な値を設定してもエラーが改善しないレベルまで差分が大きいと復旧にも時間がかかります。なのでその場合はマスタのデータをダンプし、スレーブとの差分をなるべく小さくしてからスレーブを復旧させてください。

unshiu

最近の更新履歴