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

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

レプリケーションの構築

今回はマスター・スレーブの２台構成にします

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

マスター側で実行する
許可するネットワーク：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=1001

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

データのスナップショット作成

# テーブルのロック
mysql> FLUSH TABLES WITH READ LOCK;

# マスター側のステータスの確認（ログファイル・ログポジション）
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |     1730 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

# スキーマ名を指定してスナップショットを作成する
$ mysqldump -uroot repli_test  --lock-all--tables > dbdump.db

# テーブルのロックを解除する
mysql> UNLOCK TABLES;

スレーブ側の設定

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

$ vim /etc/my.cnf
[mysqld]
# サーバ認識用のサーバIDを設定
server-id=1002

# マスターとして使う可能性がある場合は下記設定も追加する
# マスターのバイナリロギングを有効にする
log-bin=mysql-bin
# スレーブをマスターとして使いたい場合は、log_slave_updatesを追加する
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レプリケーションにおけるフェイルオーバー

スナップショットをスレーブ側へ展開する

# scpコマンドでスレーブサーバへdbdump.dbを持ってくる
$ scp dbdump.db [e-mail]:/usr/local/src

# 今回はスキーマ単位でのスナップショットなので、スレーブ側にも先に作る必要がある

# repli_testにスナップショットを展開する
$ mysql -uroot repli_test  < /usr/local/src/dbdump.db

# マスター情報を登録する
# MASTER_LOG_FILEとMASTER_LOG_POSは、マスター側のステータスを入力する
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=1730;

レプリケーションを開始する

# レプリケーションの開始
mysql> START SLAVE;

# レプリケーションの確認
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: 1730
               Relay_Log_File: relay-bin.000003
                Relay_Log_Pos: 4
        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: 2179
              Relay_Log_Space: 1017
              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: 1001
1 row in set (0.00 sec)

# レプリケーションを止める
mysql STOP SLAVE;

レプリケーションが組めてるか確認してみる

# テーブルに格納されているデータ（レコード）を確認する
# マスター・スレーブ共に確認する
$ mysql -uroot -e "SELECT user.id,user.name FROM repli_test.user;"
+----+-----------+
| id | name      |
+----+-----------+
|  1 | test_user |
|  2 | hoge      |
|  3 | neko      |
|  4 | inu       |
|  5 | dog       |
+----+-----------+

# テーブルにデータ（レコード）を追加する
# マスター側で行う
$ mysql -uroot -e "INSERT INTO repli_test.user (user.id, user.name) VALUES(6, 'cat');"

# テーブルに格納されているデータ（レコード）を確認する
# マスター・スレーブ共に確認する
$ mysql -uroot -e "SELECT user.id,user.name FROM repli_test.user;"
+----+-----------+
| id | name      |
+----+-----------+
|  1 | test_user |
|  2 | hoge      |
|  3 | neko      |
|  4 | inu       |
|  5 | dog       |
|  6 | cat       |
+----+-----------+

無事レプリが組めてるのが確認できたので、これで完了です！
今回は省略しますがDBとTABLEは用意しておいて下さい

とりあえず使いそうなコマンド一覧

# データベース作成
mysql> CREAT DATABASE {DB_NAME};

# データベース確認
mysql> SHOW DATABASES;

# 特定のデータベースを選択する
mysql> USE {DB_NAME};

# テーブル作成
mysql> CREATE TABLE user (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> name VARCHAR(20))
    -> ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

# テーブルの確認
mysql> SHOW TABLES;

# テーブルの設定情報確認する
mysql> SHOW COLUMNS FROM repli_test.user;

# テーブルにデータ（レコード）を追加する
mysql> INSERT INTO repli_test.user (id, name) VALUES(1, 'test_user');

# テーブルに格納されているデータ（レコード）を確認する
mysql> SELECT user.id,user.name FROM replitest.user;

# テーブルに格納されているデータ（レコード）を削除する
mysql> DELETE FROM user WHERE user.id=3;

# テーブルに格納されているデータ（レコード）を更新する
mysql> UPDATE repli_test.user SET id=3 WHERE id=4;