Practice of Executing FLUSH TABLE on RO Without Writing to binlog
Modo Foco
Tamanho da Fonte
This document provides practical instructions on scenarios where binlog is not generated during unexpected operations on read-only instances.
Background
TencentDB technical team observed in the online production environment that some unexpected operations abnormally generated GTID on read-only instances (RO). This scenario typically occurs when RO instances have user access traffic and users perform FLUSH TABLE operations on them. For this scenario, executing such operations generates binlog, advancing GTID generation, which leads to binlog inconsistencies and affects replication subscriptions and other issues.
In addition to the FLUSH TABLE operation, similar operations include REPAIR TABLES, ANALYZE TABLES, and OPTIMIZE TABLES. These maintenance instructions, which should be executed on the primary database or are non-transactional, erroneously participate in the GTID generation logic when run on RO instances, causing exceptions in downstream subscriptions.
For more detailed information on the basic features and special behaviors of FLUSH statements, see FLUSH STATEMENT Syntax Introduction and REPLICATION AND FLUSH Description.
Impact analysis
For the operations described in the above context, after execution, they may cause a series of binlog inconsistencies, affecting replication subscriptions and other issues. When there is a subscription on the RO instance, after subsequent configuration adjustment, migration, and switchover, the GTID generated in the above context will be lost. For downstream users using third-party tools like Flink, validation will fail, leading to errors, and forcing the business to face the risk of downtime.
Solution Analysis
Solution 1
TencentDB for MySQL provides a syntax to avoid writing binlog when FLUSH TABLE is executed, specifically by adding the NO_WRITE_TO_BINLOG keyword.
Example:
flush NO_WRITE_TO_BINLOG table;
Analysis 1
For this approach, there is an inconvenience: each execution requires adding the keyword, which may occasionally be forgotten. Therefore, this solution lacks user-friendliness.
Solution 2
TencentDB technical team has optimized the kernel to set the default behavior of FLUSH TABLE to NO_WRITE_TO_BINLOG. Executing FLUSH TABLE operations on secondary servers (with read_only = on) and read-only instances does not generate binlog.
Analysis 2
The primary goal of this optimization is to avoid binlog inconsistencies between source and secondary instances. When FLUSH TABLE is executed on source instances, binlog should be generated normally. Therefore, setting the default behavior of FLUSH TABLE to NO_WRITE_TO_BINLOG has no impact on source instances. Additionally, replication threads on secondary servers remain unaffected. When FLUSH TABLE statements executed on source instances are propagated to secondary servers, the replication threads will properly replay and generate binlog.
Solution 2 Usage Instructions
Through upgrading the kernel minor version to the Solution 2 supported version, this can be implemented.
Solution 2 supported version
MySQL 5.7 20250510
MySQL 8.0 20250430
Syntax description for Solution 2 without binlog recording
After an upgrade to the target kernel minor version is performed, on secondary servers (read_only = on) and RO instances, the syntax that does not record binlog is as follows:
REPAIR TABLE
OPTIMIZE TABLE
ANALYZE TABLE
FLUSH BINARY LOGS
FLUSH DES_KEY_FILE
FLUSH ENGINE LOGS
FLUSH ERROR LOGS
FLUSH GENERAL LOGS
FLUSH HOSTS
FLUSH LOGS
FLUSH PRIVILEGES
FLUSH OPTIMIZER_COSTS
FLUSH QUERY CACHE
FLUSH RELAY LOGS
FLUSH SLOW LOGS
FLUSH STATUS
FLUSH USER_RESOURCES
FLUSH TABLE
FLUSH TABLES
Documentation
Ajuda e Suporte
Esta página foi útil?
Você também pode entrar em contato com a Equipe de vendas ou Enviar um tíquete em caso de ajuda.
comentários