คอนฟิก MySQL Replication

บทความนี้จะอธิบายวิธีการคอนฟิก Replication ของ MySQL เพื่อทำการ replicate ข้อมูลในฐานข้อมูล (database) จากเครื่องหลัก (Master) ไปยังเครื่องสำรอง (Slave) ได้

หลังจากที่คอนฟิก Replication ถูกต้องเรียบร้อย เมื่อมีการเปลี่ยนแปลงข้อมูลในฐานข้อมูลบนเครื่องหลัก (Master) ไม่ว่าจะเป็นการ INSERT, UPDATE, DELETE หรือ แก้ไข table การเปลี่ยนแปลงนี้จะถูกส่งต่อ (replicate) ไปยังเครื่องสำรอง (Slave) โดยอัตโนมัติ

การคอนฟิก Replication เป็นการป้องกันการเสียหาย ถ้าเครื่องหลักมีปัญหา ข้อมูลที่ถูกเก็บไว้ในเครื่องสำรอง ก็ยังสามารถใช้งานได้

ระบบทดสอบ

ตัวอย่างในบทความนี้ จะทดสอบบนเครื่องสองเครื่องที่ติดตั้ง CentOS 5.4 และ MySQL 5.1.43 เรียบร้อยแล้ว

เครื่อง Master

  • ip: 192.168.1.1
  • hostname: centos54-a

เครื่อง Slave

  • ip: 192.168.1.2
  • hostname: centos54-b

ตัวอย่างการใช้คำสั่ง rpm เพื่อตรวจสอบเวอร์ชั่นของ MySQL ที่ติดตั้ง

[root@centos54-a ~]# rpm -qa | grep -i mysql
MySQL-server-community-5.1.43-1.rhel5
MySQL-client-community-5.1.43-1.rhel5
MySQL-shared-compat-5.1.43-1.rhel5

หมายเหตุ

  • MySQL 5.1.43 ที่ทดสอบนี้ ใช้เวอร์ชั่นที่ดาวน์โหลดจาก http://dev.mysql.com
  • แนะนำให้ใช้ MySQL เวอร์ชั่นเดียวกันบนเครื่อง Master และ Slave

เครื่อง Master – สร้าง user สำหรับ Replication

เริ่มต้น ต้องสร้าง user ของ MySQL บนเครื่อง Master เพื่ออนุญาตสิทธิในการทำ ‘REPLICATION’ จากเครื่อง Slave

ตัวอย่างการสร้าง user ชื่อ ‘repl’ และรหัส ‘repl1234’เพื่อให้สิทธิการทำ REPLICATION จากเครื่องที่มี ip 192.168.1.2

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

เครื่อง Master – คอนฟิก Replication Master

บนเครื่อง Master ปิดเซอร์วิส MySQL แล้วแก้ไขไฟล์ /etc/my.cnf เพื่อเปิดคุณสมบัติ binary log (log-bin) และเซ็ต server-id

[root@centos54-a ~]# service mysql stop
Shutting down MySQL..                                      [  OK  ]

ตัวอย่างไฟล์คอนฟิก /etc/my.cnf เพื่อคอนฟิกเป็น Replication Master

[root@centos54-a ~]# cat /etc/my.cnf
[mysqld]
log-bin=mysql-bin
server-id=1

ใช้คำสั่ง service เพื่อรันเซอร์วิส MySQL

[root@centos54-a ~]# service mysql start
Starting MySQL.                                            [  OK  ]

เครื่อง Master – ตรวจสอบสถานะของ Master Binary Log

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

เราจะใช้ค่า ‘File’ และ ‘Position’ เพื่อระบุบนเครื่อง Slave

[root@centos54-a ~]# mysql -u root -p
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      224 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

เครื่อง Slave – คอนฟิก Replication Slave

บนเครื่อง Slave ปิดเซอร์วิส MySQL เพื่อแก้ไขคอนฟิกไฟล์ /etc/my.cnf

[root@centos54-b ~]# service mysql stop
Shutting down MySQL..                                      [  OK  ]

เบื้องต้นบนเครื่อง Slave คอนฟิกแค่ ‘server-id’ ที่มีค่าแตกต่างจากเครื่อง Master

[root@centos54-b ~]# cat /etc/my.cnf
[mysqld]
server-id=2

รันเซอร์วิส MySQL บนเครื่อง Slave

[root@centos54-b ~]# service mysql start
Starting MySQL.                                            [  OK  ]

ใช้คำสั่ง CHANGE MASTER เพื่อระบุ ip ของ Master ชื่อ user, password ที่ใช้เพื่อการทำ Replication และต้องระบุค่า log_file (File) และ log_pos (Position) ที่ได้จากผลลัพธ์ในการรันคำสั่ง SHOW MASTER STATUS บนเครื่อง Master

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

รันคำสั่ง START SLAVE เพื่อเริ่มต้นการทำ Replicate ข้อมูลจาก เครื่อง Master

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

ตรวจสอบสถานะ Replication บนเครื่อง Slave

mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.1
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 244
               Relay_Log_File: centos54-b-relay-bin.000004
                Relay_Log_Pos: 251
        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: 244
              Relay_Log_Space: 411
              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:
1 row in set (0.00 sec)

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

ทดสอบการ Replication

เพื่อง่ายต่อการแยกแยะระหว่าง Master และ Slave ในที่นี้จะคอนฟิก mysql prompt เป็น mysql-1> และ mysql-2> ตามลำดับ

การคอนฟิก prompt บนเครื่อง Master

[root@centos54-a ~]# mysql -u root -p
Enter password:
mysql> PROMPT mysql-1>
PROMPT set to 'mysql-1> '
mysql-1>

การคอนฟิก prompt บนเครื่อง Slave

[root@centos54-b ~]# mysql -u root -p
Enter password:
mysql> PROMPT mysql-2>
PROMPT set to 'mysql-2> '
mysql-2>

ทดสอบสร้าง database บนเครื่อง Master

mysql-1> CREATE DATABASE test1;
Query OK, 1 row affected (0.01 sec)
mysql-1> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test1              |
+--------------------+
3 rows in set (0.00 sec)

ข้อมูลการสร้าง database จะถูกส่งต่อ (replicate) ไปยังเครื่อง Slave โดยอัตโนมัติ

mysql-2> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test1              |
+--------------------+
3 rows in set (0.00 sec)

สร้าง table และทดสอบการ INSERT ข้อมูล

mysql-1> CONNECT test1
mysql-1> CREATE TABLE books (id INTEGER, name VARCHAR(255));
mysql-1> INSERT INTO books VALUES (1, 'how to setup...');
mysql-1> INSERT INTO books VALUES (2, 'how to read...');
mysql-1> SELECT * FROM books;
+------+-----------------+
| id   | name            |
+------+-----------------+
|    1 | how to setup... |
|    2 | how to read...  |
+------+-----------------+
2 rows in set (0.00 sec)

การเปลี่ยนแปลงบนเครื่อง Master จะถูก replicate ไปยังเครื่อง Slave โดยอัตโนมัติ

mysql-2> CONNECT test1;
mysql-2> SELECT * FROM books;
+------+-----------------+
| id   | name            |
+------+-----------------+
|    1 | how to setup... |
|    2 | how to read...  |
+------+-----------------+
2 rows in set (0.00 sec)

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

5 thoughts on “คอนฟิก MySQL Replication”

  1. สุดยอดมากครับ อ่านแล้วได้ความรู้ดี

  2. ขอบคุณครับ ได้ความรู้มากเลย

  3. ข้อมูลก่อนการทำ Replication จะมาด้วยหรือเปล่าครับ
    หรือมาเฉพาะข้อมูลที่เริม replication

  4. บทความเยี่ยมมากเลยครับ
    อยากทราบว่า ถ้ากรณี เครื่อง Slave หรือเครื่อง Master เครื่องใดเครื่องหนึ่ง ดับ ไฟดับ อะไรก็ตาม

    ถ้าเปิดเครื่องหรือ Run มาอีกที จะมีคำสั่งตรงไหน ให้มันตรวจสอบอัตโนมัติ ว่าให้ทำการตรวจสอบ Replication ต่อข้อมูลต่อเนื่องไปเลยคับ

Leave a Reply

Your email address will not be published.