Fixing mysqldump error: access denied for … when using LOCK TABLES
April 5th, 2010
I use the tool mysqldump to backup my databases today I wanted to backup a database but the following error occured (The command backups the database to the gzipped mydb.sql.gz file):
$ mysqldump -uuser -ppassword -hlocalhost mydb | gzip –best -c -f > /root/backups/db/mydb.sql.gz
mysqldump: Got error: 1044: Access denied for user ‘user’@'localhost’ to database ‘database’ when using LOCK TABLES
mysqldump: Got error: 1044: Access denied for user ‘user’@'localhost’ to database ‘database’ when using LOCK TABLES
In order to fix this error you need to grant the LOCK TABLES permission to the user.
$ mysql -uuser -ppassword -hlocalhost mydb
mysql> GRANT SELECT, LOCK TABLES ON mydb.* TO ‘user’@'localhost’;
Done! You can now export your databases.
You might want to add this command to your cronjob in order to create daily/weekly/monthly backups. I’ve put the script “backup-db.sh” in my /etc/cron.daily folder which has the following content:
#!/bin/sh filename="db-mydb-"`eval date +%Y%m%d`".sql.gz" mysqldump -uuser -ppassword -hlocalhost mydb | gzip --best -c -f > /root/backups/db/mydb.sql.gz
I created a special backup user which has almost-root permissions for this. Saves using the real root user and a lot of headaches otherwise