How to Configure TiDB as a Read-Only Replica When Acting as a MySQL Slave

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 当TIDB作为mysql从库时如何配置为只读模式

| username: 孤君888

[TiDB Usage Environment] Production Environment
[TiDB Version] v6.1.0
[Encountered Problem]

Currently, my TiDB cluster is configured as a replica using the DM component, with MySQL 5.7 as the upstream. Now I want to configure TiDB as a read-only replica. How can I achieve this? It seems that setting read_only results in an error?

[Reproduction Path] What operations were performed that led to the problem
[Problem Phenomenon and Impact]

mysql> set global read_only = 1;
ERROR 1235 (42000): function READ ONLY has only noop implementation in tidb now, use tidb_enable_noop_functions to enable these functions
mysql>
mysql>

image
image812×462 57.1 KB

Additionally, do I need to enable this variable (tidb_enable_noop_functions)?

[Attachments]

Please provide the version information of each component, such as cdc/tikv, which can be obtained by executing cdc version/tikv-server --version.

2
| username: h5n1 | Original post link

tidb_restricted_read_only Introduced from version v5.2.0

  • Scope: GLOBAL
  • Persisted to the cluster: Yes
  • Default value: OFF
  • Optional values: OFF and ON
  • This variable controls the read-only state of the entire cluster. When enabled (i.e., the value is ON), all TiDB servers in the cluster will enter a read-only state, and only statements that do not modify data, such as SELECT, USE, SHOW, etc., can be executed. Other statements like INSERT, UPDATE, etc., will be rejected.
  • Enabling this variable ensures that the entire cluster eventually enters read-only mode. When the variable’s state change has not yet been synchronized to other TiDB servers, the unsynchronized TiDB servers will remain in non-read-only mode.
  • When the variable is enabled, ongoing SQL statements will not be affected; only newly executed SQL statements will be checked for read-only status.
  • When the variable is enabled, for uncommitted transactions:
    • If there are uncommitted read-only transactions, they can be committed normally.
    • If the uncommitted transaction is non-read-only, any SQL statements that perform writes within the transaction will be rejected.
    • If the uncommitted transaction has already made data changes, its commit will also be rejected.
  • After the cluster enters read-only mode, all users (including SUPER users) will be unable to execute SQL statements that may write data unless the user is explicitly granted the RESTRICTED_REPLICA_WRITER_ADMIN privilege.
  • Users with RESTRICTED_VARIABLES_ADMIN or SUPER privileges can modify this variable. If the user has enabled Security Enhanced Mode, only users with RESTRICTED_VARIABLES_ADMIN privileges can modify this variable.
3
| username: ShawnYan | Original post link

Also, refer to this parameter tidb_super_read_only.

4
| username: 孤君888 | Original post link

If I set tidb_restricted_read_only to ON, will replication through the DM component still work normally?

5
| username: 孤君888 | Original post link

I just tested it, and the incremental replication based on the DM component configuration reports an error, as follows:

mysql> show global variables like '%restricted%';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| tidb_restricted_read_only | ON    |
+---------------------------+-------+
1 row in set (0.00 sec)

mysql>

Starting component `dmctl`: /root/.tiup/components/dmctl/v6.1.0/dmctl/dmctl query-status --master-addr=x.x.x.x:8261
{
    "result": true,
    "msg": "",
    "tasks": [
        {
            "taskName": "task_mysql1322033307_incremental",
            "taskStatus": "Error - Some error occurred in subtask. Please run `query-status task_mysql1322033307_incremental` to get more details.",
            "sources": [
                "mysql1322033307"
            ]
        }
    ]
}
6
| username: wuxiangdong | Original post link

By managing user permissions. Change all business accounts to read-only.

7
| username: 孤君888 | Original post link

Huh? This doesn’t seem reasonable.

8
| username: h5n1 | Original post link

What is the specific error message from DM? Are you using the tidb root user?

9
| username: xiaohetao | Original post link

Yes,

image
image861×254 58.8 KB

10
| username: 孤君888 | Original post link

Yes, I configured the root user for DM incremental replication.

11
| username: 孤君888 | Original post link

I couldn’t find the parameter tidb_super_read_only in the 6.1 documentation under system variables, but it appears in the TIDB show global variables.

12
| username: h5n1 | Original post link

We introduced the TIDB_RESTRICTED_READ_ONLY global variable. Turning it on will make the cluster read-only eventually for all users, including users with SUPER or CONNECTION_ADMIN privilege.

13
| username: 孤君888 | Original post link

So, in TiDB’s read-only mode, how can we maintain continuous incremental replication with DM?

14
| username: h5n1 | Original post link

Try granting the RESTRICTED_REPLICA_WRITER_ADMIN privilege to root.

15
| username: 啦啦啦啦啦 | Original post link

The link above makes it very clear that the RESTRICTED_REPLICA_WRITER_ADMIN privilege is required.

  • When the cluster is in read-only mode, no user (including the SUPER user) can execute SQL statements that might write data, unless the user is explicitly granted the RESTRICTED_REPLICA_WRITER_ADMIN privilege.
16
| username: 孤君888 | Original post link

Thank you, I didn’t read the documentation carefully. Adding the RESTRICTED_REPLICA_WRITER_ADMIN permission worked.

17
| username: alfred | Original post link

The design of RESTRICTED_REPLICA_WRITER_ADMIN is quite special :+1:

18
| username: cheng | Original post link

Wouldn’t such a design cause many problems? After all, TiDB is not fully compatible with MySQL.

19
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.