MySql information_schema error - Lock Tables

Question

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

Answer 1

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

Answer 2

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 [email protected].

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.