MySQL
初心者
基礎

MySQLのmasterとslaveの手動切り替え

More than 1 year has passed since last update.

半年間、MySQLを基礎からやり直すことにしたので、その間に勉強したことをQiitaに投稿していきます。

設定自体は、前回の続きになるので下記のリンクを参考にして下さい

CentOS6.5にMySQL5.5.38をインストールする手順

MySQLでレプリケーションを組んでみる


スレーブ側の設定


レプリケーション用のユーザ作成

スレーブ側で実行する

許可するネットワーク:XXX.XXX.XXX.XXX

ユーザ名:repli

パスワード:repli

# ユーザの作成

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repli'@'XXX.XXX.XXX.XXX' IDENTIFIED BY 'repli';
Query OK, 0 rows affected (0.00

# ユーザの確認
mysql> select user, host from mysql.user where user = 'repli';
+-------+----------------------------+
| user | host |
+-------+----------------------------+
| repli | XXX.XXX.XXX.XXX |
+-------+----------------------------+


my.cnfファイルの設定変更

$ vim /etc/my.cnf

[mysqld]
# バイナリロギングを有効にする
log-bin=mysql-bin
# サーバ認識用のサーバIDを設定
server-id=1002

# スレーブをマスターとして使いたい場合は、log_slave_updatesを追加する
# 今回は2台構成なおかつ、単純にmaster-slave構成なので付けていても一応問題ない
log_slave_updates

# my.cnf変更したので、mysqlプロセスを再起動する
$ /etc/init.d/mysql restart
Shutting down MySQL. SUCCESS!
Starting MySQL... SUCCESS!


log_slave_updatesの有効・無効について

オフィシャルサイトには「log-slave-updatesはレプリケーションサーバを

チェーン状に構成する場合に使用する」とかいてあります。

この辺のリンクを参考にして下さい

5.3.6. フェイルオーバでのマスタ切り替え

MySQLレプリケーションにおけるフェイルオーバー


フェイルオーバの手順


現マスター側で実施

# プロセス(クエリ)が流れてないことを確認

mysql> SHOW PROCESSLIST;

# 書き込みロックを実行する(ロックが外れるのでmysqlから出ない)
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 3031
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

# Key_blocks_not_flushedが0であることを確認する(ログへフラッシュされた)
mysql> SHOW STATUS LIKE 'Key_blocks_not_flushed';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| Key_blocks_not_flushed | 0 |
+------------------------+-------+
1 row in set (0.00 sec)

# InnoDBで、ログからテーブルスペースへ書き込み中の可能性を一応考慮して
# Log sequence numberとLog flushed up toの値に差異がないことを確認する
$ mysql -u root -e "SHOW ENGINE INNODB STATUS\G" | grep -e "Log sequence number" -e "Log flushed up to"
Log sequence number 1603757
Log flushed up to 1603757


現スレーブ側で実施

# レプリケーション(IO_THREAD)を停止する

mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.01 sec)

# レプリケーションの確認
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: XXX.XXX.XXX.XXX
Master_User: repli
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 3031
Relay_Log_File: relay-bin.000007
Relay_Log_Pos: 469
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: No
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 3031
Relay_Log_Space: 638
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1001
1 row in set (0.00 sec)

# Has read all relay logが出るまで待機
mysql> SHOW PROCESSLIST;
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| 1 | system user | | NULL | Connect | 489 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL |
| 5 | root | localhost | NULL | Query | 0 | NULL | SHOW PROCESSLIST |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)

# レプリケーションを完全に停止する
mysql> STOP SLAVE;
Query OK, 0 rows affected (0.01 sec)

# Master_Log_FileとRead_Master_Log_Posをメモ
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: XXX.XXX.XXX.XXX
Master_User: repli
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 3031
Relay_Log_File: relay-bin.000003
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 3031
Relay_Log_Space: 568
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1002
1 row in set (0.00 sec)

Master_Log_FileとRead_Master_Log_Posがマスター・スレーブ共に同じことを確認する


現マスター側で実施

# レプリケーション系の設定を初期化

mysql> RESET SLAVE ALL;
Query OK, 0 rows affected (0.00 sec)

# レプリケーション系の設定を初期化
mysql> RESET MASTER;
Query OK, 0 rows affected (0.02 sec)

# レプリケーションの確認
mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 3031
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)


現スレーブ側で実施

# レプリケーション系の設定を初期化

mysql> RESET SLAVE ALL;
Query OK, 0 rows affected (0.01 sec)

# レプリケーション系の設定を初期化
mysql> RESET MASTER;
Query OK, 0 rows affected (0.01 sec)

# レプリケーションの確認
mysql> SHOW SLAVE STATUS\G
Empty set (0.00 sec)


新スレーブ側で実施(旧マスター)

mysql> CHANGE MASTER TO

-> MASTER_HOST='XXX.XXX.XXX.XXX',
-> MASTER_USER='repli',
-> MASTER_PASSWORD='repli',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=3031;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: XXX.XXX.XXX.XXX
Master_User: repli
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 3031
Relay_Log_File: relay-bin.000003
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 107
Relay_Log_Space: 568
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1002
1 row in set (0.00 sec)

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)


新マスター側で実施(旧スレーブ)

mysql> SHOW MASTER STATUS\G

*************************** 1. row ***************************
File: mysql-bin.000001
Position: 3031
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

mysql> SHOW SLAVE HOSTS\G
*************************** 1. row ***************************
Server_id: 1001
Host:
Port: 3306
Master_id: 1002
1 row in set (0.00 sec)

mysql> SHOW SLAVE STATUS\G
Empty set (0.00 sec)

最後に新マスター側へレコード追加して、新スレーブ側に反映されれば完了です!


おまけ


もしもCHANGE MASTER TO間違えた時の対処法

# MySQL-5.0/5.1は、以下の手順で対応

mysql> STOP SLAVE;
mysql> CHANGE MASTER TO MASTER_HOST='';
mysql> RESET SLAVE;

# MySQL-5.5/5.6は、以下の手順で対応
mysql> STOP SLAVE;
mysql> RESET SLAVE ALL;

参考

MySQL-5.5/5.6でのレプリケーション利用者に伝えたい「RESET SLAVE」にまつわる怖い話


SLAVE STARTした後に、STATUS確認したらこんなエラーが出る

# レプリケーションの確認(スレーブ側で実施)

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position;

#######################################################################################
# 対応方法
#######################################################################################

# マスター側でもう一度下記コマンドを実行し、File・Positionの位置を再確認する
mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 107

# 再度、CHANGE MASTER TOを実行
mysql> CHANGE MASTER TO MASTER_HOST='XXX.XXX.XXX.XXX' ,
> MASTER_USER='repli',
> MASTER_PASSWORD='repli',
> MASTER_LOG_FILE='mysql-bin.000001',
> MASTER_LOG_POS=107;

# レプリケーションの確認(スレーブ側で実施)
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Last_IO_Error:

エラーが出ていなければ、完了です!