☰

MySql information_schema 错误-锁定表

tag-icon • tag-icon mysql ubuntu-10.04 shell-scripting
MySql information_schema 错误-锁定表

我编写了一个快速 shell 脚本来备份 MySql 数据库，效果很好。我为所有 mysql 服务器工具使用相同的脚本，我已将其安装在 ubuntu 10.04 服务器上。

现在的问题是，当运行 shell 脚本时，我收到一条错误，如下所示

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

我已授予information_schema数据库权限，但上述错误仍然存​​在。

有谁能帮我解决这个问题吗？如果需要对 shell 脚本进行任何更改，请告诉我。

向我所有的朋友提供简单的 Shell 脚本

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

答案1

问题是它还试图转储架构，直到最近，这个请求才被自动忽略。您需要向脚本添加一个变量来忽略某些表。

这是我的脚本，它忽略 information_scheme，并将日期和 24 小时时间添加到文件名中。

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

答案2

这一页建议您可以通过添加--single-transaction命令或向用于 mysqldump 的用户mysqldump授予权限来修复它。LOCK TABLES

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

我不知道为什么在 mysql.* 上授予 LOCK TABLES 会解决在 information_schema.* 中锁定表的问题，但这就是他们所说的有效方法。

您说“我已授予 information_schema 数据库权限”。MySQL 权限比这稍微复杂一些。

您到底赋予了 root 用户什么权限？

你把它给了哪个 root 用户？MySQL 用户帐户实际上是“user@host”。root@localhost 可以拥有不同的密码和权限集[电子邮件保护]。

这个查询可能会有帮助：

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

我还应该提一下，拥有没有密码的 root 用户并不是一个好主意。

相关内容