Tăng performance của SQL Database với replication [Phần 1]

Đặt vấn đề

Slow Query hoặc timeout là một vấn đề thường gặp khi làm việc với database, vấn đề này ảnh hưởng rất lớn đến performance và trải nghiệm người dùng, thậm chí còn dẫn tới downtime toàn bộ hệ thống. Cùng xem xét các giải pháp để giải quyết vấn đề này:

Tối ưu logic

Các tối ưu về mặt logic phần mềm kể đến như tối ưu các query, gắn index... Đây là những giải pháp lâu dài và bền vững cho cả hệ thống, nhưng đòi hỏi nhiều thời gian để thực hiện và kiểm thử.

Mở rộng phần cứng

Bên cạnh các giải pháp phần mềm kể trên, ta cũng cần cân nhắc khía cạnh tăng tài nguyên về mặt xử lý, đồng nghĩa với việc giảm tối đa các thay đổi về mặt logic mà vẫn tăng được performance khi làm việc với cơ sở dữ liệu. Giải pháp này sẽ đáp ứng nhanh chóng về mặt thời gian và giảm thiểu rủi ro, nhưng cũng sẽ tiêu tốn tài nguyên phần cứng hơn việc tối ưu logic phục vụ truy vấn.

Bài viết này sẽ giới thiệu giải pháp về mở rộng phần cứng để tăng performance khi làm việc với cơ sở dữ liệu.

Nguyên lý và thiết kế

Thông thường với các hệ thống, khi lượng dữ liệu không quá lớn, chúng ta sẽ thực hiện tất cả các thao tác đọc/ghi trên cùng một cơ sở dữ liệu như sau:

Tuy nhiên, theo thời gian, lượng dữ liệu gia tăng, cùng với độ ổn định của cơ sở dữ liệu không được đảm bảo. Để tăng hiệu suất đọc ghi cho cơ sở dữ liệu, ngoài việc tối ưu query và đánh index như trên thì còn giải pháp khác nữa là tạo bản sao của cơ sở dữ liệu.

Điều này cung cấp khả năng dự phòng khi một trong các máy chủ cơ sở dữ liệu bị lỗi và có thể cải thiện tính khả dụng, khả năng mở rộng cũng như hiệu suất tổng thể của cơ sở dữ liệu. Việc đồng bộ hóa dữ liệu trên nhiều cơ sở dữ liệu riêng biệt được gọi là sao chép (replication).

MySQL là một hệ quản trị cơ sở dữ liệu có quan hệ, và là cơ sở dữ liệu quan hệ mã nguồn mở phổ biến nhất hiện nay. Nó được cài đặt sẵn một số tính năng sao chép tích hợp, cho phép chúng ta duy trì nhiều bản sao dữ liệu của mình.

Trong bài viết này mình sẽ hướng dẫn các bạn cách cấu hình phiên bản MySQL trên một máy chủ làm cơ sở dữ liệu nguồn, sau đó cấu hình các phiên bản MySQL trên máy chủ khác hoạt động như các bản sao của nó.

Để cấu hình được như trên, mình đã tạo sẵn 4 VM chạy hệ điều hành ubuntu 20.04 và cài sẵn mysql với cấu hình như sau:

Trong đó chức năng của từng thành phần như sau:

• mysql-master đóng vai trò là cơ sở dữ liệu nguồn, chúng ta có thể thực hiện các câu lệnh sql như thêm, sửa xóa, lấy dữ liệu từ cơ sở dữ liệu nguồn này. Master DB cung cấp chức năng cả đọc, ghi và làm nguồn đồng bộ dữ liệu sang các Slave DB.
• mysql-slave1, mysql-slave2 sẽ đóng vai trò là các bản sao của cơ sở dữ liệu nguồn mysql-master.

Haproxy/Proxy SQL đóng vai trò có nhiệm vụ là load balancer cho các cơ sở dữ liệu. Để migrate từ hệ thống dùng chung một cơ sở dữ liệu sang cơ sở dữ liệu sao chép, với Haproxy hệ thống cần hỗ trợ kết nối với nhiều cơ sở dữ liệu, khi đó haproxy sẽ dựa vào port hoặc IP để điều hướng câu lệnh query từ hệ thống đến các cơ sở dữ liệu.

Còn đối với Proxy SQL thì không cần làm thêm gì cả vì tự nó có thể điều hướng đến các cơ sở dữ liệu dựa trên câu lệnh query trực tiếp từ hệ thống. (Phần setup này mình sẽ nói chi tiết hơn ở bài sau.)

Quay trở lại với chủ đề chính của bài này, chúng ta sẽ bắt đầu đi cấu hình với VM cài cơ sở dữ liệu nguồn (mysql-master):

Bước 1: Điều chỉnh tường lửa máy chủ nguồn

SSH vào mysql-master với địa chỉ ip là 35.240.187.215. Mở cổng 3306 (cổng mặc định của mysql) cho phép các con slave truy cập để đồng bộ dữ liệu từ master thông qua cổng này. Để mở cổng 3306 bạn cần thêm cổng này vào firewalls như trên Sunteco Cloud hoặc Security Group trên các nền tảng cloud khác.

Dùng thử Sunteco Cloud miễn phí ngay

Tiếp theo thực hiện câu lệnh:

Generated-by-Nova@mysql-master:~$ sudo ufw allow from replica_server_ip to any port 3306

Bước 2: Cấu hình cơ sở dữ liệu nguồn (mysql-master)

Để cơ sở dữ liệu MySQL nguồn của bạn bắt đầu sao chép dữ liệu, bạn cần thực hiện một số thay đổi đối với cấu hình của nó. Bạn cần edit một vài thông số trong file mysqld.cnf. Mở file với câu lệnh như sau:

Generated-by-Nova@myql-master:~$ sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

Tìm đến dòng :

. . .

bind-address = 127.0.0.1

. . .

Chỉnh thành:

bind-address = 0.0.0.0

Cho phép các cơ sở dữ liệu ở các máy chủ khác có thể truy cập vào máy chủ cơ sở dữ liệu nguồn (mysql-master) để tiến hành sao chép

Tiếp theo tìm đến dòng:

. . .

# server-id = 1

. . .

Bỏ comment dòng này để đánh dấu địa chỉ của cơ sở dữ liệu nguồn (mysql-master), bạn có thể thay số 1 thành một số khác bất kỳ miễn là nó không trùng với các cơ cơ sở dữ liệu sao chép

Các cơ sở dữ liệu sao chép sẽ phải đọc file log nhị phần của cơ sở dữ liệu nguồn (mysql-master) để biết khi nào cũng như cách sao chép chép dữ liệu nguồn, do đó bạn cần tìm và bỏ comment dòng này:

. . .

log_bin = /var/log/mysql/mysql-bin.log

. . .

sau khi hoàn thành việc sửa file chúng ta lưu lại và restart mysql-master để nó chạy với cấu hình mới:

Generated-by-Nova@mysql-master:~$ sudo systemctl restart mysql

Bước 3 Tạo các tài khoản cho phép truy cập dữ liệu từ các cơ sở dữ liệu sao chép

Mỗi bản sao mysql kết nối đến cơ sở dữ liệu nguồn (mysql-master) thông qua username và password. Để tạo tài khoản bạn thực hiện các câu lệnh sau:

Generated-by-Nova@myql-master:~$ mysql -u root -p

Sau đó điền password với tài khoản root mà bạn đã tạo khi cài đặt mysql.

Tạo các tài khoản và cấp quyền cho các bản sao mysql:

mysql> CREATE USER 'slave1'@'34.143.249.6' IDENTIFIED WITH mysql_native_password BY 'Slave1@123';

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'34.143.249.6'; 

mysql> FLUSH PRIVILEGES

mysql> CREATE USER 'slave2'@'35.187.255.209' IDENTIFIED WITH mysql_native_password BY 'Slave2@123'; 

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave2'@'35.187.255.209'; 

mysql> FLUSH PRIVILEGES;

Bước 4: Cấu hình đồng bộ dữ liệu từ nguồn qua tọa độ log nhị phân

MySQL triển khai bản sao bằng cách sao chép các sự kiện cơ sở dữ liệu từ tệp nhật ký nhị phân của nguồn theo từng dòng và triển khai từng sự kiện trên bản sao.

Sau khi tạo tài khoản xong ở bước 3, bạn chạy câu lệnh sau để đóng tất cả các bảng đang mở trên cơ sở dữ liệu nguồn (mysql-master):

mysql> FLUSH TABLES WITH READ LOCK;

Chạy câu lệnh tiếp theo để xem thông tin trạng thái hiện tại của file log nhị phân cơ sở dữ liệu nguồn (mysql-master):

mysql> SHOW MASTER STATUS;

Sau khi chạy lệnh trên, bạn sẽ nhìn thấy một bảng tương tự như dưới đây:

Đây là vị trí mà từ đó bản sao sẽ bắt đầu sao chép các sự kiện cơ sở dữ liệu.

Ghi lại tên tệp (File) và giá trị vị trí (Position), vì bạn sẽ cần những thứ này sau khi bắt đầu sao chép.

Nếu đây là lần đầu tiên bạn tạo cơ sở dữ liệu nguồn (mysql-master) và không có dữ liệu cần migrate sang cơ sở dữ liệu sao chép thì bạn thực hiện câu lệnh sau và chuyển sang bước tiếp theo:

mysql> UNLOCK TABLES;

Nếu trước đó bạn đã có cơ sở dữ liệu nguồn (mysql-master) và giờ bạn muốn tạo thêm các bản sao chép thì bạn cần migrate dữ liệu nguồn sang các bản sao chép

Thực hiện câu lệnh sau để tạo file db.sql export cơ sở dữ liệu nguồn (mysql-master):

Generated-by-Nova@myql-master:~$ sudo mysqldump -u root “database name” > db.sql

Gửi file mysqldump.sql qua các VM cài đặt cơ sở dữ liệu sao chép mysql-slave1 và mysql-slave2 : 

Generated-by-Nova@myql-master:~$ scp -i <private-key.pem> Generated-by-Nova@34.143.249.6:~/tmp/

Generated-by-Nova@myql-master:~$ scp -i <private-key.pem> Generated-by-Nova@35.187.255.209:~/tmp/

Tiếp theo bạn cần truy cập vào các các bản sao mysql : mysql-slave1, mysql-slave2

Thực hiện SSH vào VM cài mysql-slave1 qua ip 34.143.249.6

Đăng nhập vào mysql với tài khoản root:

Generated-by-Nova@mysql-slave1:~$ mysql -u root -p

Thực hiện câu lệnh sau để migrate dữ liệu cho các bản sao:

Generated-by-Nova@mysql-slave1:~$ sudo mysql “database name” < /tmp/db.sql

Thực hiện migrate dữ liệu với mysql-salve2 tương tự như mysql-slave1

Sau khi migrate dữ liệu xong ta quay lại VM chứa cơ sở dữ liệu nguồn (mysql-master) và thực hiện câu lệnh:

mysql> UNLOCK TABLES;

Bước 5: Cấu hình các cơ sở dữ liệu sao chép

Thực hiện chỉnh sửa file mysqld.cnf trên các các cơ sở dữ liệu sao chép

Thực hiện câu lệnh sau để mở và chỉnh sửa file:

Generated-by-Nova@mysql-slave1:~$ sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

Tìm đến dòng :

. . .

bind-address = 127.0.0.1

. . .

Chỉnh thành:

bind-address = 0.0.0.0

Tìm đến dòng và chỉnh sửa server-id như dưới đây

server-id = 2

Sau đó lưu file lại và restart mysql bằng câu lệnh sau :

Generated-by-Nova@mysql-slave1:~$ sudo systemctl restart mysql

Tiếp theo đăng nhập vào mysql với tài khoản root:

Generated-by-Nova@mysql-slave1:~$ mysql -u root -p

Tiếp theo chạy các lệnh, sau để định cấu hình một số cài đặt sao chép MySQL:

mysql> CHANGE REPLICATION SOURCE TO

mysql> SOURCE_HOST='35.240.187.215', 

mysql> SOURCE_USER='slave1',

mysql> SOURCE_PASSWORD='Slave1@123,

mysql> SOURCE_LOG_FILE='mysql-bin.000147,

mysql> SOURCE_LOG_POS=157;

Trong đó:

SOURCE_HOST là ip của mysql-master SOURCE_USER là username của tài khoản tạo Bước 3 SOURCE_PASSWORD là password của tài khoản tạo ở Bước 3 SOURCE_LOG_FILE là tên File trong bảng ở Bước 4 SOURCE_LOG_POS là giá trị vị trí (Position) ở Bước 4

Sau khi cấu hình xong bạn cần kích hoạt cơ sở dữ liệu sao chép bằng câu lệnh:

mysql> START REPLICA;

Kiểm tra chi tiết trạng thái hoạt động của bản sao bằng câu lệnh sau:

mysql> SHOW REPLICA STATUS\G;

Kết quả của câu lệnh trên sẽ như sau:

Đối với mysql-slave2 làm tương tự như trên chỉ cần thay:

server-id = 3

Bước 6: Thực hiện test đồng bộ dữ liệu từ cơ sở dữ liệu nguồn (mysql-master) và các bản sao

Từ cơ sở dữ liệu nguồn (mysql-master) bạn đăng nhập vào mysql bằng tài khảo root

Tạo một cơ sở dữ liệu nguồn (mysql-master) tên là test bằng câu lệnh sau:

mysql> CREATE DATABASE test;

Tiếp theo bạn tạo bảng :

mysql> USE test;

mysql> CREATE TABLE example_table (example_column varchar(30));

Tạo dữ liệu cho bảng:

mysql> INSERT INTO example_table VALUES

mysql> ('This is the first row'),

mysql> ('This is the second row'),

mysql> ('This is the third row');

Thực hiện câu lệnh sau để lấy dữ liệu từ bảng: 

mysql> select * from example_table;

Kết quả trả về sẽ như sau:

Tiếp theo chúng ta cần truy cập vào 2 con mysql-slave1 và mysql-salve2 để kiểm tra dữ liệu đã được đồng bộ hay chưa

Từ con mysql-slave1 bạn đăng nhập vào mysql với tài khoản root:

Generated-by-Nova@mysql-slave1:~$ mysql -u root -p

Thực hiện câu lệnh sau để lấy dữ liệu từ bảng được đồng bộ từ mysql-master :

mysql> use test;

mysql> select * from example_table;

Kết quả tra như sau:

Bạn có thể thấy rằng kết quả trả ra dữ liệu giống với mysql-master ở trên.

Vậy là mình đã hướng dẫn xong các bạn các bước cài đặt và cấu hình cho để tạo các cơ sở dữ liệu sao chép từ cơ sở dữ liệu nguồn (mysql-master).

Cảm ơn bạn đã đọc đến đây. Hy vọng bài hướng dẫn này hữu ích với các bạn! 🎉.

Dùng thử hệ sinh thái Sunteco Cloud

Đọc thêm các bài viết công nghệ chuyên sâu tại [Tech Sharing](https://sunteco.vn/tin-tuc/tech-sharing/?utm_source=viblo&utm_medium=July&utm_campaign=database-replication)