How to solve the LOCK tables error of automysqlbackup

I recently installed automysqlbackup on a Slackware 14.1 server running Maria DB and was very pleased with it.

If you don't know what it is, automysqlbackup is a shell script that automates the ponderous task of planning a mysql backup every day (or week, etc...). It's very well done, despite its longish name... ;-)

Installing it is easy by following the installation instructions supplied with it (TL;DR: copy script, make it executable, create config dir, create config file, done), so I am not going over it right now. What was bothering me was the following error message:

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

Ooops... What went wrong?

Turns out the solution was very easy, just edit the following line into the configuration file /etc/automysqlbackup/myserver.conf as follows:

# List of DBNAMES to EXCLUDE if DBNAMES is empty, i.e. ().
# ----------------------------------------------------------
# PLEASE NOTE: the two following schemas must always be excluded!!
# MODIF by GIL ANDRE Mon Aug 18 17:40:47 CEST 2014
# ----------------------------------------------------------
CONFIG_db_exclude=( 'information_schema' 'performance_schema' )

In other words, look for CONFIG_db_exclude in the config file you created during the installation...

And... that's it really! Now, the backups of the databases run smooth, and I can relax, knowing my little databases are backed up correctly every day!

Completing this script with a function that will copy the db dumps to another machine is left as an exercice to the reader...

See Also: