Bug #112013 Implement the mysqlpump "--users" feature in mysqldump before removing mysqlpump
Submitted: 9 Aug 12:51 Modified: 10 Aug 13:10
Reporter: Larry Philps Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server: mysqldump Command-line Client Severity:S4 (Feature request)
Version: OS:Any
Assigned to: CPU Architecture:Any

[9 Aug 12:51] Larry Philps
The release notes for MySQL v8.0.34 state that the mysqlpump command is deprecated and will be removed in a future version stating the rationale:

"Since MySQL provides other means of performing database dumps and backups with the same or additional functionality ..."

That statement is true *except* for the functionality provided by mysqlpump's "--users" feature. This provides a simple way to dump/backup all the users, and their grants, in a format that can be used directly to create a duplicate of the db server.

In a cloud environment (such as AWS RDS) in which the "admin user" does not have the SUPER grant, and does not possess the ability to restore the mysql database itself, a dump of create user and grant statements is the only way to transfer user information to a new server.

To the best of my knowledge no other MySQL provided utility provides equivalent functionality.

It would be wonderful if mysqlpump's --users functionality could be ported to the mysqldump command before mysqlpump is removed from the product.

How to repeat:
mysqlpump --set-gtid-purged=OFF --exclude-databases=% --users --exclude-users=mysql.sys,mysql.session,mysql.infoschema,rdsadmin,rdsrepladmin
[9 Aug 13:29] MySQL Verification Team
Hi Mr. Philips,

Thank you for your feature request.

MySQL Shell has some utilities that provide exactly what you desire.

We already have some feature requests for that product that will improve this functionality.

Not a bug.
[9 Aug 13:53] MySQL Verification Team
Also, you can continue to use that mysqlpump for 8.0 , 8.1 and 8.2, while you are getting acquainted with MySQL Shell.
[10 Aug 13:10] Larry Philps
Many thanks for the extremely quick response and the suggestion about trying MySQL shell.  I'll confess that, to date, I've had no need to use the MySQL shell for anything and have never used it.

That being said, I just read some documentation, and installed it, but it won't run for me at all "out of the box", failing to start with:

While processing defaults options:
mysqlsh: unknown option --default-character-set

I'm not sure what the cause of that is, and don't have time to investigate today, although it seems somebody already reported this problem in bug 110545.

Anyway, according to the doc I found, the only way to dump users/grants is by using "util.dumpInstance"  Is that correct? I guess like with mysqlpump, I'd need to provide options on suppress the dump of all databases & tables, to end up with just the users & grants.

Right now, for numerous db servers, I run cron jobs daily to dump users and grants (as part of the nightly backups) and archive them.  This is all done in a batch mode without any user interaction.  I hope MySQL shell can be run the same way.  If I can't run it non-interactively, from a cron job, it won't work for  me.

If you can suggest a command line option for mysql shell I can use in a cron job, I'd be most appreciative.

Anyway, once again, many thanks for the quick response & suggestions!