-
MySQL Major Version Upgrade Checklist – how to
This article provides MySQL Major Version Upgrade Checklist along with video, one may follow to ease the upgarde task.
The post MySQL Major Version Upgrade Checklist – how to first appeared on Change Is Inevitable.
-
Missed the May 2026 MySQL Contributor Summit? Watch Every Session On Demand
The inaugural MySQL Contributor Summit, held in May 2026, brought together Oracle engineers, customers, partners, and members of the open source community for a full day of technical collaboration focused on the future of MySQL. The Summit featured more than 20 sessions covering topics including AI integration, performance, observability, replication, developer experience, extensibility, and community […]
-
Inside MySQL 9.7 LTS Features
MySQL 9.7, a Long-Term Support (LTS) release, incorporates a variety of potential features spanning across multiple technical domains. This article covers some of the primary features introduced and evaluates their practical utility within the MySQL database environment.
Following the End-of-Life (EOL) status of MySQL 8.0, this subsequent LTS release is designed to provide enhanced stability alongside significant architectural innovations.
Let’s discuss each of these features below with some examples and usage.
Flow-control monitoring in Group Replication
Flow control monitoring has been improved and provides more granularity by introducing the additional status variables listed below.
Gr_flow_control_throttle_count : It denotes the number of transactions that have been throttled.
Gr_flow_control_throttle_time_sum :It denotes the time in microseconds that transactions have been throttled.
Gr_flow_control_throttle_active_count :It denotes the number of transactions currently being throttled.
Gr_flow_control_throttle_last_throttle_timestamp : It denotes the most recent date and time that a transaction was throttled.
To use these status variables, we must install the “Group Replication Flow Control Statistics” component.
mysql> Install component 'file://component_group_replication_flow_control_stats';After the component is installed, the statistics will be visible.mysql> SELECT * FROM performance_schema.global_status WHERE VARIABLE_NAME LIKE 'Gr_flow_control%';
+--------------------------------------------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+--------------------------------------------------+----------------+
| Gr_flow_control_throttle_active_count | 0 |
| Gr_flow_control_throttle_count | 0 |
| Gr_flow_control_throttle_last_throttle_timestamp | |
| Gr_flow_control_throttle_time_sum | 0 |
+--------------------------------------------------+----------------+
Multi-threaded applier extended statistics
We now have additional verbosity for the Applier threads for both Asynchronous and Group Replication topologies. This means we can get more details of the transactions or potential misbehaviours during the transactions applier stage. This feature is particularly useful for troubleshooting performance bottlenecks in multi-threaded replication environments, where understanding the specific cause of lag can be challenging.
This requires installing the “Replication Applier Metrics” component.mysql> Install component 'file://component_replication_applier_metrics';Upon successful installation of the requisite component, the performance schema tables facilitate tracking of transaction details and various performance metrics during the replication applier phase. For instance, monitoring the table “replication_applier_metrics” enables observing channel-specific operations.mysql> SELECT * FROM performance_schema.replication_applier_metrics where CHANNEL_NAME='group_replication_applier'\G;
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
TOTAL_ACTIVE_TIME_DURATION: 0
LAST_APPLIER_START: 0000-00-00 00:00:00
TRANSACTIONS_COMMITTED_COUNT: 0
TRANSACTIONS_ONGOING_COUNT: 0
TRANSACTIONS_PENDING_COUNT: 0
TRANSACTIONS_COMMITTED_SIZE_BYTES_SUM: 0
TRANSACTIONS_ONGOING_FULL_SIZE_BYTES_SUM: 0
TRANSACTIONS_ONGOING_PROGRESS_SIZE_BYTES_SUM: 0
TRANSACTIONS_PENDING_SIZE_BYTES_SUM: NULL
EVENTS_COMMITTED_COUNT: 0
WAITS_FOR_WORK_FROM_SOURCE_COUNT: 0
WAITS_FOR_WORK_FROM_SOURCE_SUM_TIME: 0
WAITS_FOR_AVAILABLE_WORKER_COUNT: 0
WAITS_FOR_AVAILABLE_WORKER_SUM_TIME: 0
WAITS_COMMIT_SCHEDULE_DEPENDENCY_COUNT: 0
WAITS_COMMIT_SCHEDULE_DEPENDENCY_SUM_TIME: 0
WAITS_FOR_WORKER_QUEUE_MEMORY_COUNT: 0
WAITS_FOR_WORKER_QUEUE_MEMORY_SUM_TIME: 0
WAITS_WORKER_QUEUES_FULL_COUNT: 0
WAITS_WORKER_QUEUES_FULL_SUM_TIME: 0
WAITS_DUE_TO_COMMIT_ORDER_COUNT: 0
WAITS_DUE_TO_COMMIT_ORDER_SUM_TIME: 0
TIME_TO_READ_FROM_RELAY_LOG_SUM_TIME: 0In addition to aggregate metrics, MySQL 9.7 provides a way to inspect the progress of individual worker threads via monitoring stats in the “replication_applier_progress_by_worker” table. This level of detail helps administrators identify if a single transaction is monopolising a specific worker, causing overall replication delay.mysql> SELECT * FROM performance_schema.replication_applier_progress_by_worker\G;
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
WORKER_ID: 0
THREAD_ID: 62
ONGOING_TRANSACTION_TYPE: UNASSIGNED
ONGOING_TRANSACTION_FULL_SIZE_BYTES: 0
ONGOING_TRANSACTION_APPLIED_SIZE_BYTES: 0
*************************** 2. row ***************************
CHANNEL_NAME: group_replication_applier
WORKER_ID: 1
THREAD_ID: 63
ONGOING_TRANSACTION_TYPE: UNASSIGNED
ONGOING_TRANSACTION_FULL_SIZE_BYTES: 0
ONGOING_TRANSACTION_APPLIED_SIZE_BYTES: 0
*************************** 3. row ***************************
CHANNEL_NAME: group_replication_applier
WORKER_ID: 2
THREAD_ID: 64
ONGOING_TRANSACTION_TYPE: UNASSIGNED
ONGOING_TRANSACTION_FULL_SIZE_BYTES: 0
ONGOING_TRANSACTION_APPLIED_SIZE_BYTES: 0
*************************** 4. row ***************************
CHANNEL_NAME: group_replication_applier
WORKER_ID: 3
THREAD_ID: 65
ONGOING_TRANSACTION_TYPE: UNASSIGNED
ONGOING_TRANSACTION_FULL_SIZE_BYTES: 0
ONGOING_TRANSACTION_APPLIED_SIZE_BYTES: 0
Automatic eviction & rejoin
The Group Replication resource manager now provides auto-eviction functionality, which we can configure using the available options. This basically ensures that the unhealthy node is removed from the Group to maintain the cluster’s high availability and overall performance.
This requires installing the “group replication resource manager” component.mysql> INSTALL COMPONENT 'file://component_group_replication_resource_manager';Once the component is available, we can use various options to decide the node expulsion policy.
1) Applier channel
We can set the applier channel replication lag threshold values using the configuration parameter below.mysql> set global group_replication_resource_manager.applier_channel_lag = <value>;If lag exceeds “applier_channel_lag” threshold 10 times or more in a row, this server is expelled from the group. The status variable below is used for tracking the lag exceed rate.mysql> show global status like 'Gr_resource_manager_applier_channel_lag';
+-----------------------------------------+-------+
| Variable_name | Value |
+-----------------------------------------+-------+
| Gr_resource_manager_applier_channel_lag | 0 |
+-----------------------------------------+-------+
2) Recovery Channel
Similarly, we can define a threshold for the group member recovery process to attempt to rejoin the cluster. mysql> set global group_replication_resource_manager.recovery_channel_lag = <value>;If the secondary’s recovery lag exceeds “recovery_channel_lag”, 10 times or more in succession, the server is expelled from the group. mysql show global status like 'Gr_resource_manager_recovery_channel_lag';
+------------------------------------------+-------+
| Variable_name | Value |
+------------------------------------------+-------+
| Gr_resource_manager_recovery_channel_lag | 0 |
+------------------------------------------+-------+3) Memory/Resource Usage
We can also define an expelled condition based on the group member’s memory or resource usage %.mysql> set global group_replication_resource_manager.memory_used_limit = 10;If the memory usage exceeds memory_used_limit % by 10 or more consecutive times, the node will be expelled from the group.mysql> show global status like 'Gr_resource_manager_memory_used%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| Gr_resource_manager_memory_used | 78 |
+---------------------------------+-------+
1 row in set (0.002 sec)In addition to the discussed options above, we can also track various server status variables to monitor group replication and the resource manager component.mysql> select * from performance_schema.global_status where variable_name in ('Gr_resource_manager_applier_channel_threshold_hits','Gr_resource_manager_applier_channel_eviction_timestamp','Gr_resource_manager_recovery_channel_threshold_hits','Gr_resource_manager_recovery_channel_eviction_timestamp','Gr_resource_manager_memory_threshold_hits','Gr_resource_manager_memory_eviction_timestamp');
+---------------------------------------------------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+---------------------------------------------------------+----------------+
| Gr_resource_manager_applier_channel_eviction_timestamp | |
| Gr_resource_manager_applier_channel_threshold_hits | 0 |
| Gr_resource_manager_memory_eviction_timestamp | |
| Gr_resource_manager_memory_threshold_hits | 6703 |
| Gr_resource_manager_recovery_channel_eviction_timestamp | |
| Gr_resource_manager_recovery_channel_threshold_hits | 0 |
+---------------------------------------------------------+----------------+
6 rows in set (0.003 sec)The expelled node can attempt to automatically rejoin based on the value of the group_replication_autorejoin_tries variable.mysql> show variables like '%group_replication_autorejoin_tries%';
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| group_replication_autorejoin_tries | 3 |
+------------------------------------+-------+
1 row in set (0.006 sec)If the node cannot join, it will perform the behaviour specified in the group_replication_exit_state_action variable.mysql> show variables like '%group_replication_exit_state_action%';
+-------------------------------------+--------------+
| Variable_name | Value |
+-------------------------------------+--------------+
| group_replication_exit_state_action | OFFLINE_MODE |
+-------------------------------------+--------------+
1 row in set (0.005 sec)After a server is evicted from the group (for whatever reason), it gets a grace period (group_replication_resource_manager) when it rejoins. During this period, the Resource Manager won’t immediately kick it out again, even if it’s still lagging or breaching the defined threshold as discussed above.mysql> show variables like '%group_replication_resource_manager.quarantine_time%';
+----------------------------------------------------+-------+
| Variable_name | Value |
+----------------------------------------------------+-------+
| group_replication_resource_manager.quarantine_time | 3600 |
+----------------------------------------------------+-------+
Up-to-date aware Primary election
The Primary election process is more mature and cohesive. The Group Replication Manager now uses the most up-to-date status as a criterion for selecting the new primary.
Here is how the Group Replication Manager performs the most up-to-date primary selection prior to MySQL v9.7.
The lowest MySQL version is checked for each member.
If more than one member is running the lowest MySQL Server version, each member’s weight is determined by the “group_replication_member_weight” system variable.
If there is more than one member running the lowest MySQL Server version, and also more than one of those members has the highest member weight, the third factor considered is the lexicographical order of the generated server UUIDs “server_uuid” of each group member. The member with the lowest server UUID is chosen as the new primary.
In MySQL version 9.7, “group_replication_elect_prefers_most_updated” was introduced, so the failover will be determined by how many transactions are in the secondary backlog. Basically the secondary with the least backlog will be selected as Primary.
Now, it will consider the “most up-to-date” node first, then “weight” and then “UUID”.
To use “group_replication_elect_prefers_most_updated”, we need to install the “Group Replication Primary Election” component listed below on each Group Member.mysql> Install component 'file://component_group_replication_elect_prefers_most_updated';By default, the most up-to-date group member selection is enabled. We need to make sure it’s enabled on all Group Members. mysql> select @@group_replication_elect_prefers_most_updated.enabled;
+--------------------------------------------------------+
| @@group_replication_elect_prefers_most_updated.enabled |
+--------------------------------------------------------+
| 1 |
+--------------------------------------------------------+
1 row in set (0.007 sec)In the event that a new primary is elected via the most up-to-date selection mechanism, this metric represents the transaction processing differential between the newly designated primary and the secondary node with the highest level of synchronisation.mysql> show status like 'Gr_latest_primary_election_by_most_uptodate_members_trx_delta';
+---------------------------------------------------------------+-------+
| Variable_name | Value |
+---------------------------------------------------------------+-------+
| Gr_latest_primary_election_by_most_uptodate_members_trx_delta | 0 |
+---------------------------------------------------------------+-------+Also, we can track the timestamp of the most recent primary election on the most up-to-date node.mysql> show status like 'Gr_latest_primary_election_by_most_uptodate_member_timestamp';
+--------------------------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------------------------+-------+
| Gr_latest_primary_election_by_most_uptodate_member_timestamp | |
+--------------------------------------------------------------+-------+
1 row in set (0.005 sec)The database logs also tell exactly what criteria the primary member selected during failover.2026-06-14T10:04:02.243809Z 0 [System] [MY-015575] [Repl] Plugin group_replication reported: 'Member with uuid 00021702-2222-2222-2222-222222222222 was elected primary since it was the most up-to-date member with 2755 transactions more than second most up-to-date member 00021703-3333-3333-3333-333333333333. In case of a tie member weight and then uuid lexical order was used over the most updated members.'
MySQL JSON duality views
With the introduction of JSON duality views, we can leverage a single unified JSON document for both relational and hierarchical JSON data. This provides a common, structured JSON format for the application, allowing it to perform both read and write operations.
Let’s see a quick scenario below on how it works.
Below are two relational tables from which we obtain aggregated information in JSON format. mysql> CREATE TABLE products (
product_id INT PRIMARY KEY,
product_type VARCHAR(100)
);
mysql> CREATE TABLE products_details (
product_detail_id INT PRIMARY KEY,
product_id INT,
name VARCHAR(100),
active varchar(10)
);mysql> INSERT INTO products (product_id,product_type) VALUES (1,'IT'), (2,'TEL');
mysql> INSERT INTO products_details (product_detail_id,product_id,name,active) VALUES (1,1,'Laptop','Yes'), (2,2,'Mobile','Yes');Here is the exact Json View which fetch the columns from the relation table based on the join condition. Each of those relational table columns is mapped with a JSON data structure (_id,v_product_type,v_product_type ), and the complete details of the product details table are fetched into the (product) array.mysql> CREATE JSON RELATIONAL DUALITY VIEW view_product AS
SELECT JSON_DUALITY_OBJECT( WITH(INSERT,UPDATE,DELETE)
'_id': product_id,
'v_product_type': product_type,
'product': (
SELECT JSON_ARRAYAGG(
JSON_DUALITY_OBJECT(WITH(INSERT,UPDATE,DELETE)
'v_product_detail_id': product_detail_id,
'v_name': name,
'v_active': active
)
)
FROM products_details
WHERE products_details.product_id = products.product_id
)
)
FROM products;mysql> select * from view_product;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| data |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {"_id": 1, "product": [{"v_name": "Laptop", "v_active": "Yes", "v_product_detail_id": 1}], "_metadata": {"etag": "313642c2aa24f0571264332afa140715"}, "v_product_type": "IT"} |
| {"_id": 2, "product": [{"v_name": "Mobile", "v_active": "Yes", "v_product_detail_id": 2}], "_metadata": {"etag": "3d229ada02ac660f9f6cac994b44831a"}, "v_product_type": "TEL"} |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.002 sec)Once the duality view is created, we can perform both read/write operations.
Reading the duality viewmysql> select * from view_product;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| data |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {"_id": 1, "product": [{"v_name": "Laptop", "v_active": "Yes", "v_product_detail_id": 1}], "_metadata": {"etag": "313642c2aa24f0571264332afa140715"}, "v_product_type": "IT"} |
| {"_id": 2, "product": [{"v_name": "Mobile", "v_active": "Yes", "v_product_detail_id": 2}], "_metadata": {"etag": "3d229ada02ac660f9f6cac994b44831a"}, "v_product_type": "TEL"} |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+Writing the underlying table in the duality viewmysql> UPDATE view_product
SET data = JSON_SET(
data,
'$.product[0].v_name',
'Notepad'
)
WHERE JSON_EXTRACT(data, '$._id') = 1;mysql> select * from products_details;
+-------------------+------------+---------+--------+
| product_detail_id | product_id | name | active |
+-------------------+------------+---------+--------+
| 1 | 1 | Notepad | Yes |
| 2 | 2 | Mobile | Yes |
+-------------------+------------+---------+--------+After performing the above write operations, we can see that the view now shows the updated data.mysql > select * from view_product;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| data |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {"_id": 1, "product": [{"v_name": "Notepad", "v_active": "Yes", "v_product_detail_id": 1}], "_metadata": {"etag": "72c4368420cdc698842d0ab4bd9315ab"}, "v_product_type": "IT"} |
| {"_id": 2, "product": [{"v_name": "Mobile", "v_active": "Yes", "v_product_detail_id": 2}], "_metadata": {"etag": "3d229ada02ac660f9f6cac994b44831a"}, "v_product_type": "TEL"} |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Hypergraph Optimizer
With the Hypergraph Optimiser, we now have more advanced optimisation for complex queries and a broader set of Join plans than the older traditional method, missing earlier. By using “Join hypergraph”, the optimiser now has better reach to all tables in the join condition.
Hypergraph Optimiser is OFFmysql> SET optimizer_switch='hypergraph_optimizer=off';mysql> SELECT t1.k, COUNT(*) AS cnt
FROM sbtest1 t1
JOIN sbtest2 t2 ON t1.id = t2.id
JOIN sbtest3 t3 ON t1.id = t3.id
WHERE t1.k BETWEEN 200000 AND 500000
GROUP BY t1.k
ORDER BY cnt DESC
LIMIT 100;Output:| 498870 | 119 |
| 498729 | 119 |
| 497668 | 119 |
| 498076 | 119 |
+--------+-----+
100 rows in set (4.000 sec)Explain output:-> Limit: 100 row(s)
-> Sort: cnt DESC, limit input to 100 row(s) per chunk
-> Stream results (cost=1.22e+6 rows=175136)
-> Group aggregate: count(0) (cost=1.22e+6 rows=175136)
-> Nested loop inner join (cost=1.1e+6 rows=493200)
-> Nested loop inner join (cost=601547 rows=493200)
-> Filter: (t1.k between 200000 and 500000) (cost=99122 rows=493200)
-> Covering index range scan on t1 using k_1 over (200000 <= k <= 500000) (cost=99122 rows=493200)
-> Single-row covering index lookup on t2 using PRIMARY (id = t1.id) (cost=0.919 rows=1)
-> Single-row covering index lookup on t3 using PRIMARY (id = t1.id) (cost=0.919 rows=1)Hypergraph Optimiser is ONmysql> SET optimizer_switch='hypergraph_optimizer=on';mysql> SELECT t1.k, COUNT(*) AS cnt
FROM sbtest1 t1
JOIN sbtest2 t2 ON t1.id = t2.id
JOIN sbtest3 t3 ON t1.id = t3.id
WHERE t1.k BETWEEN 200000 AND 500000
GROUP BY t1.k
ORDER BY cnt DESC
LIMIT 100;Output:| 499721 | 119 |
| 499052 | 119 |
| 498870 | 119 |
| 498384 | 119 |
+--------+-----+
100 rows in set (0.498 sec)Explain output:-> Sort: cnt DESC, limit input to 100 row(s) per chunk (cost=1.96e+6..1.96e+6 rows=100)
-> Table scan on <temporary> (cost=1.87e+6..1.9e+6 rows=175136)
-> Aggregate using temporary table (cost=1.87e+6..1.87e+6 rows=175136)
-> Inner hash join (t2.id = t3.id) (cost=990754..1.44e+6 rows=493200)
-> Covering index scan on t3 using k_1 (cost=0.312..308240 rows=986400)
-> Hash
-> Inner hash join (t1.id = t2.id) (cost=370988..824021 rows=493200)
-> Covering index scan on t2 using k_1 (cost=0.312..308240 rows=986400)
-> Hash
-> Filter: (t1.k between 200000 and 500000) (cost=0.416..205287 rows=493200)
-> Covering index range scan on t1 using k_1 over (200000 <= k <= 500000) (cost=0.359..176877 rows=493200)We can see that with “hypergraph_optimizer=enabled”, the query execution time is almost 8x faster.
The performance difference might not be noticeable with a few joins or a smaller table’s data set, but with more complex joins, it can yield better performance. In the above example, we can see that when “hypergraph_optimizer=enabled”, the optimiser replaces “Nested loop inner join” with “Inner hash join”, which is generally better for large datasets.
Higher version source allowed
Now, it’s possible that a lower version replica can connect to a higher version source when the major versions differ. That means we don’t have to rely on all replicas being upgraded in one go; we can just upgrade the source, verify it, and later perform rolling upgrades on lower-version replicas as per our own timelines and convenience.
Of course, we have to be cautious not to run any such feature or change on the source that doesn’t support lower-version replicas.
Please note – This won’t be applicable to previous releases, say (8.4, 8.0), as they didn’t restrict such replication connectivity. It would be useful for 9.7 or the next major release.
To enable this functionality, we need to ensure the following variable is enabled on the Replica. By default its enabled on 9.7mysql> show variables like 'replica_allow_higher_version_source';
+-------------------------------------+-------+
| Variable_name | Value |
+-------------------------------------+-------+
| replica_allow_higher_version_source | ON |
+-------------------------------------+-------+
1 row in set (0.008 sec)
Summary
The above discussion highlights key advancements in MySQL 9.7 LTS, ranging from some innovative or operational improvements to developer-centric features such as “JSON Duality” Views. Also, the “Hypergraph Optimiser” is now available for community release, which was previously exclusive to MySQL Heatwave/Enterprise. As a Long-Term Support (LTS) release, MySQL 9.7 is structured to provide a stable and consistent environment, prioritising architectural reliability over frequent experimental changes.
One more important mention here: It’s suggested to use MySQL 9.7.1, or the next sub-releases, as 9.7.0 has some higer severity CVE’s. If you are using Percona Server for MySQL (PS), we skipped 9.7.0 and are shipping the fixed 9.7.1 version directly.
Still, it’s highly recommended to test any new component or changes in your lower/staging environment before deploying in production to better assess the overall impact on existing workload, queries, and database behaviour.
The post Inside MySQL 9.7 LTS Features appeared first on Percona.
-
MyDumper Locking Mechanisms Revisited: Introducing SAFE_NO_LOCK
About a year ago, we discussed how MyDumper refactored its locking mechanisms to move away from old, rigid flags and transitioned towards more flexible, streamlined execution. Since then, the MyDumper community hasn’t stood still.
In recent releases, the locking architecture was further standardized under a single overarching option: --sync-thread-lock-mode. Along with this modernization came a powerful new safety feature designed to give you lock-free thread synchronization without risking silent inconsistency: SAFE_NO_LOCK (merged in PR #2031).
Let’s explore the new thread-synchronization landscape and break down when you should use each mode.
What is --sync-thread-lock-mode?
Previously, flags like -k, --no-locks or --lock-all-tables dictated how MyDumper behaved. These have now been deprecated in favor of --sync-thread-lock-mode, which accepts five core values: AUTO, FTWRL, LOCK_ALL, GTID, NO_LOCK, and the newly added SAFE_NO_LOCK.
As a multi-threaded tool, MyDumper’s main challenge is ensuring that every single worker thread establishes its database snapshot at the exact same point in time. The sync mode you choose completely alters how MyDumper orchestrates this point-in-time synchronization.
Understanding SAFE_NO_LOCK
MyDumper fires off START TRANSACTION WITH CONSISTENT SNAPSHOT across its threads. It captures the binary log position at the very beginning of the process and compares it after the worker threads have attempted to synchronize.
When using NO_LOCK, if the threads don’t actually hit the same point in time—meaning they fail to synchronize—MyDumper simply logs a warning and continues backing up. This results in an inconsistent backup, which is a massive gamble for production systems.
SAFE_NO_LOCK adds a strict transactional safety net. If MyDumper detects any differences or drift in the binlog position among the threads during the synchronization phase, it immediately stops the backup. This prevents you from generating a corrupted, out-of-sync backup that will fail or cause data anomalies during a later restore.
Choosing the Right Mode
Depending on your architecture, uptime requirements, and database vendor, here is the breakdown of when to use each mode:
AUTO (The Default)
What it does: MyDumper automatically evaluates the database vendor, version, and capabilities to choose the safest, least-intrusive method.
When to use it: The vast majority of standard backups. It removes the guesswork and adapts dynamically if your database infrastructure upgrades.
FTWRL (Flush Tables With Read Lock)
What it does: It is the traditional method. It issues a global read lock via FLUSH TABLES WITH READ LOCK on the main connection, forces all threads to establish their consistent snapshot at that exact freeze frame, and then releases the lock.
When to use it:
When you have non-transactional tables (like MyISAM or ARCHIVE) that must be consistently backed up alongside InnoDB tables.
When your database lacks advanced snapshot-tracking capabilities (older MySQL versions).
Downside: It blocks writes across the entire instance during synchronization, which can cause a queue cascade on a busy production server.
GTID
Leverages a specific server variable in Percona Server called binlog_snapshot_gtid_executed to instantly verify if all threads are watching the exact same transaction state.
When to use it: If you are running Percona Server with GTID enabled and want a lightning-fast, lockless synchronization method that is guaranteed to be transactionally accurate.
SAFE_NO_LOCK
What it does: Uses transaction isolation to sync threads without global locks, but immediately aborts the backup if binlog positions diverge during initialization.
When to use it:
On highly sensitive production systems, where global write locks are absolutely forbidden due to strict SLAs.
When you are entirely utilizing transactional engines (InnoDB).
When you want a lock-free backup but require absolute certainty that your backup is 100% consistent.
Downside: In high-throughput write environments, threads may fail to align within the retry window, causing the backup job to abort. (Though an abort is always preferable to an inconsistent backup!).
NO_LOCK
What it does: Attempts lockless synchronization but logs a warning and proceeds even if consistency fails.
When to use it: Rarely, if ever, in the production primary server. It is acceptable for staging environments, development seeding, or scratch pads where data accuracy and point-in-time consistency are entirely secondary to getting a quick data dump without locking the server.
LOCK_ALL
What it does: Explicitly issues a LOCK TABLE command for every single table being exported.
When to use it: Primarily a fallback mode. Use this only when FLUSH TABLES WITH READ LOCK is completely unavailable due to restricted cloud permissions (certain restricted PaaS environments) or specific database limitations.
Conclusion
The addition of --sync-thread-lock-mode=SAFE_NO_LOCK bridges a long-standing gap in logical MySQL backups: achieving a completely lockless synchronization state without flying blind. By implementing a strict fail-fast policy, MyDumper ensures that database administrators never have to sacrifice backup integrity for system availability.
The post MyDumper Locking Mechanisms Revisited: Introducing SAFE_NO_LOCK appeared first on Percona.
-
Dynamic Data Masking (DDM) with MySQL Enterprise Edition 9.7: Reduce your sensitive data exposure.
With the new LTS (Long Term Support) release of MySQL 9.7.0 https://dev.mysql.com/doc/relnotes/mysql/9.7/en/ , Dynamic Data Masking (DDM) is one of the new features introduced as part of Enterprise Edition. The recent blog by Mike Frank, MySQL Product Management Director, details why DDM is important in every industry where PII (Personal Identifiable Information) data is stored […]
|
