คอนฟิก MySQL 5.5 Replication บน CentOS 6

บทความนี้จะอธิบายคอนฟิก MySQL Replication เพื่อ replicate ข้อมูลระหว่าง MySQL Database Server สองเครื่อง ทั้งแบบ Master-Slave และ Master-Master โดยทดสอบกับเวอร์ชั่น 5.5 ที่ติดตั้งบน CentOS 6


ระบบทดสอบ

เครื่อง MySQL Server#1 (DB1)

  • IP : 192.168.5.61
  • Hostname : cent6-db1

เครื่อง MySQL Server#2 (DB2)

  • IP : 192.168.5.62
  • Hostname : cent6-db2

แนะนำให้ใช้ MySQL เวอร์ชั่นเดียวกันทั้งสองเครื่อง ในที่นี้ทดสอบกับเวอร์ชั่น 5.5.37

 

คอนฟิกบนเครื่อง MySQL Server#1 (DB1)

แก้ไขคอนฟิกไฟล์ /etc/my.cnf เพื่อทำเป็น Replication

เพิ่มบรรทัดคอนฟิก log-bin และ server-id ภายใต้ [mysqld]

server-id เป็น id ประจำตัวของ MySQL Server แต่ละเครื่องที่จะทำ replication กัน ซึ่งต้องเป็นคนละค่ากัน

[root@cent6-db1 ~]# vi /etc/my.cnf
[mysqld]
log-bin=mysql-bin
server-id=1

ใช้คำสั่ง service เพื่อรีสตาร์ตเซอร์วิส MySQL เพื่อให้คอนฟิกที่แก้ไขใหม่มีผล

[root@cent6-db1 ~]# service mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]

รันคำสั่ง mysql เพื่อสร้างและอนุญาต (GRANT) user ให้เครื่อง DB2 (repl@192.168.5.62) สามารถมาทำ replicate ข้อมูลได้

[root@cent6-db1 ~]# mysql -u root -p
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.5.62' IDENTIFIED BY 'repl1234';
Query OK, 0 rows affected (0.00 sec)

ในคำสั่ง mysql ใช้คำสั่ง SHOW MASTER STATUS ตรวจสอบสถานะของของ Replication Master

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      107 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

คอนฟิกบนเครื่อง MySQL Server#2 (DB2)

แก้ไขคอนฟิกไฟล์ /etc/my.cnf เพื่อทำเป็น Replication

เพิ่มบรรทัดคอนฟิก server-id ภายใต้ [mysqld]

ถ้าต้องการคอนฟิก DB2 ให้เป็นแค่ SLAVE ไม่ได้ส่งต่อข้อมูลหรือเป็น MASTER ของเซิร์ฟเวอร์อื่นๆ  ไม่จำเป็นต้องใส่ออปชั่น log-bin

แต่เราจะคอนฟิกเป็น DUAL-MASTER ของอีกเครื่องด้วย ต้องเปิดไว้

[root@cent6-db2 ~]# vi /etc/my.cnf
[mysqld]
log-bin=mysql-bin
server-id=2

ใช้คำสั่ง service เพื่อรีสตาร์ตเซอร์วิส MySQL เพื่อให้คอนฟิกที่แก้ไขใหม่มีผล

[root@cent6-db2 ~]# service mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]

รันคำสั่ง mysql เพื่อคอนฟิกเป็น Replication Slave

[root@cent6-db2 ~]# mysql -u root -p
mysql> CHANGE MASTER TO MASTER_HOST='192.168.5.61',
                        MASTER_USER='repl',
                        MASTER_PASSWORD='repl1234',
                        MASTER_LOG_FILE='mysql-bin.000001',
                        MASTER_LOG_POS=107;
Query OK, 0 rows affected (0.03 sec)

คำอธิบาย

  • MASTER_LOG_FILE ดูค่า File จากคำสั่ง SHOW MASTER STATUS บนเครื่อง DB1
  • MASTER_LOG_POST ดูค่า Position จากคำสั่ง SHOW MASTER STATUS บนเครื่อง DB1

รันคำสั่ง START SLAVE เพื่อเริ่มการทำ Replicate

mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

ดูสถานะของ Replicate SLAVE

mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 192.168.5.61
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 107
               Relay_Log_File: mysqld-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Connecting
            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: 107
              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: 2003
                Last_IO_Error: error connecting to master 'repl@192.168.5.61:3306' - retry-time: 60  retries: 86400
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 0
1 row in set (0.00 sec)

mysql>

หากคอนฟิกถูกต้อง ค่า ‘Slave_IO_State’ จะแสดงเป็น “Waiting for master to send event”

แต่ผลลัพธ์ที่ได้ค่า Slave_IO_State ยังแสดงค่าเป็น “Connecting to master” ดูค่า Last_IO_Error แสดงปัญหา “error connecting to master…

สาเหตุหนึ่งที่เป็นไปได้คือบนเครื่อง MASTER หรือ DB1 มีการคอนฟิก firewall ไว้ ดีฟอลต์ firewall ของ CentOS 6 จะอนุญาตเฉพาะให้ ping กับ ssh

ดังนั้นเพื่อทำ replicate ได้ ต้องอนุญาตหรือเปิดพอร์ต mysql หรือ TCP พอร์ต 3306 บนเครื่อง Master

แก้ไข Firewall บนเครื่อง DB1

แก้ไขไฟล์คอนฟิก firewall /etc/sysconfig/iptables

[root@cent6-db1 ~]# vi /etc/sysconfig/iptables

# Firewall configuration written by system-config-firewall
# Manual customization of this file is not recommended.
*filter
:INPUT ACCEPT [0:0]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [0:0]
-A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT
-A INPUT -p icmp -j ACCEPT
-A INPUT -i lo -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT
-A INPUT -j REJECT --reject-with icmp-host-prohibited
-A FORWARD -j REJECT --reject-with icmp-host-prohibited
COMMIT

ใช้คำสั่ง service เพื่อรีสตาร์ตเซอร์วิส iptables เพื่อให้คอนฟิก firewall ใหม่มีผล

[root@cent6-db1 ~]# service iptables restart
iptables: Setting chains to policy ACCEPT: filter          [  OK  ]
iptables: Flushing firewall rules:                         [  OK  ]
iptables: Unloading modules:                               [  OK  ]
iptables: Applying firewall rules:                         [  OK  ]

ลองรัน SHOW SLAVE STATUS อีกครั้งบน DB2

mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.5.61
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 107
               Relay_Log_File: mysqld-relay-bin.000003
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000002
             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: 556
              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: 1
1 row in set (0.00 sec)

mysql>

ค่า ‘Slave_IO_State’ เปลี่ยนเป็น “Waiting for master to send event” แล้ว

ทดลองสร้าง database ชื่อ test1 บน Master DB1

[root@cent6-db1 ~]# mysql -u root -p
mysql> CREATE DATABASE test1;
Query OK, 1 row affected (0.00 sec)

หากคอนฟิกถูกต้อง ลองใช้คำสั่ง SHOW DATABASES บนเครื่อง DB2 ดู จะเห็นชื่อ DATABASE test1 ขึ้นมาโดยอัตโนมัติ

[root@cent6-db2 ~]# mysql -u root -p
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test1              |
+--------------------+
4 rows in set (0.00 sec)

ถึงตรงนี้การคอนฟิก MySQL Replicate จะเป็นการ replicate ข้อมูลจาก DB1 ที่ทำหน้าที่เป็น Master ไปยัง DB2 ที่ทำหน้าที่เป็น Slave เท่านั้น หมายถึงถ้ามีการเปลี่ยนแปลงข้อมูลบน DB1 ข้อมูลจะถูก replicate ไปยัง DB2 โดยอัตโนมัติ ตามรูป

master-slave

 

คอนฟิก Dual-Master MySQL Replication

ในทำนองกลับกัน หากต้องการให้ DB2 เป็น Master ด้วย เพื่อให้ DB1 ทำหน้าที่เป็น Slave ดึงข้อมูลจาก DB2 เราสามารถทำได้โดยขั้นตอนเดียวกันเพียงแต่สลับเครื่อง

master-master

บนเครื่อง DB2 สร้างและอนุญาต (GRANT) user ให้เครื่อง DB1 (repl@192.168.5.61) สามารถมาทำ replicate ข้อมูลได้

[root@cent6-db2 ~]# mysql -u root -p
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.5.61' IDENTIFIED BY 'repl1234';

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      262 |              |                  |
+------------------+----------+--------------+------------------+

แก้ไขไฟล์คอนฟิก /etc/sysconfig/iptables แล้วรีสตาร์ตเซอร์วิส iptables

[root@cent6-db2 ~]# vi /etc/sysconfig/iptables
...
-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT
...

[root@cent6-db2 ~]# service iptables restart
iptables: Setting chains to policy ACCEPT: filter          [  OK  ]
iptables: Flushing firewall rules:                         [  OK  ]
iptables: Unloading modules:                               [  OK  ]
iptables: Applying firewall rules:                         [  OK  ]

บนเครื่อง DB1 คอนฟิกเป็น Slave เพื่อให้ดึงข้อมูลจาก DB2

mysql> CHANGE MASTER TO MASTER_HOST='192.168.5.62',
                        MASTER_USER='repl',
                        MASTER_PASSWORD='repl1234',
                        MASTER_LOG_FILE='mysql-bin.000001',
                        MASTER_LOG_POS=262;
Query OK, 0 rows affected (0.03 sec)

บน DB1 รันคำสั่ง START SLAVE เพื่อคอนฟิกเป็น Replication Slave

mysql> START SLAVE;
Query OK, 0 rows affected (0.01 sec)

ถึงตรงนี้การคอนฟิกแบบนี้จะเป็นการทำ DUAL-MASTER คือไม่ว่าเราจะแก้ไขฐานข้อมูลบนเครื่องไหน ข้อมูลก็จะถูก replicate ไปอีกเครื่องโดยอัตโนมัติ

 

ข้อมูลอ้างอิง

Leave a Reply

Your email address will not be published.