0

mysqldump is giving me a file that includes no schema or data. In researching this, I see there has been a change in MySQL and I must either use the --no-tablespaces flag, or I need to grant a process privilege to the user. However, granting a process privilege is considered insecure, and use of the --no-tablespaces flag does not address the problem -- the resulting dump file is still nearly empty, aside from a few annotations.

Once upon a time, mysqldump was easy to use and it always worked. No doubt I could have that same experience again, if I knew how to use it. So what am I missing? How can I get mysqldump to actually dump the database into a file, like in the days of old?

The user/password I specify in my mysqldump command is one that can connect to the database for all the CRUD purposes of my application, so I'm puzzled as to why the same user can't mysqldump the database.

The MySQL version is 8.0.33.

2
  • Not the answer you are looking for but have you tried mysql-shell util dumpInstance instead of the old mysqldump. mysql-shell can use multiple threads while mysqldump is single threaded among other better features it has over the old utility. Feb 9 at 23:58
  • By the way, adding your full mysqldump command and the existing privileges of the mysql user taking the backup might help. Feb 10 at 0:00

1 Answer 1

0

(Not an answer, but some research in the changelogs)

----- 2023-04-18 8.0.33 General Availability -- -- -----

Using --single-transaction with mysqldump version 8.0.32 required either the RELOAD or FLUSH_TABLES privilege. This requirement now applies only when both gtid_mode=ON (default OFF) and with --set-gtid-purged = ON|AUTO (default AUTO). (Bug #109685, Bug #109701, Bug #34993824, Bug #34998910, Bug #35020512) References: This issue is a regression of: Bug #105761, Bug #33630199.

----- 2023-01-17 8.0.32 General Availability -- -- -----

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.

Our thanks to Marcelo Altmann for the contribution.

Limitation: This fix adds a requirement for the RELOAD privilege when using --single-transaction to execute FLUSH TABLES WITH READ LOCK; the MySQL team is investigating a solution. (Bug #33630199, Bug #105761)

----- 2021-01-18 8.0.23 General Availability & 2021-01-18 5.7.33 General Availability -- -- -----

When invoked with the --all-databases option, mysqldump now dumps the mysql database first, so that when the dump file is reloaded, any accounts named in the DEFINER clause of other objects will already have been created. (Bug #32141046)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.