Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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


MyUser='root'       # Username
d=`date +%b_%d_%Y_%H_%M`
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

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.

# 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 for more information.
# -------------------------------------------------------------------------

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

# Main directory where backup will be stored

# Get 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
# Store list of databases 

# DO NOT BACKUP these databases

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

# Only root can access it!

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

for db in $DBS
    if [ "$IGGY" != "" ];
        for i in $IGGY
            [ "$db" == "$i" ] && skipdb=1 || :

    if [ "$skipdb" == "-1" ] ; then
        # 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
share|improve this answer

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@

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 |                | 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

Your Answer


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.