Where, in automysqlbackup, do I add mysqldump parameters?

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 occurs after having upgraded to MySQL 5.5 as a side-effect of having upgraded to Ubuntu 12.04 LTS.

The correct solution (thanks @sr_ for pointer to the appropriate bug report) is to exclude the 'performance_schema' database from mysqldump.

So the question is: How to do this whilst respecting the Debian packages and configurations?

The problem occurs daily after having upgraded to MySQL 5.5.

Background: My aim is to stay with the Ubuntu Debian-installed packages and configurations in as standard a manner as possible. But actually, this is a bug with the way the packages are installed / upgraded, so it is necessary to update one of the supplied configuration files.

debian-sys-maint has fullest privileges anyway, so already has sufficient to lock each table before dumping it.

What's confusing is that /usr/share/doc/automysqlbackup/README.Debian says that

All parameters can be setup by editing the /etc/default/automysqlbackup file

/etc/default/automysqlbackup invokes mysqlbackup with settings in /etc/mysql/debian.cnf which says:

Automatically generated for Debian scripts. DO NOT TOUCH!.

Answer 1

This solution has fixed it for me.

According to this bug report it is necessary to stop automysqlbackup dumping the 'performance_schema' database, and this is done by editing /etc/default/automysqlbackup as follows:

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

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

| grep -v ^performance_schema$ 

before | tr \\\r\\\n ,\

Thanks to Jérôme Drouet

This solution applies to the Debian version of automysqlbackup as in Ubuntu 12.04. Evidently the syntax for excluding the "performance_schema" database varies per Linux distro -- see @stat below.

Answer 2

Edit: Forget everything I said.

See this bug report. Perhaps you find a way to "to exclude the 'performance_schema' database from mysqldump" as comment #2 indicates; otherwise the poster of the bug reports edited the automysqlbackup script to add the --skip-lock-tables parameter, but I suppose this can hurt you (as laid out below).

---

Short answer: You probably want to lock them.

I guess not locking them is not what you want since it could get you useless backups, i.e. when something changes the table while backing it up is in progress --- which is what the lock is there to prevent.

This fork's README as well as this block entry mention something about granting the user 'autobackup'@'localhost' access to lock tables.

That said, your error message indicates that the "user" in question actually is 'debian-sys-maint'@'localhost'. So I guess if you grant the needed priviledges to this user or change the credentials of the backup script in automysqlbackup.conf (which might be /etc/default/automysqlbackup in Debian, consult man automysqlbackup and /usr/share/doc/automysqlbackup/README.Debian), you should be fine.

---

Edit From this manual I quote what --lock-tables is supposed to do

--lock-tables, -l

For each dumped database, lock all tables to be dumped before dumping them.

It's linked from the only occurrence of --skip-lock-tables in the manual, so I take it to mean that --skip-lock-tables causes mysqldump to not lock tables when dumping them. This, as I argued above, is not advisable. I do not think it has anything to do with dumping lock tables but with locking tables.

I don't, however, understand why it doesn't work if, as you said, the user in question should be able to do it...

Answer 3

I was mistaken see solution 1 which does indeed work on debian

I was able to fix this "temporarily" by changing the line in /etc/default/automysqlbackup to the following:

CONFIG_db_exclude=( 'information_schema' 'performance_schema' )

I am not sure what effect this has but test restorations work for me and the backup completes without errors.

After the last update my solution which was on debian sid failed but solution one (which did not work for me before) does work. That is grepping the /etc/default/automysqlbackup and adding the line | grep -v ^performance_schema$. The above excludes that I used before seem to be ignored in the dump.