MySQL/レプリケーション

目次 †

内容 †

参考 †

• Ubuntu Server セットアップ備忘録 (17) - 遠隔地で MySQL レプリケーション（SSHポートフォワード使用） - kamicupの備忘録
• MYSQLレプリケーション設定 - エーエイチレフ linuxサーバー技術情報
• すみっこのメモ - MySQLでレプリケーション構成時にマスタとスレイブを入れ替える手順メモ
• MySQLレプリケーションを安全に利用するための10のテクニック
• MySQL 5.1 リファレンスマニュアル5.3.7 SSLを使用するレプリケーションの設定

作業ステップ（暗号化なしの場合） †

レプリケーション元の設定 †

• my.cnfでバイナリログを有効化する

  log-bin=mysql-bin

• サーバIDをmy.cnfで確認しておく

  server-id       = 1

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

  # mysql -u root -p
  mysql> GRANT REPLICATION SLAVE ON *.* TO [email protected]'%' IDENTIFIED BY '******';

• MASTER STATUSを確認（テーブルをロックして実行）

  mysql> FLUSH TABLES WITH READ LOCK;
  mysql> SHOW MASTER STATUS;
  +------------------+----------+--------------+------------------+
  | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
  +------------------+----------+--------------+------------------+
  | mysql-bin.000001 |    85296 |              |                  |
  +------------------+----------+--------------+------------------+
  1 row in set (0.00 sec)

• データのダンプを保存

  # mysql_dump -u root -p <<データベース名>>> /tmp/dump.sql

• テーブルのロックを解除

  mysql> UNLOCK TABLES;

レプリケーション先の設定 †

• レプリケーション元のデータを取り込み

  # mysql -u root -p 
  mysql> drop database <<データベース名>>;
  mysql> create database <<データベース名>>;
  mysql> \q
  # mysql -u root -p <<データベース名>> < /tmp/dump.sql

• サーバIDが重複しないようにmy.cnfを編集

  server-id   = 11

• また、my.cnfに次の指定があることを確認

  [mysqlhotcopy]
  interactive-timeout

• レプリケーション先の指定

  # mysql -u root -p
  mysql>CHANGE MASTER TO
  MASTER_HOST='レプリケーション元サーバ名',
  MASTER_PORT=3306,
  MASTER_USER='repl',
  MASTER_PASSWORD='******',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=85296 ;

• レプリケーションの開始

  mysql> START SLAVE;

• レプリケーションの状態チェック

  mysql> SHOW SLAVE STATUS\G
  *************************** 1. row ***************************
                 Slave_IO_State: Waiting for master to send event
                    Master_Host: レプリケーション元サーバ名
                    Master_User: repl
                    Master_Port: 3306
                  Connect_Retry: 60
                Master_Log_File: mysql-bin.000013
            Read_Master_Log_Pos: 676129616
                 Relay_Log_File: etch1-relay-bin.000002
                  Relay_Log_Pos: 440
          Relay_Master_Log_File: mysql-bin.000022
               Slave_IO_Running: Yes
              Slave_SQL_Running: Yes

• Slave_IO_RunningとSlave_SQL_RunningがYesと出ればOK

• レプリケーションの停止

  mysql> SLAVE STOP

SSHトンネリングを使う場合 †

レプリケーション先サーバの設定 †

• SSHトンネリングのコマンドを実行

  # ssh -f -N -L 13306:127.0.0.1:3306 [email protected]レプリケーション元サーバ名

• レプリケーション実行時の指定

  # mysql -u root -p
  mysql>CHANGE MASTER TO
  MASTER_HOST='localhost',
  MASTER_PORT=13306,
  MASTER_USER='repl',
  MASTER_PASSWORD='******',
  MASTER_LOG_FILE='mysql-bin.000004',
  MASTER_LOG_POS=189;

  • サーバ名に「localhost」、ポート番号はSSHトンネリングコマンドで指定した値（例：13306）、を指定する。
  • 設定したらSLAVE STARTを忘れずに。

注意 †

孫スレーブ（スレーブをマスターにしてレプリケーション）する場合 †

• A→B→C、というレプリケーションなど、スレーブがマスターになる場合、マスター（この場合はB）に次の設定が必要となる。

  log_slave_updates

  • 参考：[MySQL]MySQLのレプリケーションでマスタとスレーブの両方の機能を持つサーバの設定 - wadsのblog