คอนฟิก MySQL 8.0 Replication บน Ubuntu 20.04

เมื่อมีปริมาณการใช้งานเซิร์ฟเวอร์ฐานข้อมูล (Database Server) เพิ่มมากขึ้น อาจทำให้เซิร์ฟเวอร์ตัวเดียวไม่สามารถรองรับได้ ทำให้ต้องมีการขยายขนาด โดยอาจทำได้สองวิธีหลักๆ คือ

  1. เปลี่ยนเครื่องให้มี CPU, Memory หรือ Disk ที่มีความเร็วสูงขึ้น
  2. อีกวิธีที่จะแนะนำในที่นี้ คือเพิ่มเครื่องแล้วคอนฟิก Replication เพื่อให้ทุกเครื่องมีฐานข้อมูลที่เหมือนกัน เพื่อกระจายการรองรับการใช้งาน

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

ระบบทดสอบ

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

  • IP : 192.168.56.11
  • Hostname : u20-db1

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

  • IP : 192.168.56.12
  • Hostname : u20-db2

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

ติดตั้ง MySQL Server และคอนฟิก Replication บนเครื่อง #1 (db1)

ใช้คำสั่ง sudo apt install ติดตั้งแพ็คเกจ mysql-server

u20-db1:~$ sudo apt install mysql-server

ตรวจสอบสถานะเซอร์วิส mysql

u20-db1:~$ systemctl status mysql
● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: active (running)
     since Sun 2021-05-23 11:42:34 UTC; 20s ago
   Main PID: 13072 (mysqld)
     Status: "Server is operational"
      Tasks: 38 (limit: 2281)
     Memory: 332.4M

ต้องขึ้นสถานะ active (running)

แก้ไขคอนฟิกไฟล์ /etc/mysql/mysql.conf.d/mysqld.cnf เพื่อเปิดการใช้งาน Replication

บรรทัดที่แก้ไข

  • bind-address = 0.0.0.0 เปิดการเชื่อมต่อจากเครื่องอื่นๆ เพื่อให้เซิร์ฟเวอร์เครื่องอื่นมา replicate ข้อมูลได้
  • server-id = 1 เป็น id ประจำเครื่อง MySQL Server ที่จะทำ replication กัน ซึ่งค่าต้องไม่ซ้ำกัน
  • log_bin = /var/log/mysql/mysql-bin.log เปิดการเก็บ statement ที่มีการเปลี่ยนแปลงข้อมูลในฐานข้อมูล

ตัวอย่างไฟล์คอนฟิกทั้งหมด ที่ไม่รวมบรรทัดที่ขึ้นต้นด้วยเครื่องหมาย # ที่เป็นการ comment

u20-db1~$ sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
user = mysql
bind-address = 0.0.0.0
mysqlx-bind-address = 127.0.0.1
key_buffer_size = 16M
myisam-recover-options = BACKUP
log_error = /var/log/mysql/error.log
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 100M

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

u20-db1:~$ sudo systemctl restart mysql

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

u20-db1:~$ sudo mysql -u root -p

mysql-db1> CREATE USER 'repl'@'192.168.56.12' IDENTIFIED BY 'secret';
mysql-db1> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.56.12';

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

mysql-db1> SHOW MASTER STATUS G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 946
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

ข้อมูล File กับ Position จะถูกใช้ตอนคอนฟิก Replica บนเครื่อง db2

ติดตั้ง MySQL Server และคอนฟิก Replication บนเครื่อง #2 (db2)

ใช้คำสั่ง sudo apt install ติดตั้งแพ็คเกจ mysql-server

u20-db2:~$ sudo apt install mysql-server

ตรวจสอบสถานะเซอร์วิส mysql

u20-db2:~$ systemctl status mysql
● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; ...
     Active: active (running)
     since Sun 2021-05-23 12:09:51 ...

ต้องขึ้นสถานะ active (running)

ถ้าต้องการคอนฟิก db2 ให้เป็นแค่ SLAVE ไม่ได้ส่งต่อข้อมูลหรือเป็น MASTER ของเซิร์ฟเวอร์อื่นๆ แก้ไขแค่ค่า server-id บรรทัดเดียวก็พอ แต่เราจะเตรียมคอนฟิกเป็น Master-Master ของอีกเครื่องด้วย ดังนั้นจะแก้ไขเหมือนกับคอนฟิกของเครื่อง db1

แก้ไขคอนฟิกไฟล์ /etc/mysql/mysql.conf.d/mysqld.cnf

บรรทัดที่แก้ไข

  • bind-address = 0.0.0.0 เปิดการเชื่อมต่อจากเครื่องอื่นๆ เพื่อให้เซิร์ฟเวอร์เครื่องอื่นมา replicate ข้อมูลได้
  • server-id = 2 เป็น id ประจำเครื่อง MySQL Server ที่จะทำ replication กัน ซึ่งค่าต้องไม่ซ้ำกัน
  • log_bin = /var/log/mysql/mysql-bin.log เปิดการเก็บ statement ที่มีการเปลี่ยนแปลงข้อมูลในฐานข้อมูล

หมายเหตุ อย่าลืมเปลี่ยน server-id ไม่ให้ซ้ำกับเครื่อง db1

ตัวอย่างไฟล์คอนฟิกทั้งหมด ที่ไม่รวมบรรทัดที่ขึ้นต้นด้วยเครื่องหมาย # ที่เป็นการ comment

u20-db2:~$ sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
user = mysql
bind-address = 0.0.0.0
mysqlx-bind-address = 127.0.0.1
key_buffer_size = 16M
myisam-recover-options = BACKUP
log_error = /var/log/mysql/error.log
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 100M

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

u20-db2:~$ sudo systemctl restart mysql

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

u20-db2:~$ sudo mysql -u root -p

mysql-db2> CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.56.11',
    SOURCE_LOG_FILE='mysql-bin.000001',
    SOURCE_LOG_POS=946,
    SOURCE_SSL=1;
Query OK, 0 rows affected (0.02 sec)

อย่าลืมสังเกตผลลัพธ์จากการรันคำสั่งด้วย ต้องขึ้น Query OK

คำอธิบาย

  • SOURCE_LOG_FILE ดูค่า File จากคำสั่ง SHOW MASTER STATUS บนเครื่อง db1
  • SOURCE_LOG_POST ดูค่า Position จากคำสั่ง SHOW MASTER STATUS บนเครื่อง db1

รันคำสั่ง START REPLICA ตามด้วย USER, PASSWORD ที่สร้างไว้ เพื่อเริ่มการทำ Replication

mysql-db2> START REPLICA USER='repl' PASSWORD='secret';

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

mysql-db2> SHOW REPLICA STATUS G
*************************** 1. row ***************************
             Replica_IO_State: Waiting for master to send event
                  Source_Host: 192.168.56.11
                  Source_User: repl
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: mysql-bin.000001
          Read_Source_Log_Pos: 946
               Relay_Log_File: u20-db2-relay-bin.000002
                Relay_Log_Pos: 324
        Relay_Source_Log_File: mysql-bin.000001
           Replica_IO_Running: Yes
          Replica_SQL_Running: Yes
          ...

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

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

mysql-db1> CREATE DATABASE test;

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

mysql-db2> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+

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

master-slave

คอนฟิก Master-Master MySQL Replication

ในทำนองกลับกัน หากต้องการให้ db2 เป็น Master ด้วย เช่นถ้าเปลี่ยนแปลงข้อมูลบน db2 ข้อมูลจะถูก replicate กลับไปยัง db1

master-master

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

mysql-db2> CREATE USER 'repl'@'192.168.56.11' IDENTIFIED BY 'secret';
mysql-db2> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.56.11';

รันคำสั่ง SHOW MASTER บนเครื่อง db2 เพื่อดูสถานะ File และ Position

mysql-db2> SHOW MASTER STATUS G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 904
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

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

mysql-db1> CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.56.12',
    SOURCE_LOG_FILE='mysql-bin.000001',
    SOURCE_LOG_POS=904,
    SOURCE_SSL=1;

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

mysql-db1> START REPLICA USER='repl' password='secret';

รันคำสั่ง SHOW REPLICA STATUS เพื่อดูสถานะ replication slave บนเครื่อง db1

mysql-db1> SHOW REPLICA STATUS G
*************************** 1. row ***************************
             Replica_IO_State: Waiting for master to send event
                  Source_Host: 192.168.56.12
                  Source_User: repl
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: mysql-bin.000001
          Read_Source_Log_Pos: 904
               Relay_Log_File: u20-db1-relay-bin.000002
                Relay_Log_Pos: 324
        Relay_Source_Log_File: mysql-bin.000001
           Replica_IO_Running: Yes
          Replica_SQL_Running: Yes
          ...

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

เช่นถ้าลองสร้าง table บนเครื่อง db2 ข้อมูลนั้นก็จะถูก replicate กลับไปยัง db1

mysql-db2> use test
Database changed

mysql-db2> CREATE TABLE students (id int, name varchar(255));

บนเครื่อง db1 ก็จะมีตาราง students ขึ้นมาโดยอัตโนมัติ

mysql-db1> use test
Database changed

mysql-db1> show tables;
+----------------+
| Tables_in_test |
+----------------+
| students       |
+----------------+

Leave a Reply

Your email address will not be published.