Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: DM 没有LOCK 权限能不能备份全量？
|
username: TiDBer_jYQINSnf
DM 8.1 version
The source database is sharded MySQL, but LOCK permission cannot be granted. Is it possible to perform a full backup?
It doesn’t mention the need for lock permissions. Can you show the error message?
|
username: {
"result": true,
"msg": "",
"sourceStatus": {
"source": "xxxx_db",
"worker": "dmxxxx",
"result": null,
"relayStatus": null
},
"subTaskStatus": [
{
"name": "wallet",
"stage": "Paused",
"unit": "Dump",
"result": {
"isCanceled": false,
"errors": [
{
"ErrCode": 32001,
"ErrClass": "dump-unit",
"ErrScope": "internal",
"ErrLevel": "high",
"Message": "mydumper/dumpling runs with error, with output (may empty): ",
"RawCause": "sql: LOCK TABLES `xxxx_db`.`xxx_xxx` READ,......: Error 1044 (42000): Access denied for user 'xxx'@'1.1.1.1' to database 'xxxx_db'"
}
]
}
}
]
}
Your task configuration file will have such a configuration.
mydumpers: # Configuration parameters for the dump processing unit
global: # Configuration name
threads: 4 # Number of threads for the dump processing unit to export data from the upstream database instance and for check-task to access the upstream, default value is 4
chunk-filesize: 64 # Size of the data files generated by the dump processing unit, default value is 64, unit is MB
extra-args: “–consistency none” # Other parameters for the dump processing unit, no need to configure table-list in extra-args, DM will automatically generate it
Note this
extra-args: “–consistency none”
Explanation of this parameter in the dumpling documentation.
- LOCK TABLES: Required when using consistency lock, the exported database tables need to have this permission.
When this parameter is set to lock, this permission is required.
Check if there is such a configuration in the task configuration file.
Unless set to none, locking is required, right? MySQL also doesn’t support --snapshot.
Is there no --single-transaction option?
For MySQL, to ensure safety, it’s actually about either locking or flushing.
One requires FLUSH TABLES WITH READ LOCK, and the other requires a lock.
Or just enable incremental verification and correct it afterward. There’s really no better way.
By the way, can I ask another question? DM can’t use the binlog+offset method for synchronization, right? It can only use GTID, right? My database has other synchronization tools that handle the full data, and there are binlog positions, but they are in offset format. DM can’t use them directly, right?
DM can be configured without GTID. In this case, it seems to use the binlog+offset method.
However, if other synchronization tools perform a full sync, the recorded binlog position needs to be the same for each table in the database, which seems to require locking to ensure consistency.
If you don’t lock, the binlog position for each table might be different, and if you need to set up an incremental task for each table individually, it can be quite troublesome when there are many tables.
Other tools have the correct full binlog position, which is a binlog+offset position for the entire database. If DM does not set GTID, how should the position be configured? I see only these two items in the source configuration, without offset:
source-id: "mysql-replica-01"
# Whether to enable GTID
enable-gtid: false
# Whether to enable relay log
enable-relay: false # This configuration item is deprecated from DM v2.0.2, use the `start-relay` command to enable relay log
relay-binlog-name: "" # The starting file name of the upstream binlog
relay-binlog-gtid: "" # The starting GTID of the upstream binlog
# relay-dir: "relay-dir" # The directory to store relay log, default is "relay-dir". This configuration is deprecated from v6.1 and replaced by the same parameter in the worker configuration
Additionally, other tools perform full backups like this:
Record the current binlog position. Then execute select on the source database and insert into the target database, making arbitrary modifications during the process.
Then replay the binlog, changing the write method to replace into. This way, the binlog eventually catches up, and the source and target databases are consistent. Does DM support this method?
Configure it in the task configuration file.
mysql-instances:
source-id: "mysql-replica-01" # Corresponds to `source-id` in source.toml meta: # The starting position for binlog migration when `task-mode` is `incremental` and the downstream database's `checkpoint` does not exist; if the checkpoint exists, it takes precedence. If neither the `meta` item nor the downstream database's `checkpoint` exists, migration starts from the latest binlog position in the upstream. binlog-name: binlog.000001 binlog-pos: 4 binlog-gtid: "03fc0263-28c7-11e7-a653-6c0b84d59f30:1-7041423,05474d3c-28c7-11e7-8352-203db246dd3d:1-170" # For incremental tasks where `enable-gtid: true` is specified in the source, this value needs to be specified.
Thank you, thank you! I’m not familiar with DM, but I’ve learned a lot!
The issue is that without a lock, there might be changes between the time the binlog is recorded and the time of the select. For example, an insertion.
So, when replaying the binlog, a piece of data inserted during this period might be inserted twice. Either rewrite the insert to replace (DM’s safe mode) or it will directly report an error (in non-safe mode, DM will directly report an error and get stuck).
It seems very difficult to ensure that there are no changes between the time the binlog is recorded and the time of the select without a lock.
I used replace, and it worked. It seems that DM has a safe mode that can be used this way. I’ll check it out this afternoon to see if I can use safe mode to create a backup.
It should not be a permissions issue. Check if there are any error logs.
|
username: