Dumping events requires SUPER on certain databases

[utdrmac]

If you remove -E, no errors regarding SUPER are produced.

mydumper should A) abort when such messages regarding access happen, B) update documentation to mention dumping events requires SUPER

$ mydumper --regex='(api\.|auth\.)' -o dumper -c -G -R -E -h 10.1.1.1 -u root -p 1234567 -v 3
** Message: 03:52:46.999: Connected to a MySQL server
** Message: 03:52:47.008: Started dump at: 2020-08-27 03:52:47

** Message: 03:52:47.009: Written master status
** Message: 03:52:47.019: Thread 1 connected using MySQL connection ID 5853092
** Message: 03:52:47.030: Thread 2 connected using MySQL connection ID 5853093
** Message: 03:52:47.040: Thread 3 connected using MySQL connection ID 5853094
** Message: 03:52:47.050: Thread 4 connected using MySQL connection ID 5853095

** (mydumper:18230): CRITICAL **: 03:52:47.093: Error: DB: mysql - Could not execute query: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

** (mydumper:18230): CRITICAL **: 03:52:47.118: Error: DB: sys - Could not execute query: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
...

root@percona-pxc8-1 [(none)]> SHOW EVENTS FROM mysql;
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation

root@percona-pxc8-1 [(none)]> SHOW EVENTS FROM sys;
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation

[davidducos]

Hi @utdrmac ,
I tried to reproduce your issue with this user:

mysql> show grants;
+-----------------------------------------------------------------------+
| Grants for mydumper@%                                                 |
+-----------------------------------------------------------------------+
| GRANT RELOAD ON *.* TO 'mydumper'@'%'                                 |
| GRANT SELECT, SHOW VIEW, EVENT, TRIGGER ON `test`.* TO 'mydumper'@'%' |
+-----------------------------------------------------------------------+

and when I execute the show events:

mysql> show events from sys;
ERROR 1044 (42000): Access denied for user 'mydumper'@'%' to database 'sys'

which doesn't mention anything about SUPER privileges.
Another thing that surprise me is that schema sys and mysql are not being filtered out.
I'm testing against Percona Server 5.7.32-35, what is the version of your db server?

[utdrmac]

@maxbube This was on a node of PXC8

[davidducos]

@utdrmac,

I found out what is the issue, but it is not related to the db version and I'm still not getting the SUPER privilege error (I'm getting an access denied).

Mydumper is getting the list of schemas with SHOW DATABASES, but with this privileges:

mysql> show grants;
+-----------------------------------------------------------------------+
| Grants for mydumper@%                                                 |
+-----------------------------------------------------------------------+
| GRANT RELOAD ON *.* TO `mydumper`@`%`                                 |
| GRANT SELECT, SHOW VIEW, EVENT, TRIGGER ON `test`.* TO `mydumper`@`%` |
| GRANT SELECT ON `test1`.* TO `mydumper`@`%`                           |
+-----------------------------------------------------------------------+
3 rows in set (0.01 sec)

you will get this list:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
| test1              |
+--------------------+

Then, when it checks if they have events it does get a similar output to:

mysql> show events from `test`;
Empty set (0.00 sec)

mysql> show events from `test1`;
ERROR 1044 (42000): Access denied for user 'mydumper'@'%' to database 'test1'

which is what we are seeing in the log.

I'm thinking that some possible solutions is that -B could receive a list of databases which avoids the execution of SHOW DATABASES.

[davidducos]

This is related to #454

[davidducos]

We workaround this kind of scenarios. Now, you can use -B to filter the databases and use regex just to filter out tables.