Monday, April 23, 2012

mysqldump: Got error: 1044: Access denied for user ‘root’@'localhost’ to database ‘information_schema’ when using LOCK TABLES

I'm trying to make backups using the mysqldump command and getting the following error or warning:
mysqldump: Got error: 1044: Access denied for user 'root'@'localhost' to database 'information_schema' when using LOCK TABLES
How do I fix this problem?

You can pass the --single-transaction option to mysqldump command:
$ mysqldump --single-transaction -u user -p DBNAME > backup.sql
Another option is to grant LOCK TABLES to your user:
$ mysql -u root -p
And type:
mysql> GRANT SELECT,LOCK TABLES ON DBNAME.* TO 'username'@'localhost';

Sample Shell Script

#!/bin/bash
# Purpose: Backup mysql
# Author: Vivek Gite; under GNU GPL v2.0+
NOW=$(date +"%d-%m-%Y")
DEST="/.backup/mysql"
# set mysql login info
MUSER="root" # Username
MPASS='PASSWORD-HERE' # Password
MHOST="127.0.0.1" # Server Name
 
# guess binary names
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
GZIP="$(which gzip)"
 
[ ! -d "${DEST}" ] && mkdir -p "${DEST}"
# get all db names
DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')"
for db in $DBS
do
FILE=${DEST}/mysql-${db}.${NOW}-$(date +"%T").gz
# get around error
$MYSQLDUMP --single-transaction -u $MUSER -h $MHOST -p$MPASS $db | $GZIP -9 > $FILE
done


No comments:

Post a Comment