Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have written a quick shell script to take each backup of MySql database & that works good. I was using the same scripts for all mysql servers tool, I have installed it on ubuntu 10.04 server.

Now the problem is when run the shell script I am getting an error saying that as below

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

I have given the permission to the information_schema database but the above error still persists.

Can any one please help me to solve this issue. Let me know if any changes needs to be done in shell script.

Simple Shell Script to all my friends

#!/bin/bash

MyUser='root'       # Username
d=`date +%b_%d_%Y_%H_%M`
m="/var/backups/bugzilla_backup/Bugzilla_Dump_$d"
mkdir $m


#Get List of Databases
/usr/bin/mysql -u $MyUser -Bse 'show databases' > /usr/local/bin/dbname

# Backup listed Databases
while read l; do /usr/bin/mysqldump $l -u $MyUser > $m/$l-$d; /bin/bzip2 $m/$l-$d; sleep 15; done < /usr/local/bin/dbname
share|improve this question
add comment

2 Answers 2

The problem is that is is also trying to dump schema, this request was auto ignored until recently. You need to add a variable to the script to ignore certain tables.

Here is my script that ignores information_scheme, and also add the date and 24 hour time to the file names.

#!/bin/bash
# Shell script to backup MySql database 
# To backup Nysql databases file to /backup dir and later pick up by your 
# script. You can skip few databases from backup too.
# Last updated: Aug - 2005- and march 2013
# --------------------------------------------------------------------
# This is a free shell script under GNU GPL version 2.0 or above
# Copyright (C) 2004, 2005 nixCraft project
# -------------------------------------------------------------------------
# This script is part of nixCraft shell script collection (NSSC)
# Visit http://bash.cyberciti.biz/ for more information.
# -------------------------------------------------------------------------

MyUSER="YOURUSERNAME"     # USERNAME
MyPASS="YOURPASSSWORD FOR THE ABOVE USERNAME"       # PASSWORD 
MyHOST="localhost"          # Hostname

# Linux bin paths, change this if it can not be autodetected via which command
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
CHOWN="$(which chown)"
CHMOD="$(which chmod)"
GZIP="$(which gzip)"

# Backup Dest directory, change this if you have someother location
DEST="/root/dbbackup"

# Main directory where backup will be stored
MBD="$DEST/mysql"

# Get hostname
HOST="$(hostname)"

# Get date and time  in mm-dd-yyyy--H:M:S format
NOW="$(date +"%m-%d-%Y--%H%M")"

# File to store current backup file
FILE=""
# Store list of databases 
DBS=""

# DO NOT BACKUP these databases
IGGY="information_schema"

[ ! -d $MBD ] && mkdir -p $MBD || :

# Only root can access it!
$CHOWN 0.0 -R $DEST
$CHMOD 0600 $DEST

# Get all database list first
DBS="$($MYSQL -u $MyUSER -h $MyHOST -p$MyPASS -Bse 'show databases')"

for db in $DBS
do
    skipdb=-1
    if [ "$IGGY" != "" ];
        then
        for i in $IGGY
        do
            [ "$db" == "$i" ] && skipdb=1 || :
        done
    fi

    if [ "$skipdb" == "-1" ] ; then
        FILE="$MBD/$db.$HOST.$NOW.gz"
        # do all inone job in pipe,
        # connect to mysql using mysqldump for select mysql database
        # and pipe it out to gz file in backup dir :)
            $MYSQLDUMP -u $MyUSER -p$MyPASS $db | $GZIP -9 > $FILE
    fi
done
share|improve this answer
add comment

This page suggests you can fix it either by adding --single-transaction to your mysqldump command or by granting the LOCK TABLES privilege to the user you are using for mysqldump.

mysql> GRANT SELECT,LOCK TABLES ON mysql.* TO 'root'@'localhost'; 

I don't know why granting LOCK TABLES on mysql.* would fix a problem with locking tables in information_schema.* but that's what they say works.


You said "I have given the permission to the information_schema database". MySQL permissions are somewhat more complex than that.

Exactly what permission did you give the root user?

And which root user did you give it to? MySQL user accounts are actually "user@host". root@localhost can have a different password and set of privileges to root@127.0.0.1.

This query might help:

mysql> SELECT user, host , Lock_tables_priv FROM mysql.user WHERE user = 'root';
+------+--------------------------+------------------+
| user | host                     | Lock_tables_priv |
+------+--------------------------+------------------+
| root | localhost                | Y                |
| root | %                        | N                |
| root | 127.0.0.1                | Y                |
+------+--------------------------+------------------+

I should also mention that having a root user without a password is not a great idea.

share|improve this answer
    
Thanks for the quick reply. Even I check the link which you had given, In that he has mentioned DBNAME there i was giving information_schema.* as a dbname & the query which you had given to check its showing Lock_tables_priv (Y) for all host,root. Even after running query GRANT SELECT,LOCK TABLES ON mysql.* TO 'root'@'localhost'; its not working its still shows the error mysqldump: Got error: 1044: Access denied for user 'root'@'localhost' to database 'information_schema' when using LOCK TABLES –  Caterpillar Mar 16 '12 at 11:55
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.