Can I use mysqldump in aurora mysql ?

Question

I need to transfer data from one test Aurora mysql instance to another. And don't want to set up DMS for such a small task.
But any option (single-transaction etc) I give to mysqldump command, i get error:
```
mysqldump: Couldn't execute 'FLUSH TABLES WITH READ LOCK': Access denied for user 'dbroot'@'%' (using password: YES)
```
Is there an option parameter i'm missing or i cant mysqldump from Aurora mysql ?

Answer

Hello.

The following explanation may be relevant.  
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-32.html  
> Data and GTIDs backed up by mysqldump were inconsistent when the options --single-transaction and --set-gtid-purged=ON were both used. This was because, between the start of the transaction by mysqldump and the fetching of GTID_EXECUTED, GTIDs on the server could have increased already. With this fix, a FLUSH TABLES WITH READ LOCK is performed before fetching GTID_EXECUTED, to ensure that its value is consistent with the snapshot taken by mysqldump.

Therefore, try running the command with the option "--set-gtid-purged=OFF" as shown below.  
```
mysqldump --single-transaction --set-gtid-purged=OFF -h {host} {schema}
```

Answer

Hi,

The Aurora official documentation clearly states that it is supported: https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Migrating.ExtMySQL.mysqldump.html

Also, see section "Tooling" of following doc: https://d0.awsstatic.com/product-marketing/Aurora/Aurora_Export_Import_Best_Practices_v1-3.pdf

Best,

Didier

Can I use mysqldump in aurora mysql ?

相關內容