How to Disable DDL and DML Operations When Using BR for Recovery?

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

Original topic: 在使用br进行恢复时,怎样禁止DDL和DML操作?

| username: 滴滴嗒嘀嗒

When using BR for recovery operations, how can DDL and DML operations be prohibited?
Should all TiDB-server nodes be taken offline? But what negative impact will this have on the database?
Are there any other ways?

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

Setting the database to read_only might work, but I haven’t tried it. It needs to be tested to see if it affects BR recovery.

| username: 像风一样的男子 | Original post link

When restoring, the database must be empty with no data being written, otherwise it will fail.

| username: 路在何chu | Original post link

Restoring like this can still allow business operations to be written in. Open the business after the restoration is complete.

| username: zhanggame1 | Original post link

Access to the database is not allowed during BR recovery.

| username: 滴滴嗒嘀嗒 | Original post link

I didn’t see any explanation in the official documentation. Have you done any corresponding verification?

| username: 滴滴嗒嘀嗒 | Original post link

Is this conclusion supported by official documentation or verification results?

| username: 滴滴嗒嘀嗒 | Original post link

What does “open business” mean?

| username: 滴滴嗒嘀嗒 | Original post link

How is read_only set? Do I need to restart the database for this setting to take effect?

| username: 像风一样的男子 | Original post link

After the BR recovery is complete, there will be a checksum. If the data is inconsistent, it will directly fail.

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

set global tidb_super_read_only=1
No need to restart

| username: Fly-bird | Original post link

It seems that writing is not allowed during BR, so no operation is needed.

| username: 大飞哥online | Original post link

Revoke permissions, only grant select permissions, leave br permissions unchanged, that’s fine.

| username: 大飞哥online | Original post link

If new data is written during the recovery process, it will conflict with the backup data, potentially leading to data inconsistency or loss. To ensure the accuracy and completeness of the recovery, please avoid performing write, delete, or create table operations (DDL and DML operations) in the cluster during the recovery process. This is because the backup data may contain DDL operations that rename tables, and if the cluster data is modified during recovery, the recovery process will not be able to determine whether the deleted or existing tables were caused by external operations, affecting the accuracy of the next recovery attempt.

| username: 滴滴嗒嘀嗒 | Original post link

What exactly should be done? Is it the operation mentioned by this friend?

| username: 大飞哥online | Original post link

This teacher has set the system to read-only mode. This hasn’t been tested. Permissions can be changed; except for the backup user, all others have only select permissions.

| username: 舞动梦灵 | Original post link

How can your reply make the application operate? If you are migrating or in other situations where the new TiDB or old TiDB needs to import data from another TiDB, and for various reasons, this TiDB has business connections… TiDB will not restore account passwords during recovery.
So you can avoid creating the relevant account passwords first, and prevent the existing business from accessing with the corresponding account passwords. After the recovery is complete, then create the corresponding account passwords.

| username: 舞动梦灵 | Original post link

Do not set anything to read-only, it increases complexity. Just directly prohibit all external connections, either isolate the network or delete all users, leaving only one root for yourself. Prohibit any connections.

| username: heiwandou | Original post link

Stop VIP.

| username: 随缘天空 | Original post link

Try the following methods:

  1. Use the --no-schema parameter: When using the br restore command, you can add the --no-schema parameter to prevent the restoration of database schema information, which will stop DDL operations from being executed.

    Example command: br restore --no-schema

  2. Use the --no-restore-txn parameter: When using the br restore command, you can add the --no-restore-txn parameter to prevent the restoration of transaction data, which will stop DML operations from being executed.

    Example command: br restore --no-restore-txn

    Note: Disabling DDL and DML operations may affect data integrity and consistency. It is recommended to back up your data before performing any database operations and to verify in a secure test environment.