Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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'

share|improve this question
    
related: serverfault.com/questions/268566/… –  rubo77 Nov 20 '13 at 11:49
    
@rubo77, Not really, this error is due to a bug, that is not. –  Pacerier Feb 4 at 6:57

3 Answers 3

up vote 4 down vote accepted

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

share|improve this answer

'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 {} \;
share|improve this answer
    
+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

It's a bug (#73082) with the MySQL server that has been fixed on MySQL 5.7.6:

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

It doesn't matter which client you use, because it's a server bug:

mysql> use performance_schema
Database changed
mysql> show grants /* will show grants for current session.
   /*>                as can be seen we have "All Privileges". */;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show events;
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'performance_schema'
mysql>

Upgrade your MySQL server and you won't see the error message.

Alternatively, you can avoid it by removing the command show events. You can either remove it manually in the file, or use a script (see rubo's answer), or use the option --skip-events (see Adrian's answer).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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