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


How to fix this problem ?

$ mysqldump –single-transaction -u user -p DBNAME > backup.sql

$ mysql -u root -p

mysql> GRANT SELECT,LOCK TABLES ON DBNAME.* TO ‘username’@’localhost’;

#!/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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

shisdew

Listens until think alike

moses.spaceku@yahoo.com / voip ipbx

Hosted PBX, IP-PBX SOHO/ CALL CENTER, VOICE GATEWAY, VOICE CARD, COST EFECTIVE SOLUTIONS (LCR), GSM/CDMA GATEWAY

%d bloggers like this: