Just wondering, why I can’t make dumps of database even though I have ALL PRIVILEGES granted.
+----------------------------------------------------------+
| Grants for me@% |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO `me`@`%` |
| GRANT ALL PRIVILEGES ON `me`.* TO `me`@`%` |
+----------------------------------------------------------+
2 rows in set (0.01 sec)
When trying
mysqldump --single-transaction --skip-add-locks --routines --triggers --column-statistics=FALSE --set-gtid-purged=OFF --user=$MYSQL_USER --password=$MYSQL_PASS --host=$MYSQL_HOST --port=$MYSQL_PORT $MYSQL_DATABASE > test.sql
it is throwing an error
mysqldump: Couldn't execute 'FLUSH TABLES': Access denied; you need (at least one of) the RELOAD or FLUSH_TABLES privilege(s) for this operation (1227)
Doesn’t ALL mean ALL (therefore, it will also give RELOAD and FLUSH_TABLES privilege)?
Well, I made sure that this is the good database I’m connecting to (it is). Double checked my permissions.
>Solution :
PROCESS is a global privilege. Your me user does not have this privilege as you have only granted all privileges on your me database, which does not grant global privileges.
You can grant the global privilege with
GRANT PROCESS TO `me`@`%`
This however will grant your me user access to information about all databases and all tables on the database server, which may not be what you want.
Instead of granting this privilege, you can thus use the --no-tablespaces option on your mysqldump command. While this will cause tablespaces to not be dumped, your user does not require the global PROCESS privilege anymore.
To quote the mysqldump documentation:
mysqldumprequires at least theSELECTprivilege for dumped tables,SHOW VIEWfor dumped views,TRIGGERfor dumped triggers,LOCK TABLESif the--single-transactionoption is not used, and (as of MySQL 8.0.21)PROCESSif the--no-tablespacesoption is not used. Certain options might require other privileges as noted in the option descriptions.