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

半年間、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を追加する
# 今回は２台構成なおかつ、単純に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:

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