読者です 読者をやめる 読者になる 読者になる

ユアマイスター株式会社エンジニアブログ

ユアマイスター株式会社のエンジニアが日々徒然。

VagrantでVMを2台立ち上げてMySQLのMaster/Slaveを作ってレプリケーション

こんにちは!ユアマイスターの星です。

今回、ローカル環境で、MySQLレプリケーションを動作確認する機会があったので、その手順を備忘のために書いておきます。

大きく詰まる所はなく、トントンと行けると思います。

VMはCentOS6.3で立ててあります。(MySQLのバージョンは5.6)

フェイルオーバーとか試したくなった時に、気軽にローカルでチャチャッと環境作れるのは嬉しいですね!

それでは、手順です。

1. Masterの初期設定

SU

sudo su

/etc/my.cnfの編集

cp /etc/my.cnf /etc/my.cnf.bak

vi /etc/my.cnf

diff -u /etc/my.cnf /etc/my.cnf.bak
# replication
log-bin=mysql-bin
server-id=101
[root@localhost vagrant]# diff -u /etc/my.cnf /etc/my.cnf.bak
--- /etc/my.cnf 2017-04-26 06:35:41.002033325 +0000
+++ /etc/my.cnf.bak 2017-04-26 06:36:13.929031577 +0000
@@ -20,10 +20,6 @@
 datadir=/var/lib/mysql
 socket=/var/lib/mysql/mysql.sock
 
-# replication
-log-bin=mysql-bin
-server-id=101
-
 # Disabling symbolic-links is recommended to prevent assorted security risks
 symbolic-links=0

MySQL再起動

/etc/rc.d/init.d/mysqld restart
[root@localhost vagrant]# /etc/rc.d/init.d/mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]

MySQLにログイン

mysql -u root -p

ユーザー作成

grant replication slave on *.* to replica@'%' identified by 'replica';

flush privileges;

exit;
[root@localhost vagrant]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.36-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> grant replication slave on *.* to replica@'%' identified by 'replica';
Query OK, 0 rows affected (0.03 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> exit;
Bye

2. Slaveの初期設定

SU

sudo su

/etc/my.cnfの編集

cp /etc/my.cnf /etc/my.cnf.bak

vi /etc/my.cnf

diff -u /etc/my.cnf /etc/my.cnf.bak
# replication
log-bin=mysql-bin
server-id=102
read_only=1
report-host=server2
[root@localhost vagrant]# diff -u /etc/my.cnf /etc/my.cnf.bak
--- /etc/my.cnf 2017-04-26 06:45:34.169021154 +0000
+++ /etc/my.cnf.bak 2017-04-26 06:45:16.461021094 +0000
@@ -20,12 +20,6 @@
 datadir=/var/lib/mysql
 socket=/var/lib/mysql/mysql.sock
 
-# replication
-log-bin=mysql-bin
-server-id=102
-read_only=1
-report-host=server2
-
 # Disabling symbolic-links is recommended to prevent assorted security risks
 symbolic-links=0

MySQL再起動

/etc/rc.d/init.d/mysqld restart
[root@localhost vagrant]# /etc/rc.d/init.d/mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]

3. MasterのDumpをとる

全テーブルをロック

mysql -u root -p 
flush tables with read lock;

FileとPositionをメモ

show master status;
[vagrant@localhost vagrant]$ mysql -u root -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.36-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.01 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      400 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)

別コンソールを開き、Dumpを取得

mysqldump -u root -p --all-databases --lock-all-tables --events > mysql_dump.sql
[vagrant@localhost ~]$ mysqldump -u root -p --all-databases --lock-all-tables --events > mysql_dump.sql
Enter password: 
[vagrant@localhost ~]$ ls -l mysql_dump.sql 
-rw-rw-r--. 1 vagrant vagrant 1426633 Apr 26 06:52 mysql_dump.sql

server2へSCP転送

scp mysql_dump.sql 192.168.33.41:/tmp/
[vagrant@localhost ~]$ scp mysql_dump.sql 192.168.33.41:/tmp/
vagrant@192.168.33.41's password: 
mysql_dump.sql                                100% 1393KB   1.4MB/s   00:00   

元のコンソールでロック解除

unlock tables;

exit;
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

mysql> exit;
Bye

4. Slaveでレプリケーション有効化

マスターから転送したダンプデータをインポート

mysql -u root -p < /tmp/mysql_dump.sql
[vagrant@localhost ~]$ mysql -u root -p < /tmp/mysql_dump.sql
Enter password: 

レプリケーション詳細設定

mysql -u root -p

メモったFileとPositionを記載する

change master to
master_host='192.168.33.40',
master_user='replica',
master_password='replica',
master_log_file='mysql-bin.000001',
master_log_pos=400;
[vagrant@localhost ~]$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.36-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> change master to
    -> master_host='192.168.33.40',
    -> master_user='replica',
    -> master_password='replica',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=400;
Query OK, 0 rows affected, 2 warnings (0.07 sec)

レプリケーション開始

start slave;
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)

ステータス確認

show slave status\G
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.33.40
                  Master_User: replica
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 475
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 358
        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: 475
              Relay_Log_Space: 532
              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: 101
                  Master_UUID: d3e8ac27-2a19-11e7-aab9-080027c363c3
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)