Couldn't execute 'show events' on mysqldump

Question

While trying to backup all MySQL databases with mysqldump I get the following error:

Couldn't execute 'show events': Access denied for user 'user'@'localhost' to database 'performance_schema'

related: http://serverfault.com/questions/268566/cant-backup-the-mysql-table-with-mysqldump-select-lock-tabl-command-denied-for — rubo77, Nov 20 '13 at 11:49
@rubo77, Not really, [this error is due to a bug](http://serverfault.com/a/664829/87017), that is not. — Pacerier, Feb 04 '15 at 06:57

Adrian Ber · Accepted Answer · 2015-11-02T10:00:31.470

4

This is a virtual database so you will need to add the option --skip-events to make it work.

edited Nov 02 '15 at 10:00

answered Nov 06 '13 at 15:07

Adrian Ber

305
2
5
16

The flag ```--skip-events``` is not mentioned in the docs and it doesn't seem to work either. – Marcel Burkhard Nov 02 '15 at 08:02
This flag is included in the documentation https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_events. – Adrian Ber Nov 02 '15 at 09:59
You linked ```mysqlpump``` and ```--exclude-events``` – Marcel Burkhard Nov 02 '15 at 18:09
No! I actually linked `--events`. Read at the end of that paragraph (or better, entirely). – Adrian Ber Nov 02 '15 at 19:31
Alright but still, the question is about mysqldump not mysqlpump – Marcel Burkhard Nov 03 '15 at 07:18
1

It works for mysqldump too: http://dev.mysql.com/doc/refman/5.7/en/mysqldump-stored-programs.html. Both are used for backup. – Adrian Ber Nov 03 '15 at 08:49

Pacerier · Answer 2 · 2015-06-30T14:37:57.573

3

The other two answers will work, but their rationale and explanation are wrong.

The error you're getting is not due to the database being virtual, but due to a bug with the query show events:

SHOW EVENTS in the performance_schema database returned an access-denied error, rather than an empty result

The bug has been fixed in MySQL server 5.7.6. Simply update your server and you won't be seeing this error anymore.

In the meantime, if you are stuck with older versions of MySQL server, avoid calling show events by using the solutions provided in the other two answers.

edited Jun 30 '15 at 14:37

answered Feb 04 '15 at 06:56

Pacerier

521
16
35

This information is nowhere else to be found; at least not where I looked. Thanks a lot! – Master Azazel Jan 17 '17 at 08:29

score 3 · Answer 3 · answered Aug 05 '15 at 10:43

3

If you encountered this error with an ordinary database, you need to grant your user the EVENT permission.

answered Aug 05 '15 at 10:43

michaeljoseph

131
1

score 2 · Answer 4 · answered Apr 23 '20 at 20:15

2

GRANT EVENT ON `database`.* TO 'user'@'localhost';

answered Apr 23 '20 at 20:15

Mike

21
1

score 2 · Answer 5 · answered Nov 20 '13 at 11:48

'performance_schema' is a virtual database so you don't have to backup it
(see http://dev.mysql.com/doc/refman/5.5/en/performance-schema.html )

to skip it in your backup script use it like this:

#!/bin/sh
BAK=/var/backups/mysql/
DATE=$(date +%Y-%m-%d)
MYSQLPATH=/var/lib/mysql/
for i in /var/lib/mysql/*/; do
    if [ $i != "$MYSQLPATH"'performance_schema/' ] ; then 
        dbname=`basename "$i"` 
        mysqldump -u root "$dbname" | gzip > $BAK/"$dbname"-$DATE.sql.gz
    fi
done

# optionally delete old backups of databases that don't exist anymore
# find $BACKUP_DIR -atime +30 -exec rm {} \;

+1 I was actually using this in a similar script, but I haven't specify in my question. Anyway, good sharing. — Adrian Ber, Nov 25 '13 at 11:31

Couldn't execute 'show events' on mysqldump

5 Answers5