Go to Qiita Advent Calendar 2024 Top
LoginSignup
15
20

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@shotets

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

Last updated at Posted at 2017-03-31

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

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

/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グラフを生成するプラグイン

/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; }'

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

プラグインを設置

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

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

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

テスト

munin-run mysql_replag
munin-run mysql_repstatus

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

/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の設定をする

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

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

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

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

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> SHOW SLAVE STATUS;
:
Seconds_Behind_Master: 0

Seconds_Behind_Master が 0 であること。

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

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

マスター側

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

スレーブ側

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が発生している可能性が高い。

後者の場合の対処例

バイナリログについて

バイナリログの確認方法

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

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

バイナリログの削除

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

スレーブ側

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

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

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

マスター側

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> PURGE MASTER LOGS TO 'mysql-bin.000039';

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

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

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

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

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

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

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

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

スレーブ側のサーバで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> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
mysql> START SLAVE SQL_THREAD;

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

mysql> SHOW SLAVE STATUS;

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

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

mysql> STOP SLAVE;

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

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をダンプ。

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

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

mysql> UNLOCK TABLES;
mysql> START SLAVE;

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

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

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

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

⑦動作確認

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

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

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

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

15
20
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
15
20

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?