Hướng Dẫn Chuẩn Bị Nâng Cấp MySQL 5.7: Tối Ưu Hiệu Suất và Bảo Mật

MySQL 5.7 là phiên bản ứng cử viên phát hành mới nhất của cơ sở dữ liệu mã nguồn mở nổi tiếng. Nó mang đến những tính năng mở rộng khả năng mở rộng (scalability) mới khiến bạn hẳn háo hức muốn chuyển sang sử dụng.

Để làm nổi bật một trong những thay đổi, khả năng mở rộng đã được cải thiện vượt bậc. Ở mức cao, MySQL 5.7 có khả năng mở rộng tuyến tính trên các máy chủ 48 lõi. Ở mức thấp, MySQL 5.7 cũng có thể hoạt động “ngay lập tức” trên một DigitalOcean Droplet 512 MB (điều mà trước đây không thể thực hiện được nếu không thay đổi cấu hình trong MySQL 5.6).

Để nâng cấp MySQL 5.7 hiệu quả, việc chọn một VPS server mạnh mẽ là yếu tố then chốt. VPS server chất lượng cao đảm bảo tốc độ xử lý nhanh, bảo mật tối ưu và hỗ trợ liên tục. Khám phá các giải pháp VPS server tại DataOnline để tối ưu hóa cơ sở dữ liệu của bạn ngay hôm nay!

Hiệu suất đỉnh mới cho một máy chủ MySQL vượt quá 640K truy vấn mỗi giây, và API memcached, nói chuyện trực tiếp với engine lưu trữ InnoDB, có khả năng xử lý trên 1,1 triệu yêu cầu mỗi giây.

Trước khi bạn vội chạy lệnh mysql_upgrade, hãy chắc chắn rằng mình đã sẵn sàng. Hướng dẫn này sẽ giúp bạn chuẩn bị mọi thứ cần thiết.

Lưu ý: Tính từ ngày 1 tháng 7 năm 2022, DataOnline không còn hỗ trợ việc tạo Droplets FreeBSD mới thông qua Control Panel hoặc API. Tuy nhiên, bạn vẫn có thể khởi tạo Droplets FreeBSD bằng hình ảnh tùy chỉnh. Hãy xem tài liệu sản phẩm của chúng tôi để biết cách nhập hình ảnh tùy chỉnh.

Các thay đổi về tính toàn vẹn dữ liệu, kèm ví dụ

Một thay đổi lớn trong MySQL 5.7 là việc cải thiện tính toàn vẹn dữ liệu sao cho phù hợp hơn với kỳ vọng của các nhà phát triển và DBA dày dặn kinh nghiệm. Trước đây, MySQL sẽ tự động điều chỉnh các giá trị không chính xác về giá trị chính xác gần nhất, nhưng với các mặc định mới, nó sẽ trả về lỗi thay vì điều chỉnh.

Dưới đây là 5 ví dụ về các truy vấn cần được điều chỉnh để có thể chạy “ngay lập tức” trên MySQL 5.7. Ứng dụng của bạn có sử dụng những hành vi này không?

1) Chèn giá trị âm vào cột Unsigned

Tạo bảng với một cột unsigned:

CREATE TABLE test (  
 id int unsigned  
);

Chèn một giá trị âm.

Hành vi trước đây:

INSERT INTO test VALUES (-1);
Query OK, 1 row affected, 1 warning (0.01 sec)

MySQL 5.7:

INSERT INTO test VALUES (-1);  
ERROR 1264 (22003): Out of range value for column 'a' at row 1

2) Chia cho số 0

Tạo bảng test:

CREATE TABLE test2 (  
 id int unsigned  
);

Thử chia cho số 0.

Hành vi trước đây:

INSERT INTO test2 VALUES (0/0);  
Query OK, 1 row affected (0.01 sec)

MySQL 5.7:

INSERT INTO test2 VALUES (0/0);  
ERROR 1365 (22012): Division by 0

3) Chèn chuỗi 20 ký tự vào cột 10 ký tự

Tạo bảng với cột varchar(10):

CREATE TABLE test3 (  
a varchar(10)  
);

Thử chèn một chuỗi dài hơn.

Hành vi trước đây:

INSERT INTO test3 VALUES ('abcdefghijklmnopqrstuvwxyz'); 
Query OK, 1 row affected, 1 warning (0.00 sec)

MySQL 5.7:

INSERT INTO test3 VALUES ('abcdefghijklmnopqrstuvwxyz');  
ERROR 1406 (22001): Data too long for column 'a' at row 1

4) Chèn ngày 0 không chuẩn vào cột Datetime

Tạo bảng với cột datetime:

CREATE TABLE test3 (  
a datetime  
);

Chèn giá trị ‘0000-00-00 00:00:00’.

Hành vi trước đây:

INSERT INTO test3 VALUES ('0000-00-00 00:00:00');  
Query OK, 1 row affected, 1 warning (0.00 sec)

MySQL 5.7:

INSERT INTO test3 VALUES ('0000-00-00 00:00:00');  
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'a' at row 1

5) Sử dụng GROUP BY và Lựa chọn một cột mơ hồ

Tình huống xảy ra khi cột “description” không thuộc GROUP BY và không có hàm tổng hợp (ví dụ MIN hoặc MAX) được áp dụng.

Hành vi trước đây:

SELECT id, invoice_id, description FROM invoice_line_items GROUP BY invoice_id;  
+----+------------+-------------+  
| id | invoice_id | description |  
+----+------------+-------------+  
| 1 | 1 | New socks             |  
| 3 | 2 | Shoes                 |  
| 5 | 3 | Tie                   |  
+----+------------+-------------+  
3 rows in set (0.00 sec)

MySQL 5.7:

SELECT id, invoice_id, description FROM invoice_line_items GROUP BY invoice_id;  
ERROR 1055 (42000): Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'invoice_line_items.description' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Hiểu các hành vi được thiết lập bởi sql_mode

Theo cách nói của MySQL, mỗi hành vi đã nêu ở phần trước đều chịu ảnh hưởng của cái gọi là sql_mode.

Tính năng này ra mắt từ MySQL 4.1 (2004), tuy nhiên không được tích hợp mặc định. MySQL 5.7 bật các chế độ sau theo mặc định:

● ONLY_FULL_GROUP_BY
● STRICT_TRANS_TABLES
● NO_ENGINE_SUBSTITUTION
● NO_AUTO_CREATE_USER

Chế độ STRICT_TRANS_TABLES cũng trở nên nghiêm ngặt hơn và kích hoạt hành vi trước đây được định nghĩa trong các chế độ ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE và NO_ZERO_IN_DATE.

Nhấp vào bất kỳ tên mode nào ở trên để truy cập tài liệu MySQL, tìm hiểu thêm thông tin.

Một số đề xuất để chuyển đổi

Nếu bạn đang sử dụng phiên bản WordPress, Drupal, hay Magento gần đây, tin tốt là bạn không cần làm gì thêm. Những ứng dụng này đã nhận thức được tính năng sql_mode của MySQL và khi kết nối, chúng sẽ tự động đặt các tham số tương thích.

Nếu bạn đang xây dựng một ứng dụng mới, có thể bạn nên thay đổi cấu hình của máy chủ MySQL 5.6 hiện tại để nó hoạt động theo các thiết lập sql_mode được tích hợp trong MySQL 5.7.

Nếu bạn có ứng dụng hiện có, bạn có thể muốn chuyển dần các cập nhật. Một số đề xuất giúp bạn chuyển đổi:

● Whitelist: Cho phép các phần mới của ứng dụng kích hoạt các tùy chọn sql_mode mặc định mới. Ví dụ, nếu bạn xây dựng một bộ cron job để tái tạo cache dữ liệu, chúng có thể đặt sql_mode ngay khi kết nối đến MySQL. Mã ứng dụng hiện tại ban đầu vẫn có thể giữ hành vi không nghiêm ngặt như cũ.

● Blacklist: Khi bạn đã chuyển đổi được một phần ứng dụng, đã đến lúc đặt sql_mode mới làm mặc định cho máy chủ. Bạn vẫn có thể để các ứng dụng cũ giữ hành vi trước đó bằng cách thay đổi sql_mode khi kết nối đến MySQL. Ngoài ra, trên từng câu lệnh riêng, MySQL cũng hỗ trợ tham số IGNORE để hạ cấp lỗi. Ví dụ:INSERT IGNORE INTO my_table ...

● Staged Rollout (Triển Khai Giai Đoạn): Nếu bạn kiểm soát hoàn toàn ứng dụng của mình, bạn có thể triển khai một tính năng để thay đổi sql_mode theo từng người dùng. Điều này giúp người dùng nội bộ thử nghiệm từng phần, tạo điều kiện cho quá trình chuyển đổi dần dần.

Bước 1 – Tìm các câu lệnh không tương thích sinh ra cảnh báo hoặc lỗi

Trước tiên, hãy kiểm tra xem có câu lệnh nào trong ứng dụng của bạn đang sinh ra cảnh báo hoặc lỗi không. Điều này hữu ích vì hành vi của một số câu lệnh đã thay đổi từ cảnh báo ở MySQL 5.6 thành lỗi ở MySQL 5.7, do đó bạn có thể bắt được các cảnh báo trước khi nâng cấp.

MySQL có tính năng performance_schema (được bật mặc định từ MySQL 5.6 trở lên) cho phép bạn viết câu truy vấn để liệt kê tất cả các câu lệnh đã gây ra lỗi hoặc cảnh báo.

Truy vấn MySQL 5.6+ để báo cáo các câu lệnh sinh ra lỗi hoặc cảnh báo:

SELECT 
`DIGEST_TEXT` AS `query`,
`SCHEMA_NAME` AS `db`,
`COUNT_STAR` AS `exec_count`,
`SUM_ERRORS` AS `errors`,
(ifnull((`SUM_ERRORS` / nullif(`COUNT_STAR`,0)),0) * 100) AS `error_pct`,
`SUM_WARNINGS` AS `warnings`,
(ifnull((`SUM_WARNINGS` / nullif(`COUNT_STAR`,0)),0) * 100) AS `warning_pct`,
`FIRST_SEEN` AS `first_seen`,
`LAST_SEEN` AS `last_seen`,
`DIGEST` AS `digest`
FROM
 performance_schema.events_statements_summary_by_digest
WHERE
((`SUM_ERRORS` > 0) OR (`SUM_WARNINGS` > 0))
ORDER BY
 `SUM_ERRORS` DESC,
 `SUM_WARNINGS` DESC;

Truy vấn MySQL 5.6+ để báo cáo các câu lệnh sinh ra lỗi:

SELECT 
`DIGEST_TEXT` AS `query`,
`SCHEMA_NAME` AS `db`,
`COUNT_STAR` AS `exec_count`,
`SUM_ERRORS` AS `errors`,
(ifnull((`SUM_ERRORS` / nullif(`COUNT_STAR`,0)),0) * 100) AS `error_pct`,
`SUM_WARNINGS` AS `warnings`,
(ifnull((`SUM_WARNINGS` / nullif(`COUNT_STAR`,0)),0) * 100) AS `warning_pct`,
`FIRST_SEEN` AS `first_seen`,
`LAST_SEEN` AS `last_seen`,
`DIGEST` AS `digest`
FROM
 performance_schema.events_statements_summary_by_digest
WHERE
 `SUM_ERRORS` > 0
ORDER BY
 `SUM_ERRORS` DESC,
 `SUM_WARNINGS` DESC;

Bước 2 – Khiến MySQL 5.6 hoạt động như MySQL 5.7

Bạn cũng có thể thử chạy thử trên MySQL 5.6 để nó hoạt động theo kiểu MySQL 5.7.

Tác giả, Morgan Tocker từ nhóm MySQL, đã cung cấp một dự án trên GitHub với tệp cấu hình mẫu giúp bạn làm điều này. Bằng cách sử dụng các mặc định sắp tới trong MySQL 5.6, bạn sẽ có thể loại bỏ khả năng ứng dụng của bạn phụ thuộc vào hành vi kém nghiêm ngặt.

Tệp cấu hình khá ngắn, dưới đây là nội dung của nó:

# This makes a MySQL 5.6 server behave similar to the new defaults
# in MySQL 5.7

[mysqld]

# MySQL 5.7 enables more SQL modes by default, but also
# merges ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, NO_ZERO_IN_DATE
# into the definition of STRICT_TRANS_TABLES.
# Context: http://dev.mysql.com/worklog/task/?id=7467

sql-mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ZERO_IN_DATE"

# The optimizer changes the default from 10 dives to 200 dives by default
# Context: http://mysqlserverteam.com/you-asked-for-it-new-default-for-eq_range_index_dive_limit/

eq_range_index_dive_limit=200

# MySQL 5.7 contains a new internal server logging API.
# The setting log_warnings is deprecated in 5.7.2 in favour of log_error_verbosity.
# *But* the default fo log_warnings also changes to 2 as well:

log_warnings=2

# MySQL 5.7.7 changes a number of replication defaults
# Binary logging is still disabled, but will default to ROW when enabled.

binlog_format=ROW
sync_binlog=1
slave_net_timeout=60

# InnoDB defaults to the new Dynamic Row format with Barracuda file format.
# large_prefix is also enabled, which allows for longer index values.

innodb_strict_mode=1
innodb_file_format=Barracuda
innodb_large_prefix=1
innodb_purge_threads=4 # coming in 5.7.8
innodb_checksum_algorithm=crc32

# In MySQL 5.7 only 20% of the pool will be dumped, 
# But 5.6 does not support this option

innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1

# These two options had different names in previous versions
# (binlogging_impossible_mode,simplified_binlog_gtid_recovery)
# This config file targets 5.6.23+, but includes the 'loose' modifier to not fail
# prior versions.

loose-binlog_error_action=ABORT_SERVER
loose-binlog_gtid_recovery_simplified=1

# 5.7 enable additional P_S consumers by default
# This one is supported in 5.6 as well.
performance-schema-consumer-events_statements_history=ON

Bước 3 – Thay đổi sql_mode theo phiên làm việc

Đôi khi, bạn muốn kiểm tra hoặc nâng cấp máy chủ theo từng giai đoạn. Thay vì thay đổi tệp cấu hình toàn cục của MySQL để sử dụng các sql_mode mới, bạn cũng có thể thay đổi chúng theo từng phiên. Ví dụ:

Tạo bảng để kiểm tra sql_mode:

CREATE TABLE sql_mode_test (a int);

Không đặt sql_mode:

set sql_mode = '';
INSERT INTO sql_mode_test (a) VALUES (0/0);
Query OK, 1 row affected (0.01 sec)

Đặt sql_mode nghiêm ngặt:

set sql_mode = 'STRICT_TRANS_TABLES';
INSERT INTO sql_mode_test (a) VALUES (0/0);
ERROR 1365 (22012): Division by 0

Sẵn sàng nâng cấp

Tại thời điểm này, bạn đã có đủ tự tin rằng mình đã chuẩn bị cho việc nâng cấp lên MySQL 5.7. Hãy theo dõi hướng dẫn nâng cấp chính thức của MySQL để chuyển đổi.

Kết luận

MySQL 5.7 mang lại những cải tiến đáng kể về cấu hình mặc định và tính toàn vẹn dữ liệu, giúp nâng cao hiệu suất cho các ứng dụng hiện đại. Chúng tôi hy vọng rằng bài viết này sẽ cung cấp cho bạn những hướng dẫn cần thiết để thực hiện quá trình chuyển đổi một cách dễ dàng và hiệu quả!

Khi chuẩn bị nâng cấp MySQL, đừng bỏ qua yếu tố chi phí. Giá thuê VPS hiện nay rất cạnh tranh, phù hợp với mọi nhu cầu từ cá nhân đến doanh nghiệp. Tìm hiểu ngay các gói giá thuê VPS tại DataOnline để chọn giải pháp tiết kiệm và hiệu quả nhất!