Intro MySQL replication

2015/09/01

Categories: mysql

1. Giới thiệu

Right tool for right job. Trước tiên phải hiểu là MySQL Replication không phải là giải pháp giải quyết mọi bài toán về quá tải hệ thống cơ sở dữ liệu. Để mở rộng một hệ thống ta có hai phương pháp mở rộng là scale up và scale out. Bắt đầu với 1 máy chủ thì hai phương pháp trên được diễn giải như sau:

Scale up có nghĩa là với một máy chủ ta làm cách nào đó để nó có thể phục vụ nhiều hơn số lượng kết nối, truy vấn. Nghĩa là giá trị 1/(số kết nối phục vụ) càng nhỏ thì càng tốt. Để đạt được mục đích này thì có 2 phương pháp:

Scale out là giải pháp tăng số lượng server và dùng các giải pháp load-balacer để phân phối truy vấn ra nhiều server. Ví dụ bạn có 1 server có khả năng phục vụ 500 truy vấn. Nếu ta dựng thêm 5 server nữa có cấu hình tương tự, đặt thêm một LB phía trước để phân phối thì có khả năng hệ thống có thể phục vụ đc 5x500 truy vấn đồng thời.

MySQL Replication là một giải pháp scale out (tăng số lượng instance MySQL) nhưng không phải bài toán nào cũng dùng được. Các bài toán mà MySQL Replication sẽ giải quyết tốt:

1.1 Scale Read

Scale Read thường gặp ở các ứng dụng mà số truy vấn đọc dữ liệu nhiều hơn ghi, tỉ lệ read/write có thể 8020 hoặc hơn. Các ứng dụng thường gặp là báo, trang tin tức.

Với scale read ta sẽ chỉ có một Master instance phục vụ cho việc đọc/ghi dữ liệu. Có thể có một hoặc nhiều Slave instance chỉ phục vụ cho việc đọc dữ liệu

Một số ứng dụng write nhiều (thương mại điện tử) cũng có sử dụng MySQL Replication để scale out hệ thống

1.2 Data Report

Một số hệ thống cho phép một số người (leader, manager, người làm report, thống kê, data) truy cập vào dữ liệu trên production phục vụ cho công việc của họ. Việc chọc thẳng vào data production sẽ rất nguy hiểm vì:

Việc setup một máy chủ làm data report (application cũng sẽ không kết nối tới server này) làm giảm thiểu 2 rủi ro trên

1.3 Real time backup

Với cơ sở dữ liệu lớn việc backup không thể thực hiện thường xuyên được (hàng giờ, hàng phút). Với các ứng dụng giao dịch tài chính, thanh toán, TMDT nếu bị mất dữ liệu 1 giờ, 1 ngày thì thiệt hại sẽ rất lớn (máy chủ chính tư dưng bị hỏng). Real time backup là một giải pháp bổ sung cho offline backup, chạy đồng thời cả 2 phương pháp này để bảo đảm sự an toàn cho dữ liệu.

Tuy nhiên, việc dùng replicate để backup dữ liệu chỉ đảm bảo nếu đĩa cứng của master bị hỏng, trong trường hợp human error khi xóa nhầm dữ liệu, hành động xóa sẽ được replicate sang slave luôn => vẫn bị mất dữ liệu.

Để tránh xảy ra trường hợp trên và giảm thiểu rủi ro mất dữ liệu, mình có giới thiệu một bài khác delay-replication.

2. Hoạt động như thế nào?

2.1 Một số mô hình

mysql-replication

Với cả hai mô hình ta luôn chỉ có 1 Master database phục vụ cho Write dữ liệu, có thể có một hoặc nhiều Slave database. Tùy từng mô hình ta có thể cấu hình mỗi web node connect vào một Slave DB tương ứng hoặc có thể dùng một LB đặt trước cụm Slave để LB tự động phân phối connection vào từng Slave DB theo thuật toán của LB

mysql-replication-lb

2.2 Cách hoạt động

Trên Master:

Trên Slave:

arch

Về logic mỗi Slave DB sẽ chỉ nhận dữ liệu từ Master DB, mọi hành động cập nhật dữ liệu BẮT BUỘC phải được thực hiện trên Master. Về nguyên tắc nếu ghi dữ liệu trực tiếp lên Slave DB => hỏng replication. Nhưng thực chất ta hoàn toàn có thể ghi dữ liệu trên Slave miễn sao khi Slave đọc binlog và apply không đụng gì tới những trường dữ liệu mà ta mới ghi vào thì sẽ không bị lỗi (mục này sẽ nói thêm ở các phần sau)

Với MySQL 5.5 thì mỗi slave sẽ chỉ có một slave_thread connect tới Master, tuy nhiên từ phiên bản 5.6 chúng ta có thể cấu hình nhiều slave_thread để việc apply bin log tới các slave nhanh hơn.

3. Hướng dẫn cài đặt và cấu hình

Mô hình:

Trên Master DB

Cấu hình my.cnf

event-scheduler = on
bind-address = 172.17.0.1
server-id = 1

log-bin
binlog-format=row
binlog-do-db=dwh_prod
binlog-ignore-db=mysql
binlog-ignore-db=test

sync_binlog=0
expire_logs_days=2

Tạo user replication

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'172.16.0.2' IDENTIFIED BY 'p@ssword';
FLUSH PRIVILEGES;

Tạo schema, dữ liệu để test

CREATE SCHEMA dwh_prod CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE tb1 (
 id INT,
 data VARCHAR(100)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
CREATE TABLE tb2 (
 id INT,
 data VARCHAR(100)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SHOW TABLES;

Trên Slave DB

Cấu hình my.cnf

event_scheduler=off
bind-address = 172.17.0.2
server-id=2

log-bin
binlog-format=row
binlog-do-db=dwh_prod
binlog-ignore-db=mysql
binlog-ignore-db=test

transaction-isolation=read-committed
sync_binlog=0
expire_logs_days=2

Tạo replication và kiểm tra

Nguyên tắc khi tạo replication là phải LOCK tất cả các table trên Master DB, để dữ liệu không thay đổi, sau đó xác định binlog và position, 2 thông số dùng để cấu hình trên Slave xác định đoạn dữ liệu bắt đầu đồng bộ

Trên Master DB

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

+----------------+----------+--------------+------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------+----------+--------------+------------------+
| m01-bin.000001 |      827 | dwh_prod     | mysql,test       |
+----------------+----------+--------------+------------------+

Giá trị cần quan tâm là

Sau đó ta sẽ dump dữ liệu từ Master DB và đẩy qua Slave DB (sau khi dump xong có thể UNLOCK TABLES; để Master DB có thể hoạt động lại).

mysqldump -uroot -p dwh_prod > dwh_prod_03072015.sql
rsync -avz -P -e'ssh' dwh_prod_03072015.sql root@172.17.0.2:/root/

Trên Slave

mysql -uroot -p dwh_prod < /root/dwh_prod_03072015.sql

> CHANGE MASTER TO MASTER_HOST='172.17.0.1',MASTER_USER='slave_user', MASTER_PASSWORD='p@ssword', MASTER_LOG_FILE='m01-bin.000001', MASTER_LOG_POS=827;
> START SLAVE
> SHOW SLAVE STATUS\G

Một số thông tin đã được lược bỏ cho dễ nhìn

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.0.0.1
                  Master_User: slave_user
              Master_Log_File: m01-bin.000001
          Read_Master_Log_Pos: 827
               Relay_Log_File: m02-relay-bin.000002
                Relay_Log_Pos: 251
        Relay_Master_Log_File: m01-bin.000001
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 827
              Relay_Log_Space: 405
        Seconds_Behind_Master: 0
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
             Master_Server_Id: 1

Các thông số cần quan tâm là

Hai thông số đầu tiên là lỗi khi Slave DB thực thi các event đọc từ relay log. Thông số Seconds_Behind_Master cho ta biết dữ liệu của Slave DB đang bị trễ (delay, lag) bao nhiêu giây so với Master DB. Các phần sau ta sẽ nói kỹ hơn về replication lag này.

4. Vận hành hệ thống MySQL Replicatione

4.1 Test logic replication

Ở trạng thái bình thường dữ liệu trên Slave DB đã đồng bộ với Master DB. Kiểm tra

Trên Master

mysql> USE dwh_prod
mysql> SHOW TABLES;
+--------------------+
| Tables_in_dwh_prod |
+--------------------+
| tb1                |
| tb2                |
| tb3                |
| tb4                |
+--------------------+

Trên Slave

mysql> USE dwh_prod
mysql> SHOW TABLES;
+--------------------+
| Tables_in_dwh_prod |
+--------------------+
| tb1                |
| tb2                |
| tb3                |
| tb4                |
+--------------------+
mysql -e -p 'SHOW SLAVE STATUS\G' | grep -i 'error\|seconds'
                   Last_Error: 
        Seconds_Behind_Master: 0
                Last_IO_Error: 
               Last_SQL_Error:

Mọi thứ đều ổn, không lỗi và không có Lag.

Giờ giả sử ta sẽ tạo một table với tên là tb00 trên Slave và kiểm tra xem có đúng là khi ghi dữ liệu lên Slave DB thì replication có bị hỏng hay không.

mysql> CREATE TABLE tb00 (
 id INT,
 data VARCHAR(100)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql> SHOW TABLES;
+--------------------+
| Tables_in_dwh_prod |
+--------------------+
| tb00               |
| tb1                |
| tb2                |
| tb3                |
| tb4                |
+--------------------+
5 rows in set (0.00 sec)

Kiểm tra các table trên Master DB

mysql> SHOW TABLES;
+--------------------+
| Tables_in_dwh_prod |
+--------------------+
| tb1                |
| tb2                |
| tb3                |
| tb4                |
+--------------------+

Và kiểm tra lại trạng thái của replication

mysql -e 'SHOW SLAVE STATUS\G' | grep -i 'error\|seconds'                                                                                                   
            Last_Error: 
      Seconds_Behind_Master: 0
            Last_IO_Error: 
            Last_SQL_Error:

=> Như ta thấy rõ ràng là dữ liệu trên Slave và Master đã khác nhau (Slave có tb00 nhưng Master thì không) nhưng trạng thái của replication vẫn hoàn toàn ổn.

Giờ chúng ta sẽ thử thêm một trường hợp nữa là trên Master ta sẽ tạo một table tên là tb6 để kiểm tra xem chuyện gì sẽ xảy ra

mysql> CREATE TABLE tb6 (
 id INT,
 data VARCHAR(100)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql> SHOW TABLES;
+--------------------+
| Tables_in_dwh_prod |
+--------------------+
| tb1                |
| tb2                |
| tb3                |
| tb4                |
| tb6                |
+--------------------+

Kiểm tra các table trên Slave DB

mysql> SHOW TABLES;
+--------------------+
| Tables_in_dwh_prod |
+--------------------+
| tb00               |
| tb1                |
| tb2                |
| tb3                |
| tb4                |
| tb6                |
+--------------------+

=> bảng tb6 đã được đồng bộ từ Master qua, kiểm tra trạng thái replication

mysql -e 'SHOW SLAVE STATUS\G' | grep -i 'error\|seconds'
                   Last_Error: 
        Seconds_Behind_Master: 0
                Last_IO_Error: 
               Last_SQL_Error:

=> Mọi thứ vẫn ổn, nghĩa là dù ta có ghi dữ liệu vào Slave, nhưng nếu Master thực thi các câu truy vấn không đụng gì tới dữ liệu được ghi mới ở Slave thì trạng thái của replication vẫn ổn.

Giờ ta sẽ thực hiện thêm một thử nghiệm nữa là trên Master ta tạo một table tên là tb00, trùng với table đã tạo lúc trước ở Slave phía trên và kiểm tra lại trạng thái của replication

Kiểm tra trạng thái replication trên Slave

mysql -e 'SHOW SLAVE STATUS\G' | grep -i 'error\|seconds'
              Last_Error: Error 'Table 'tb00' already exists' on query. Default database: 'dwh_prod'. Query: 'CREATE TABLE tb00 (
   Seconds_Behind_Master: NULL
           Last_IO_Error:
          Last_SQL_Error: Error 'Table 'tb00' already exists' on query. Default database: 'dwh_prod'. Query: 'CREATE TABLE tb00 (

=> như ta thấy hệ thống báo lỗi do trên Slave không thể thực thi hành động tạo table tb00 từ Master đẩy xuống (do table này đã tồn tại trước đó)

Kết Luận: Việc ghi dữ liệu vào Slave là có thể thực hiện được, nhưng sẽ gây ra rủi ro hỏng replication ở một lúc nào đó. Nhất là các câu truy vấn dạng SELECT … UPDATE. Tốt nhất là nên tránh ghi dữ liệu vào Slave

4.2 Replication Lag

Replication Lag là độ trễ dữ liệu của Slave so với Master. Khi triển khai một hệ thống MySQL Replication thì Lag là vấn đề chắc chắn gặp phải. Ta chỉ có thể giảm thiểu độ trễ dữ liệu trong mức chấp nhận được chứ không thể không có lag. Lí do là việc đồng bộ dữ liệu là Asynchronous, nghĩa là các Slave server không cần thông báo cho Master biết khi transaction thực hiện trên Slave thành công -> điều này giúp giữ nguyên hiệu suất (khác với cơ chế đồng bộ synchronous, một transaction được gọi là thành công khi nó committed trên master server và master server nhận được một thông báo từ slave server là transaction này đã được write và committed. Quá trình này đảm bảo tính thống nhất giữa master và slave server nhưng đồng thời nó làm giảm hiệu suất đi một nữa do các vấn đề về network, bandwidth, location.)

Vấn đề của replication lag ảnh hưởng tới các truy vấn vừa write dữ liệu xuống là read dữ liệu lên liền. Ví dụ

Một trang thương mại điện tử có tính năng add vào giỏ hàng một sản phẩm. Sau khi sản phẩm được add vào giỏ hàng sẽ trừ số lượng trong tồn kho. 2 user thực hiện mua sản phẩm đó (sản phẩm đó có số lượng tồn kho là 1). Cả 2 đều thấy sản phẩm đó trên website hiển thị trạng thái CÒN HÀNG. Khi một user mua sản phẩm đó và thanh toán thành công. Do độ trễ dữ liệu (ví dụ 5s) nên dữ liệu chưa đc cập nhật tồn kho xuống Slave là sản phẩm đã hết hàng. Khi user đó add giỏ hàng và thanh toán thì lúc này dữ liệu mới cập nhật và trả về mã lỗi là thanh toán không thành công do số lượng tồn kho không đáp ứng => ảnh hưởng tới trải nghiệm của user trên hệ thống.

Thường với những trường hợp này (truy vấn write xong là read liền) thì nên sử dụng cấu hình truy vấn trên Master (đây là lí do Master có thể vừa write vừa read chứ không nhất thiết là chỉ có write)

4.3 Lb mysql và healthchk

Như 1 trong 2 mô hình phía trên thì với mô hình thứ 2 ta có thể dùng haproxy làm lb cho các MySQL Instance.

Với mô hình 1 nhược điểm là nếu MySQL instance bị delay quá lâu, server quá tải hoặc rủi ro nhất là instace đó bị down thì ta không có cách nào check hoặc remove instance đó ra được.

Với mô hình 2 nhược điểm là ta mất thêm 1 layer (haproxy) nữa mới có thể kết nối tới MySQL (tốn thời gian, xử lí nhiều lớp) nhưng lợi điểm là có thể cấu hình healthchk, hoặc remove instance theo một số điều kiện.

5. Một số lưu ý

5.1 Vấn đề về server, phần cứng

Các vấn đề về CPU, RAM, đĩa cứng (kích thước, loại đĩa cứng, SSD hay HDD, tốc độ đọc ghi của đĩa cứng)

Với một hệ thống DB các thông số phần cứng NÊN quan tâm là

Với đĩa cứng

Khác với các ứng dụng khác như web, static (thường CPU không cần nhiều core, đĩa cứng không cần nhiều và nhanh), máy chủ CSDL sẽ cần nhiều các thông số trên

Với AWS khi chọn Instance cũng nên chú ý các điểm trên

5.2 Các vấn đề về kích thước dữ liệu

Vấn đề kích thước dữ liệu ảnh hưởng khá nhiều đến vận hành một hệ thống MySQL Replication. Dữ liệu lớn thì quá trình replication đầu tiên hoặc khi hỏng replication sẽ rất lâu => Slave không thể sử dụng được trong thời gian replication, đến khi Second_Behind_Master = 0 thì mới có thể sử dụng được.

Ngoài ra các yếu tố về ổ đĩa cứng (SSD, tốc độ đọc ghi) cũng ảnh hưởng nhiều đến việc import hoặc apply các binlog từ Master

Dưới đây là một mô tả thực tế:

6. Failover

Một vấn đề khác ngoài chuyện scale đó là nếu master db chết thì chuyện gì xảy ra?. Có một số mindset mà bạn bắt buộc phải hiểu khi chọn giải pháp replication master-slave đó là:

Quay trở lại mô hình 1 master và 2 slave (gọi lần lượt là S1 và S2), ta cần trả lời là nếu master chết thì chuyện gì xảy ra với hệ thống và cách promote một slave lên thay thế master là gì?

Mặc định, Slave vẫn sẽ có binlog, và binlog này là của riêng slave chứ không giống với binlog của master (binlog của master khi đẩy qua slave sẽ thành relay-log), có nghĩa là nếu S1 đẩy lên làm master thì S2 sẽ không còn đồng bộ với S1 nữa và ta sẽ cần build lại S2.

Để giải quyết vấn đề này, mysql khuyến cáo chúng ta bật --skip-log-slave-updates trên Slave, chuyện này đảm bảo:

Sau khi việc promote hoàn thành, ta có thể cập nhật lại ở phía client địa chỉ củ a S1 và hoàn thành việc bảo trì hệ thống. Tuy nhiên để ý là quá trình trên là thủ công và ta vẫn có downtime trong quá trình promote.

Tuy nhiên, điều trên chỉ đúng chỉ đúng khi slave sync với master trước khi master chết với second_behind_master = 0.

https://dev.mysql.com/doc/refman/8.0/en/replication-solutions-switch.html

7. Semi-synchronous

Có một vấn đề với asynchronous đó là nếu bạn có nhu cầu đọc ngay dữ liệu vừa ghi xuống thì có thể dữ liệu sẽ sai, do slave chưa kịp apply dữ liệu từ master (lag dữ liệu), có 2 cách giải quyết tạm:

Semi-synchronous là một kiểu lai giữa asynchronous và synchronous. Bình thường nếu xài synchronous thì càng nhiều slave thì càng giảm tốc độ ghi dữ liệu, do slave phải committed và trả lời ngược về master. Với semi-synchronous thì master coi như ghi thành công là khi có tối thiểu một slave đã nhậnghi ra relay log event mà master gửi qua. Điểm khác biệt là không cần tất cả các slave gửi tín hiệu ngược lại master, và event cũng không bắt buộc phải được execute và commited trên slave, chỉ cần đảm bảo là đã nhận và ghi ra relay log là đủ.

Như mô tả ở trên thì slave vẫn có thể không có dữ liệu nếu relay log bị tác động với con người, hoặc server bị hỏng ngay khi chưa kịp apply relay log, tuy nhiên nhờ việc đảm bảo binlog event đã đc nhận với slave và ghi xuống đĩa đã làm giảm thời gian delay và vấn đề về data race condition có thể được hạn chế phần nào.