การทำ MySQL Replication

การทำ MySQL Replication ช่วยทำให้ข้อมูลจากฐานข้อมูล MySQL ที่เครื่อง Master ถูกดึงไปสำรองเก็บไว้ที่เครื่อง MySQL อีกเครื่องนึงที่เรียกว่า Slave โดยอัตโนมัติแม้ว่าการดึงข้อมูลจะเป็นไปตามรอบเวลาไม่ได้เป็น Realtime แต่ก็ถือว่าระบบมีการสำรองไว้เกือบจะตลอดเวลาทำให้เมื่อเกิดเหตุการณ์ฉุกเฉินสามารถย้ายจากเครื่อง Master ไปใช้ฐานข้อมูลเครื่อง Slave ได้ ทำให้สามารถเพิ่มความมั่นคงปลอดภัยให้กับระบบสารสนเทศได้ในระดับหนึ่ง ต่อไปนี้เป็นขั้นตอนการทำ MySQL Replication ที่จะกล่าวถึงในเอกสารฉบับนี้

แม้ว่าเอกสารฉบับนี้จะติดตั้ง MySQL Replication บน MySQL 5.5 แล้วขั้นตอนการติดตั้งจะเป็นแบบเดียวกันไม่ว่าจะเป็นเวอร์ชัน MySQL 5.5 หรือ MySQL 8.0 หรือแม้แต่บน MariaDB ก็ตาม (เพียงแต่คำสั่ง Master / Slave อาจจะมีการเปลี่ยนแปลงบ้างเล็กน้อยซึ่งสำหรับ MariaDB เราจะนำมาเขียนในเอกสารถัดไป)

ในหัวข้อนี้เป็นบันทึกขั้นตอนการทำ MySQL Replication บน MySQL 5.5 การทำ MySQL Replication ของ MySQL 5.5 สามารถดูเพิ่มเติมได้จาก

https://dba.stackexchange.com/questions/12345/how-to-setup-replicationmaster-slave-in-mysql-5-5-20 แสดง Script ชื่อว่า CreateMySlave.sh เราจะแกะคำสั่งจาก Script นี้

ต่อไปนี้เป็นผลการวิเคราะห์ Script ว่าทำอะไรบ้าง

1. ผลการวิเคราะห์ขั้นตอนในสคริปต์

ผลการวิเคราะห์ขั้นตอนในสคริปต์เป็นดังนี้

รูปภาพที่ 1 ผลการวิเคราะห์สคริปต์

หลังจากการวิเคราะห์สคริปต์ข้างต้นเราจะทดลองทำตามสคริปต์ไปทีละขั้นด้วยวิธีมือ เพื่อให้เข้าใจโดยมีขั้้นตอนคือ

• ติดตั้ง MySQL เอาไว้บน Docker container 2 ตัวจำลองเป็น Master ตัวนึงและอีกคอนเทนเนอร์นึงจำลองเป็น Slave
• ทดลองรันสคริปต์บนคอนเทนเนอร์ที่จำลองตัวเป็น Master
• ทดลองสร้างฐานข้อมูล สร้างตาราง และเอาข้อมูลลง
• ติดตามว่าถูก Replicate ไปที่คอนเทนเนอร์ที่จำลองเป็น Slave หรือไม่

ต่อไปนี้เป็นรายละเอียดการทดลอง

2. ทดสอบแบบข้ามเครื่อง

โดยสมมุติว่ามีเครื่องเป้าหมายคือ 10.1.2.20 เป็น Master และ 10.1.2.30 เป็น Slave เราจะสร้าง docker container อยู่ภายในเครื่องทั้ง 2 ตัวนี้เพื่อใช้เป็น MySQL Server ที่รันแบบ docker container ทั้งนี้ก็เพื่อให้สามารถล้างทิ้งได้ง่ายแล้วทำใหม่ได้เรื่อยๆหากว่าทำผิดพลาด ก่อนที่ท่านจะทดลองตามหัวข้อในเอกสารนี้ท่านจะต้อง

• มีเครื่องอย่างน้อย 2 เครื่องที่ติดตั้งระบบปฏิบัติการ Ubuntu 18+ เอาไว้
• ติดตั้ง docker บน Ubuntu ไว้พร้อมแล้ว สามารถรันคำสั่ง docker ได้
• เครื่องทั้ง 2 เครื่องสามารถ ping หากันเจอ
• เครื่องทั้ง 2 เครื่องไม่มี Firewall (เนื่องจากเป็นการทดลอง หากมี Firewall ให้ท่านปิดก่อน)
• ท่านจะต้องติดตั้ง mysql client เอาไว้บนเครื่อง Ubuntu ทั้ง 2 เครื่อง

ต่อไปนี้เป็นขั้นตอนดำเนินการทดลอง

3. การล้างทิ้งแล้วเริ่มใหม่

ถ้าได้ดำเนินการไปแล้วแล้วมีข้อผิดพลาดบางอย่าง และต้องการล้างทิ้งแล้วทำใหม่ ให้ท่านไปที่เครื่อง Master ที่ 10.1.2.20 แล้วรันคำสั่ง

docker stop mysql-master
docker container rm mysql-master

และไปที่เครื่อง Slave ที่ 10.1.2.30 แล้วรันคำสั่ง

docker stop mysql-slave
docker container rm mysql-slave

ทั้งนี้คอนเทนเนอร์ที่ชื่อว่า mysql-master ที่บนเครื่อง Master เป็นตัวที่เราจะทดลองสร้างขึ้น รวมทั้งคอนเทนเนอร์ที่ชื่อว่า mysql-slave บนเครื่อง Slave ด้วยเช่นกัน เมื่อรันคำสั่งข้างต้นแล้วคอนเทนเนอร์จะถูกลบทิ้งไปและเริ่มต้นใหม่ได้

4. การจัดเตรียมไฟล์คอนฟิกเกอเรชัน

ไปที่เครื่อง Master 10.1.2.20 แล้วสร้างโฟลเดอร์สำหรับวางไฟล์

mkdir -p /etc/mysql/master

แล้วสร้างไฟล์ master.cnf รอไว้โดยมีเนื้อหาเป็นดังนี้

[mysqld]
bind_address=0.0.0.0
server-id=1
log_bin=/var/log/mysql/mysql-bin.log
binlog_do_db=test
relay-log=/var/log/mysql/mysql-relay-bin.log

ไปที่เครื่อง Slave 10.1.2.30 แล้วสร้างโฟเดอร์สำหรับวางไฟล์

mkdir -p /etc/mysql/slave

แล้วสร้างไฟล์ slave.cnf รอไว้โดยมีเนื้อหาเป็นดังนี้

[mysqld]
bind_address=0.0.0.0
server-id=2
log_bin=/var/log/mysql/mysql-bin.log
binlog_do_db=test
relay-log=/var/log/mysql/mysql-relay-bin.log

ให้สังเกตว่า server-id จะต้องไม่ตรงกัน ในที่นี้เครื่อง Master มี server-id=1 ส่วน Slave มี server-id=2

5. ติดตั้ง Docker ของ MySQL ที่เครื่อง Master

ไปที่เครื่อง Master 10.1.2.20 แล้วสร้าง docker network ด้วยคำสั่ง

docker network create repl \
--subnet=172.32.0.0/24 \
--ip-range 172.32.0.0/24 \
--gateway=172.32.0.1

ในที่นี้ใช้ subnet เป็น 172.32.0.0/24 (Class C) ท่านอาจจะเปลี่ยนเป็น subnet อื่นได้ (สำหรับ docker network ใช้ subnet ที่เป็น private subnet ขึ้นต้นด้วย 172.x.x.x)

ไปที่เครื่อง Master 10.1.2.20 แล้วรันคำสั่ง docker run เพื่อรันคอนเทนเนอร์ของ MySQL ตัวที่จะใช้เป็น Master ดังนี้

docker run -d \
--net=repl \
--restart=always \
-p 33061:3306 \
--ip=172.32.0.2 \
--name=mysql-master \
-e MYSQL_ROOT_PASSWORD=1122334455 \
-v /etc/localtime:/etc/localtime:ro \
-v /etc/mysql/master:/etc/mysql/conf.d \
mysql:5.6

สังเกตว่าเราทดลองโดยใช้รหัสผ่านเป็น 1122334455 ท่านสามารถตั้งรหัสผ่านเป็นค่าอื่นได้ตามที่ต้องการ หลังจากรันคอนเทนเนอร์แล้วจะมีพอร์ต 33061 เปิดไว้เราจะทดลองเข้าใช้งานผ่าน mysql console ด้วยคำสั่ง

mysql -uroot -p1122334455 -h10.1.2.20 -P33061 --protocol=tcp

หากพบว่าสามารถเชื่อมต่อได้สำเร็จ แสดงว่าปัจจุบันเราได้ MySQL ที่จะใช้เป็น Master แล้ว

6. ติดตั้ง Docker ของ MySQL ที่เครื่อง Slave

ไปที่เครื่อง Slave 10.1.2.30 แล้วสร้าง docker network ด้วยคำสั่ง

docker network create repl \
--subnet=172.32.0.0/24 \
--ip-range 172.32.0.0/24 \
--gateway=172.32.0.1

ไปที่เครื่อง Slave 10.1.2.30 แล้วรันคำสั่ง docker run เพื่อรันคอนเทนเนอร์ของ MySQL ที่จะใช้เป็น Slave ดังนี้

docker run -d \
--net=repl \
--restart=always \
-p 33062:3306 \
--ip=172.32.0.3 \
--name=mysql-slave \
-e MYSQL_ROOT_PASSWORD=1122334455 \
-v /etc/localtime:/etc/localtime:ro \
-v /etc/mysql/slave:/etc/mysql/conf.d \
mysql:5.6

ติดตั้ง MySQL Client ที่เครื่อง Slave ด้วยเพื่อให้ใช้คำสั่ง mysql ได้หลังจากนั้นทดลองเชื่อมต่อด้วยคำสั่ง

mysql -uroot -p1122334455 -h10.1.2.30 -P33062 --protocol=tcp

หากพบว่าสามารถเชื่อมต่อได้สำเร็จ แสดงว่าปัจจุบันเรามี MySQL ที่เครื่อง Slave เพื่อทดลองต่อไปแล้ว

7. สร้างผู้ใช้สำหรับทำ Replication

ไปที่เครื่อง Master 10.1.2.20 แล้วเชื่อมต่อ MySQL ด้วยคำสั่ง

mysql -uroot -p1122334455 -h10.1.2.20 -P33061 --protocol=tcp

แล้วใช้คำสั่งสร้างผู้ใช้ชื่อว่า repluser สำหรับใช้เชื่อมต่อจาก Slave มาที่ Master ดังนี้

GRANT REPLICATION SLAVE ON *.* TO repluser@'%' IDENTIFIED BY 'replpass';
FLUSH PRIVILEGES;

รันคำสั่งเพื่อปิดการใช้งนตารางทุกตาราง

FLUSH TABLES WITH READ LOCK;

รันคำสั่งเพื่อแสดงสถานะของเครื่อง Master

SHOW MASTER STATUS;

จะได้ผลลัพธ์คล้ายกับภาพต่อไปนี้

รูปภาพที่ 1 ตรวจสอบสถานะของเครื่อง Master (ตำแหน่ง 393 ไฟล์ mysql-bin.000004)

ถ้าพบว่าปัจจุบันไม่มีตารางไหนถูกใช้งานอยู่เลยให้ปลดล็อคตาราง

UNLOCK TABLES;

8. สร้างฐานข้อมูลรอไว้ทดลอง Replication

ที่ตัวเครื่อง Master ให้สร้างฐานข้อมูลชื่อว่า test รอไว้ก่อน โดยเข้า mysql console แล้วใช้คำสั่งดังนี้

create database test character set 'utf8';

9. การจัดเตรียมเครื่อง Slave

ไปที่เครื่อง Slave 10.1.2.30 แล้วใช้คำสั่งเชื่อมต่อเข้า MySQL ดังนี้

mysql -uroot -p1122334455 -h10.1.2.30 -P33062 --protocol=tcp

หลังจากนั้นใช้คำสั่งต่อไปนี้บอกให้ MySQL เครื่อง Slave รู้ว่าจะไปดึงข้อมูลจาก Master ที่อยู่ที่ตำแหน่งไหน

CHANGE MASTER TO master_host='10.1.2.20', 
master_port=33061, 
master_user='repluser',
master_password='replpass', 
master_log_file='mysql-bin.000004', 
master_log_pos=1;

สังเกตว่าจะต้องชี้ IP Address ของเครื่อง Master และ port ต้องถูกต้อง และ master_user และ master_password จะต้องตรงกับที่เครื่อง Master ได้จัดเตรียมไว้ ส่วน master_log_file จะต้องตรงกับที่ Master มีบันทึกไว้จริงๆดังนั้นหากบนเครื่องของท่านใช้ master_log_file ชื่ออื่นให้ท่านเปลี่ยนชื่อไฟล์นี้ให้ตรงด้วย

ที่ mysql console ของเครื่อง Slave 10.1.2.30 ให้ท่านสตาทร์ตัว Slave ด้วยคำสั่ง

start slave;

เสร็จแล้วตรวจสอบว่าปัจจุบัน process ของ slave ทำงานอยู่ด้วยคำสั่ง

show slave status\G;

เมื่อทำมาถึงขั้นตอนนี้ให้ตรวจสอบว่า status ไม่ปรากฏข้อความ Error แสดงว่าสามารถดึงข้อมูลจากเครื่อง Master มาที่ Slave ได้แล้ว

10. สร้างตารางและข้อมูลทดสอบ

ไปที่เครื่อง Master 10.1.2.20 แล้วเข้า mysql console ด้วยคำสั่ง

mysql -uroot -p1122334455 -h10.1.2.20 -P33061 --protocol=tcp

แล้วรันคำสั่ง

use test;
create table person (id integer primary key, name varchar(50));
insert into person values (1, 'John');
insert into person values (2, 'Jack');

จะเป็นการสร้างตาราง person เข้าไปในฐานข้อมูล test และเพิ่มแถว 2 แถวเป็นตัวอย่างเอาไว้

ไปที่เครื่อง Slave 10.1.2.30 แล้วเชื่อมต่อ mysql console ด้วยคำสั่ง

mysql -uroot -p1122334455 -h10.1.2.30 -P33062 --protocol=tcp

แล้วรันคำสั่ง

use test;
select * from person;

จะพบข้อมูล 2 แถวถูกดึงจาก Master มาปรากฏที่ Slave อย่างถูกต้องนั่นแสดงว่า Replication ทำงานถูกต้อง

ทดลองแก้ไขข้อมูลที่เครื่อง Master

update person set name = 'Jim' where id = 1;

แล้วไปที่เครื่อง Slave ดึงข้อมูลออกมาดูว่าถูกแก้ไขหรือไม่

select * from person;

ไปที่เครื่อง Master ทดลองลบข้อมูลทิ้งไป

delete from person where id = 1;

แล้วกลับมาที่เครื่อง Slave ลองดึงข้อมูลมาดู

select * from person;

ท่านควรจะพบว่าข้อมูลถูกลบตามเครื่อง Master เรียบร้อย เมื่อทดลองมาถึงขั้นตอนนี้ท่านจะพบว่า การ Create Table, Insert, Update, Delete ได้รับการโอนถ่ายข้อมูลจาก Master มาที่ Slave อย่างถูกต้อง ซึ่งแสดงว่าเราประสบความสำเร็จในการติดตั้ง MySQL Replication แล้ว