MySQLのレプリケーション管理に関するメモ

## レプリケーション監視用muninプラグイン

### マスターとスレーブの間の遅延（秒）を示すMuninグラフを生成するプラグイン

```bash:/usr/share/munin/plugins/mysql_replag
#!/bin/sh 
# Plugin to monitor the Seconds_Behind_Master of replication on a MySQL slave
  
MYSQLOPTS=$mysqlopts
MYSQL=${mysql:-mysql}   
if [ "$1" = "autoconf" ]; then 
	$MYSQL --version 2>/dev/null >/dev/null 
	if [ $? -eq 0 ] 
	then 
		$MYSQL $MYSQLOPTS -e '' 2>/dev/null >/dev/null 
		if [ $? -eq 0 ] 
		then 
			echo yes 
			exit 0 
		else 
			echo "no (could not connect to mysql)" 
		fi 
	else 
		echo "no (mysql not found)" 
	fi 
	exit 1 
fi   

if [ "$1" = "config" ]; then 
	echo 'graph_title Replication lag' 
	echo 'graph_args --base 1000 -l 0' 
	echo 'graph_vlabel lag in secs' 
	echo 'graph_category mysql' 
	echo 'lag.label lag' 
	exit 0 
fi   

/usr/bin/printf "lag.value " 
mysql $MYSQLOPTS -e 'show slave status\G' | grep Seconds_Behind_Master | awk '{print $2}'
```

### スレーブが実行されている場合は1、それ以外の場合は0の値を持つMuninグラフを生成するプラグイン

```bash:/usr/share/munin/plugins/mysql_repstatus
#!/bin/sh 
# Plugin to monitor the replication status on a MySQL slave

MYSQLOPTS=$mysqlopts
MYSQL=${mysql:-mysql}   
if [ "$1" = "autoconf" ]; then 
	$MYSQL --version 2>/dev/null >/dev/null 
	if [ $? -eq 0 ] 
	then 
		$MYSQL $MYSQLOPTS -e '' 2>/dev/null >/dev/null 
		if [ $? -eq 0 ] 
		then 
			echo yes 
			exit 0 
		else 
			echo "no (could not connect to mysql)" 
		fi 
	else 
		echo "no (mysql not found)" 
	fi 
	exit 1 
fi   

if [ "$1" = "config" ]; then 
	echo 'graph_title Replication status' 
	echo 'graph_args -l 0 --upper-limit 2' 
	echo 'graph_vlabel slave is running' 
	echo 'graph_info slave is running' 
	echo 'graph_category mysql' 
	echo 'status.label IO running' 
	echo 'status.info IO running : 1=yes, 0=no' 
	print_warning status
	print_critical status
	echo 'sql_status.label SQL running' 
	echo 'sql_status.info IO running : 2=yes, 0=no' 
	print_warning sql_status
	print_critical sql_status
	exit 0 
fi   

mysql $MYSQLOPTS -e 'show slave status\G' | awk 'NR == 1 { io = 0; sql = 0; next } /Slave_IO_Running: Yes/ { io = 1; next } /Slave_SQL_Running: Yes/ { sql = 2; next } END { print "status.value " io; print "sql_status.value " sql; }'
```

### プラグインを設置して監視を有効にする

プラグインを設置

```bash
vi /usr/share/munin/plugins/mysql_replag
vi /usr/share/munin/plugins/mysql_repstatus
```

権限変更、シンボリックリンク作成、再起動

```bash
chmod 755 /usr/share/munin/plugins/mysql_rep*
ln -s /usr/share/munin/plugins/mysql_replag /etc/munin/plugins/
ln -s /usr/share/munin/plugins/mysql_repstatus /etc/munin/plugins/
service munin-node restart
```

テスト

```bash
munin-run mysql_replag
munin-run mysql_repstatus
```

Muninの設定に監視の設定をする

```bash:/etc/munin/munin.conf
# warningも通知するメールグループ
contact.warning_mail.command mail -s "Munin Alerts ${var:group}::${var:host}" warning@example.com
contact.warning_mail.always_send warning critical

# criticalを通知するメールグループ
contact.critical_mail.command mail -s "Munin Alerts ${var:group}::${var:host}" critical@example.com
contact.critical_mail.always_send critical

# 監視対象（スレーブ）
[WEB;slave_server_name]
    address xxx.xxx.xxx.xxx
    use_node_name yes
    
    # レプリケーション
    mysql_replag.lag.warning 5
    mysql_replag.lag.critical 10
    mysql_repstatus.status.critical 1:
```

cronの設定をする

```bash:/etc/cron.d/munin
*/5 * * * * munin test -x /usr/bin/munin-cron && /usr/bin/munin-cron
```


## レプリケーションの状態確認

レプリケーションの点検は以下を確認する。

### ①スレーブが動いているか

```mysql
mysql> SHOW SLAVE STATUS;
:
Slave_IO_Running  : Yes
Slave_SQL_Running : Yes
Last_Errno        : 0
Last_Error        :
```

`Slave_IO_Running` と `Slave_SQL_Running` の両方が `Yes` になっていること。
`Slave_IO_Running` が `No` の場合はネットワークの問題。
`Slave_SQL_Running` が `No` の場合はSQL処理の問題。

### ②遅延が発生していないか(1)

```mysql
mysql> SHOW SLAVE STATUS;
:
Seconds_Behind_Master: 0
```

`Seconds_Behind_Master` が `0` であること。

### ③遅延が発生していないか(2)

マスターとスレーブのポジションの差、スレーブのI/OスレッドとSQLスレッドの差を調べる。

マスター側

```mysql
mysql> SHOW MASTER STATUS;
:
`(A)`
File                  : mysql-bin.000040
Position              : 98
```

スレーブ側

```mysql
mysql> SHOW SLAVE STATUS;
:
`(B)`
Master_Log_File       : mysql-bin.000040
Read_Master_Log_Pos   : 98
:
`(C)`
Relay_Master_Log_File : mysql-bin.000040
Exec_Master_Log_Pos   : 98
```

遅延がなければ全て同じになっているはずだが、
ズレがある場合は、それぞれを比べることで何が問題かがある程度特定できる。

(A)と(B)に差がある場合はネットワークに問題が発生している可能性が高い。
(B)と(C)に差がある場合はスレーブ側で処理しきれないSQLが発生している可能性が高い。

[後者の場合の対処例](http://d.hatena.ne.jp/hironey/20100814/1281751980)


## バイナリログについて

### バイナリログの確認方法

バイナリログは `mysqlbinlog` コマンドで確認することができる。

```bash
mysqlbinlog /var/log/mysql/master-bin.00001
```

### バイナリログの削除

まずはスレーブがどこまで読み込んでいるかを確認する。

スレーブ側

```mysql
mysql> SHOW SLAVE STATUS;
:
Master_Log_File       : mysql-bin.000040
```

`mysql-bin.000040` まで読み込み終わっているので
`mysql-bin.000039` までは削除してよいということになる。

次いで、マスターでバイナリログを確認。

マスター側

```mysql
mysql> SHOW MASTER LOGS;
+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
| mysql-bin.000001 | 1074149684 |
| mysql-bin.000002 | 1073741963 |
| mysql-bin.000003 | 1073742026 |
| mysql-bin.000004 | 1073741905 |
:
| mysql-bin.000039 | 1073742014 |
| mysql-bin.000040 |   65746708 |
+------------------+------------+
```

レプリケーションに遅延がなければ、スレーブで確認したバイナリログが
マスター側の最後のバイナリログになっているはず。

マスターで `mysql-bin.000039` までを削除する。
ここでバイナリログは `rm` で削除してはいけない。
indexと整合性がとれなくなる。

```mysql
mysql> PURGE MASTER LOGS TO 'mysql-bin.000039';
```

マスターのバイナリログを再確認。

```mysql
mysql> SHOW MASTER LOGS;
+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
| mysql-bin.000040 |   65746708 |
+------------------+------------+
```


### バイナリログが開けないというエラーの対処法

MySQLのログで以下のようなエラーが発生している場合。
誤って rm コマンド等でバイナリログを削除した場合などに起こる。

```bash
cat /var/log/mysqld.log
:
Failed to open log (file '', errno 2)
```

ログファイルが見つからないというエラーなので
/var/log/mysql/mysql-bin.index を確認し、
存在しないバイナリログファイルの名前を削除すればいい。


## レプリケーションが停止した場合の再開手順

### レプリケーションの停止原因を確認する

スレーブ側のサーバでMySQLにログインし、レプリケーションの状態を確認する。

```mysql
mysql> SHOW SLAVE STATUS;
```

`Slave_IO_Running` が `No` の場合はネットワークの問題。
マスターとスレーブ間で問題が起きていないか確認する。

`Slave_SQL_Running` が `No` の場合はSQL処理の問題。
スレーブ側で更新してしまった、マスター側でスレーブ側に無いデータの更新をした等が主な原因。
詳細はエラーメッセージを見て判断する。

```
Last_Error: Error 'Operation DROP USER failed for ''@'master.example.com'' on query. Default database: 'mysql'. Query: 'DROP USER ''@'master.example.com''
```

### レプリケーションの再開（簡易再開）

停止原因を確認し、エラーになったSQLをスキップすれば再開できそうならスキップしてみる。

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

再開できているか確認する。

```mysql
mysql> SHOW SLAVE STATUS;
```

### レプリケーションの再開（マスター⇒スレーブの場合）

①スレーブのレプリケーションを停止。

```mysql
mysql> STOP SLAVE;
```

②マスターのレプリケーションを停止し、ポジションを確認。

```mysql
mysql> STOP SLAVE;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
+------------------+-----------+--------------+------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+-----------+--------------+------------------+
| mysql-bin.000001 | 240831635 |              |                  |
+------------------+-----------+--------------+------------------+
```

③スレーブからマスターのDBをダンプ。

```bash
mysqldump --user=master_user_name --password=password database_name --host=master_host | gzip > /tmp/master.sql.gz
```

④マスターのレプリケーションを再開。

```mysql
mysql> UNLOCK TABLES;
mysql> START SLAVE;
```

⑤ダンプしたマスターのDBをスレーブに復元。

```bash
gunzip < /tmp/master.sql.gz | mysql --user=slave_user_name --password=password database_name --host=localhost
```

⑥スレーブでポジションを再設定し、レプリケーションを再開。
ポジションは②で確認したものを設定する。

```mysql
mysql> CHANGE MASTER TO
       MASTER_LOG_FILE='mysql-bin.000001',
       MASTER_LOG_POS=240831635;
mysql> START SLAVE;
```

⑦動作確認

```mysql
mysql> SHOW SLAVE STATUS;
:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_IO_Error:
Last_SQL_Error:
```

### レプリケーションの再開（マスター⇒スレーブ１⇒スレーブ２の場合）

スレーブ１⇒スレーブ２の間で問題があり、マスター⇒スレーブ１の間に問題が無かった場合は
スレーブ１⇒スレーブ２間だけ修復すればよい。
上記のマスター⇒スレーブの場合の方法で
マスターをスレーブ１、スレーブをスレーブ２に置き換えて作業する。

マスター⇒スレーブ１の間に問題があった場合は
スレーブ１、スレーブ２の両方を `STOP SLAVE` で停止させ、
まずは上記の方法でマスター⇒スレーブ１間を修復、再開させる。
マスター⇒スレーブ１間が再開できたら
同様にスレーブ１⇒スレーブ２間を修復、再開させる。