how do I stop automysqlbackup throwing LOCK TABLES error?

Question

How to stop automysqlbackup's cron.daily script -- as installed and configured from apt-get install automysqlbackup -- throwing the following error:

 mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to user 
'debian-sys-maint'@'localhost' for table 'cond_instances' when using LOCK TABLES

This first occurred (for me) after having upgraded to MySQL 5.5 as a consequence of having upgraded to Ubuntu 12.04 LTS.

Solution

This bug (which was fixed in automysqlbackup 2.6+debian.2-1) can be worked around by stopping automysqlbackup from dumping the performance_schema and information_schema databases. This can be done by editing /etc/default/automysqlbackup as follows:

Add an additional pipe to grep step to remove performance_schema and information_schema from the list of DBNAMES.

So, in the line that starts DBNAMES=, and after | grep -v ^mysql$ insert

| grep -v ^performance_schema$ | grep -v ^information_schema$

before | tr \\\r\\\n ,\ such that the final line should look like

DBNAMES=`mysql --defaults-file=/etc/mysql/debian.cnf --execute="SHOW DATABASES" | awk '{print $1}' | grep -v ^Database$ | grep -v ^mysql$ | grep -v ^performance_schema$ | grep -v ^information_schema$ | tr \\\r\\\n ,\ `

Thanks to Jérôme Drouet.

Alternatively, if you use the original automysqlbackup package from sourceforge and not the apt-package, you can add the performance_schema database to your excluded databases by modifying the line that contains CONFIG_db_exclude in your /etc/automysqlbackup/myserver.conf:

# List of DBNAMES to EXLUCDE if DBNAMES is empty, i.e. ().
CONFIG_db_exclude=( 'information_schema' 'performance_schema' )

Comments

[yop83]

Your answer works. Go ahead and select your answer as the correct one.

[Michael Thompson]

This is the fix on OS X (via Homebrew) as well.