Go to Qiita Advent Calendar 2024 Top
LoginSignup
70
68

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.

@shotaTsuge(Shota Tsuge)

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

Last updated at Posted at 2015-11-17

半年間、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:

エラーが出ていなければ、完了です！
70
68
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
70
68

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?